MySQL 8.0 mysqldump 详解
作者公眾號(hào),歡迎一起交流。
mysqldump命令是MySQL自帶的客戶端程序,可用于對(duì)數(shù)據(jù)庫進(jìn)行備份,由于是邏輯備份,故可以看到備份的內(nèi)容是有一系列的SQL語句組成,并可根據(jù)實(shí)際情況對(duì)備份內(nèi)容進(jìn)行修改。當(dāng)然,使用mysqldump進(jìn)行數(shù)據(jù)庫備份,需要對(duì)應(yīng)的權(quán)限,比如備份表,至少需要對(duì)表的select權(quán)限,備份視圖,需要對(duì)視圖的show view權(quán)限,同樣的,將備份重新加載進(jìn)數(shù)據(jù)庫,也需要相應(yīng)的權(quán)限,本篇將對(duì)mysqldump命令進(jìn)行一個(gè)詳細(xì)的介紹。
1 語法結(jié)構(gòu)
使用mysqldump進(jìn)行數(shù)據(jù)庫備份有三種方法,默認(rèn)時(shí)將導(dǎo)出的SQL語句輸出到屏幕,可將其保存到文件中,用法分別是:
Usage: mysqldump [OPTIONS] database [tables]OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]OR mysqldump [OPTIONS] --all-databases [OPTIONS]1.1 mysqldump [OPTIONS] database [tables]
用于導(dǎo)出某個(gè)數(shù)據(jù)庫或表,在導(dǎo)出的腳本中,沒有create database和use語句,在重新導(dǎo)入時(shí),可指定數(shù)據(jù)庫,若沒有數(shù)據(jù)庫,則需要提前進(jìn)行創(chuàng)建。
1.2 mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
用于導(dǎo)出特定的數(shù)據(jù)庫,--databses選項(xiàng)指定后面所導(dǎo)出的數(shù)據(jù)庫列表,以空格分割,在每個(gè)數(shù)據(jù)庫導(dǎo)出之前,在導(dǎo)出的腳本中會(huì)寫create database和use語句,用于數(shù)據(jù)庫的創(chuàng)建和指定要操作的庫,在導(dǎo)入時(shí)如果數(shù)據(jù)庫不存在,則會(huì)進(jìn)行創(chuàng)建。
1.3 mysqldump [OPTIONS] --all-databases [OPTIONS]
用于導(dǎo)出所有數(shù)據(jù)庫,默認(rèn)不會(huì)導(dǎo)出performance_schema、sys,也不會(huì)導(dǎo)出information_schema,若需要導(dǎo)出時(shí),需指定--databases選項(xiàng)。
2 選項(xiàng)說明
mysqldump的選項(xiàng)有很多,可使用mysqldump --help進(jìn)行查看,選項(xiàng)有兩種格式,一種是長格式,一種是短格式,這里將對(duì)選項(xiàng)進(jìn)行說明。
2.1 幫助選項(xiàng)
使用mysqldump時(shí),可通過以下選項(xiàng)展示幫助信息:
- -?, --help:展示幫助信息
- -V, --version:展示版本信息
2.2 數(shù)據(jù)庫連接選項(xiàng)
要dump數(shù)據(jù),需要先連接到數(shù)據(jù)庫,那么對(duì)應(yīng)的選項(xiàng)分別如下:
- --bind-address=name:當(dāng)服務(wù)器有多個(gè)網(wǎng)卡時(shí),使用該選項(xiàng)綁定要連接MySQL服務(wù)器的網(wǎng)卡
- -C,--compress:壓縮客戶端和服務(wù)器間發(fā)送的信息,MySQL 8.0.18后廢棄該選項(xiàng)
- --compress-algorithms=name:MySQL 8.0.18新增,指定壓縮的算法,默認(rèn)是uncompressed,壓縮算法包括:zstd、ziib、uncompressed
- --default-auth=name:客戶端使用的默認(rèn)認(rèn)證插件
- --enable-cleartext-plugin:激活/禁用mysql_clear_password認(rèn)證插件
- -h,--host=name:指定連接的主機(jī)名或IP,默認(rèn)主機(jī)是localhost
- --login-path=#:在.mylogin.cnf登錄路徑文件讀取選項(xiàng),創(chuàng)建或修改登錄文件,需使用mysql_config_editor工具
- -p,--password[=name]:指定連接數(shù)據(jù)庫的密碼
- --plugin-dir=name:指定客戶端查看的目錄
- -P,--port=#:使用TCP/IP連接時(shí)指定數(shù)據(jù)庫端口
- --skip-mysql-schema:MySQL 8.0.27引入,在還原dump文件時(shí),不drop mysql schema,默認(rèn)情況下,drop mysql schema
- -S,--socket=name:若連接localhost,需指定該選項(xiàng),指定使用Unix socket進(jìn)行連接
- -u,--user=name:指定連接數(shù)據(jù)庫的用戶名
- --zstd-compression-level=#:使用zstd壓縮算法時(shí),指定壓縮級(jí)別,默認(rèn)是3,可設(shè)置1-22之間的某一個(gè)值
- --ssl*:這里暫不介紹
2.3 選項(xiàng)文件選項(xiàng)
在使用mysqldump時(shí),可指定選項(xiàng)文件來控制使用哪些選項(xiàng):
- --defaults-extra-file=#:在讀取全局選項(xiàng)文件后,讀取該選項(xiàng)文件
- --defaults-file=#:只讀取給定的選項(xiàng)文件
- --defaults-group-suffix=#:不僅讀取常規(guī)選項(xiàng)組,還讀取以字串為前綴的常規(guī)名字命名的組
- --no-defaults:指定該選項(xiàng)時(shí),不會(huì)讀取任何選項(xiàng)文件
- --print-defaults:讀取程序名稱以及選項(xiàng)文件中的所有選項(xiàng)
2.4 DDL相關(guān)選項(xiàng)
使用mysqldump時(shí),和DDL相關(guān)的選項(xiàng)如下:
- --add-drop-database:導(dǎo)出時(shí)在每個(gè)create語句前增加drop database語句
- --add-drop-table:默認(rèn)ON,導(dǎo)出時(shí)在每個(gè)create語句前增加drop table語句
- --add-drop-trigger:導(dǎo)出時(shí)在每個(gè)create語句前增加drop trigger語句
- -Y,--all-tablespaces:導(dǎo)出所有的表空間,僅用于NDB Cluster表
- -n,--no-create-db:指定--all-databases或--databases時(shí)抑制create database語句輸出
- -t,--no-create-info:導(dǎo)出時(shí)不輸出表創(chuàng)建信息,即只導(dǎo)出數(shù)據(jù),不導(dǎo)出表定義
- -y,--no-tablespaces:導(dǎo)出時(shí)不輸出所有的create logfile group和create tablespace語句
- --replace:導(dǎo)出時(shí)使用replace into代替insert into
2.5 數(shù)據(jù)庫過濾選項(xiàng)
使用mysqldump時(shí),可通過選項(xiàng)對(duì)導(dǎo)出的對(duì)象進(jìn)行過濾:
- -A,--all-databases:導(dǎo)出所有數(shù)據(jù)庫下的所有表
- -B,--databases:導(dǎo)出指定的數(shù)據(jù)庫
- -E,--events:導(dǎo)出events
- --ignore-error=name:指定導(dǎo)出遇到錯(cuò)誤時(shí)忽略的錯(cuò)誤號(hào)碼
- --ignore-table=name:導(dǎo)出時(shí)排除導(dǎo)出的表,若排除多個(gè)表,則需指定多次,每次指定一個(gè)表,例如:--ignore-table=db.table
- -d,--no-data:導(dǎo)出時(shí)不導(dǎo)出數(shù)據(jù)行,即只導(dǎo)出表定義,不導(dǎo)出數(shù)據(jù)
- -R,--routines:導(dǎo)出存儲(chǔ)過程和函數(shù)
- --tables:覆蓋--databases或-B選項(xiàng),導(dǎo)出表
- --triggers:導(dǎo)出觸發(fā)器,默認(rèn)和表一起導(dǎo)出
- -w,--where=name:導(dǎo)出滿足條件的數(shù)據(jù)行,需用雙引號(hào)引起條件
2.6 復(fù)制選項(xiàng)
使用mysqldump時(shí),可創(chuàng)建空的實(shí)例,或者用于創(chuàng)建復(fù)制環(huán)境:
- --apply-replica-statements:MySQL 8.0.26引入,和--dump-replica一起,導(dǎo)出時(shí)在語句之前增加stop replica語句,并在最后增加start replica語句
- --delete-source-logs:MySQL 8.0.26引入,備份之前刷新日志,相當(dāng)于執(zhí)行flush logs,執(zhí)行dump操作之后通過發(fā)送perge binary logs刪除binlog,該選項(xiàng)自動(dòng)激活--source-data選項(xiàng)
- --dump-replica[=#]:導(dǎo)出時(shí)輸出中包含binlog文件名和位置,指定1,則直接以change replication source to輸出,指定2,以備注的形式輸出
- --include-source-host-port:和--dump-replica一起導(dǎo)出時(shí)在change replication source to后增加master_host=<host>,master_port=<port>
- --source-data[=#]:導(dǎo)出時(shí)輸出中包含binlog文件名和位置,指定1,則直接以change replication source to輸出,指定2,以備注的形式輸出
- --set-gtid-purged[=name]:輸出中增加SET @@GLOBAL.GTID_PURGED,默認(rèn)值是AUTO,該選項(xiàng)包含的值分別是ON、COMMENTED、OFF和AUTO
2.7 格式選項(xiàng)
使用mysqldump時(shí),可通過以下選項(xiàng)對(duì)輸出內(nèi)容進(jìn)行格式化:
- --compact:控制輸出更少的信息,該選項(xiàng)啟用--skip-add-drop-table、--skip-add-locks、--skip-comments、--skip-disable-keys和--skip-set-character選項(xiàng)
- --compatible=name:兼容其它數(shù)據(jù)庫或舊版本的MySQL服務(wù)器,唯一允許的值是ANSI
- -c,--complete-insert:導(dǎo)出時(shí)使用完整的insert語句
- -a,--create-options:默認(rèn)ON,導(dǎo)出時(shí)包括create table語句中所有的表選項(xiàng)
- --fields-terminated-by=name、--fields-enclosed-by=name、--fields-optionally-enclosed、--fields-escaped-by=name:使用--tab選項(xiàng)時(shí)指定,和使用load data時(shí)相關(guān)的fields有同樣的作用
- --hex-blob:以16進(jìn)制打印binary字符串(binary、var binary、blob)
- --lines-terminated-by=name:導(dǎo)出時(shí)行分隔符,使用--tab選項(xiàng)時(shí)指定
- -Q,--quote-names:默認(rèn)ON,導(dǎo)出時(shí)使用·來括住表和列名,可使用--skip-quote-names禁用
- -r, --result-file=name:直接輸出到指定的文件,用于Windows對(duì)換行的處理
- -T,--tab=name:創(chuàng)建tab分割的文本文件,表定義和數(shù)據(jù)分開存放,分別創(chuàng)建tb1_name.sql和tb1_name.txt文件
- --tz-utc:默認(rèn)ON,該選項(xiàng)可使timestamp列dump和加載進(jìn)不同時(shí)區(qū)的服務(wù)器
- -X,--xml:以xml格式導(dǎo)出數(shù)據(jù)庫
2.8 性能選項(xiàng)
使用mysqldump時(shí),可通過以下選項(xiàng)對(duì)恢復(fù)時(shí)的性能進(jìn)行控制:
- --column-statistics:在輸出中添加analyze table語句,用于加載時(shí)搜集統(tǒng)計(jì)信息
- -K,--disable-keys:默認(rèn)ON,該選項(xiàng)只對(duì)MyISAM表的非唯一索引有效
- -e,--extended-insert:默認(rèn)ON,導(dǎo)出時(shí)使用包含多個(gè)VALUES列表的多行insert語句
- --insert-ignore:導(dǎo)出時(shí)使用insert ignore插入行
- --max-allowed-packet=#:默認(rèn)24M,最大1G,用于在客戶端/服務(wù)器通信的最大緩沖區(qū)
- --net-buffer-length=#:客戶端/服務(wù)器通信的初始緩沖區(qū)大小
- -M, --network-timeout:默認(rèn)ON
- --opt:默認(rèn)ON,相當(dāng)于--add-drop-table、--add-locks、--create-options、--quick、--extended-insert、--lock-tables、--set-character和--disable-keys,可使用--skip-opt禁用
- -q, --quick:用于dump大表,用于逐行檢索數(shù)據(jù),而不是檢索整個(gè)行集并緩存到內(nèi)存再dump
2.9 事務(wù)選項(xiàng)
使用mysqldump時(shí),可通過如下選項(xiàng)做事務(wù)相關(guān)的設(shè)置:
- --add-locks:默認(rèn)ON,導(dǎo)出時(shí)對(duì)每個(gè)表使用lock tables和unlocktables包著,加載時(shí)可更快
- -F, --flush-logs:在開始dump之前刷新MySQL服務(wù)器日志文件,如果使用--databases或--all-databases選項(xiàng),則每dump一個(gè)數(shù)據(jù)庫都會(huì)刷新一次日志;如果只想刷新一次,可以將該選項(xiàng)與--lock-all-tables、--source-data或--master-data或--single-transaction一起使用
- --flush-privileges:dump mysql數(shù)據(jù)庫后在輸出文件中增加flush privileges語句
- -x,--lock-all-tables:通過在整個(gè)dump期間獲取全局讀鎖來實(shí)現(xiàn)鎖定所有數(shù)據(jù)庫中的所有表,該選項(xiàng)自動(dòng)關(guān)閉--single-transaction和--lock-tables
- -l,--lock-tables:默認(rèn)ON,對(duì)于每個(gè)被dump的數(shù)據(jù)庫,在dump前鎖定所有的表,對(duì)于InnoDB表,使用--single-transaction更優(yōu)
- --no-autocommit:將每個(gè)dump的表的insert語句封裝在set autocommit=0和commit語句之間
- --order-by-primary:導(dǎo)出時(shí)以主鍵或唯一鍵排序,主要用于導(dǎo)出MyISAM表至InnoDB表,但比較耗時(shí)
- --single-transaction:該選項(xiàng)將事務(wù)隔離級(jí)別設(shè)置為repeatable read,并在dump之前向服務(wù)器發(fā)送一條start transaction語句。只對(duì)事務(wù)表(InnoDB)有用,因?yàn)樗鼤?huì)在發(fā)出start transaction時(shí)dump數(shù)據(jù)庫的一致狀態(tài),而不會(huì)阻塞任何應(yīng)用程序;--single-transaction和--lock-tables選項(xiàng)是互斥的,因?yàn)閘ock tables會(huì)導(dǎo)致任何掛起的事務(wù)被隱式提交;要dump大表,可以將選項(xiàng)--single-transaction和--quick結(jié)合使用
2.10 國際化選項(xiàng)
使用mysqldump時(shí),可通過以下選項(xiàng)展示字符集設(shè)置:
- --character-sets-dir=name:字符集安裝的目錄
- --default-character-set=name:設(shè)置默認(rèn)字符集,若不指定,則使用utf8
- -N, --no-set-names:關(guān)閉--set-charset設(shè)置
- --set-charset:默認(rèn)ON,輸出中增加set names default_character_set語句
2.11 調(diào)式選項(xiàng)
使用mysqldump時(shí),可通過選項(xiàng)在dump文件中輸出調(diào)試信息:
- --allow-keywords:允許使用關(guān)鍵字創(chuàng)建列
- -i, --comments:默認(rèn)ON,在導(dǎo)出的dump文件中增加諸如程序版本、服務(wù)器版本和主機(jī)信息
- --dump-date:默認(rèn)ON,在導(dǎo)出的dump文件最后打印日期
- -f, --force:導(dǎo)出過程中忽略錯(cuò)誤
- --log-error=name:默認(rèn)不記錄警告和錯(cuò)誤信息,導(dǎo)出過程中可以向指定的文件輸出警告和錯(cuò)誤信息
- -v, --verbose:Verbose模式,打印更多的信息
3 dump示例
1)導(dǎo)出某一個(gè)數(shù)據(jù)庫,不指定--database選項(xiàng),可以看出導(dǎo)出的腳本不包含create database和use語句,這樣可以將dump的數(shù)據(jù)庫導(dǎo)入到其它數(shù)據(jù)庫中
[root@node1 ~]# mysqldump -h192.168.56.201 -ufordba -pxxxxx sakila --set-gtid-purged=off > db01.sql2)導(dǎo)出某一個(gè)數(shù)據(jù)庫,指定--database選項(xiàng),可以看出導(dǎo)出的腳本包含create database和use語句,若導(dǎo)出多個(gè)數(shù)據(jù)庫,則在--database后以空格分割列出多個(gè)數(shù)據(jù)庫
[root@node1 ~]# mysqldump -h192.168.56.201 -ufordba -pxxxxx --databases sakila --set-gtid-purged=off > db02.sql3)導(dǎo)出所有數(shù)據(jù)庫,使用--all-databases,或使用--databases列出多個(gè)數(shù)據(jù)庫,輸出同上
[root@node1 ~]# mysqldump -h192.168.56.201 -ufordba -pxxxxx --all-databases --set-gtid-purged=off > db03.sql4)導(dǎo)出某一張表,將其表結(jié)構(gòu)和數(shù)據(jù)分開
[root@node1 tmp]# mysqldump -h 192.168.56.201 -ufordba -pxxxxx sakila actor -T /tmp --single-transaction --set-gtid-purged=off[root@node1 tmp]# ll actor.*-rw-r--r--. 1 root root 1614 Dec 26 09:19 actor.sql-rw-r-----. 1 mysql mysql 7399 Dec 26 09:19 actor.txt5)導(dǎo)出整庫或者大表時(shí),指定--quick,可提升導(dǎo)出的速度,若導(dǎo)出存儲(chǔ)過程、函數(shù)、event時(shí),可指定--routines、--events和--triggers選項(xiàng)
[root@node1 ~]# mysqldump -h 192.168.56.201 -ufordba -pxxxxx sakila --routines --events --triggers --single-transaction --quick --set-gtid-purged=off > db04.sql6)若搭建復(fù)制環(huán)境,可通過指定--source-data等選項(xiàng)在導(dǎo)出的腳本中增加change replication語句,將dump的腳本用于搭建從庫
[root@node1 ~]# mysqldump -h 192.168.56.201 -ufordba -pxxxxx --all-databases --routines --events --triggers --single-transaction --quick --set-gtid-purged=off --source-data=2 > db05.sql7)基于從庫搭建新的從庫,可在從庫dump數(shù)據(jù)庫,然后在新從庫上執(zhí)行dump的腳本
[root@node2 ~]# mysqldump -h192.168.56.202 -ufordba -pxxxxx --all-databases --dump-replica=2 --include-source-host-port --single-transaction --set-gtid-purged=off > db01.sql4 mysqldump使用限制
使用mysqldump可對(duì)數(shù)據(jù)庫進(jìn)行導(dǎo)出,但也有一些限制:
- 默認(rèn)不導(dǎo)出performance_schema或sys數(shù)據(jù)庫,若導(dǎo)出,可通過--databases選項(xiàng)指定
- 不導(dǎo)出information_schema數(shù)據(jù)庫
- 不導(dǎo)出InnoDB create tablespace語句
- 不導(dǎo)出NDB Cluster ndbinfo數(shù)據(jù)庫
- 導(dǎo)出mysql數(shù)據(jù)庫時(shí)會(huì)重建general_log和slow_query_log表,但不導(dǎo)出數(shù)據(jù)
總結(jié)
以上是生活随笔為你收集整理的MySQL 8.0 mysqldump 详解的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 求自动检测技术及应用课后练习答案
- 下一篇: 10-0001 3dsmax快捷键