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