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=' ' 明确指定分隔符）
--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