Mysql双服互备配置实践记录

Mysql双服互备配置实践记录

A库：192.168.1.10
B库：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 = 1
datadir = /usr/local/mysql/data/
log_bin = mysql-bin.log
relay_log = mysql-relay.log
log-slave-updates = ON
sync_binlog=1
auto-increment-increment=2
auto-increment-offset=1

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

[mysqld]
server-id = 2
datadir = /usr/local/mysql/data/
log_bin = mysql-bin.log
relay_log = mysql-relay.log
log-slave-updates = ON
sync_binlog=1
auto-increment-increment=2
auto-increment-offset=2

两个mysql重启

A库、B库查看master状态:

show master status;

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

change master to
master_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 to
master_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

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