mysql数据库主从数据同步失败重新构建主从关系

逃离我推掉我的手 2022-01-22 20:19 338阅读 0赞

背景：数据库主库进行日志表分割时，产生了一个15G的数据表，因15G大事务堵塞SQL和IO线程，导致了数据库主从同步失败，现要恢复主从数据一致

主库地址：192.168.1.8  
从库地址：192.168.1.9  
数据库版本：5.7.25

解决办法：重新构建数据库主从，将主数据库的数据copy到从数据库，在从库上配置恢复  
注：需要安装xtarbackup，备份速度和恢复速度快

1，备份数据库  
创建/home/mysqlbak/mysql\_bak目录用来存放备份的数据

> xtrabackup --backup --slave-info --user=root --password=‘xxxxxxxx’ --parallel=4 --socket=/home/mysql/var/mysql.sock --no-timestamp --stream=xbstream | xbstream -x -C /home/mysqlbak/mysql\_bak

2，备份完毕后，对备份数据执行反编译命令

> xtrabackup --prepare --apply-log --target-dir=/home/mysqlbak/mysql\_bak

3，在mysql的data同级目录下创建mysql\_bak，将data目录下的所有文件移动到mysql\_bak下

> mv /home/mysql/data/\* /home/mysql/data\_bak/

4，将步骤2反编译后的主库数据同步到从库mysql的data目录下，用scp传也行，rsync主要可以看到具体传输进度

> rsync -avprP /home/mysqlbak/mysql\_bak/ [mysql@192.168.1.9][mysql_192.168.1.9]:/home/mysql/data/

5，在从库上执行备份并修改my.cnf配置文件，在mysqld配置项下加入skip\_slave\_start配置项，不要mysql启动时自动拉起复制进程(后边需要配置好后再启动)。

6，在从库上启动mysql，检查mysql目录的属性，确保是mysql用户，

> chown -R mysql. /home/mysql/

然后使用root用户运行启动命令

> service mysqld start

7，查看/home/mysql/data/xtrabackup\_slave\_info目录下xtrabackup\_binlog\_info文件内容，此文件记录了步骤1中备份完成时的事务点，下一步会使用，或者xtrabackup\_slave\_info内容也是一样，里边已经写好了需要配置的语句，步骤1中–slave-info参数生成。

> cat /home/mysql/data/xtrabackup\_slave\_info  
> SET GLOBAL  
> gtid\_purged=‘4f256816-497c-11e9-ab06-fa163eaeb12d:1-197,  
> ad72360b-497b-11e9-b3e6-fa163ed7da68:1-17271284:118159792,  
> c44e4998-9b61-11e5-82bf-141877341ec6:1-2247,  
> d393d2e3-9b61-11e5-82bf-141877342ba0:1-14563313’;

8，登陆从库192.168.1.9，配置已执行事务集合，不能登录错主机，一定是刚用备份恢复后启动的MySQL机器执行步骤8中得到的语句，告诉恢复的MySQL步骤7中的事务已经执行完毕，可能gtid\_purged不为空，需要先reset master后才可更改。

> SQL> reset master;  
> SQL> SET GLOBAL  
> gtid\_purged=‘4f256816-497c-11e9-ab06-fa163eaeb12d:1-197,  
> ad72360b-497b-11e9-b3e6-fa163ed7da68:1-17271284:118159792,  
> c44e4998-9b61-11e5-82bf-141877341ec6:1-2247,  
> d393d2e3-9b61-11e5-82bf-141877342ba0:1-14563313’;  
> SQL> show master status;

9，在主库192.168.1.8配置主从关系

> SQL> stop slave;  
> SQL> reset slave all;  
> SQL> CHANGE MASTER TO MASTER\_HOST=‘192.168.1.9’,  
> MASTER\_PORT=3306,  
> MASTER\_USER=‘repl’,  
> MASTER\_PASSWORD=‘123456’,  
> MASTER\_AUTO\_POSITION = 1 FOR CHANNEL ‘hahaha’;  
> SQL> start slave;  
> SQL> show slave status\\G;

10，在主库192.168.1.9配置主从关系

> SQL> stop slave;  
> SQL> reset slave all;  
> SQL> CHANGE MASTER TO MASTER\_HOST=‘192.168.1.8’,  
> MASTER\_PORT=3306,  
> MASTER\_USER=‘repl’,  
> MASTER\_PASSWORD=‘123456’,  
> MASTER\_AUTO\_POSITION = 1 FOR CHANNEL ‘hahaha’;  
> SQL> start slave;  
> SQL> show slave status\\G;

注：5.7的多源复制功能，在配置主从关系时，使用不同的channel对主从复制进行标识  
channel可以随便写(我用的hahaha)，但要主从关系相对应

[mysql_192.168.1.9]: mailto:mysql@192.168.1.9