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

0

–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

0

–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

0

–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