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

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

问题背景
在停服发版更新时，需对 200GB 大表（约 200 亿行数据）进行快速备份以预防操作失误。
因为曾经出现过有开发写的发版语句里，UPDATE语句的WHERE条件写错了，原本只必要更新几行数据，终极导致更新了半张表的数据。
MySQL版本是MySQL 8.0.X，为了预防这种情况，必要对某个重要的大表进行预先备份，以便可以及时回滚，及时恢复，及时回退，对于备份方法大概有下面几种:
方案优点缺点mysqldump  导出简单易用大表导出耗时（200GB 大概需数小时）CREATE TABLE...SELECT直接 SQL 操作数据复制慢，锁表风险高表空间 传输秒级备份 ，零数据复制需操作系统权限，依赖文件拷贝主从复制/延迟复制无需停服，恢复灵活需主从架构，维护本钱高这个场景的核心需求：停服更新的时间非常有限，比如1个小时之内要完成更新。
操作流程

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

• 源表与备胎表结构
  

1. -- 源表（aa）
2. CREATE TABLE aa (
3. id int(11) DEFAULT NULL,
4.   sname VARCHAR(100)
5. ) ENGINE=InnoDB;
7. -- 备胎表（bb）
8. CREATE TABLE bb (
9. id int(11) DEFAULT NULL,
10.   sname VARCHAR(100)
11. ) ENGINE=InnoDB;
13. greatsql> INSERTINTO aa SELECT1,"nihao";

复制代码

2、查看两个表的表ID和表空间ID，可以看到aa表的表ID是1081 表空间ID是13，bb表的表ID是1082 表空间ID是14

1. greatsql> select * from information_schema.innodb_tables where name='school/aa'\G
2. *************************** 1. row ***************************
3.           TABLE_ID: 1081
4.               NAME: school/aa
5.               FLAG: 33
6.             N_COLS: 6
7.              SPACE: 13
8.         ROW_FORMAT: Dynamic
9.      ZIP_PAGE_SIZE: 0
10.         SPACE_TYPE: Single
11.       INSTANT_COLS: 0
12. TOTAL_ROW_VERSIONS: 0
13. 1 row in set (0.01 sec)
15. greatsql> select * from information_schema.innodb_tables where name='school/bb'\G
16. *************************** 1. row ***************************
17.           TABLE_ID: 1082
18.               NAME: school/bb
19.               FLAG: 33
20.             N_COLS: 6
21.              SPACE: 14
22.         ROW_FORMAT: Dynamic
23.      ZIP_PAGE_SIZE: 0
24.         SPACE_TYPE: Single
25.       INSTANT_COLS: 0
26. TOTAL_ROW_VERSIONS: 0
27. 1 row in set (0.00 sec)

复制代码

3、备胎表卸载表空间：

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

复制代码

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

1. greatsql> USE school;
2. greatsql> FLUSH TABLES aa FOR EXPORT;   

复制代码

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

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

复制代码

6、在雷同数据库下，备胎表导入表空间

1. greatsql> USE school;
2. greatsql> UNLOCK TABLES;  
3. greatsql> ALTER TABLE bb IMPORT TABLESPACE;

复制代码

7、查询表数据，验证数据同等性

1. greatsql> USE school;
2. greatsql> SELECT * FROM bb;
3. greatsql> SELECT * FROM aa;

复制代码

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

1. greatsql> SELECT * FROM aa;
2. +------+-------+
3. | id   | sname |
4. +------+-------+
5. |    1 | nihao |
6. +------+-------+
7. 1 row in set (0.01 sec)
9. greatsql> SELECT * FROM bb;
10. +------+-------+
11. | id   | sname |
12. +------+-------+
13. |    1 | nihao |
14. +------+-------+
15. 1 row in set (0.00 sec)

复制代码

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

1. $ ll
2. total 228
3. -rw-r----- 1 mysql mysql 114688 Mar  4 16:51 aa.ibd
4. -rw-r----- 1 mysql mysql    781 Mar  4 16:52 bb.cfg
5. -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变了是防止与现有表冲突

1. mysql> select * from information_schema.innodb_tables where name='school/aa'\G
2. *************************** 1. row ***************************
3.           TABLE_ID: 1081
4.               NAME: school/aa
5.               FLAG: 33
6.             N_COLS: 6
7.              SPACE: 13
8.         ROW_FORMAT: Dynamic
9.      ZIP_PAGE_SIZE: 0
10.         SPACE_TYPE: Single
11.       INSTANT_COLS: 0
12. TOTAL_ROW_VERSIONS: 0
13. 1 row in set (0.00 sec)
16. mysql> select * from information_schema.innodb_tables where name='school/bb'\G
17. *************************** 1. row ***************************
18.           TABLE_ID: 1083
19.               NAME: school/bb
20.               FLAG: 33
21.             N_COLS: 6
22.              SPACE: 14
23.         ROW_FORMAT: Dynamic
24.      ZIP_PAGE_SIZE: 0
25.         SPACE_TYPE: Single
26.       INSTANT_COLS: 0
27. TOTAL_ROW_VERSIONS: 0
28. 1 row in set (0.00 sec)

复制代码

9、发版更新与回滚

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

复制代码

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

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

复制代码

总结

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

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

特殊适合于这几种场景：无主从架构的单实例环境、大表快速备份、停服时间敏感。
当然，如果有主从架构的话，则更加保举利用第四种方法，在操作上也更加可控，短时间也能保证能够完成。

 
 

本文版权归作者所有，未经作者同意不得转载。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。