在线重建MySQL主从同步

发表于 阅读次数： 评论数： 本文字数： 652 阅读时长 ≈ 2 分钟

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

现象

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

1
2
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_file 与 master_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