MySQL下200GB大表備份，利用傳輸表空間解決停服發版表備份問題

問題背景

在停服發版更新時，需對 200GB 大表（約 200 億行數據）進行快速備份以預防操作失誤。

因為曾經出現過有開發寫的發版語句里，UPDATE語句的WHERE條件寫錯了，原本只需要更新幾行數據，最終導致更新了半張表的數據。

MySQL版本是MySQL 8.0.X，為了預防這種情況，需要對某個重要的大表進行預先備份，以便可以及時回滾，及時恢復，及時回退，對于備份方法大概有下面幾種:

這個場景的核心需求：停服更新的時間非常有限，比如1個小時之內要完成更新。

操作流程

前面兩種都比較簡單，通過導數據的方法來備份舊表，萬一出現問題，可以使用導出來的數據進行快速恢復，第三種方法估計比較少人用，下面是具體操作方法

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> INSERTINTO aa SELECT1,"nihao";

2、查看兩個表的表ID和表空間ID，可以看到aa表的表ID是1081 表空間ID是13，bb表的表ID是1082 表空間ID是14

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、備胎表卸載表空間：

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

4、源表執行表空間導出：

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

5、拷貝表空間文件（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/*

6、在相同數據庫下，備胎表導入表空間

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

7、查詢表數據，驗證數據一致性

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)

查看表的數據文件，沒什么問題

$ 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變了是防止與現有表沖突

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、發版更新與回滾

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

10、如果發版有問題，直接交換表名，最快速度恢復整個表的數據

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

總結

整個操作最重要的是第4步，操作系統級別的拷貝就完成了整個表的備份，相比于數據倒來倒去在速度上要快不少。另外，第5步的備胎表也可以不用導入，只有當發現發版出現問題時候，再導入也可以。

這種方法的關鍵優勢如下

• 直接拷貝 .ibd 文件，無需逐行復制數據。
• 零鎖表時間：FLUSH TABLES tablename FOR EXPORT 僅短暫加鎖（秒級）。
• 快速恢復：通過表名交換實現秒級回滾。

特別適合于這幾種場景：無主從架構的單實例環境、大表快速備份、停服時間敏感。

當然，如果有主從架構的話，則更加推薦使用第四種方法，在操作上也更加可控，短時間也能保證能夠完成。

本文版權歸作者所有，未經作者同意不得轉載。