percona mysql 主从_percona主从配置

Percona 为MySQL数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

--来自百度百科

安装Percona

一.安装Percona前的准备工作

1.安装Percona的yum源yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpmRetrieving http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Preparing...                ########################################### [100%]

1:percona-release        ########################################### [100%]

2.检测Percona的yum源yum list | grep perconapercona-release.noarch   0.1-3             @/percona-release-0.1-3.noarch

...

Percona-Server-client-56.x86_64            5.6.15-rel63.0.519.rhel6      percona

Percona-Server-devel-56.x86_64             5.6.15-rel63.0.519.rhel6      percona

Percona-Server-server-56.x86_64            5.6.15-rel63.0.519.rhel6      percona

Percona-Server-shared-56.x86_64            5.6.15-rel63.0.519.rhel6      percona

Percona-Server-test-56.x86_64              5.6.15-rel63.0.519.rhel6      percona

...

percona-xtrabackup.x86_64                  2.2.4-5004.el6                percona

二.安装Perconayum install Percona-Server-client-56.x86_64 Percona-Server-server-56.x86_64 Percona-Server-shared-56.x86_64 Percona-Server-devel-56.x86_64 Percona-Server-shared-compat-51.x86_64 Percona-Server-shared-51

配置Percona主从

Percona主从的配置与mysql主从的配置基本一样。

一.Percona_master的配置：

1.在master上创建复制用户

进入Percona_master环境，执行以下mysql语句：

mysql> create user repl_user;

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'backup'@'192.168.1.145'identified by '123456';

Query OK, 0 rows affected (0.00 sec)

IP地址是从服务器的地址，backup是master给slave的用户名。

2.  在master上开启二进制日志cp /usr/share/percona-server/my-default.cnf /etc/my.cnfvim /etc/my.cnf

#增加如下几行

log-bin=master-bin

log-bin-index=master-bin.index

server-id=1

二.Percona_slave的配置：

1.配置slave服务器vim /etc/my.cnf

[mysqld]

server-id=2

relay-log=slave-relay-bin

relay-log-index=slave-relay-bin.index

这三行的顺序必须如上。

2.进入slave的mysql控制台进行主从设置

mysql> change master to

-> master_host = '192.168.1.144',

-> master_port = 3306,

-> master_user = 'backup',

-> master_password = '123456';

Query OK, 0 rows affected (0.10 sec)

master_host是主服务器的IP地址。

3.开启从服务器

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

4.查看从服务器的状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.101.181

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 120

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 284

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 120

Relay_Log_Space: 457

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 57628ead-7b7d-11e4-ad72-525400eafa03

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)