## 问题背景 在停服发版更新时，需对 200GB 大表（约 200 亿行数据）进行快速备份以预防操作失误。 因为曾经出现过有开发写的发版语句里，`UPDATE`语句的`WHERE`条件写错了，原本只需要更新几行数据，最终导致更新了半张表的数据。 MySQL版本是MySQL 8.0.X，为了预防这种情况，需要对某个重要的大表进行预先备份，以便可以及时回滚，及时恢复，及时回退，对于备份方法大概有下面几种: | **方案** | **优点** | **缺点** | | ----------------------- | --------------- | -------------------- | | `mysqldump` 导出 | 简单易用 | 大表导出耗时（200GB 可能需数小时） | | `CREATE TABLE...SELECT` | 直接 SQL 操作 | 数据复制慢，锁表风险高 | | **表空间** 传输 | **秒级备份** ，零数据复制 | 需操作系统权限，依赖文件拷贝 | | 主从复制/延迟复制 | 无需停服，恢复灵活 | 需主从架构，维护成本高 | **这个场景的核心需求**：停服更新的时间非常有限，比如1个小时之内要完成更新。 ## 操作流程 前面两种都比较简单，通过导数据的方法来备份旧表，万一出现问题，可以使用导出来的数据进行快速恢复，第三种方法估计比较少人用，下面是具体操作方法 1. 源表与备胎表结构 ```sql -- 源表（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> INSERTINTO aa SELECT1,"nihao"; ``` 2、查看两个表的表ID和表空间ID，可以看到aa表的表ID是1081 表空间ID是13，bb表的表ID是1082 表空间ID是14 ```sql greatsql> select * from information_schema.innodb_tables where name='school/aa'\G *************************** 1. row *************************** TABLE_ID: 1081 NAME: school/aa FLAG: 33 N_COLS: 6 SPACE: 13 ROW_FORMAT: Dynamic ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single INSTANT_COLS: 0 TOTAL_ROW_VERSIONS: 0 1 row in set (0.01 sec) greatsql> select * from information_schema.innodb_tables where name='school/bb'\G *************************** 1. row *************************** TABLE_ID: 1082 NAME: school/bb FLAG: 33 N_COLS: 6 SPACE: 14 ROW_FORMAT: Dynamic ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single INSTANT_COLS: 0 TOTAL_ROW_VERSIONS: 0 1 row in set (0.00 sec) ``` 3、备胎表卸载表空间： ```sql greatsql> ALTER TABLE bb DISCARD TABLESPACE; -- 加锁并生成配置文件 ``` 4、源表执行表空间导出： ```sql greatsql> USE school; greatsql> FLUSH TABLES aa FOR EXPORT; ``` 5、拷贝表空间文件（ibd和cfg文件），然后重新赋予权限，确保导入表空间时候不会出现问题 ```shell $ cd /data/mysql/mysql3306/data/school $ cp aa.ibd bb.ibd $ cp aa.cfg bb.cfg $ chown -R mysql:mysql /data/mysql/mysql3306/data/* ``` 6、在相同数据库下，备胎表导入表空间 ```sql greatsql> USE school; greatsql> UNLOCK TABLES; greatsql> ALTER TABLE bb IMPORT TABLESPACE; ``` 7、查询表数据，验证数据一致性 ```sql greatsql> USE school; greatsql> SELECT * FROM bb; greatsql> SELECT * FROM aa; ``` 查询表数据正常，没有任何问题 ```sql 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) ``` 查看表的数据文件，没什么问题 ```shell $ ll total 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 ``` 8、再次查看两个表的表ID和表空间ID，可以看到aa表的表ID是1081 表空间ID是13（没有变化），bb表的表ID是1083 表空间ID是14（表空间ID已经变了），bb表的表ID变了是防止与现有表冲突 ```sql mysql> select * from information_schema.innodb_tables where name='school/aa'\G *************************** 1. row *************************** TABLE_ID: 1081 NAME: school/aa FLAG: 33 N_COLS: 6 SPACE: 13 ROW_FORMAT: Dynamic ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single INSTANT_COLS: 0 TOTAL_ROW_VERSIONS: 0 1 row in set (0.00 sec) mysql> select * from information_schema.innodb_tables where name='school/bb'\G *************************** 1. row *************************** TABLE_ID: 1083 NAME: school/bb FLAG: 33 N_COLS: 6 SPACE: 14 ROW_FORMAT: Dynamic ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single INSTANT_COLS: 0 TOTAL_ROW_VERSIONS: 0 1 row in set (0.00 sec) ``` 9、发版更新与回滚 ```sql -- 发版操作（示例） greatsql> UPDATE aa SET sname = 'new_value' WHERE id > 1; ``` 10、如果发版有问题，直接交换表名，最快速度恢复整个表的数据 ```sql -- 回滚操作（交换表名） greatsql> ALTER TABLE aa RENAME TO aa_temp; greatsql> ALTER TABLE bb RENAME TO aa; ``` ## 总结 整个操作最重要的是**第4步**，操作系统级别的拷贝就完成了整个表的备份，相比于数据倒来倒去在速度上要快不少。另外，**第5步**的备胎表也可以不用导入，只有当发现发版出现问题时候，再导入也可以。 这种方法的关键优势如下 - 直接拷贝 .ibd 文件，无需逐行复制数据。 - 零锁表时间：`FLUSH TABLES tablename FOR EXPORT` 仅短暂加锁（秒级）。 - 快速恢复：通过表名交换实现秒级回滚。 特别适合于这几种场景：无主从架构的单实例环境、大表快速备份、停服时间敏感。 当然，如果有主从架构的话，则更加推荐使用**第四种**方法，在操作上也更加可控，短时间也能保证能够完成。 最后修改：23天前 © 著作权归作者所有