背景与环境

  • 场景描述:生产环境MySQL主从架构中,从库同步中断,需要使用物理备份快速重建从库。

  • 环境参数:

    • 数据量:主库物理文件约 300G。

    • 磁盘限额:从库/mydata 仅有 532G,看似足够,实则可用空间极度紧绷。

    • 技术栈:CentOS + MySQL 5.7 + Percona XtraBackup。

第一阶段:环境准备

  1. 确认主库配置

    确保主库的 my.cnf 中 gtid_mode = ON 且 enforce_gtid_consistency = ON。以及配置里 server_id与从库的 server_id不同。

  2. 重置主库创建同步账号及密码

ALTER USER 'repl'@'10.10.10.%' IDENTIFIED BY 'NewPassword123!';

第二阶段:数据迁移

对于 300G 数据,直接备份到本地会占用过多空间。本次使用 XtraBackup 的流式备份直接传给从库:

1. 清理从库目录(在从库执行)

systemctl stop mysqld
rm -rf /mydata/3306/data/*
rm -rf /mydata/3306/log/*

2. 主库流式备份并直接传给从库(在主库执行)

使用 xbstream 模式,不产生本地备份文件,直接通过网络传走:

innobackupex --user=root --password='密码' --stream=xbstream /tmp | ssh root@从库IP "xbstream -x -C /mydata/3306/data"

注:此操作会将 300G 数据通过网络推送,需确保内网带宽充足。

插曲: 数据推送出错,主要由于数据库表太多,超过了系统和 XtraBackup 默认的文件句柄限制(Open Files Limit)。

260416 18:02:36 >> log scanned up to (1173999731051)
InnoDB: Opened 3 undo tablespaces
InnoDB: 0 undo tablespaces made active
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 394 for bbp/hi_sys_fu_prop, old maximum was 3
260416 18:02:37 >> log scanned up to (1173999731051)
260416 18:02:38 >> log scanned up to (1173999731051)
InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'
InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
InnoDB: File ./dev_hrqc/c_qc_all_score_69319c3bd9b637b025cd3518.ibd: 'open' returned OS error 124. Cannot continue operation

解决: 1、调高当前 Shell 的限制,运行以下命令:

ulimit -n 65535

2、在命令中显式指定限制

innobackupex --user=root --password='密码' \
--open-files-limit=65535 \
--stream=xbstream /tmp | ssh root@10.10.10.203 "xbstream -x -C /mydata/3306/data"

传输完成

主库:
60416 20:00:54 Executing UNLOCK TABLES
260416 20:00:54 All tables unlocked
260416 20:00:54 [00] Streaming ib_buffer_pool to <STDOUT>
260416 20:00:54 [00]        ...done
260416 20:00:55 Backup created in directory '/tmp/'
MySQL binlog position: filename 'mysql-bin.001432', position '239748182', GTID of the last change 'bd3f7c33-03eb-11ee-9613-001a4a16018e:1-47660046'
260416 20:00:55 [00] Streaming <STDOUT>
260416 20:00:55 [00]        ...done
260416 20:00:55 [00] Streaming <STDOUT>
260416 20:00:55 [00]        ...done
xtrabackup: Transaction log of lsn (1173999731042) to (1173999731051) was copied.
260416 20:00:55 completed OK!
[root@172-21-47-195-new ~]# 

从库:

[root@localhost tmp]# du -sh /mydata/3306/data/
483G    /mydata/3306/data/
[root@localhost tmp]# 
[root@localhost tmp]# 
[root@localhost tmp]# df -h
文件系统                  容量  已用  可用 已用% 挂载点
/dev/vda1                  99G   31G   64G   33% /
devtmpfs                  7.7G     0  7.7G    0% /dev
tmpfs                     7.7G   24K  7.7G    1% /dev/shm
tmpfs                     7.7G  794M  7.0G   11% /run
tmpfs                     7.7G     0  7.7G    0% /sys/fs/cgroup
/dev/mapper/vg0-lv_rdbms   50G  5.9G   45G   12% /mysql
/dev/mapper/vg0-lv_data   532G  483G   50G   91% /mydata
tmpfs                     1.6G     0  1.6G    0% /run/user/0

注:

数据点位:1-47660046 后面需要用到。

主库实际物理数据空间约300G,但是同步到从库后,产生了483G的数据空间。

这很可能是因为主库的 dev_hrqc 库中存在大量的碎片(Fragmenation)。

在主库磁盘上,文件系统可能通过“稀疏文件”技术忽略了空洞,但在 xbstream 传输过程中,这些空洞被转换成了实际的数据块, 物理备份 300G 变成 475G 意味着碎片率极高。

针对InnoDB 碎片化 严重的问题。长远方案,在之后有计划的维护窗口,可以考虑对大表进行 OPTIMIZE TABLE 操作。不过这需要额外的临时磁盘空间(大约是原表的大小),所以在扩容磁盘之前千万不能操作。

第三阶段:从库数据准备

1. 应用日志(Prepare)

数据传输完成后,从库上的数据是不一致的,需要应用 redo log:

# 在从库执行
# 限制内存使用,防止内存和磁盘 IO 竞争
innobackupex --apply-log --use-memory=4G /mydata/3306/data

2. 权限修正

确保 MySQL 对数据和日志目录有完全控制权:

chown -R mysql:mysql /mydata/3306/data
chown -R mysql:mysql /mydata/3306/log
chmod -R 750 /mydata/3306/data

3. 启动从库

systemctl start mysqld

第四阶段:开启 GTID 同步

1. 在从库执行同步指令

登录从库 MySQL:

前置措施:鉴于现在只有 49G 可用空间,需要采取措施,防止日志撑爆磁盘。

在 MySQL 终端执行:

-- 1. 缩短 binlog 保留时间,从 7 天改为 2 天(立即生效,会自动触发旧日志清理)
SET GLOBAL expire_logs_days = 2;

-- 2. 确保从库在执行完 relay log 后立即自动删除,不留痕迹
SET GLOBAL relay_log_purge = ON;

正式开启同步

-- 彻底重置从库以前的残留信息
STOP SLAVE;
RESET SLAVE ALL;

-- 设置主库信息,建立新连接
CHANGE MASTER TO 
    MASTER_HOST='主库内网IP', 
    MASTER_USER='repl', 
    MASTER_PASSWORD='密码', 
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION = 1; -- 核心:让 GTID 自动寻找差异


-- 开启同步
START SLAVE;

2. 检查同步状态

SHOW SLAVE STATUS\G

看这两个关键项:

  • Slave_IO_Running: Yes

  • Slave_SQL_Running: Yes

插曲: 从库状态异常



mysql> START SLAVE;
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.195
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001432
          Read_Master_Log_Pos: 239748182
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.001432
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'bd3f7c33-03eb-11ee-9613-001a4a16018e:47653343' at master log mysql-bin.001432, end_log_pos 756. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 194
              Relay_Log_Space: 239748556
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'bd3f7c33-03eb-11ee-9613-001a4a16018e:47653343' at master log mysql-bin.001432, end_log_pos 756. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 175716270
                  Master_UUID: bd3f7c33-03eb-11ee-9613-001a4a16018e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 260416 20:26:44
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: bd3f7c33-03eb-11ee-9613-001a4a16018e:47653343-47660046
            Executed_Gtid_Set: bd3f7c33-03eb-11ee-9613-001a4a16018e:1-47653342
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> 

遇到 Error 1062 (Duplicate entry) ,这说明从库在尝试执行主库的某个 GTID 事务时,发现数据已经存在了。

这种情况通常是因为物理备份(XtraBackup)记录的位点和 GTID 集合在恢复后有微小的重叠。由于已经确认了物理备份是完整的,最快且最标准的解决方法是跳过这个报错的事务。

报错提示 bd3f7c33-03eb-11ee-9613-001a4a16018e:47653343 冲突了,我们手动告诉从库这个号已经执行过了:

停止从库 SQL 线程:

STOP SLAVE;

设置一个空事务(注意:UUID 和编号必须完全对应报错信息):

SET GTID_NEXT='bd3f7c33-03eb-11ee-9613-001a4a16018e:47653343';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';

重新启动从库:

START SLAVE;

再次执行 SHOW SLAVE STATUS\G:

发现报错从 ...:47653343 变成了 ...:47653344。这说明刚才跳过一个事务的操作是有效的,但这一段事务(从备份开始到结束那段时间产生的)在从库上都已经存在了。

既然通过 XtraBackup 物理恢复的,为了避免一个一个跳(可能有成千上万个),直接用大跨步的方式,把从库的 GTID 指针直接拉到主库备份完成时的那个点。

彻底对齐 GTID 的操作步骤

在主库完成备份时,日志明确输出了位点:1-47660046。我们将从库直接强制对齐到这里。 在从库 MySQL 中执行:

-- 1. 停止从库
STOP SLAVE;

-- 2. 重置所有 GTID 信息(这会清空 Executed_Gtid_Set)
RESET MASTER;

-- 3. 手动注入直到备份结束时的所有 GTID
-- 这一步相当于告诉从库:47660046 之前的所有事务你都“假装”执行过了
SET GLOBAL gtid_purged='bd3f7c33-03eb-11ee-9613-001a4a16018e:1-47660046';

-- 4. 重新启动从库
START SLAVE;

为什么这样做是安全的?

  • 物理备份的特性:XtraBackup 在备份完成时(completed OK 之前),会把当时数据库的状态完全封存。你恢复出来的 300G 数据,对应的就是主库 47660046 这个时刻的状态。

  • 避免冲突:刚才报错是因为从库试图去执行主库 47653344 的动作,但这个动作的结果其实已经在物理备份的数据文件里了。直接 Purge 到 47660046,能让从库从主库后续的 47660047 开始拉取真正“新鲜”的增量数据。

再次执行SHOW SLAVE STATUS\G ,显示 状态恢复正常


mysql> 
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> SET GLOBAL gtid_purged='bd3f7c33-03eb-11ee-9613-001a4a16018e:1-47660046';
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> 
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.195
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001432
          Read_Master_Log_Pos: 239748182
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 205308278
        Relay_Master_Log_File: mysql-bin.001432
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 205308105
              Relay_Log_Space: 239749558
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 73587
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 175716270
                  Master_UUID: bd3f7c33-03eb-11ee-9613-001a4a16018e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for dependent transaction to commit
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: bd3f7c33-03eb-11ee-9613-001a4a16018e:47653343-47660046
            Executed_Gtid_Set: bd3f7c33-03eb-11ee-9613-001a4a16018e:1-47660046
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> 

性能与空间防御

永久化配置(从库数据追平后执行)

等 Seconds_Behind_Master 变为 0 之后,把关键参数写入从库的 /etc/my.cnf,防止重启后失效:

[mysqld]

expire_logs_days = 2             # 限制本地 binlog 只保留 2 天
relay_log_space_limit = 30G      # 限制中继日志占用的磁盘总空间 
relay_log_purge = 1              # 确保及时自动清理已执行的中继日志

修改 /etc/my.cnf 中的参数必须重启 MySQL 服务才能生效。

1. 重启前的安全检查

  • 确认同步已追平:你现在的 Seconds_Behind_Master 是 0,这是最好的重启时机。

  • 清理 ibtmp1:如果磁盘空间是因为临时表空间文件(ibtmp1)变大而吃满的,重启会自动删除并重建它(默认初始很小),这能帮你瞬间腾出不少空间。

  • 刷脏页(缩短启动时间):为了让重启变快,可以先手动把内存中的脏数据刷入磁盘:

SET GLOBAL innodb_fast_shutdown = 1; -- 默认就是1,确保快速关闭

2 执行重启

systemctl restart mysqld

3. 重启后的必看项

重启完成后,登录数据库确认刚才改的参数是否生效:

-- 检查参数是否变为了你设置的值(例如 30G)
SHOW VARIABLES LIKE 'relay_log_space_limit';
SHOW VARIABLES LIKE 'expire_logs_days';

-- 重新开启同步。重要!
START SLAVE;

-- 确认同步是否自动恢复
SHOW SLAVE STATUS\G

经验总结

  • 物理备份不等于实际大小:一定要预留足够的 Buffer 应对碎片膨胀。

  • GTID 的优势:在故障恢复时,GTID 提供了比传统 Binlog Pos 更灵活的对齐方式。

  • 持续监控:持续监控主从库的磁盘空间状态,及时扩容磁盘空间。