mysql 5.7 差异备份_MySQL 5.7 新备份工具mysqlpump 使用说明 - 运维小结
之前詳細(xì)介紹了Mysqldump備份工具使用,下面說下MySQL5.7之后新添加的備份工具mysqlpump。mysqlpump是mysqldump的一個衍生,mysqldump備份功能這里就不多說了,現(xiàn)在看看mysqlpump到底有了哪些提升,詳細(xì)可以查看官網(wǎng)文檔。mysqlpump和mysqldump一樣,屬于邏輯備份,備份以SQL形式的文本保存。邏輯備份相對物理備份好處是不關(guān)心log的大小,直接備份數(shù)據(jù)即可。
Mysqlpump主要特點(diǎn)
-? 并行備份數(shù)據(jù)庫和數(shù)據(jù)庫中的對象的,加快備份過程。
-? 更好的控制數(shù)據(jù)庫和數(shù)據(jù)庫對象(表,存儲過程,用戶帳戶)的備份。
-? 備份用戶賬號作為帳戶管理語句(CREATE USER,GRANT),而不是直接插入到MySQL的系統(tǒng)數(shù)據(jù)庫。
-? 備份出來直接生成壓縮后的備份文件。
-? 備份進(jìn)度指示(估計值)。
-? 重新加載(還原)備份文件,先建表后插入數(shù)據(jù)最后建立索引,減少了索引維護(hù)開銷,加快了還原速度。
-? 備份可以排除或則指定數(shù)據(jù)庫。
Mysqlpump缺點(diǎn)
-? 只能并行到表級別,如果表特別大,開多線程和單線程是一樣的,并行度不如mydumper;
-? 無法獲取當(dāng)前備份對應(yīng)的binlog位置;
-? MySQL5.7.11之前的版本不要使用,并行導(dǎo)出和single-transaction是互斥的;
參數(shù)說明:Mysqlpump絕大部分參數(shù)使用和Mysqldump一致,下面順便重溫一下。注意對于mysqlpump 專有參數(shù)會用背景色標(biāo)記出來。
1)?--add-drop-database:在建立庫之前先執(zhí)行刪庫操作
DROP DATABASE IF EXISTS `...`;
2)--add-drop-table:在建表之前先執(zhí)行刪表操作
DROP TABLE IF EXISTS `...`.`...`;
3) --add-drop-user:在CREATE USER語句之前增加DROP USER。注意:這個參數(shù)需要和--users一起使用,否者不生效。
DROP USER 'backup'@'172.16.60.%';
4) --add-locks:備份表時,使用LOCK TABLES和UNLOCK TABLES。注意:這個參數(shù)不支持并行備份,需要關(guān)閉并行備份功能:--default-parallelism=0
LOCK TABLES `...`.`...` WRITE;
...
UNLOCK TABLES;
5) --all-databases:備份所有庫,即 -A。
6) --bind-address:指定通過哪個網(wǎng)絡(luò)接口來連接Mysql服務(wù)器(一臺服務(wù)器可能有多個IP),防止同一個網(wǎng)卡出去影響業(yè)務(wù)。
7) --complete-insert:dump出包含所有列的完整insert語句。
8)--compress: 壓縮客戶端和服務(wù)器傳輸?shù)乃械臄?shù)據(jù),即 -C。
9) --compress-output:默認(rèn)不壓縮輸出,目前可以使用的壓縮算法有LZ4和ZLIB
[root@localhost ~]# mysqlpump --compress-output=LZ4 > dump.lz4
[root@localhost ~]# lz4_decompress dump.lz4 dump.txt
[root@localhost ~]# mysqlpump --compress-output=ZLIB > dump.zlib
[root@localhost ~]# zlib_decompress dump.zlib dump.txt
10) --databases:手動指定要備份的庫,支持多個數(shù)據(jù)庫,用空格分隔,即-B。
11) --default-character-set:指定備份的字符集。
12)--default-parallelism:指定并行線程數(shù),默認(rèn)是2,如果設(shè)置成0,表示不使用并行備份。注意:每個線程的備份步驟是:先create table但不建立二級索引(主鍵會在create table時候建立),再寫入數(shù)據(jù),最后建立二級索引。
13) --defer-table-indexes:延遲創(chuàng)建索引,直到所有數(shù)據(jù)都加載完之后,再創(chuàng)建索引,默認(rèn)開啟。若關(guān)閉則會和mysqldump一樣:先創(chuàng)建一個表和所有索引,再導(dǎo)入數(shù)據(jù),因?yàn)樵诩虞d還原數(shù)據(jù)的時候要維護(hù)二級索引的開銷,導(dǎo)致效率比較低。關(guān)閉使用參數(shù):--skip--defer-table-indexes。
14) --events:備份數(shù)據(jù)庫的事件,默認(rèn)開啟,關(guān)閉使用--skip-events參數(shù)。
15)--exclude-databases:備份排除該參數(shù)指定的數(shù)據(jù)庫,多個用逗號分隔。類似的還有--exclude-events、--exclude-routines、--exclude-tables、--exclude-triggers、--exclude-users
[root@localhost ~]# mysqlpump --exclude-databases=mysql,sys -p123456 --set-gtid-purged=off >/root/db.sql #備份過濾mysql和sys數(shù)據(jù)庫
[root@localhost ~]# mysqlpump --exclude-tables=rr,tt -p123456 --set-gtid-purged=off > /root/db.sql #備份過濾所有數(shù)據(jù)庫中rr、tt表
[root@localhost ~]# mysqlpump -B test --exclude-tables=tmp_ifulltext,tt -p123456 --set-gtid-purged=off >/root/db.sql #備份過濾test庫中的rr、tt表
...
注意:要是只備份數(shù)據(jù)庫的賬號,需要添加參數(shù)--users,并且需要過濾掉所有的數(shù)據(jù)庫,如
#備份除dba和backup的所有賬號。
[root@localhost ~]# mysqlpump --users --exclude-databases=sys,mysql,db1,db2 --exclude-users=dba,backup -p123456 --set-gtid-purged=off >/root/db.sql
16) --include-databases:指定備份數(shù)據(jù)庫,多個用逗號分隔,類似的還有--include-events、--include-routines、--include-tables、--include-triggers、--include-users,大致方法使用同15。
17) --insert-ignore:備份用insert ignore語句代替insert語句。
18) --log-error-file:備份出現(xiàn)的warnings和erros信息輸出到一個指定的文件。
19) --max-allowed-packet:備份時用于client/server直接通信的最大buffer包的大小。
20) --net-buffer-length:備份時用于client/server通信的初始buffer大小,當(dāng)創(chuàng)建多行插入語句的時候,mysqlpump 創(chuàng)建行到N個字節(jié)長。
21)--no-create-db:備份不寫CREATE DATABASE語句。要是備份多個庫,需要使用參數(shù)-B,而使用-B的時候會出現(xiàn)create database語句,該參數(shù)可以屏蔽create database 語句。
22) --no-create-info:備份不寫建表語句,即不備份表結(jié)構(gòu),只備份數(shù)據(jù),即 -t。
23) --hex-blob: 備份binary字段的時候使用十六進(jìn)制計數(shù)法,受影響的字段類型有BINARY、VARBINARY、BLOB、BIT。
24) --host :備份指定的數(shù)據(jù)庫地址,即 -h。
25) --parallel-schemas=[N:]db_list:指定并行備份的庫,多個庫用逗號分隔,如果指定了N,將使用N個線程的地隊列,如果N不指定,將由 --default-parallelism才確認(rèn)N的值,可以設(shè)置多個--parallel-schemas
#4個線程備份vs和aa,3個線程備份pt。通過show processlist 可以看到有7個線程。
[root@localhost ~]# mysqlpump --parallel-schemas=4:vs,aa --parallel-schemas=3:pt -p123456 --set-gtid-purged=off > /root/db.sql
#默認(rèn)2個線程,即2個線程備份vs和abc,2個線程備份pt
[root@localhost ~]# mysqlpump --parallel-schemas=vs,abc --parallel-schemas=pt -p123456 --set-gtid-purged=off > /root/db.sql
#當(dāng)然要是硬盤IO不允許的話,可以少開幾個線程和數(shù)據(jù)庫進(jìn)行并行備份
26) --password:備份需要的密碼。
27) --port:備份數(shù)據(jù)庫的端口。
28) --protocol={TCP|SOCKET|PIPE|MEMORY}:指定連接服務(wù)器的協(xié)議。
29) --replace:備份出來replace into語句。
30) --routines:備份出來包含存儲過程和函數(shù),默認(rèn)開啟,需要對 mysql.proc表有查看權(quán)限。生成的文件中會包含CREATE PROCEDURE 和 CREATE FUNCTION語句以用于恢復(fù),關(guān)閉則需要用--skip-routines參數(shù)。
31) --triggers:備份出來包含觸發(fā)器,默認(rèn)開啟,使用--skip-triggers來關(guān)閉。
31) --set-charset:備份文件里寫SET NAMES default_character_set 到輸出,此參默認(rèn)開啟。-- skip-set-charset禁用此參數(shù),不會在備份文件里面寫出set names...
32)--single-transaction:該參數(shù)在事務(wù)隔離級別設(shè)置成Repeatable Read,并在dump之前發(fā)送start transaction 語句給服務(wù)端。這在使用innodb時很有用,因?yàn)樵诎l(fā)出start transaction時,保證了在不阻塞任何應(yīng)用下的一致性狀態(tài)。對myisam和memory等非事務(wù)表,還是會改變狀態(tài)的,當(dāng)使用此參的時候要確保沒有其他連接在使用ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等語句,否則會出現(xiàn)不正確的內(nèi)容或則失敗。--add-locks和此參互斥,在mysql5.7.11之前,--default-parallelism大于1的時候和此參也互斥,必須使用--default-parallelism=0。5.7.11之后解決了--single-transaction和--default-parallelism的互斥問題。
33) --skip-definer:忽略那些創(chuàng)建視圖和存儲過程用到的 DEFINER 和 SQL SECURITY 語句,恢復(fù)的時候,會使用默認(rèn)值,否則會在還原的時候看到?jīng)]有DEFINER定義時的賬號而報錯。
34)--skip-dump-rows:只備份表結(jié)構(gòu),不備份數(shù)據(jù),即-d。注意:mysqldump支持--no-data,mysqlpump不支持--no-data
35) --socket:對于連接到localhost,Unix使用套接字文件,在Windows上是命名管道的名稱使用,即 -S。
36) --ssl:--ssl參數(shù)將要被去除,用--ssl-mode取代。關(guān)于ssl相關(guān)的備份。
37) --tz-utc:備份時會在備份文件的最前幾行添加SET TIME_ZONE='+00:00'。注意:如果還原的服務(wù)器不在同一個時區(qū)并且還原表中的列有timestamp字段,會導(dǎo)致還原出來的結(jié)果不一致。默認(rèn)開啟該參數(shù),用 --skip-tz-utc來關(guān)閉參數(shù)。
38)--user:備份時候的用戶名,即 -u。
39)--users:備份數(shù)據(jù)庫用戶,備份的形式是CREATE USER...,GRANT...,只備份數(shù)據(jù)庫賬號可以通過如下命令
#過濾掉所有數(shù)據(jù)庫
[root@localhost ~]# mysqlpump --exclude-databases=% --users -p123456 --set-gtid-purged=off >/root/db.sql
40) --watch-progress:定期顯示進(jìn)度的完成,包括總數(shù)表、行和其他對象。該參數(shù)默認(rèn)開啟,用--skip-watch-progress來關(guān)閉。
Mysqlpump的多線程架構(gòu)圖如下
-? mysqlpump是MySQL5.7的官方工具,用于取代mysqldump,其參數(shù)與mysqldump基本一樣;
-? mysqlpump是多線程備份,但只能到表級別,單表備份還是單線程;
-? mysqldump備份時,有個默認(rèn)隊列(default),隊列下開N個線程去備份數(shù)據(jù)庫/數(shù)據(jù)庫中的表;
-? 支持開多個隊列(對應(yīng)不同庫/表),然后每個隊列設(shè)置不同線程,進(jìn)行備份;
Mysqlpump支持基于庫和表的并行導(dǎo)出,Mysqlpump的并行導(dǎo)出功能的架構(gòu)為:隊列+線程,允許有多個隊列(--parallel-schemas),每個隊列下有多個線程(N),而一個隊列可以綁定1個或者多個數(shù)據(jù)庫(逗號分隔)。Mysqlpump的備份是基于表并行的,對于每張表的導(dǎo)出只能是單個線程的,這里會有個限制是如果某個數(shù)據(jù)庫有一張表非常大,可能大部分的時間都是消耗在這個表的備份上面,并行備份的效果可能就不明顯。這里可以利用Mydumper其是以chunk的方式批量導(dǎo)出,即Mydumper支持一張表多個線程以chunk的方式批量導(dǎo)出。但相對于Mysqldump有很大提升。
對比測試如下
mysqlpump壓縮備份kevin數(shù)據(jù)庫 三個并發(fā)線程備份,消耗時間:222s
[root@localhost ~]# mysqlpump -uroot -p123456 -h172.16.60.211 --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B kevin > /data/db_backup/kevin_db.sql.lz4
mysqldump備份壓縮kevin數(shù)據(jù)庫 單個線程備份,消耗時間:900s,gzip的壓縮率比LZ4的高
[root@localhost ~]# mysqldump -uroot -p123456 -h172.16.60.211 --default-character-set=utf8 -P3306 --skip-opt --add-drop-table --create-options --quick --extended-insert --single-transaction -B kevin | gzip > /data/db_backup/kevin.sql.gz
mydumper備份kevin數(shù)據(jù)庫 三個并發(fā)線程備份,消耗時間:300s,gzip的壓縮率比LZ4的高
[root@localhost ~]# mydumper -u root -p123456 -h 172.16.60.211 -P 3306 -t 3 -c -l 3600 -s 10000000 -B kevin -o /data/db_backup/kevin/
mydumper備份kevin數(shù)據(jù)庫,五個并發(fā)線程備份,并且開啟對一張表多個線程以chunk的方式批量導(dǎo)出,-r。消耗時間:180s
[root@localhost ~]# mydumper -u root -p123456 -h 172.16.60.211 -P 3306 -t 5 -c -r 300000 -l 3600 -s 10000000 -B kevin -o /data/db_backup/kevin/
注意: 如果是開啟了GTID功能的數(shù)據(jù)庫,備份時還需要添加"--set-gtid-purged=off"參數(shù),否則可能會報錯!
從上面看出,mysqlpump的備份效率是最快的,mydumper次之,mysqldump最差。所以在IO允許的情況下,能用多線程就別用單線程備份。并且mysqlpump還支持多數(shù)據(jù)庫的并行備份,而mydumper要么備份一個庫,要么就備份所有庫??梢钥闯?#xff0c;在mysql數(shù)據(jù)庫備份方面,mysqlpump比mysqldump的測試結(jié)果要好。由于實(shí)際情況不同,測試給出的速度提升只是參考。到底開啟多少個并行備份的線程,這個看磁盤IO的承受能力,若該服務(wù)器只進(jìn)行備份任務(wù),可以最大限制的來利用磁盤。
測試中發(fā)現(xiàn)mysqlpump和mysqldump對比:
- mysqldump默認(rèn)是不會有建庫命令, 但是默認(rèn)會有drop table的命令;
- mysqlpump默認(rèn)是有建庫命令,但是不會有drop table的命令,所以mysqlpump恢復(fù)的時候不要直接< file.sql ;
- mysqldump恢復(fù)時會先創(chuàng)建表及其所有索引,然后再導(dǎo)入數(shù)據(jù);mysqlpump恢復(fù)時會先創(chuàng)建表,然后再導(dǎo)入數(shù)據(jù),最后建索引;
- mysqlpump可以指定多線程并發(fā)備份,默認(rèn)是2個;備份時會有進(jìn)度指示,雖然只是估計值,但不會再想mysqldump備份時那么枯燥,看不到過程.
注意:mysqlpump備份的幾個重要參數(shù)
--default-parallelism 指定線程數(shù),默認(rèn)開2個線程進(jìn)行并發(fā)備份
--parallel-schemas 指定哪些數(shù)據(jù)庫進(jìn)行并發(fā)備份
--set-gtid-purged=OFF 這個是5.7.18版本后加入的參數(shù),
--set-gtid-purged=OFF這個參數(shù)很重要,如果備份命令里不加上,則備份可能會報錯:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed
suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass
--all-databases --triggers --routines --events.
備份演示如下
-? ?備份命令如下
[root@localhost ~]# mysqlpump --single-transaction --set-gtid-purged=OFF --parallel-schemas=2:kevin --parallel-schemas=4:dbt3 -B kevin dbt3 -p123456 > /tmp/backup.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 1/5 tables, 0/7559817 rows
Dump progress: 3/15 tables, 286750/12022332 rows
Dump progress: 3/15 tables, 686750/12022332 rows
Dump progress: 3/15 tables, 1042250/12022332 rows
...
Dump completed in 43732 milliseconds
接著另外打開一個終端會話,登錄mysql看下情況
(root@172.16.0.10) [(none)]> show processlist;
+--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 138199 | root | 172.16.60.50:39238 | NULL | Query | 0 | starting | show processlist |
| 138267 | root | 172.16.60.50:39776 | NULL | Sleep | 2 | | NULL |
| 138268 | root | 172.16.60.50:39778 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `emp_no`,`dept_no`,`from_date`,`to_date` FROM `kevin`.`dept_emp` |
| 138269 | root | 172.16.60.50:39780 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date` FROM `emplo |
| 138270 | root | 172.16.60.50:39782 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `o_orderkey`,`o_custkey`,`o_orderstatus`,`o_totalprice`,`o_orderDATE`,`o_orderpr |
| 138271 | root | 172.16.60.50:39784 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `p_partkey`,`p_name`,`p_mfgr`,`p_brand`,`p_type`,`p_size`,`p_container`,`p_retai |
| 138272 | root | 172.16.60.50:39786 | NULL | Query | 2 | Sending data | SELECT SQL_NO_CACHE `l_orderkey`,`l_partkey`,`l_suppkey`,`l_linenumber`,`l_quantity`,`l_extendedpric |
| 138273 | root | 172.16.60.50:39788 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `c_custkey`,`c_name`,`c_address`,`c_nationkey`,`c_phone`,`c_acctbal`,`c_mktsegme |
| 138274 | root | 172.16.60.50:39790 | NULL | Sleep | 2 | | NULL |
| 138275 | root | 172.16.60.50:39792 | NULL | Sleep | 1 | | NULL |
+--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
可以看到138268和138269在備份kevin庫,138270,138271,138272,138273在備份dbt3,這里沒打印全。
-? ?備份過程如下:
終端會話1:
(root@localhost) [(none)]> truncate mysql.general_log;
Query OK, 0 rows affected (0.10 sec)
(root@localhost) [(none)]> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]> set global general_log = 1;
Query OK, 0 rows affected (0.03 sec)
終端會話2:
[root@VM_0_5_centos ~]# mysqlpump --single-transaction kevin --set-gtid-purged=OFF -p123456> /tmp/backup.sql
Dump completed in 592 milliseconds
(root@localhost) [(none)]> select thread_id,left(argument, 64) from mysql.general_log order by event_time;
................
................
+-----------+------------------------------------------------------------------+
| 7 | root@localhost on using Socket |
| 7 | FLUSH TABLES WITH READ LOCK |
| 7 | SHOW WARNINGS |
| 7 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| 7 | SHOW WARNINGS |
| 7 | START TRANSACTION WITH CONSISTENT SNAPSHOT |
| 7 | SHOW WARNINGS |
| 8 | root@localhost on using Socket |
| 8 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| 8 | SHOW WARNINGS |
| 8 | START TRANSACTION WITH CONSISTENT SNAPSHOT |
| 8 | SHOW WARNINGS |
| 9 | root@localhost on using Socket |
| 9 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| 9 | SHOW WARNINGS |
| 9 | START TRANSACTION WITH CONSISTENT SNAPSHOT |
| 9 | SHOW WARNINGS |
| 7 | UNLOCK TABLES |
| 7 | SHOW WARNINGS |
| 9 | SET SQL_QUOTE_SHOW_CREATE= 1 |
| 9 | SHOW WARNINGS |
| 9 | SET TIME_ZONE='+00:00' |
| 8 | SET SQL_QUOTE_SHOW_CREATE= 1 |
| 8 | SHOW WARNINGS |
| 8 | SET TIME_ZONE='+00:00' |
| 3 | set global general_log = 0 |
+-----------+------------------------------------------------------------------+
根據(jù)上面信息,可以看出:
- 線程7 進(jìn)行 FLUSH TABLES WITH READ LOCK 。對表加一個讀鎖
- 線程7、8、9分別開啟一個事務(wù)(RR隔離級別)去備份數(shù)據(jù),由于之前鎖表了,所以這三個線程備份出的數(shù)據(jù)是具有一致性的
- 線程7 解鎖 UNLOCK TABLE
- 整個過程都沒有獲取二進(jìn)制位置點(diǎn)
-? ?compress-output
mysqlpump支持壓縮輸出,支持LZ4和ZLIB(ZLIB壓縮比相對較高,但是速度較慢)
[root@localhost tmp]# mysqlpump --single-transaction --compress-output=lz4 kevin --set-gtid-purged=OFF -p123456 > /tmp/backup_kevin.sql
Dump completed in 511 milliseconds
-? ?備份恢復(fù)
未壓縮的備份
mysql < source /tmp/backup.sql;
壓縮過的備份
先解壓
[root@localhost ~]# lz4_decompress /tmp/backup_kevin.sql /tmp/kevin.sql
再導(dǎo)入
mysql < source /tmp/kevin.sql;
可以看出來,這個導(dǎo)入是單線程。mysqlpump備份的數(shù)據(jù)恢復(fù)時會先插入數(shù)據(jù), 再建索引, 而mysqldump備份的數(shù)據(jù)恢復(fù)是在建立表的時候就把索引加上了, 所以前者備份的數(shù)據(jù)恢復(fù)時速度要快一點(diǎn)!
總體來說mysqlpump還是很好用的,尤其是多數(shù)據(jù)庫表的備份。不過如果有一張表格外大,那么備份的大部分時間還是要消耗在這張表上,因?yàn)閙ysqlpump的備份是基于表并行的,對于每張表的導(dǎo)出只能是單個線程的。另外注意mysqlpump備份時并發(fā)線程的數(shù)量還是要看自身服務(wù)器的IO負(fù)載能力,并不是說一味的增加并發(fā)線程數(shù)量就可以加快速度。mysqldump和mysqlpump的使用方法絕大部分一致,mysqlpump新的參數(shù)文章上已經(jīng)標(biāo)明,到底用那種工具備份數(shù)據(jù)庫這個要在具體的環(huán)境下才能做出選擇,有些時候可能用物理備份更好(xtrabackup),總之根據(jù)需要進(jìn)行測試,最后再決定使用哪種備份工具進(jìn)行備份。
總結(jié)
以上是生活随笔為你收集整理的mysql 5.7 差异备份_MySQL 5.7 新备份工具mysqlpump 使用说明 - 运维小结的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: centos上如何装python_cen
- 下一篇: java趣味_Java趣味分享:try