mysql HA数据不同步，在线处理方案

mysql重新HA
1、两库 stop slave;
2、主库锁表 flush tables with read lock; 锁定为只读状态
3、主库备份：
导出所有数据库
mysqldump -uroot -p2D0snAY6GFCA6Pw2 --all-databases>all100.sql
导出db1、db2两个数据库的所有数据
mysqldump -uroot -p2D0snAY6GFCA6Pw2 --set-gtid-purged=OFF --databases ambari hive ranger --triggers --routines --events > dpall.sql
4、导入备库：
导入：source /home/test/database.sql
5、备库同步主库
查看@@@209.24.100 mysql master 状态
mysql> show master status;
±-----------------±---------±-------------------±--------------------------------------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------------±--------------------------------------------±------------------+
| mysql-bin.000002 | 3087 | ambari,hive,ranger | mysql,information_schema,performance_schema | |
±-----------------±---------±-------------------±--------------------------------------------±------------------+
按照@@@209.24.100 master的状态设置 slave @@@209.24.101 mysql–
change master to
master_host=‘@@@209.24.100’,
master_user=‘replication’,
master_password=‘password’,
master_log_file=‘mysql-bin.000092’,
master_log_pos=924645737; #对端状态显示的值
start slave;
6、备库锁表 flush tables with read lock; 锁定为只读状态
7、keepalived切换vip
8、主库解锁：unlock tables; 同步备库
查看@@@209.24.101 mysql master 状态(MySQL服务器二进制文件名与位置)
mysql> show master status;
±-----------------±---------±-------------------±--------------------------------------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------------±--------------------------------------------±------------------+
| mysql-bin.000002 | 3087 | ambari,hive,ranger | mysql,information_schema,performance_schema | |
±-----------------±---------±-------------------±--------------------------------------------±------------------+
slave上指定master同步信息(告知二进制文件名与位置)
—按照@@@209.24.101 master的状态设置 slave @@@209.24.100 mysql–
change master to
master_host=‘@@@209.24.101’,
master_user=‘replication’,
master_password=‘password’,
master_log_file=‘mysql-bin.000092’,
master_log_pos=162205334; #对端状态显示的值
start slave;
9、查看同步情况
SHOW SLAVE STATUS\G
10、备库解锁 unlock tables;