实验m-m mysql 配置

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;