mysql如何備份一個表單_Mysql億級數據大表單表備份

上一篇Mysql已有億級數據大表按時間分區，介紹了億級數據大表如何按時間分區，也留下了一個問題：備份億級數據大表要耗時多久。本篇將就如何備份億級數據大表展開討論。

注意：我這里所說的備份指的是數據從一張表拷貝到另外一張表，也就是說單表備份。

創建原表t_send_message_send的sql：

CREATE TABLE `t_send_message_send` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`plan_id` bigint(20) DEFAULT NULL,

`job_uuid` varchar(36) DEFAULT NULL,

`send_port` varchar(16) DEFAULT NULL,

`mobile` varchar(16) DEFAULT NULL,

`content` varchar(200) DEFAULT NULL,

`product_code` varchar(16) DEFAULT 'HELP',

`fake` bit(1) DEFAULT b'0',

`date_push` datetime DEFAULT NULL,

`activity_id` bigint(20) DEFAULT '0',

PRIMARY KEY (`id`),

KEY `mobile` (`mobile`),

KEY `date_push` (`date_push`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

原表一個自增主鍵id，兩個索引mobile、date_push，數據量如下圖：

創建新表的t_send_message_send2的sql：

CREATE TABLE `t_send_message_send2` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`plan_id` bigint(20) DEFAULT NULL,

`job_uuid` varchar(36) DEFAULT NULL,

`send_port` varchar(16) DEFAULT NULL,

`mobile` varchar(16) DEFAULT NULL,

`content` varchar(200) DEFAULT NULL,

`product_code` varchar(16) DEFAULT 'HELP',

`fake` bit(1) DEFAULT b'0',

`date_push` datetime NOT NULL,

`activity_id` bigint(20) DEFAULT '0',

PRIMARY KEY (`id`,`date_push`),

KEY `mobile` (`mobile`),

KEY `date_push` (`date_push`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE COLUMNS(date_push)

(PARTITION p2016 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,

PARTITION p2017 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,

PARTITION p2018 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,

PARTITION p2019 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,

PARTITION p2020 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB);

新表一個聯合主鍵(id，date_push)，兩個索引mobile、date_push，5個分區，字段和結構跟原表一樣，數據量為0。

上一篇提供了兩類備份方式：①在線備份；②離線備份。

1.在線備份；

數據一直在數據庫中不離線。

insert into t_send_message_send2 (select * from t_send_message_send);

sql很簡單，意思很明確，就是將select的查詢結果插入到t_send_message_send2。這個過程我跑了一個多小時，沒跑完，被我中止了。用navicate查看t_send_message_send2的對象信息，看到有500多萬行記錄，打開t_send_message_send2表，里面一行記錄都沒有，空的。應該是請求中止了，數據還沒提交。好吧，看下為什么慢，解析下：

EXPLAIN

insert into t_send_message_send2 (select * from t_send_message_send);

執行結果：

"id""select_type""table""partitions""type""possible_keys""key""key_len""ref""rows""filtered""Extra"

"1""INSERT""t_send_message_send2""p2016,p2017,p2018,p2019,p2020""ALL"""""""""""""""

"1""SIMPLE""t_send_message_send""""ALL""""""""""100568970""100.00"""

好家伙，第5列type都是ALL。type表示MySQL在表中找到所需行的方式，又稱“訪問類型”。常用的類型有： ALL, index, range, ref, eq_ref, const, system, NULL(從前往后，性能從差到好)。ALL，Full Table Scan， MySQL將遍歷全表以找到匹配的行。明白了吧，每次插入全表掃描，這能不慢嗎？

2. 離線備份

數據先導出到本地，再從本地導回數據庫。

1)數據導出(數據備份)

離線備份也分為冷備和熱備。

冷備：數據庫處于關閉的狀態下的備份，優點是：①保證數據庫的完整性；②備份過程簡單并且恢復速度快。缺點是：①關閉數據庫，意味著相關的業務無法正常進行，用戶無法訪問你的業務，一般冷備用于不是很重要、非核心業務上面。

冷備顯然是不滿足我的業務需求的，冷備是全庫備份，而我只是單表備份。

熱備：數據庫處于運行狀態下的備份，不影響現有業務的進行。熱備又分為裸文件備份和邏輯備份。裸文件備份：基于底層數據文件的copy datafile。進入到數據庫的數據目錄，再進入到你的庫目錄，你會發現在這個目錄下有很多.frm文件和.ibd文件，.frm文件是表的結構文件，.ibd文件是表的數據文件。邏輯備份：備份成SQL語句或者其他文件(如csv)，恢復時執行SQL，實現數據庫數據的重現。

裸文件備份顯然也是全庫備份，也是不滿足我的業務需求的，下面討論邏輯備份。

邏輯備份常見的兩種方式：

①mysqldump

mysqldump -u root -p marketing t_send_message_send > e:/mysql/marketing_t_send_message_send.sql;

哈哈哈，暴露了在windows上操作的。

mysqldump導出相當快，億級的記錄，50多個G數據量，大概僅用了40分鐘左右。沒記錄到具體時間，是因為執行這個腳本不需要登錄到mysql，命令行就可以了，而命令行不會提示執行腳本花了多長時間，如果登錄mysql，每次執行都會提示執行腳本好了多長時間。

②select … into outfile …;

mysql> use marketing;

Database changed

mysql> select * from t_send_message_send into outfile 'e:/mysql/t_send_message_send.csv';

Query OK, 110900005 rows affected (34 min 10.22 sec)

mysql>

億級的記錄，50多個G數據量，僅需要34分鐘，就問你快不快？

2)數據導入(數據恢復)

①mysqldump方式導出的

mysql> use marketing;

Database changed

mysql> source e:/mysql/marketing_t_send_message_send.sql

或者

mysql -uroot -p marketing < e:/mysql/marketing_t_send_message_send.sql

mysqldump方式不滿足我的業務需求的，mysqldump備份了整個t_send_message_send表，包括表結構，而表結構是我不需要的，如果恢復的話，只會是恢復成t_send_message_send，數據不會恢復到t_send_message_send2中。

②select … into outfile …;導出的

mysql> use marketing;

Database changed

mysql> load data infile 'e:/mysql/t_send_message_send.csv' into table t_send_message_send2;

或者

將備份的t_send_message_send.csv重命名為t_send_message_send2.csv，然后命令行里面執行：

mysqlimport -u root -p marketing e:/mysql/t_send_message_send2.csv

很遺憾，這種方式不可行，我從凌晨1點開始執行，到早上9點多還沒執行完。七八個小時，插入了2700多萬記錄，13個G數據量，1.7個G索引。

之前我一直覺得應該是可行的，開始執行的那一刻我就感覺不對。分析下了原因，大概是因為有索引。我的理解是這樣的：索引相當于排序，插入數據前，還得先全表掃描下，才曉得數據應該插入到哪個位置，插入一億條記錄，就得一億次全表掃描，這能不慢嗎？那既然這樣，先把索引刪了，先不排序，數據直接插到最后面，等數據插完之后再排序，再建索引，這樣應該會快一些。開搞，先刪除索引：

##先truncate掉t_send_message_send2##

TRUNCATE TABLE t_send_message_send2;

ALTER TABLE t_send_message_send2 DROP INDEX mobile;

ALTER TABLE t_send_message_send2 DROP INDEX date_push;

然后再次導入。

C:\Users\maanjun>mysqlimport -u root -p marketing e:/mysql/t_send_message_send2.csv

Enter password: ******

marketing.t_send_message_send2: Records: 110900005 Deleted: 0 Skipped: 0 Warnings: 0

耗時3個多小時，跟Mysql數據庫快速插入億級數據差不多。最后，再重建索引：

ALTER TABLE `t_send_message_send2` ADD INDEX (mobile);

ALTER TABLE `t_send_message_send2` ADD INDEX (date_push);

重建兩個索引，一個varchar類型，一個datetime類型，建一個索引差不多二三十分鐘，加上數據導入過程耗時，數據導入、重建索引總共耗時4個小時。

回過頭來想，插入數據前刪除索引，然后插入數據，最后重建索引，不管是哪種導入方式差不多都是耗時3個多小時，加上重建索引的時間，整個恢復過程差不多4個小時。再加上導出耗費的時間，5個小時內億級記錄表單表備份是可以的。當然這說的離線備份，其實如果順利的話，在線備份花費的時間會更短，因為在線備份也可以是刪除索引–>插入數據–>重建索引這個過程，況且在線備份不需要耗費導出數據這段時間。其次，在線備份也不需要占用本地幾十個G的中轉空間。但是在線備份一定好嗎？未必！在線備份頻繁地查詢原表，會不會影響線網業務？我是在本機測試的，直接操作數據庫，沒有業務在跑，當然沒有關系，如果是線網那就值得考慮下啦。再者，我在用navicate進行在線備份過程中連接無故中斷了。

[SQL]insert into t_send_message_send2 (select * from t_send_message_send);

[Err] 2013 - Lost connection to MySQL server during query

在數據導出導入過程中還踩了一些，這些坑在百度上搜一下，都有解決方法。下一篇，將對整個mysql億級數據大表分區的過程做個總結。

附：

type

表示MySQL在表中找到所需行的方式，又稱“訪問類型”。

常用的類型有： ALL, index, range, ref, eq_ref, const, system, NULL(從左到右，性能從差到好)

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行

index: Full Index Scan，index與ALL區別為index類型只遍歷索引樹

range:只檢索給定范圍的行，使用一個索引來選擇行

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

eq_ref: 類似ref，區別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關聯條件