mysql数据库互为主备失效，重新同步

背景介绍

发现互为主备数据库中，两台数据库内数据存在不一致情况。登录数据库服务器，查看数据库服务状态信息。

mysql> show slave status\G                                                
Slave_IO_Running: Yes
Slave_SQL_Running: No

发现主从存在问题，需保证两台服务器数据完全一致，所以重新做主备。
变量介绍：
数据库服务A（目前主数据库，数据正常）
数据库服务B（从数据库，数据同步异常）

操作步骤

数据库服务A

0.停止从同步服务B

mysql> stop slave;

1.进行锁表，防止数据写入

mysql> flush tables with read lock;

锁定为只读状态

2.数据备份

mysqldump -uroot -p -hlocalhost > mysql.bak.sql

3.查看点位状态

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

4.备份文件传输

scp mysql.bak.sql root@192.168.128.101:/tmp/

数据库服务B

5.停止从库状态

mysql> stop slave;

6.执行数据恢复

mysql> source /tmp/mysql.bak.sql

7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项

mysql> change master to master_host = '192.168.128.100', master_user = 'rsync',  master_port=3306, master_password='', master_log_file =  'mysqld-bin.000001', master_log_pos=3260;

8.打开从库同步状态

mysql> start slave;

9.查看状态

mysql> show slave status\G  

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

数据库服务A

10.查看服务B点位状态

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

11.服务A同步B

mysql> change master to master_host = '192.168.128.100', master_user = 'rsync',  master_port=3306, master_password='', master_log_file =  'mysqld-bin.000001', master_log_pos=3260;

12.打开从库同步状态

mysql> start slave;

13.查看状态

mysql> show slave status\G  

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

14.主库解除表锁定

UNLOCK TABLES;

https://blog.51cto.com/svenman/1363110s