MySQL 常见数据拆分办法

常用拆库思路概述

面对需要将部分数据从原库迁移至新库的需求，常见的解决思路主要包括以下三类：

1. 逻辑备份还原法：使用 mysqldump 将指定表结构及数据导出为 SQL 文件，随后在目标库执行 source 导入。该方法兼容性强，但大数据量下效率较低。
2. 物理备份恢复法：利用 xtrabackup 工具对特定表进行备份，并在新实例上进行恢复。此方法适合大规模数据迁移，能保留索引等物理属性，速度较快。
3. 表空间转移法：利用 MySQL 5.6.6 及以上版本支持的 Transportable Tablespace 特性，直接交换 .ibd 文件。适用于同版本或兼容版本间的小规模表迁移，速度最快且无需停止服务过久。

第一部分：使用 Transport TableSpace 迁移单表

适用条件：MySQL 5.6.6 或更高版本；源表和目标表必须具有相同的表结构；InnoDB 引擎表。

假设我们需要将表 tab_test1 从数据库 db_A 移动/复制到 db_B，具体操作步骤如下：

第1步：获取源表结构

目的：确保目标库中的表结构与源表完全一致。

操作：

USE db_A;
SHOW CREATE TABLE tab_test1;

注意：记录输出的建表语句，用于后续创建空表。

第2步：在目标库创建空表并废弃表空间

目的：建立目标表的占位符，并释放原有的 .ibd 文件句柄，以便替换。

操作：

USE db_B;
-- 使用第一步获取的语句创建表
CREATE TABLE tab_test1 (...); -- 此处应填入完整的建表语句

-- 废弃当前表空间（此时只保留 .frm 元数据文件）
ALTER TABLE tab_test1 DISCARD TABLESPACE;

第3步：锁定源表以确保数据一致性

目的：将内存中的数据刷新到磁盘，并锁定表以防止写入，生成 .cfg 配置文件。

操作：

USE db_A;
FLUSH TABLES tab_test1 FOR EXPORT;

完成判断：执行后，源表进入只读状态，且在数据目录下会生成一个名为 tab_test1.cfg 的文件，该文件包含了表的元数据信息。

第4步：拷贝物理文件

目的：将最新的表数据和元数据文件传输至目标库路径。

操作：

• 找到 db_A 目录下的 tab_test1.ibd 和 tab_test1.cfg 文件。
• 将这两个文件拷贝到 db_B 所在的数据目录中。

第5步：解锁源表

目的：恢复正常业务访问。

操作：

USE db_A;
UNLOCK TABLES;

第6步：导入表空间至目标库

目的：让 MySQL 识别新的 .ibd 文件并构建索引。

操作：

USE db_B;
ALTER TABLE tab_test1 IMPORT TABLESPACE;

完成判断：执行成功后，db_B.tab_test1 即可正常查询数据。

第二部分：使用 XtraBackup 导出部分表迁移

当需要迁移大量表或创建全新实例时，Percona XtraBackup 是更高效的选择。其核心流程包括：指定表格备份 -> 应用日志导出 -> 在新实例恢复。

第1步：备份指定表

准备：准备一个文本文件（如 /mnt/back_tables.txt），每行填写要备份的库名.表名（格式：database.table）。

操作：

nohup perl /path/to/innobackupex \
--ibbackup=/path/to/xtrabackup_55 \
--user=root \
--password=your_password \
--defaults-file=/etc/my.cnf \
--slave-info \
--tables-file=/mnt/back_tables.txt \
/mnt/backup_dir &

注意：确保路径正确，并根据实际安装的版本调整二进制文件路径。

第2步：应用日志并准备导出数据

目的：回放红志使数据处于一致状态，并使用 --export 参数生成可供其他 MySQL 实例导入的元数据文件（.cfg, .exp, .ibd）。

操作：

nohup perl /path/to/innobackupex \
--ibbackup=/path/to/xtrabackup_55 \
--export \
--apply-log \
--defaults-file=/home/node-1-1/backup-my.cnf \
/path/to/backup_dir > /tmp/r.log 2>&1 &

第3步：在新实例中配置并启动

目的：接收备份数据，可选择性配置复制关系以实现增量同步或主从架构。

操作：

1. 修改新实例的 my.cnf。
2. 如需只复制部分表，可配置：

   replicate-wild-do-table=db_A.%
   replicate-wild-ignore-table=mysql.%

   或者使用 --tables 参数启动 mysqld。
3. 使用 mysqlbinlog 或 mysqld 的相关恢复命令加载数据。

第三部分：使用 mysqldump 逻辑迁移

mysqldump 是最通用且易于理解的方案，生成的是可读的 SQL 文本。

关键参数说明

在执行 mysqldump 时，务必关注 --single-transaction 参数。

• --single-transaction：对于 InnoDB 表，该参数会在备份开始时启动一个事务，保证获取到的数据是一致快照，而无需锁表。这对于生产环境至关重要，可实现热备。

基本用法

mysqldump -u root -p --single-transaction database_name table_name > backup.sql

随后在新服务器上执行：

mysql -u root -p database_name < backup.sql

方案选择建议

在实际工作中，选择何种拆库手段需综合考虑以下因素：

• 数据量大小：小数据量可用 mysqldump；中等数据量推荐使用 Transport TableSpace；大数据量首选 XtraBackup。
• 停机时间要求：若要求零停机或极短停机，Transport TableSpace 和带有 --single-transaction 的 mysqldump 较为友好；XtraBackup 虽快但仍需短暂 DDL 操作。
• 服务器资源：XtraBackup 依赖较多 CPU 和 IO 资源进行处理；Transport TableSpace 主要依赖网络 IO（拷贝文件）。
• 版本兼容性：Transport TableSpace 严格限制 MySQL 大版本一致，跨版本迁移需谨慎测试或使用 XtraBackup。

以上就是 MySQL 常见数据拆分办法的详细内容，更多关于 MySQL 拆库、迁移优化的资料请关注本站其它相关文章！