快捷導(dǎo)航

MySQL下200GB大表備份的操作(利用傳輸表空間解決停服發(fā)版表備份問題)

更新時(shí)間：2025年04月15日 08:45:14 作者：GreatSQL

這篇文章主要介紹了MySQL下200GB大表備份的操作(利用傳輸表空間解決停服發(fā)版表備份問題),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下

問題背景

在停服發(fā)版更新時(shí)，需對(duì) 200GB 大表（約 200 億行數(shù)據(jù)）進(jìn)行快速備份以預(yù)防操作失誤。

因?yàn)樵?jīng)出現(xiàn)過(guò)有開發(fā)寫的發(fā)版語(yǔ)句里，UPDATE語(yǔ)句的WHERE條件寫錯(cuò)了，原本只需要更新幾行數(shù)據(jù)，最終導(dǎo)致更新了半張表的數(shù)據(jù)。

MySQL版本是MySQL 8.0.X，為了預(yù)防這種情況，需要對(duì)某個(gè)重要的大表進(jìn)行預(yù)先備份，以便可以及時(shí)回滾，及時(shí)恢復(fù)，及時(shí)回退，對(duì)于備份方法大概有下面幾種:

方案	優(yōu)點(diǎn)	缺點(diǎn)
`mysqldump` 導(dǎo)出	簡(jiǎn)單易用	大表導(dǎo)出耗時(shí)（200GB 可能需數(shù)小時(shí)）
`CREATE TABLE...SELECT`	直接 SQL 操作	數(shù)據(jù)復(fù)制慢，鎖表風(fēng)險(xiǎn)高
表空間傳輸	秒級(jí)備份，零數(shù)據(jù)復(fù)制	需操作系統(tǒng)權(quán)限，依賴文件拷貝
主從復(fù)制/延遲復(fù)制	無(wú)需停服，恢復(fù)靈活	需主從架構(gòu)，維護(hù)成本高

這個(gè)場(chǎng)景的核心需求：停服更新的時(shí)間非常有限，比如1個(gè)小時(shí)之內(nèi)要完成更新。

操作流程

前面兩種都比較簡(jiǎn)單，通過(guò)導(dǎo)數(shù)據(jù)的方法來(lái)備份舊表，萬(wàn)一出現(xiàn)問題，可以使用導(dǎo)出來(lái)的數(shù)據(jù)進(jìn)行快速恢復(fù)，第三種方法估計(jì)比較少人用，下面是具體操作方法

源表與備胎表結(jié)構(gòu)

-- 源表（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、源表執(zhí)行表空間導(dǎo)出：

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

4、拷貝表空間文件（ibd和cfg文件），然后重新賦予權(quán)限，確保導(dǎo)入表空間時(shí)候不會(huì)出現(xiàn)問題

$ 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、在相同數(shù)據(jù)庫(kù)下，備胎表和源表都導(dǎo)入表空間

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

6、查詢表數(shù)據(jù)，驗(yàn)證數(shù)據(jù)一致性

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

查詢表數(shù)據(jù)正常，沒有任何問題

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)

查看表的數(shù)據(jù)文件，沒什么問題

$ 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

7、發(fā)版更新與回滾

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

8、如果發(fā)版有問題，直接交換表名，最快速度恢復(fù)整個(gè)表的數(shù)據(jù)

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

總結(jié)

整個(gè)操作最重要的是第4步，操作系統(tǒng)級(jí)別的拷貝就完成了整個(gè)表的備份，相比于數(shù)據(jù)倒來(lái)倒去在速度上要快不少。另外，第5步的備胎表也可以不用導(dǎo)入，只有當(dāng)發(fā)現(xiàn)發(fā)版出現(xiàn)問題時(shí)候，再導(dǎo)入也可以。

這種方法的關(guān)鍵優(yōu)勢(shì)如下

直接拷貝 .ibd 文件，無(wú)需逐行復(fù)制數(shù)據(jù)。
零鎖表時(shí)間：FLUSH TABLES tablename FOR EXPORT 僅短暫加鎖（秒級(jí)）。
快速恢復(fù)：通過(guò)表名交換實(shí)現(xiàn)秒級(jí)回滾。

特別適合于這幾種場(chǎng)景：無(wú)主從架構(gòu)的單實(shí)例環(huán)境、大表快速備份、停服時(shí)間敏感。

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

關(guān)于 GreatSQL

GreatSQL是適用于金融級(jí)應(yīng)用的國(guó)內(nèi)自主開源數(shù)據(jù)庫(kù)，具備高性能、高可靠、高易用性、高安全等多個(gè)核心特性，可以作為MySQL或Percona Server的可選替換，用于線上生產(chǎn)環(huán)境，且完全免費(fèi)并兼容MySQL或Percona Server。

到此這篇關(guān)于MySQL下200GB大表備份，利用傳輸表空間解決停服發(fā)版表備份問題的文章就介紹到這了,更多相關(guān)使用java代碼獲取JVM信息內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: