背景与环境
-
场景描述:生产环境MySQL主从架构中,从库同步中断,需要使用物理备份快速重建从库。
-
环境参数:
-
数据量:主库物理文件约 300G。
-
磁盘限额:从库
/mydata仅有 532G,看似足够,实则可用空间极度紧绷。 -
技术栈:CentOS + MySQL 5.7 + Percona XtraBackup。
-
第一阶段:环境准备
-
确认主库配置
确保主库的
my.cnf中gtid_mode = ON且enforce_gtid_consistency = ON。以及配置里server_id与从库的server_id不同。 -
重置主库创建同步账号及密码
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 更灵活的对齐方式。
-
持续监控:持续监控主从库的磁盘空间状态,及时扩容磁盘空间。