实验m-m mysql 配置

ezeke

1.grants
GRANT REPLICATION SLAVE,FILE ON *.* TO 'repl1'@'192.168.56.221' IDENTIFIED
BY '123456';
FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE,FILE ON *.* TO 'repl2'@'192.168.56.77' IDENTIFIED
BY '123456';
FLUSH PRIVILEGES;
2.
(56.77)
log-bin=mysql-bin #slave会基于此log-bin来做replication
server-id=1 #master的标示
binlog-do-db=tongchao
binlog-ignore-db=mysql
replicate-do-db=tongchao
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1

#user = mysql #未知
#log-bin=mysql-bin #在从服务器上启动日志记录，不是必须，但是官方建议
#server-id= 1 #服务器编号,唯一
#binlog-do-db=asteriskcdrdb #要同步的数据库
#binlog-ignore-db=mysql #不同步的数据?
#replicate-do-db=asteriskcdrdb #复制的数据库
#replicate-ignore-db=mysql #未知
#log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。
#slave-skip-errors=all #
#sync_binlog=1 #同步时钟
#auto_increment_increment=2 #
#auto_increment_offset=1 #自增涨增量

(56.221)
log-bin=mysql-bin
server-id= 2
binlog-do-db=tongchao
binlog-ignore-db=mysql
replicate-do-db=tongchao
replicate-ignore-db=mysql
log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
#sync_binlog=1
#auto_increment_increment=2
#auto_increment_offset=2

3.重启mysql
4.
进入MYSQL的SHELL。
flush tables with read lock
show master status
56.77
*************************** 1. row ***************************
File: mysql-bin.000012
Position: 106
Binlog_Do_DB: tongchao
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
56.221
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 98
Binlog_Do_DB: tongchao
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
5.在各自机器上执行CHANGE MASTER TO命令
56.77
change master to
master_host='192.168.56.221',
master_user='repl2',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=98;
start slave;
56.221
change master to
master_host='192.168.56.77',
master_user='repl1',
master_password='123456',
master_log_file='mysql-bin.000012',
master_log_pos=106;
start slave;
6、查看各自机器上的IO进程和 SLAVE进程是否都开启。
show processlist\G
7、释放掉各自的锁，然后进行插数据测试。
unlock tables;