1.备库执行/关闭主从/重置
SQL> stop slave;
SQL> reset slave ;
SQL> reset master;
1.主库执行
SQL> reset master;
2.主库使用mysqldump备份数据
#/usr/local/mysql/bin/mysqldump -uroot -p -S /data/mysql/mysql_3306/tmp/mysql_3306.sock --set-gtid-purged=ON --all-databases --source-data=2 --triggers --routines --events > all_new.sql
3.备份完成后传到备库进行数据恢复
SQL> Source all_new.sql
4.进入全备文件查看位置点
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=157;
5.从新CHANGE MASTER TO
2. 使用GTID模式,从库首先执行
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
CHANGE MASTER TO MASTER_HOST='10.21.100.31',MASTER_PORT=3306,MASTER_USER='repl', MASTER_PASSWORD='', MASTER_AUTO_POSITION=1;
1 使用binlog模式执行
SQL> CHANGE MASTER TO
MASTER_HOST='0.0.0.0',
MASTER_USER='repl',
MASTER_PASSWORD='0',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=157;
2 从新启动主从/检查状态
SQL> Start slave;
SQL> Show slave status\G