【MySQL】关于恢复MySQL主主数据的同步问题 - Go语言中文社区

【MySQL】关于恢复MySQL主主数据的同步问题

一、实验背景和环境

MySQL + keepalived主主(互为主从)架构

操作系统：CentOS7.5 Minimal

mysql01 192.168.1.104 数据库目录/opt/data/mysql

mysql02 192.168.1.105 数据库目录/opt/data/mysql

vip 192.168.1.110

现在mysql02去同步mysql01失败, 数据相差几十上百G，vip一旦漂移到mysql02上，会导致整个业务不可用。

怎样可以快速恢复两个数据库的数据同步，使得vip漂移到任何一台数据库，业务都正常运行？

二、实验预期

现在需要实现：

1.在mysql01 mysql02数据库上执行 show slave statusG

Slave_ IO_ Running: Ye

Slave_ SQL_ Running: Yes

2.数据文件大小同步

三、实验操作

从实验背景可知，mysql01和mysql02两个数据刚安装完成时，互为主从，数据同步正常，运行过程中某些原因导致mysql02去同步mysql01故障，而对外提供服务的vip一直在mysql上，数据一直写入在mysql01上，最终导致发现时，mysql02数据跟mysql01上的数据相差巨大，需要手工同步，同时也警示我们要重视数据同步监控报警的重要性！

从上可知，对mysql02而言，其数据已经没有意义了，可以直接删除，现在需要的直接将mysql01的数据拷贝给mysql02，实现主从同步。

大方向没有问题，关键在于细节处理，mysql01 mysql02 都开启了二进制binlog 中继日志 relaylog 和慢日志slowlog ，我们拷贝mysql01的系统数据和业务数据到mysql02，而不需要mysql01的二进制binlog 中继日志 relaylog 和慢日志slowlog。

1. 暂停业务，找一台空闲服务器，将mysql01、 mysql02的所有数据备份

2. 分别在mysql01 mysql02 停止mysql服务

# systemctl stop mysqld

3. 在mysql02 ，删除mysql所有其他数据，只备份/opt/data/mysql/auto.cnf 文件

# cp /opt/data/mysql/auto.cnf /home/auto.cnf.bak

# rm -rf /opt/data/mysql/*

4. 删除mysql01的二进制binlog 中继日志 relaylog 和慢日志slowlog

# cd /opt/data/mysql

# rm -rf 主机名-bin.* 主机名-relay-bin.* 主机名-slow*

5. 拷贝mysql01的数据文件到mysql02数据目录

# scp -r /opt/data/mysql/* root@192.168.1.105:/opt/data/mysql/

6. 在mysql02上还原auto.cnf文件，更改拷贝过来的文件属主属组

# cp /home/auto.cnf.bak /opt/data/mysql/auto.cnf

# chown -R mysql:mysql /opt/data/mysql/

7. 在mysql01 mysql02 上启动 mysql和keepalived

# systemctl start mysqld

# systemctl restart keepalived

# systemctl status mysqld keepalived

四、数据同步验证

登录mysql01

# mysql -u root -p"MySQL@123"

> show slave statusG

mysql01 同步mysql02正常

登录mysql02

# mysql -u root -p"MySQL@123"

> show slave statusG

我们发现了报错：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

关键字眼: master and slave have equal MySQL server ids

我们在数据库上做如下操作,清楚一切同步信息：

> stop slave;

> reset slave;

> reset master;

> change master to master_host='192.168.1.104',master_port=3306,master_user='repl',master_password='MySQL@123',master_auto_position = 1;

> start slave;

> show slave statusG

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

这次报错不一样了，谷歌了一下，是典型的 MySQL开启GTID主从同步出现1236错误。

一般两种情况会出现以上现象：

1.在主库上手动执行清除二进制日志文件

2.主库重启，重新同步时

解决方法：

1.在mysql01上执行以下命令，查询gtid_purged，并记录其值

# mysql -u root -p"MySQL@123"

> show global variables like '%gtid%'G

在mysql02执行以下命令，查询gtid_executed,并做记录

# mysql -u root -p"MySQL@123"

> show global variables like '%gtid%'G

此处mysql02上的gtid_executed值为空。

3. 在mysql02上执行以下命令停止同步线程及重置同步相关信息

# mysql -u root -p"MySQL@123"

> stop slave;

> reset slave;

> reset master;

设置mysql的gtid_purged值，该值有两个来源，一是在主库上查询的gtid_purged，二是在从库上查询的已经执行过的gtid_executed值。

注意：一定记得加上从库上已经执行过的gtid_executed值，若只设置了主库上的gtid_purged，此时从库会重新拉取主库上所有的二进制日志文件，同步过程会出现其他错误，导致同步无法进行，但此处我们从库的gtid_executed值为空，没什么可加，就不用加了！

> set @@global.gtid_purged='d19dcd86-39cf-11e9-a540-000c29d0d7ee:1-1042789';

>change master to master_host='192.168.1.104',master_port=3306,master_user='repl',master_password='MySQL@123',master_auto_position = 1;

> start slave;

> show slave statusG

现在,mysql02同步mysql01正常了，实现了 mysql01 mysql02 的主主同步，剩下的事就是启动服务，看对业务是否有影响。

五、回退方案

如果同步失败，我们至少保留了同步前，mysql01 的全部数据，停服务器和数据库，回填数据，启动数据库和服务。

六、参考

用shell脚本批量生成测试用SQL语句

https://my.oschina.net/zhuguowei/blog/474572

Tools for Generating Mock Data?

https://stackoverflow.com/questions/591892/tools-for-generating-mock-data

MySQL Sharding 批量执行工具

https://github.com/shilion/mysqlbatch

解决mysql开启GTID主从同步出现1236错误问题

https://blog.51cto.com/hnr520/1883282

版权声明：本文来源简书，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://www.jianshu.com/p/25d253ad709b
站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。

发表于 2020-01-12 13:27:19
阅读 ( 890 )
分类：数据库