Mysql双服互备配置实践记录

A库：192.168.1.10B库：192.168.1.11

A库建用户

grant replication slave on *.* to 'repl_user'@'192.168.1.11' identified by 'backupbpassword';flush privileges;

B库建用户

grant replication slave on *.* to 'repl_user'@'192.168.1.10' identified by 'backupbpassword';flush privileges;

A库my.cnf配置(添加以下内容)：

[mysqld]server-id = 1datadir = /usr/local/mysql/data/log_bin = mysql-bin.logrelay_log = mysql-relay.loglog-slave-updates = ONsync_binlog=1auto-increment-increment=2auto-increment-offset=1

B库my.cnf配置(添加以下内容)：

[mysqld]server-id = 2datadir = /usr/local/mysql/data/log_bin = mysql-bin.logrelay_log = mysql-relay.loglog-slave-updates = ONsync_binlog=1auto-increment-increment=2auto-increment-offset=2

两个mysql重启

A库、B库查看master状态:

show master status;

A库根据B库查询的master状态，设置master_log_file为File，master_log_pos为Position:

change master tomaster_host='192.168.1.11',master_user='repl_user',master_password='backupbpassword',master_log_file='mysql-bin.000002',master_log_pos=100;start slave;show slave status\G

B库根据A库查询的master状态，设置master_log_file为File，master_log_pos为Position:

change master tomaster_host='192.168.1.10',master_user='repl_user',master_password='backupbpassword',master_log_file='mysql-bin.000002',master_log_pos=100;start slave;show slave status\G

测试插入，更新是否正确。