【mysql】【17】主主

1.机器

主机a
192.168.1.25
主机b
192.168.1.26

2.主机192.168.1.25上

server-id=1
log-bin=mysql-bin
auto_increment_offset=1
auto_increment_increment=1
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=test
replicate-ignore-db=mysql,information_schema
slave-skip-errors=all
log-slave-updates
sync_binlog=1

主机上执行
创建复制用户
grant replication slave on *.* to 'copy'@'192.168.1.26' IDENTIFIED BY 'xxxxx';
查看是否可以登录
mysql -h192.168.1.26 -ucopy -p	


查看数据库指针位置
show master status\G;
mysql-bin.000001
107

上锁
flush tables with read lock;
mysql -uroot -pxxx	
停止复制
stop slave;
设置复制位置
change master to master_host='192.168.1.26' ,master_user='copy' ,master_password='xxxxx',master_log_file='mysql-bin.000001',master_log_pos=107;
开始复制
start slave;
查看复制是否成功
show slave status\G;
解锁
unlock tables;

3.192.168.1.26机上配置

server-id=2
log-bin=mysql-bin
auto_increment_offset=1
auto_increment_increment=2
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=test
replicate-ignore-db=mysql,information_schema
slave-skip-errors=all
log-slave-updates
sync_binlog=1

从机上执行
创建复制用户
grant replication slave on *.* to 'copy'@'192.168.1.25' IDENTIFIED BY 'xxxxx';
查看是否可以登录
mysql -h192.168.1.25 -ucopy -pxxx	
show master status\G;
mysql-bin.000001
107

上锁
flush tables with read lock;
查看数据库指针位置

停止复制
stop slave;
设置复制位置
change master to master_host='192.168.1.25' ,master_user='copy' ,master_password='xxxxxx',master_log_file='mysql-bin.000001',master_log_pos=107;
开始复制
start slave;
查看复制是否成功
show slave status\G;
解锁
unlock tables;