测试环境: 192.168.1.23:3307(master) 192.168.1.24:3307(slave)
1、主库创建授权用户
> grant replication slave,replication client on *.* to 'rep'@192.168.1.%' identified by 'D1L8Sf';
> flush privileges;
2、从库指向主库,并开启同步
> change master to master_host='192.168.1.23', master_port=3307,master_user='rep',master_password='D1L8Sf', master_auto_position=1;
> start slave;
之后执行show slave status查看slave状态是否正常
如果主从同步有问题
先导出主库数据
# mysqldump --quick --events --routines --triggers --single-transaction --default-character-set=utf8 -A -S /data/mysql/3307/mysql.sock > 3307all.sql
在备库中导入
>reset master #先执行,不然导入库会报错
# mysql -uroot -p123456 -S /data/mysql/3307/mysql.sock --default-character-set=utf8 < 3307all.sql
然后在备库执行同步操作
>change master to master_host='192.168.1.23',master_port=3307,master_user='rep',master_password='D1L8Sf',master_auto_position=1;
>start slave;
如果查看slave status报错1236,则需要stop slave –> reset slave –> start slave,重置下slave状态