MySQL 下 200GB 大表备份，利用传输表空间解决停服发版表备份问题

作者：GreatSQL

2025-04-09
福建
本文字数：1418 字
阅读完需：约 5 分钟

MySQL 下 200GB 大表备份，利用传输表空间解决停服发版表备份问题

问题背景

在停服发版更新时，需对 200GB 大表（约 200 亿行数据）进行快速备份以预防操作失误。

因为曾经出现过有开发写的发版语句里，UPDATE语句的WHERE条件写错了，原本只需要更新几行数据，最终导致更新了半张表的数据。

MySQL 版本是 MySQL 8.0.X，为了预防这种情况，需要对某个重要的大表进行预先备份，以便可以及时回滚，及时恢复，及时回退，对于备份方法大概有下面几种:

这个场景的核心需求：停服更新的时间非常有限，比如 1 个小时之内要完成更新。

操作流程

前面两种都比较简单，通过导数据的方法来备份旧表，万一出现问题，可以使用导出来的数据进行快速恢复，第三种方法估计比较少人用，下面是具体操作方法

源表与备胎表结构

-- 源表（aa）CREATE TABLE aa (  id int(11) DEFAULT NULL,  sname VARCHAR(100)) ENGINE=InnoDB;
-- 备胎表（bb）CREATE TABLE bb (  id int(11) DEFAULT NULL,  sname VARCHAR(100)) ENGINE=InnoDB;
greatsql> INSERT INTO aa SELECT 1,"nihao";

复制代码

2、备胎表卸载表空间：

greatsql> ALTER TABLE bb DISCARD TABLESPACE;  -- 加锁并生成配置文件

复制代码

3、源表执行表空间导出：

greatsql> USE school;greatsql> FLUSH TABLES aa FOR EXPORT;

复制代码

4、拷贝表空间文件（ibd 和 cfg 文件），然后重新赋予权限，确保导入表空间时候不会出现问题

$ cd /data/mysql/mysql3306/data/school$ cp aa.ibd  bb.ibd  $ cp aa.cfg  bb.cfg$ chown -R mysql:mysql /data/mysql/mysql3306/data/*

复制代码

5、在相同数据库下，备胎表和源表都导入表空间

greatsql> USE school;greatsql> UNLOCK TABLES;  greatsql> ALTER TABLE bb IMPORT TABLESPACE;greatsql> ALTER TABLE aa IMPORT TABLESPACE;

复制代码

6、查询表数据，验证数据一致性

greatsql> USE school;greatsql> SELECT * FROM bb;greatsql> SELECT * FROM aa;

复制代码

查询表数据正常，没有任何问题

greatsql> SELECT * FROM aa;+------+-------+| id   | sname |+------+-------+|    1 | nihao |+------+-------+1 row in set (0.01 sec)
greatsql> SELECT * FROM bb;+------+-------+| id   | sname |+------+-------+|    1 | nihao |+------+-------+1 row in set (0.00 sec)

复制代码

查看表的数据文件，没什么问题

$ lltotal 228-rw-r----- 1 mysql mysql 114688 Mar  4 16:51 aa.ibd-rw-r----- 1 mysql mysql    781 Mar  4 16:52 bb.cfg-rw-r----- 1 mysql mysql 114688 Mar  4 16:52 bb.ibd

复制代码

7、发版更新与回滚

-- 发版操作（示例）greatsql> UPDATE aa SET sname = 'new_value' WHERE id > 1;

复制代码

8、如果发版有问题，直接交换表名，最快速度恢复整个表的数据

-- 回滚操作（交换表名）greatsql> ALTER TABLE aa RENAME TO aa_temp; greatsql> ALTER TABLE bb RENAME TO aa;

复制代码

总结

整个操作最重要的是第 4 步，操作系统级别的拷贝就完成了整个表的备份，相比于数据倒来倒去在速度上要快不少。另外，第 5 步的备胎表也可以不用导入，只有当发现发版出现问题时候，再导入也可以。

这种方法的关键优势如下

直接拷贝 .ibd 文件，无需逐行复制数据。
零锁表时间：FLUSH TABLES tablename FOR EXPORT 仅短暂加锁（秒级）。
快速恢复：通过表名交换实现秒级回滚。

特别适合于这几种场景：无主从架构的单实例环境、大表快速备份、停服时间敏感。

当然，如果有主从架构的话，则更加推荐使用第四种方法，在操作上也更加可控，短时间也能保证能够完成。

发布于: 刚刚阅读数: 2

原文链接:【http://xie.infoq.cn/article/0fc5faffa5de88f8e61b56ca5】。文章转载请联系作者。

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

发布

暂无评论

创作场景

MySQL 下 200GB 大表备份，利用传输表空间解决停服发版表备份问题

MySQL 下 200GB 大表备份，利用传输表空间解决停服发版表备份问题

问题背景

操作流程

总结

GreatSQL

评论