mysqldump指令詳解（轉載）

轉載：mysqldump指令詳解

官網參考：https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

1.簡介

mysqldump為MySQL邏輯備份工具，産生一系列SQL語句，之後重新執行以産生備份的庫、表及資料。也可産生CSV、XML等格式的資料。适用于各類引擎的表。

運作mysqldump需一定的權限。如，備份表的最低權限為select，備份視圖需show view權限，備份觸發器需trigger權限。mysqldump的輸出可能包含alter database語句，如，用于保持備份庫的字元集，此時需要有備份庫上的alter權限。若不使用—single-transaction選項則還需 lock tables權限，不同的選項可能需要對應的不同的權限。相應的，載入備份檔案也至少需與建立備份檔案時相同的權限。

2.文法

備份一個或多個表：

shell>mysqldump [options] db_name [table_name……]

備份一個或多個庫：

shell>mysqldump [options] –databases dbname……

備份所有資料庫：

shell>mysqldump [options] –all-databases

3.性能與擴充性

提供了在恢複前檢視并編輯備份檔案的便捷性和靈活性。不适用于大資料量的備份(即使備份過程較快，但在恢複資料時因插入、建索引等操作會造成高昂I/O開銷)。

對于大規模備份，考慮使用實體方法，如：mysqlbackup(适于InnoDB、MyISAM及其他表)、mysqlhotcopy(适于MyISAM表)、Xtrabackup(适于InnoDB及MyISAM表)、LVM(适于各類表)等。

4選項

mysqldump會依次從下列配置檔案中讀取預設：

1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. /usr/etc/my.cnf
4. ~/.my.cnf

mysqldump僅從配置檔案的下列節中讀取預設選項：

Ø [mysqldump]

Ø [client]

mysqldump選項：

指令行

描述

–add-drop-database

在CREATE DATABASE前DROP DATABASE。

–add-drop-table

在CREATE TABLE前DROP TABLE。

–add-drop-trigger

在CREATE TRIGGER 前DROP TRIGGER。

–add-locks

在INSERT前後分别加LOCK TABLES和UNLOCK TABLES。

–all-databases

備份所有庫中所有表。

–allow-keywords

允許列名包含關鍵字。

–apply-slave-statements

在CHANGE MASTER前加 STOP SLAVE在最後添加 START SLAVE。

–bind-address=ip_address

對于有多個網絡接口的機器選擇使用指定的接口連接配接MySQL。

–comments

為備份檔案添加注釋。

–compact

禁用結構化注釋及首尾結構體。開啟–skip-add-drop-table、–skip-add-locks、–skip-comments、–skip-disable-keys、–skip-set-charset以産生更緊促的輸出。

–compatible=name[,name,…]

産生與其他資料庫或者老版MySQL相容的備份檔案，可用的值有ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options。可同時使用多個以逗号分隔的值。

–complete-insert

使用帶有列名的完整INSERT。

–create-options

在CREATE TABLE中使用MySQL特定的表選項。

–databases

備份多個資料庫，選項後跟多個庫名。備份檔案中會包含USE db_name。

–debug[=debug_options]

寫debug日志。

–debug-check

程式退出時列印一些調試資訊

–debug-info

程式退出時列印一些有關cpu和memory的統計資訊。

–default-auth=plugin

指明要使用的用戶端認證插件

–default-character-set=charset_name

設定預設字元集

–delayed-insert

使用INSERT DELAYED 而非INSERT。MySQL5.6.6中INSERT DELAYED已不被推薦使用，相應的該選項也将來未來的mysqldump中移除。

–delete-master-logs

在master上備份後删除其二進制日志。該選項會自動激活—master-data選項。

–disable-keys

對每個表在INSERT前後分别加/!40000 ALTER TABLE

articles

DISABLE KEYS /和/!40000 ALTER TABLE

articles

ENABLE KEYS /。可使從備份檔案恢複資料更快，因為索引在所有行插入後建立。僅對于MyISAM表的非唯一索引。

–dump-date

若使用了—comments選項則會在末尾顯示– Dump completed on xxxxxx，以标記時間。

–dump-slave[=value]

會在輸出結果添加包含master二進制檔案和位置資訊的CHANGE MASTER語句。與—master-data選項類似，但用于複制slave以建立另外的與其同master的slave。會開啟—lock-all- tables除非使用了—single-transaction。會在dump前停止slave的SQL thread在dump後開始該線程。可與–apply-slave-statements、–include-master-host-port結 合使用。

–events

為備份的資料庫備份事件。

–extended-insert

使用包含多個值清單的多行INSERT。

–fields-enclosed-by=string

與—tab選項結合使用。指明輸出檔案中的filed以何種字元串enclosed，與 LOAD DATA INFILE對應的選項意義相同。

–fields-escaped-by

同上

–fields-optionally-enclosed-by=string

同上

–fields-terminated-by=string

同上

–flush-logs

在備份前重新整理MySQL日志。需RELOAD權限。若同時備份多個資料庫,如使用—all-database或—database，則對于每個備份 的資料庫都會重新整理一次日志，除非使用了—master-data或—lock-all-tables，此時日志僅在鎖定表時重新整理一次。若想讓備份和重新整理日 志同時進行需結合使用—lock-all-tables或—master-data。

–flush-privileges

在備份了mysql這個資料庫後發出FLUSH PRIVILEGES語句。本分mysql庫或依賴mysql庫中資料的庫時需使用。

–help

顯示幫助資訊

–hex-blob

将二進制列，如，BINARY,VARBINARY,BLOB,BIT備份為十六進制形式。

–host

要連接配接的主機

–ignore-table=db_name.tbl_name

指明不備份的表

–include-master-host-port

使用了–dump-slave時在CHANGE MASTER語句中加入MASTER_HOST和MASTER_PORT。

–insert-ignore

用INSERT IGNORE 而非INSERT ，以忽略重複的資料。

–lines-terminated-by=string

與—tab選項結合使用。與LOAD DATA INFILE的LINE意義相同。指明行由何種字元串terminated。

–lock-all-tables

通過在備份期前加read lock鎖定所有庫的所有表。會自動關閉—single-transaction和—lock-tables。

–lock-tables

對于備份的庫在備份前鎖定将被備份的屬于該庫的表。使用READ LOCAL鎖定以允許MyIASM表的并發插入。對于事務型的表請使用—single-transaction而非—lock-tables。另外該選項 不能保證備份檔案上各資料庫間的表在邏輯上是一緻的，因為市委每個庫單獨鎖定表。一些選項如—opt會自動開啟—locak-tables，可通過使用在 其後使用–skip-lock-tables規避。

–log-error=file_name

在給定檔案中附加警告和錯誤資訊。

–login-path=name

從.mylogin.cnf登入檔案讀取登入參數。可通過mysql_config_editor建立登入檔案。

–master-data[=value]

在輸出中添加二進制日志名和位置。會開啟—lock-all-tables除非也是用了—single-transaction。會自動關閉—lock-tables。

–max_allowed_packet=value

可發送或接收的最大包分組長度。

–net_buffer_length=value

TCP/IP及socket通訊的buffer大小。

–no-autocommit

在INSERT前後添加set autocommit=0和commit。

–no-create-db

若使用了—all-databases或—databases，不在輸出中添加CREATE DATABASE。

–no-create-info

不使用CREATE TABLE 重建備份的表。

–no-data

隻備份結構不備份資料。

–no-set-names

同–skip-set-charset不設定charset。

–no-tablespaces

不在輸出中使用CREATE LOG FILE和CREATE TABLESPACE。

–opt

–add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset,和–disable-keys幾個選項合起來的簡寫形式。

–order-by-primary

将備份的表中的行按主鍵排序或者第一個唯一鍵排序。當備份MyISAM表且将被載入到InnoDB表時很有用，打包備份本身的時間會較長。

–password[=password]

連接配接主機的密碼

–pipe

在Windows上通過命名管道連接配接server。

–plugin-dir=path

插件存放目錄。

–port=port_num

通過主機所使用的端口。

–protocol=type

連接配接主機所使用的協定。

–quick

備份時逐行讀取表而非一次全部行後緩沖在記憶體中。在備份大表時有用。

–quote-names

使用“`”包圍資料庫名、表名、列名等辨別符。若使用了ANSI_QUOTES則用“””包圍。特别的，可能需要在—compatible後開啟該選項。

–replace

使用REPLACE而非INSERT。

–result-file=file

将結果輸出帶指定的檔案。

–routines

備份routines（存儲過程和函數）。

–set-charset

添加SET NAMES default_character_set。預設開啟。

–set-gtid-purged=value

确定是否在結果中添加SET @@GLOBAL.GTID_PURGED。若為ON但在server中沒有開啟GTID則發生錯誤。若OFF則什麼都不做。若AUTO則server中開啟GTID那麼添加上述語句，反之不加。

–single-transaction

在備份前設定事務隔離級别為REPEATABLE READ并向server發送START TRANSACTION語句。僅對事務型表如InnoDB有用。與–ock-tables互斥。對于大檔案備份–single- transaction與–quick結合使用。

–skip-add-drop-table

禁用–add-drop-table。

–skip-add-locks

禁用–add-locks。

–skip-comments

禁用—comments。

–skip-compact

禁用—compact。

–skip-disable-keys

禁用—disable-keys。

–skip-extended-insert

禁用–extended-insert。

–skip-opt

禁用–skip-opt。

–skip-quick

禁用–quick。

–skip-quote-names

禁用–quote-names。

–skip-set-charset

禁用—set-charset。

–skip-triggers

不備份triggers

–skip-tz-utc

禁用– tz-utc

–socket=path

連接配接本機server所使用的socket。

–ssl-ca=file_name

包含信任的SSL CAs清單的檔案名。

–ssl-capath=dir_name

包含PEM格式的可信任SSL CA憑證的目錄。

–ssl-cert=file_name

用于建立安全連接配接的SSL證書檔案的名字。

–ssl-cipher=cipher_list

一系列用于SSL加密的所允許的密碼。

–ssl-crl=file_name

包含證書廢止清單的檔案名。

–ssl-crlpath=dir_name

包含證書廢止清單的檔案的目錄。

–ssl-key=file_name

用于建立安全連接配接的SSL key檔案名。

–ssl-verify-server-cert

The server’s Common Name value in its certificate is verified against the host name used when connecting to the server

–tab=path

對于每個備份的表mysqldump建立一個包含CREATE TABLE語句的tbl_name.sql，而被連接配接的server會建立一個由tab鍵分隔的包含相應資料的tbl_name.txt。選項值為将被寫 入的目錄。當mysqldump與mysqld處于同一主機時使用。否則tbl_name.txt檔案會被寫入遠端主機相應的目錄。當使用了— databases或—all-databases時該選項不适用。

–tables

覆寫–databases 或 -B 選項。該選項後的名稱參數均被認為是表名。

–triggers

為每個備份的表備份trigger。

–tz-utc

在備份檔案中添加SET TIME_ZONE=’+00:00’。

–user=user_name

連接配接server所使用的使用者名

–verbose

詳細模式。

–version

顯示版本資訊并退出。t

–where=’where_condition’

僅備份與where條件中比對的行。

–xml

産生XML格式的輸出。

指令行選項對應的配置檔案及預設值

指令行

配置檔案

預設值

引入版本

–add-drop-database

add-drop-database

FALSE

–add-drop-table

add-drop-table

TRUE

–add-drop-trigger

add-drop-trigger

FALSE

–add-locks

add-locks

TRUE

–all-databases

all-databases

FALSE

–allow-keywords

allow-keywords

FALSE

–apply-slave-statements

apply-slave-statements

FALSE

–bind-address=ip_address

bind-address

NO

–comments

comments

TRUE

–compact

compact

FALSE

–compatible=name[,name,…]

compatible

NO

–complete-insert

complete-insert

FALSE

–create-options

create-options

TRUE

–databases

databases

FALSE

–debug[=debug_options]

debug

FALSE

–debug-check

debug-check

FALSE

–debug-info

debug-info

FALSE

–default-auth=plugin

default-auth=plugin

NO

–default-character-set=charset_name

default-character-set

utf8

–delayed-insert

delayed-insert

FALSE

–delete-master-logs

delete-master-logs

FALSE

–disable-keys

disable-keys

TRUE

–dump-date

dump-date

TRUE

–dump-slave[=value]

dump-slave

–events

events

FALSE

–extended-insert

extended-insert

TRUE

–fields-enclosed-by=string

fields-enclosed-by

NO

–fields-escaped-by

fields-escaped-by

NO

–fields-optionally-enclosed-by=string

fields-optionally-enclosed-by

NO

–fields-terminated-by=string

fields-terminated-by

NO

–flush-logs

flush-logs

FALSE

–flush-privileges

flush-privileges

FALSE

–help

–hex-blob

hex-blob

FALSE

–host

host

NO

–ignore-table=db_name.tbl_name

ignore-table

NO

–include-master-host-port

include-master-host-port

FALSE

–insert-ignore

insert-ignore

FALSE

–lines-terminated-by=string

lines-terminated-by

NO

–lock-all-tables

lock-all-tables

FALSE

–lock-tables

lock-tables

TRUE

–log-error=file_name

log-error

NO

–login-path=name

NO

5.6.6

–master-data[=value]

master-data

–max_allowed_packet=value

max_allowed_packet

25165824

–net_buffer_length=value

net_buffer_length

1046528

–no-autocommit

no-autocommit

FALSE

–no-create-db

no-create-db

FALSE

–no-create-info

no-create-info

FALSE

–no-data

no-data

FALSE

–no-set-names

no-set-names

FALSE

–no-tablespaces

no-tablespaces

FALSE

–opt

opt

TRUE

–order-by-primary

order-by-primary

FALSE

–password[=password]

password

NO

–pipe

NO

–plugin-dir=path

plugin-dir=path

NO

–port=port_num

port

–protocol=type

protocol

–quick

quick

TRUE

–quote-names

quote-names

TRUE

–replace

replace

FALSE

–result-file=file

result-file

NO

–routines

routines

FALSE

–set-charset

set-charset

TRUE

–set-gtid-purged=value

set-gtid-purged

5.6.9

–single-transaction

single-transaction

FALSE

–skip-add-drop-table

skip-add-drop-table

FALSE

–skip-add-locks

skip-add-locks

FALSE

–skip-comments

skip-comments

FALSE

–skip-compact

skip-compact

TRUE

–skip-disable-keys

skip-disable-keys

FALSE

–skip-extended-insert

skip-extended-insert

FALSE

–skip-opt

skip-opt

FALSE

–skip-quick

skip-quick

FALSE

–skip-quote-names

skip-quote-names

FALSE

–skip-set-charset

skip-set-charset

FALSE

–skip-triggers

skip-triggers

FALSE

–skip-tz-utc

skip-tz-utc

FALSE

–socket=path

socket

NO

–ssl-ca=file_name

ssl-ca

NO

–ssl-capath=dir_name

ssl-capath

NO

–ssl-cert=file_name

ssl-cert

NO

–ssl-cipher=cipher_list

ssl-cipher

NO

–ssl-crl=file_name

ssl-crl

NO

5.6.3

–ssl-crlpath=dir_name

ssl-crlpath

NO

5.6.3

–ssl-key=file_name

ssl-key

NO

–ssl-verify-server-cert

ssl-verify-server-cert

NO

–tab=path

tab

NO

–tables

tables

NO

–triggers

triggers

TRUE

–tz-utc

tz-utc

TRUE

–user=user_name

user

NO

–verbose

FALSE

–version

TRUE

–where=’where_condition’

where

NO

–xml

xml

NO

上述選項可大緻分為以下幾個方面（有些選項有簡寫形式）便于記憶：

Ø 連接配接選項

l –bind-address=ip_address

l –compress,-C

l –default-auth=plugin

l –password[=password],-p[password]

l –pipe,-W

l –plugin-dir=path

l –port=port_num,-P port_num

l –protocol={TCP|SOCKET|PIPE|MEMORY}

l –socket=path,-S path

l –ssl*

l –user=user_name,-u user_name

l max_allowed_packet

l net_buffer_length

Ø DDL選項

l –add-drop-database

l –add-drop-table

l –add-drop-trigger

l –all-tablespaces,-Y

l –no-create-db,-n

l –no-create-info,-t

l –no-tablespaces,-y

l –replace

Ø Debug選項

l –allow-keywords

l –comments,-i

l –debug[=debug_options],-# [debug_options]

l –debug-check

l –debug-info

l –dump-date

l –force,-f

l –log-error=file_name

l –skip-comments

l –verbose,-v

Ø 幫助選項

l –help,-?

l –version,-V

Ø 國際化選項

l –character-sets-dir=path

l –default-character-set=charset_name

l –no-set-names,-N

l –set-charset

Ø 複制選項

l –apply-slave-statements

l –delete-master-logs

l –dump-slave[=value]

l –include-master-host-port

l –master-data[=value]

l –set-gtid-purged=value

Ø 格式選項

l –compact

l –compatible=name

l –complete-insert,-c

l –create-options

l –fields-terminated-by=…,–fields-enclosed-by=…, –fields-optionally-enclosed-by=…,–fields-escaped-by=…

l –hex-blob

l –lines-terminated-by=…

l –quote-names,-Q

l –result-file=file_name,-r file_name

l –tab=path,-T path

l –tz-utc

l –xml,-X

Ø 過濾選項

l –all-databases,-A

l –databases,-B

l –events,-E

l –ignore-table=db_name.tbl_name

l –no-data,-d

l –routines,-R

l –tables

l –triggers

l –skip-triggers

l –where=’where_condition’,-w ‘where_condition’

Ø 性能選項

l –delayed-insert

l –disable-keys,-K

l –extended-insert,-e

l –insert-ignore

l –opt

l –quick,-q

l –skip-opt

Ø 事務選項

l –add-locks

l –flush-logs,-F

l –flush-privileges

l –lock-all-tables,-x

l –lock-tables,-l

l –no-autocommit

l –order-by-primary

l –single-transaction

Ø 選項組

l –compact

l –opt