Alliot's blog

在线重建MySQL主从同步

  一套内部业务系统的主从同步库出现了同步异常,需要在不锁表的情况下,完成在线重建主从同步,本文简单记录一下操作过程。

现象

  监控报从库 IO 线程异常,进入从库后查看从库状态:

1
sql> show slave status\G

看到 Slave_SQL_Running 状态为 No,错误信息为如下类似:
Could not execute Update_rows event on table livedb.xxl_job_info; Unknown error 1032, Error_code
这种情况一般是从库与主库的这张表不一致导致的。采取在从库导入主库数据后重建主从同步来解决。

同步数据

  全新的主从同步请参考先前的文章: MySQL 主从同步配置 | Alliot’s blog
  主从同步开始前,需要将现有的主库数据导入到从库中,之后再启用主从同步线程。
  这里通过 mysqldump 来导出主库数据,在主库执行:

1
2
3
4
5
6
# 导出所有库
mysqldump -uroot -p --single-transaction --no-autocommit --master-data=2 -A > master.sql
# 仅导出 livedb 库,在前面的现象里可以看出,我们只有livedb这个库有同步错误,因此为了节省时间,仅导出该库
# 这里注意 mysqldump 后面带的几个参数不要漏。--master-data=2 配合 --single-transaction 可以避免锁表
mysqldump -uroot -p --single-transaction --no-autocommit --master-data=2 livedb > livedb.sql

将 sql 文件导入到从库中之前需要先停止同步,在从库中执行:

1
2
sql> reset master;
sql> STOP SLAVE;

之后导入前面的 sql 到从库中:

1
mysql -uroot -p livedb< livedb.sql

重建主从

  启动主从同步需要用到master_log_filemaster_log_pos 两个参数的值,这里我们可以直接通过前面的 sql 文件来得到:

1
less livedb.sql

可以看到如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- MySQL dump 10.13 Distrib 5.7.35, for Linux (x86_64)
--
-- Host: localhost Database: livedb
-- ------------------------------------------------------
-- Server version 5.7.35-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=319017483;
--

这样我们就得到了 MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=319017483;
之后就可以在从库重新启动主从同步了:

1
2
3
sql> reset slave all;
sql> change master to master_host='172.18.0.4', master_user='copy', master_password='123456', master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=319017483;
sql> start slave;

再查看从库的状态就已经正常了:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sql> show slave status\G
show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.4
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 319074481
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 57318
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: livedb

------ 本文结束 ------

本文标题:在线重建MySQL主从同步

文章作者:Alliot

发布时间:2021年11月18日 - 19:11

最后更新:2021年12月08日 - 00:12

原始链接:https://www.iots.vip/post/rebuild-mysql-master-slave-replication.html

许可协议: 署名-非商业性使用-相同方式共享 4.0 国际 转载请保留原文链接及作者。

若文章为您解决燃眉之急或是带来些许明朗,不妨打赏 Alliot 一杯香茗或是一杯咖啡