mysqldump 工具使用详解——参数选项

mysqldump 简介

mysqldump 是一种用于逻辑备份的客户端工具，它会产生一套能够重新构建数据库或表的SQL语句。所谓逻辑备份：是利用SQL语言从数据库中抽取数据并存于二进制文件的过程。逻辑备份文件只能用来对数据库进行逻辑恢复，即数据导入，而不能按数据库原来的存储特征进行物理恢复。

用mysqldump进行备份的用户账号至少需要SELECT权限来dump表、SHOW VIEW权限dump视图、TRIGGER权限dump触发器、如果没有使用--single-transaction选项还需要LOCK TABLES权限（后面会讲到--single-transaction选项不会锁表），另外某些选项可能还需要其他权限，可在官方手册查询。

mysqldump 备份格式大概可分为3种：

shell> mysqldump [options] db_name [tbl_name ...]    #备份指定数据库或表
shell> mysqldump [options] --databases db_name ...   #备份指定数据库
shell> mysqldump [options] --all-databases    　　　　#备份所有数据库

mysqldump 选项(来源于mysql 5.7手册)

--add-drop-database

Add DROP DATABASE statement before each CREATE DATABASE statement (每个数据库创建之前添加drop数据库语句)

--add-drop-table

Add DROP TABLE statement before each CREATE TABLE statement (每个数据表创建之前添加drop table语句。默认为打开状态，使用--skip-add-drop-table取消选项)

--add-drop-trigger

Add DROP TRIGGER statement before each CREATE TRIGGER statement

--add-locks

Surround each table dump with LOCK TABLES and UNLOCK TABLES statements (在每个表导出之前增加LOCK TABLES并且之后UNLOCK  TABLES。默认为打开状态，使用--skip-add-locks取消选项)

--all-databases (-A)

Dump all tables in all databases (备份所有数据库的所有表)

--allow-keywords

Allow creation of column names that are keywords

--apply-slave-statements Include

STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output

--bind-address

Use specified network interface to connect to MySQL Server

--character-sets-dir

Directory where character sets are installed

--comments

Add comments to dump file

--compact

Produce more compact output

--compatible

Produce output that is more compatible with other database systems or with older MySQL servers

--complete-insert,  -c

Use complete INSERT statements that include column names（使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败）

--compress

Compress all information sent between client and server

--create-options

Include all MySQL-specific table options in CREATE TABLE statements

--databases (-B)

Interpret all name arguments as database names (将选项后的所有名称参数解释为数据库名称，以空格隔开)

注意：如果dump时不加 --databases  参数而直接跟数据库名，则备份的sql文件中不会打印 "CREATE DATABASE ..." 语句和 "USE `db_name`;" 语句。

--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

--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

--delete-master-logs

On a master replication server, delete the binary logs after performing the dump operation

--disable-keys

For each table, surround INSERT statements with statements to disable and enable keys

--dump-date

Include dump date as "Dump completed on" comment if --comments is given

--dump-slave

Include CHANGE MASTER statement that lists binary log coordinates of slave's master （取值0,1,2,与master-data作用相同）

# mysqldump -uroot -proot --single-transaction --dump-slave=2 -A > all.sql

在从库进行备份时，指定--dump-slave会在备份的sql中生成带有主库binlog位置的CHANGE MASTER语句；如果要给主库新增从库，可以在从库这样进行备份。

--enable-cleartext-plugin

Enable cleartext authentication plugin 5.7.10

--events

Dump events from dumped databases

--extended-insert, -e

Use multiple-row INSERT syntax （扩展插入，一条 insert 包含多条数据）

--fields-enclosed-by

This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE

--fields-escaped-by

This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE

--fields-optionally-enclosed-by

This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE

--fields-terminated-by

This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE

--flush-logs

Flush MySQL server log files before starting dump

--flush-privileges

Emit a FLUSH PRIVILEGES statement after dumping mysql database

--force

Continue even if an SQL error occurs during a table dump

--help

Display help message and exit

--hex-blob

Dump binary columns using hexadecimal notation

--host  (-h)

Host to connect to (IP address or hostname) mysqldump 支持远程备份和远程恢复（可远程导表）。

--ignore-error

Ignore specified errors 5.7.1

--ignore-table

Do not dump given table (不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……)

--include-master-host-port

Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave

--insert-ignore

Write INSERT IGNORE rather than INSERT statements

--lines-terminated-by

This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE

--lock-all-tables (-x)

Lock all tables across all databases (请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项)

--lock-tables

Lock all tables before dumping them

--log-error

Append warnings and errors to named file

--login-path

Read login path options from .mylogin.cnf

--master-data

Write the binary log file name and position to the output (该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE  MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，但当--single-transaction也被指定时，在开始导出时会获得很短的时间的全局读锁；其他内容参考下面的--single-transaction选项。该选项自动关闭--lock-tables选项)

--max_allowed_packet

Maximum packet length to send to or receive from server

--net_buffer_length

Buffer size for TCP/IP and socket communication

--no-autocommit

Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements

--no-create-db (-n)

Do not write CREATE DATABASE statements (只导出数据，而不添加CREATE DATABASE 语句)

--no-create-info (-t)

Do not write CREATE TABLE statements that re-create each dumped table (只导出数据，而不添加CREATE TABLE 语句)

--no-data (-d)

Do not dump table contents (只生成创建表结构的语句，不导出数据)

--no-defaults

Read no option files （不会读取任何配置文件。如果由于从配置文件读取未知选项而导致程序启动失败，可以使用--no-defaults来防止它们被读取）

--no-set-names

Same as --skip-set-charset

--no-tablespaces

Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output

--opt

Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.

--order-by-primary

Dump each table's rows sorted by its primary key, or by its first unique index  （mysqldump 导出时根据主键或唯一索引排序，以加快导入速度）

--password

Password to use when connecting to server

--pipe

On Windows, connect to server using named pipe

--plugin-dir

Directory where plugins are installed

--port

TCP/IP port number to use for connection

--print-defaults

Print default options

--protocol

Connection protocol to use

--quick

Retrieve rows for a table from the server a row at a time (不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项)

--quote-names

Quote identifiers within backtick characters

--replace

Write REPLACE statements rather than INSERT statements

--result-file

Direct output to a given file

--routines

Dump stored routines (procedures and functions) from dumped databases

--secure-auth

Do not send passwords to server in old (pre-4.1) format

--set-charset

Add SET NAMES default_character_set to output

--set-gtid-purged = [off | on]

Whether to add SET @@GLOBAL.GTID_PURGED to output （--set-gtid-purged=OFF 设置备份文件中不存储GTIDs，如果不设置，恢复时可能报错：ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 这时需要执行 reset master，清除GTID_EXECUTED的值）

--shared-memory-base-name

The name of shared memory to use for shared-memory connections

--single-transaction

Issue a BEGIN SQL statement before dumping data from server (该选项在导出数据之前提交一个BEGIN SQL语句，且不锁表，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK  TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。)

--skip-add-drop-table

Do not add a DROP TABLE statement before each CREATE TABLE statement

--skip-add-locks

Do not add locks

--skip-comments

Do not add comments to dump file

--skip-compact

Do not produce more compact output

--skip-disable-keys

Do not disable keys

--skip-extended-insert

Turn off extended-insert （关闭扩展插入，即sql文件中，一行只有一个insert....value()）

--skip-opt

Turn off options set by --opt

--skip-quick

Do not retrieve rows for a table from the server a row at a time

--skip-quote-names

Do not quote identifiers

--skip-set-charset

Do not write SET NAMES statement

--skip-triggers

Do not dump triggers

--skip-tz-utc

Turn off tz-utc

--socket

For connections to localhost, the Unix socket file to use

--ssl

Enable secure connection

--ssl-ca

Path of file that contains list of trusted SSL CAs

--ssl-capath

Path of directory that contains trusted SSL CA certificates in PEM format

--ssl-cert

Path of file that contains X509 certificate in PEM format

--ssl-cipher

List of permitted ciphers to use for connection encryption

--ssl-crl

Path of file that contains certificate revocation lists

--ssl-crlpath

Path of directory that contains certificate revocation list files

--ssl-key

Path of file that contains X509 key in PEM format

--ssl-mode

Security state of connection to server 5.7.11

--ssl-verify-server-cert

Verify server certificate Common Name value against host name used when connecting to server

--tab=dir_name (-T dir_name)

Produce tab-separated data files  （生成制表符分隔的文本格式数据文件。对于每个dump表，mysqldump会创建一个 tbl_name.sql 文件，其中包含创建表的CREATE TABLE语句，并且会创建一个包含该表数据的 tbl_name.txt 文件，选项值是写入文件的目录。即表结构与数据分离。默认情况下，.txt数据文件使用制表符分隔每列，换行分隔每行。也可以使用--fields-terminated-by=',' 和 --lines-terminated-by='\n\n' 明确指定分隔符）

--tables

Override --databases or -B option （覆盖--databases或-B选项。mysqldump将该选项后面的所有名称参数视为表名）

--tls-version

Protocols permitted for secure connections 5.7.10

--triggers

Dump triggers for each dumped table

--tz-utc

Add SET TIME_ZONE='+00:00' to dump file

--user

MySQL user name to use when connecting to server

--verbose

Verbose mode

--version

Display version information and exit

--where (-w)

Dump only rows selected by given WHERE condition （根据条件备份指定的行，如 --where="uid=123 and name='xxx'"）

--xml

Produce XML output