背景:数据库主库进行日志表分割时,产生了一个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:/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),但要主从关系相对应