您好,登錄后才能下訂單哦!
Mysql 5.7 之后多了一個(gè)備份工具, mysqlpump
mysqlpump客戶端 執(zhí)行的是 邏輯備份
Mysqlpump 新特性
并行執(zhí)行 數(shù)據(jù)庫(kù)和其中的對(duì)象,加快轉(zhuǎn)儲(chǔ)過(guò)程
更好的控制哪些數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象 來(lái)轉(zhuǎn)儲(chǔ)導(dǎo)出
導(dǎo)出用戶賬號(hào) 作為賬號(hào)管理語(yǔ)句( create user , grant ),而不是插入到mysql系統(tǒng)數(shù)據(jù)庫(kù)中
備份出來(lái)直接生成壓縮備份文件
備份進(jìn)度指標(biāo)(估計(jì)值 )
轉(zhuǎn)儲(chǔ)文件加載(還原),先建表后插入數(shù)據(jù),最后建立索引,減少索引的維護(hù)開(kāi)銷,加快還原速度 .
備份可以排除或指定數(shù)據(jù)庫(kù)
mysqlpump 需要的 權(quán)限, 不同的選項(xiàng),需要不同的權(quán)限,可以在選項(xiàng)說(shuō)明中查看。
功能 |
至少需要的權(quán)限 |
導(dǎo)出表 |
select 對(duì)應(yīng)表權(quán)限 |
導(dǎo)出視圖 |
show view 對(duì)應(yīng)視圖權(quán)限 |
導(dǎo)出存儲(chǔ)過(guò)程 |
trigger 對(duì)應(yīng)存儲(chǔ)過(guò)程權(quán)限 |
--single-transaction 選項(xiàng)沒(méi)有使用時(shí) |
LOCK TABLES |
導(dǎo)出用戶定義 |
Select mysql 系統(tǒng)庫(kù)的權(quán)限 |
|
|
加載dump文件,必須有執(zhí)行 dump文件所包含的語(yǔ)句 的權(quán)限,如 create 等。
NOTE
在windows上使用powershell 導(dǎo)出時(shí),重定向到一個(gè)新建文件,會(huì)使用 utf-16 編碼,這會(huì)導(dǎo)致錯(cuò)誤,因?yàn)镸ySQL 連接字符集不支持utf-16 .
shell> mysqlpump [ options ] > dump . sql 錯(cuò)誤
可以使用 --result -file 選項(xiàng),來(lái)輸出到 ASCII 格式的文件上。
shell> mysqlpump [ options ] --result-file = dump.sql 正確
Mysqldump 調(diào)用語(yǔ)法
shell> mysqlpump --all-databases
shell> mysqlpump db_name
shell> mysqlpump db_name tbl_name1 tbl_name2 ...
導(dǎo)出指定的多個(gè)庫(kù)
shell> mysqlpump --databases db_name1 db_name2 ...
默認(rèn)情況, mysql p ump 不導(dǎo)出用戶賬戶定義,即使你導(dǎo)出含有授權(quán)表的 mysql 系統(tǒng)庫(kù)。要以邏輯定義( create user 和 grant )形式導(dǎo)出授權(quán)表,使用 --users 選項(xiàng) 并且禁止所有數(shù)據(jù)庫(kù)轉(zhuǎn)儲(chǔ)。
shell> mysqlpump --exclude-databases = % --users
這里的 % 是個(gè)通配符,他匹配所有的庫(kù), --exclude-database=% 即排除所有的庫(kù)
Mysqlpump 支持幾個(gè)選項(xiàng),包含或排除數(shù)據(jù)庫(kù)、表、存儲(chǔ)過(guò)程、用戶定義。看 mysqlpump object selection ,
要加載轉(zhuǎn)儲(chǔ)文件,執(zhí)行它包含的語(yǔ)句,如下:
shell> mysqlpump [ options ] > dump . sql
shell> mysql < dump . sql
Mysqlpump 選項(xiàng)概要
mysqlpump 支持命令行指定選項(xiàng),也可以在參數(shù)文件的 [mysqlpump] and [client] 的組中指定???
Section 4.2.6, “Using Option Files” .
Table 4.15 mysqlpump Options
參數(shù)絕大多數(shù)和 mysqldump 一致 , 對(duì)于 mysqlpump 參數(shù)會(huì)用背景色 標(biāo)記出來(lái)。
Format |
Description |
Introduced |
--add-drop-database |
Add DROP DATABASE statement before each CREATE DATABASE statement 在建庫(kù)之前,先執(zhí)行刪除庫(kù)操作 DROP DATABASE IF EXISTS `...`; |
|
--add-drop-table |
Add DROP TABLE statement before each CREATE TABLE statement 在建表之前先執(zhí)行刪表操作。 DROP TABLE IF EXISTS `...`.`...`; |
|
--add-drop-user |
Add DROP USER statement before each CREATE USER statement 在CREATE USER語(yǔ)句之前增加DROP USER, 注意: 這個(gè)參數(shù)需要和 --users 一起使用,否者不生效。 DROP USER 'backup' @ '192.168.123.%' ; |
|
--add-locks |
Surround each table dump with LOCK TABLES and UNLOCK TABLES statements 備份表時(shí),使用LOCK TABLES和UNLOCK TABLES。 注意: 這個(gè)參數(shù)不支持并行備份,需要關(guān)閉并行備份功能: --default-parallelism =0
LOCK TABLES `...`.`...` WRITE;
|
|
--all-databases |
Dump all databases 備份所有庫(kù),-A。 |
|
--bind-address |
Use specified network interface to connect to MySQL Server 指定通過(guò)哪個(gè)網(wǎng)絡(luò)接口來(lái)連接 Mysql 服務(wù)器(一臺(tái)服務(wù)器可能有多個(gè) IP ),防止同一個(gè)網(wǎng)卡出去影響業(yè)務(wù)。 |
|
--character-sets-dir |
Directory where character sets are installed
|
|
--complete-insert |
Use complete INSERT statements that include column names dump 出包含所有列的完整 insert 語(yǔ)句。 |
|
--compress |
Compress all information sent between client and server 在客戶端和服務(wù)器傳輸?shù)乃械臄?shù)據(jù)包壓縮,最后的備份集大小沒(méi)有任何改變,-C。 若要改變備份集大?。? compress=true |gzip不過(guò)時(shí)間,會(huì)用到 5 倍于 compress= false 會(huì)幾倍于 --compress-output呢? |
|
--compress-output |
Output compression algorithm 默認(rèn)不壓縮輸出,目前可以使用的壓縮算法有LZ4和ZLIB。
shell> mysqlpump --compress-output=LZ4 > dump.lz4
shell> mysqlpump --compress-output=ZLIB > dump.zlib
|
|
--databases |
Interpret all name arguments as database names 手動(dòng)指定要備份的庫(kù),支持多個(gè)數(shù)據(jù)庫(kù),用空格分隔,-B。 |
|
--debug |
Write debugging log |
|
--debug-check |
Print debugging information when program exits |
|
--debug-info |
Print debugging information, memory, and CPU statistics when program exits |
|
--default-auth |
Authentication plugin to use |
|
--default-character-set |
Specify default character set 指定備份的字符集。 |
|
--default-parallelism |
Default number of threads for parallel processing 指定并行線程數(shù),默認(rèn)是2,如果設(shè)置成0,表示不使用并行備份。 注意: 每個(gè)線程的備份步驟是:先create table但不建立二級(jí)索引(主鍵會(huì)在create table時(shí)候建立),再寫(xiě)入數(shù)據(jù),最后建立二級(jí)索引。 |
|
--defaults-extra-file |
Read named option file in addition to usual option files |
|
--defaults-file |
Read only named option file |
|
--defaults-group-suffix |
Option group suffix value |
|
--defer-table-indexes |
For reloading, defer index creation until after loading table rows 延遲創(chuàng)建索引,直到所有數(shù)據(jù)都加載完之后,再創(chuàng)建索引,默認(rèn)開(kāi)啟。若關(guān)閉則會(huì)和 mysqldump 一樣:先創(chuàng)建一個(gè)表和所有索引,再導(dǎo)入數(shù)據(jù),因?yàn)樵诩虞d還原數(shù)據(jù)的時(shí)候要維護(hù)二級(jí)索引的開(kāi)銷,導(dǎo)致效率比較低。關(guān)閉使用參數(shù): --skip--defer-table-indexes 。 |
|
--events |
Dump events from dumped databases 備份數(shù)據(jù)庫(kù)的事件,默認(rèn)開(kāi)啟,關(guān)閉使用--skip-events參數(shù)。 |
|
--exclude-databases |
Databases to exclude from dump 備份排除該參數(shù)指定的數(shù)據(jù)庫(kù),多個(gè)用逗號(hào)分隔。類似的還有 --exclude-events 、 --exclude-routines 、 --exclude-tables 、 --exclude-triggers 、 --exclude-users 。 mysqlpump --exclude-databases =mysql,sys # 備份過(guò)濾 mysql 和 sys 數(shù)據(jù)庫(kù) mysqlpump --exclude-tables =rr,tt # 備份過(guò)濾所有數(shù)據(jù)庫(kù)中 rr 、 tt 表
mysqlpump -B test --exclude-tables
=tmp_ifulltext,tt #
備份過(guò)濾
test
庫(kù)中的
rr
、
tt
表
注意: 要是只備份數(shù)據(jù)庫(kù)的賬號(hào),需要添加參數(shù) --users ,并且需要過(guò)濾掉所有的數(shù)據(jù)庫(kù),如: mysqlpump --users --exclude-databases = % --exclude-users =dba,backup # 備份除 dba 和 backup 的所有賬號(hào)。
|
|
--exclude-events |
Events to exclude from dump |
|
--exclude-routines |
Routines to exclude from dump |
|
--exclude-tables |
Tables to exclude from dump |
|
--exclude-triggers |
Triggers to exclude from dump |
|
--exclude-users |
Users to exclude from dump |
|
--extended-insert |
Use multiple-row INSERT syntax |
|
--get-server-public-key |
Request RSA public key from server |
5.7.23 |
--help |
Display help message and exit |
|
--hex-blob |
Dump binary columns using hexadecimal notation 備份binary字段的時(shí)候使用十六進(jìn)制計(jì)數(shù)法,受影響的字段類型有BINARY、VARBINARY、BLOB、BIT。 |
|
--host |
Host to connect to (IP address or hostname) 備份指定的數(shù)據(jù)庫(kù)地址, -h 。 |
|
--include-databases |
Databases to include in dump 指定備份數(shù)據(jù)庫(kù),多個(gè)用逗號(hào)分隔,類似的還有 --include-events 、 --include-routines 、 --include-tables 、 --include-triggers 、 --include-users ,大致方法使用同 15 。 |
|
--include-events |
Events to include in dump |
|
--include-routines |
Routines to include in dump |
|
--include-tables |
Tables to include in dump |
|
--include-triggers |
Triggers to include in dump |
|
--include-users |
Users to include in dump |
|
--insert-ignore |
Write INSERT IGNORE rather than INSERT statements 備份用insert ignore語(yǔ)句代替insert語(yǔ)句。 |
|
--log-error-file |
Append warnings and errors to named file 備份出現(xiàn)的 warnings 和 erros 信息輸出到一個(gè)指定的文件。 |
|
--login-path |
Read login path options from .mylogin.cnf
|
|
--max-allowed-packet |
Maximum packet length to send to or receive from server 備份時(shí)用于client/server直接通信的最大buffer包的大小。 |
|
--net-buffer-length |
Buffer size for TCP/IP and socket communication 備份時(shí)用于 client/server 通信的初始 buffer 大小,當(dāng)創(chuàng)建多行插入語(yǔ)句的時(shí)候, mysqlpump 創(chuàng)建行到 N 個(gè)字節(jié)長(zhǎng)。 |
|
--no-create-db |
Do not write CREATE DATABASE statements 備份不寫(xiě)CREATE DATABASE語(yǔ)句。要是備份多個(gè)庫(kù),需要使用參數(shù)-B,而使用-B的時(shí)候會(huì)出現(xiàn)create database語(yǔ)句,該參數(shù)可以屏蔽create database 語(yǔ)句。 |
|
--no-create-info |
Do not write CREATE TABLE statements that re-create each dumped table 備份不寫(xiě)建表語(yǔ)句,即不備份表結(jié)構(gòu),只備份數(shù)據(jù), -t 。 |
|
--no-defaults |
Read no option files |
|
--parallel-schemas |
Specify schema-processing parallelism 指定并行備份的庫(kù),多個(gè)庫(kù)用逗號(hào)分隔,如果指定了N,將使用N個(gè)線程的地隊(duì)列,如果N不指定,將由 --default-parallelism才確認(rèn)N的值,可以設(shè)置多個(gè) --parallel-schemas 。 mysqlpump --parallel-schemas=4:vs,aa --parallel-schemas=3:pt #4 個(gè)線程備份 vs 和 aa , 3 個(gè)線程備份 pt 。通過(guò) show processlist 可以看到有 7 個(gè)線程。 mysqlpump --parallel-schemas=vs,abc --parallel-schemas=pt # 默認(rèn) 2 個(gè)線程,即 2 個(gè)線程備份 vs 和 abc , 2 個(gè)線程備份 pt #### 當(dāng)然要是硬盤 IO 不允許的話,可以少開(kāi)幾個(gè)線程和數(shù)據(jù)庫(kù)進(jìn)行并行備份 |
|
--password |
Password to use when connecting to server |
|
--plugin-dir |
Directory where plugins are installed |
|
--port |
TCP/IP port number for connection |
|
--print-defaults |
Print default options |
|
--protocol |
Connection protocol to use {TCP|SOCKET|PIPE|MEMORY} :指定連接服務(wù)器的協(xié)議。 |
|
--replace |
Write REPLACE statements rather than INSERT statements 備份出來(lái) replace into 語(yǔ)句。 |
|
--result-file |
Direct output to a given file |
|
--routines |
Dump stored routines (procedures and functions) from dumped databases 備份出來(lái)包含存儲(chǔ)過(guò)程和函數(shù),默認(rèn)開(kāi)啟, 需要對(duì) mysql.proc表有查看權(quán)限 。生成的文件中會(huì)包含CREATE PROCEDURE 和 CREATE FUNCTION語(yǔ)句以用于恢復(fù),關(guān)閉則需要用--skip-routines參數(shù)。 |
|
--secure-auth |
Do not send passwords to server in old (pre-4.1) format |
|
--server-public-key-path |
Path name to file containing RSA public key |
5.7.23 |
--set-charset |
Add SET NAMES default_character_set to output 備份文件里寫(xiě)SET NAMES default_character_set 到輸出,此參默認(rèn)開(kāi)啟。 -- skip-set-charset禁用此參數(shù),不會(huì)在備份文件里面寫(xiě)出set names... |
|
--set-gtid-purged |
Whether to add SET @@GLOBAL.GTID_PURGED to output |
5.7.18 |
--single-transaction |
Dump tables within single transaction 該參數(shù)在事務(wù)隔離級(jí)別設(shè)置成 Repeatable Read ,并在 dump 之前發(fā)送 start transaction 語(yǔ)句給服務(wù)端。這在使用 innodb 時(shí)很有用,因?yàn)樵诎l(fā)出 start transaction 時(shí),保證了在不阻塞任何應(yīng)用下的一致性狀態(tài)。對(duì) myisam 和 memory 等非事務(wù)表,還是會(huì)改變狀態(tài)的,當(dāng)使用此參的時(shí)候要確保沒(méi)有其他連接在使用 ALTER TABLE 、 CREATE TABLE 、 DROP TABLE 、 RENAME TABLE 、 TRUNCATE TABLE 等語(yǔ)句,否則會(huì)出現(xiàn)不正確的內(nèi)容或則失敗。 --add-locks 和此參互斥,在 mysql5.7.11 之前, --default-parallelism 大于 1 的時(shí)候和此參也互斥,必須使用 --default-parallelism=0 。 5.7.11 之后解決了 --single-transaction 和 --default-parallelism 的互斥問(wèn)題。 |
|
--skip-definer |
Omit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements 忽略那些創(chuàng)建視圖和存儲(chǔ)過(guò)程用到的 DEFINER 和 SQL SECURITY 語(yǔ)句,恢復(fù)的時(shí)候,會(huì)使用默認(rèn)值,否則會(huì)在還原的時(shí)候看到?jīng)]有DEFINER定義時(shí)的賬號(hào)而報(bào)錯(cuò)。 |
|
--skip-dump-rows |
Do not dump table rows 只備份表結(jié)構(gòu),不備份數(shù)據(jù),-d。 注意: mysqldump支持--no-data,mysqlpump不支持--no-data |
|
--socket |
For connections to localhost, the Unix socket file to use |
|
--ssl |
Enable encrypted connection --ssl 參數(shù)將要被去除,用 --ssl-mode 取代。關(guān)于 ssl 相關(guān)的備份,請(qǐng)看 官方文檔 。 |
|
--ssl-ca |
File that contains list of trusted SSL Certificate Authorities |
|
--ssl-capath |
Directory that contains trusted SSL Certificate Authority certificate files |
|
--ssl-cert |
File that contains X.509 certificate |
|
--ssl-cipher |
List of permitted ciphers for connection encryption |
|
--ssl-crl |
File that contains certificate revocation lists |
|
--ssl-crlpath |
Directory that contains certificate revocation list files |
|
--ssl-key |
File that contains X.509 key |
|
--ssl-mode |
Security state of connection to server |
5.7.11 |
--ssl-verify-server-cert |
Verify host name against server certificate Common Name identity |
|
--tls-version |
Protocols permitted for encrypted connections |
5.7.10 |
--triggers |
Dump triggers for each dumped table 備份出來(lái)包含觸發(fā)器,默認(rèn)開(kāi)啟,使用 --skip-triggers 來(lái)關(guān)閉。 |
|
--tz-utc |
Add SET TIME_ZONE='+00:00' to dump file |
|
--user |
MySQL user name to use when connecting to server . -u |
|
--users |
Dump user accounts 備份數(shù)據(jù)庫(kù)用戶,備份的形式是CREATE USER...,GRANT...,只備份數(shù)據(jù)庫(kù)賬號(hào)可以通過(guò)如下命令: mysqlpump --exclude-databases=% --users # 過(guò)濾掉所有數(shù)據(jù)庫(kù) |
|
--version |
Display version information and exit |
5.7.9 |
--watch-progress |
Display progress indicator 定期顯示進(jìn)度的完成,包括總數(shù)表、行和其他對(duì)象。該參數(shù)默認(rèn)開(kāi)啟,用 --skip-watch-progress 來(lái)關(guān)閉。 |
|
不支持的參數(shù)
--flush-logs --flush-privileges 看來(lái) 5.7 不需要導(dǎo)出時(shí)做這些動(dòng)作了
--master-data --dump-slave 沒(méi)有這個(gè)怎么搭建從庫(kù)呢
使用說(shuō)明:
mysqlpump的架構(gòu)如下圖所示 :
mysqlpump支持基于庫(kù)和表的并行導(dǎo)出,mysqlpump的并行導(dǎo)出功能的架構(gòu)為:隊(duì)列+線程,允許有多個(gè)隊(duì)列( --parallel-schemas ?),每個(gè)隊(duì)列下有多個(gè)線程(N?),而一個(gè)隊(duì)列可以綁定1個(gè)或者多個(gè)數(shù)據(jù)庫(kù)(逗號(hào)分隔)。 mysqlpump的備份是基于表并行的,對(duì)于每張表的導(dǎo)出只能是單個(gè)線程的 ,這里會(huì)有個(gè)限制是如果某個(gè)數(shù)據(jù)庫(kù)有一張表非常大,可能大部分的時(shí)間都是消耗在這個(gè)表的備份上面,并行備份的效果可能就不明顯。這里可以利用 mydumper 其是以chunk的方式批量導(dǎo)出,即 mydumper支持一張表多個(gè)線程以chunk的方式批量導(dǎo)出 。但是相對(duì)于mysqldump還是有了很大的提升。這里大致測(cè)試下mysqlpump和mysqldump的備份效率。
#mysqlpump 壓縮備份 vs 數(shù)據(jù)庫(kù) 三個(gè)并發(fā)線程備份,消耗時(shí)間: 222s
mysqlpump -uzjy -p -h292.168.123.70 --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B vs > /home/zhoujy/vs_db.sql.lz4
#mysqldump 備份壓縮 vs 數(shù)據(jù)庫(kù) 單個(gè)線程備份,消耗時(shí)間: 900s , gzip 的壓縮率比 LZ4 的高
mysqldump -uzjy -p -h292.168.123.70 --default-character-set=utf8 -P3306 --skip-opt --add-drop-table --create-options --quick --extended-insert --single-transaction -B vs | gzip > /home/zhoujy/vs.sql.gz
#mydumper 備份 vs 數(shù)據(jù)庫(kù) 三個(gè)并發(fā)線程備份,消耗時(shí)間: 300s , gzip 的壓縮率比 LZ4 的高
mydumper -u zjy -p -h 192.168.123.70 -P 3306 -t 3 -c -l 3600 -s 10000000 -B vs -o /home/zhoujy/vs/
#mydumper 備份 vs 數(shù)據(jù)庫(kù),五個(gè)并發(fā)線程備份,并且開(kāi)啟對(duì)一張表多個(gè)線程以 chunk 的方式批量導(dǎo)出, -r 。消耗時(shí)間: 180s
mydumper -u zjy -p -h 192.168.123.70 -P 3306 -t 5 -c -r 300000 -l 3600 -s 10000000 -B vs -o /home/zhoujy/vs/
從上面看出,mysqlpump的備份效率是最快的,mydumper次之,mysqldump最差。所以在IO允許的情況下,能用多線程就別用單線程備份。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。