18.docker mysql replication主从复制不一致全量恢复

需求：恢复replication主从，数据保持一致

 

1.对主库进行锁表，防止数据写入（在活跃度低的时候做）

mysql> flush tables with read lock;

 

2.在主库进行数据库备份

docker exec -it mysql_zpc_master mysqldump -uhuichao -pOoGP62irDG7uQnF1 --all-databases > /home/workspace/devopts/mysql/replica/allDatabasesBackUp.sql

 

3.删除.sql文件中第一行的警告：mysqldump: [Warning] Using a password on the command line interface can be insecure.

该警告是mysql5.6之后加入的，在命令行输入密码不安全，会写入到.sql备份文件第一行且不会被注释掉故执行.sql恢复时会报错，有两种方式解决：

1）.vim my.cnf

添加： host=localhost

user=user

password=password

2）.删除或注释掉该警告

 

sed -i '1d' /home/workspace/devopts/mysql/replica/allDatabasesBackUp.sql

这里使用的是删除第一行的警告

 

4.发送主库备份到子节点

scp /home/workspace/devopts/mysql/replica/allDatabasesBackUp root@cluster2.91qpzs.com:home/workspace/devopts/mysql/replica/

 

5.关闭子节点的主从

stop slave;

 

6.在子节点恢复数据

docker exec -i mysql_zpc_slave mysql -uhuichao -pOoGP62irDG7uQnF1 < /home/workspace/devopts/mysql/replica/allDatabasesBackUp.sql

 

7.更新/设置同步进度点

查看master进度点(File:mysql-bin.000009;Position:117304516)：show master status;

 

更新slave：

STOP SLAVE;

change master to

master_host="cluster1.91qpzs.com",

master_user="huichao",

master_password="OoGP62irDG7uQnF1",

master_log_file="mysql-bin.000009",

master_log_pos=117304516;

START slave;

 

8.取消主库的锁定

mysql> UNLOCK TABLES;

 

9.查看主从状态

SHOW MASTER STATUS;

SHOW SLAVE STATUS;