mysqldump使用详解

mysqldump的详细用法：

首先一步一步实验，创建一个用户，只授予某一个库的读取权限：

create user dump_test@"%" identified by "dump_test123";

grant select on test.* to dump_test@"%";

然后采取最基本的默认的备份方式：

从图中可以看到错误是因为缺少lock权限，为了保证数据的一致性，mysqldump对备份的表会默认加一个只读锁，可以使用

--skip-lock-tables to disable跳过

这里用到第一个参数：-l, --lock-tables   Lock all tables for read.(Defaults to on; use --skip-lock-tables to disable.)

加上skip之后：

可以看到成功备份了表

接下来授权用户创建和插入权限

然后还原备份的数据

这时候可以看到报错是因为用户没有drop权限，这是因为mysqldump默认是添加drop table命令的，这涉及到第二个参数

这个参数是用来在建表前删除表的，默认开启。可以使用--skip-add-drop-table跳过

--add-drop-table    Add a DROP TABLE before each create. (Defaults to on; use --skip-add-drop-table to disable.)

我们加上新的参数重新备份然后还原

这里我们看到--skip-add-drop-table已经起作用了，然后我们手动进行删除表e，进行还原

这时候报错运行到34行的时候没有权限执行，那么我们看一看34行是什么

这时候需要用到另一个参数了--add-locks    Add locks around INSERT statements.(Defaults to on; use --skip-add-locks to disable.)，--add-locks在INSERT语句周围添加锁。
其作用是防止其他线程改变本张表的数据，默认开启，可以使用--skip-add-locks禁用

我们接下来看一看添加了--skip-add-locks之后的备份还原效果

这时候我们看到了报错显示用户没有alter权限，这里涉及到了另一个参数 -K, --disable-keys

 '/*!40000  ALTER TABLE tb_name DISABLE KEYS */; and'/*!40000 ALTER TABLE tb_name ENABLE KEYS */;

will be put in the output.(Defaults to on; use --skip-disable-keys to disable.)，

此参数用来停用和启用索引，在插入数据之前禁用索引，在数据插入完成后启用索引，从而使数据可以快速导入，默认开启，可以使用--skip-disable-keys禁用，接下来使用--skip-disable-keys进行备份，然后还原

可以看到并没有报错，数据已经成功还原

下面介绍其他的参数

-h, --host=name     Connect to host. 链接的ip
-P, --port=#        Port number to use for connection.  链接的端口
-u, --user=name     User for login if not current user. 链接的用户名称
-p, --password[=name] Password to use when connecting to server. If password is not given it's solicited on the tty. 链接的用户密码

--allow-keywords：允许使用关键词作为列名，但是会在列名之前加上表名作为前缀，目前没有发现有任何用处，建表或修改表时用关键字做列名不成功，在关键字上加上反引号可以成功，但这样不加该参数也是可以成功

-i, --comments      Write additional information.(Defaults to on; use --skip-comments to disable.)，为备份文件添加注释信息，如数据库版本等信息，默认开启，可以通过--skip-comments禁用

  -c, --complete-insert 使用完整的插入语句
加入该参数之前的效果

加入该参数之后的效果

很明显能看出二者的差别

-C, --compress 在客户端和服务器之间启用压缩传递所有信息,实际检测了一张大小为12M的表通过客户端备份，不加-C参数用了34秒，加了参数之后用了21秒

--compatible=name 产生与其他数据库或者老版MySQL兼容的备份文件，可用的值有ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options。可同时使用多个以逗号分隔的值。

注意：需要MySQL服务器版本4.1.0或更高版本，使用方法如下图

--compact 相当于一个参数包，其中包含了--skip-add-drop-table，--skip-add-locks，--skip-comments，--skip-disable-keys，--skip-set-charset

-a, --create-options   在CREATE TABLE语句中包括所有MySQL特性选项(默认为打开状态)，可以使用--skip-create-options禁用

使用--skip-create-options后的建表语句如下图所示

可以看到后者没有设置默认的引擎和字符集

-B, --databases 备份多个数据库，选项后跟多个库名。备份文件中会包含USE db_name。

--default-character-set=设置默认字符集，默认值为utf8

-e, --extended-insert   将多行insert语句合并成一行，默认开启，这样插入的速度较快，可以使用--skip-extended-insert禁用

--fields-terminated-by=name   指定字段分隔符，需要与 -T, --tab=name联合使用

-T, --tab=name   为给定的每个表创建以制表符分隔的文本文件路径。 （创建.sql和.txt文件。）注意：这只适用如果mysqldump在与mysqld相同的机器上运行服务器。

这时会生成两个文件，一个.sql文件(指定数据结构的sql语句)，一个.txt文件(指定要求的数据文本文件)

--fields-enclosed-by=name 指定文本限定符，用法同--fields-terminated-by=name

fields-optionally-enclosed-by用法和作用同fields-enclosed-by，二者的区别是fields-optionally-enclosed-by作用在含有文本的类型中，对纯数字无影响，下图可以看出二者的区别

fields-enclosed-by                                                                         fields-optionally-enclosed-by

                                                                 

--fields-escaped-by=name   指定转义字符，用法同上

--lines-terminated-by=name 指定换行符，用法同上

-F, --flush-logs  导出之前刷新日志，假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。

从上图可以看到第一遍执行的时候报错，这是因为执行flush需要reload权限，加上相应的权限即可成功执行 ，这时候日志刷新了一份，这种操作是为了在备份之后有更新操作的时候，增量的时候能够更容易找到起始位置

这样操作就会刷新两个日志

--flush-privileges   在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。

-f, --force  遇到sql报错继续执行

--hex-blob  使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB，先插入一条二进制数据，然后观察加参数和不加参数的区别

这是不加参数的效果

这是加参数的效果

 可以看到最后一条二进制数据后者显示了出来

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

–insert-ignore  在插入行时使用INSERT IGNORE INTO语句.(个人对INSERT IGNORE INTO的理解是忽略并纠正当前错误，如果无法纠正则跳过当前语句)

 如上图所示，主键冲突的那一条信息跳过了，数据过长的那一条信息被修正为长度20，期间只有warning，并没有报错

–max_allowed_packet  服务器发送和接受的最大包长度。

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

–log-error  附加警告和错误信息到给定文件

 

–net_buffer_length  TCP/IP和socket连接的缓存大小。

–no-autocommit   使用autocommit/commit 语句包裹表。

 

可以看到这个处理是将自动提交关闭，在插入之后再手动提交

–no-create-db,  -n  只导出数据，而不添加CREATE DATABASE 语句，要结合--databases或者备份全库的时候使用

-t, --no-create-info  不写创建表的信息

-d, --no-data  不写表的具体数据，只有建表语句

-N, --no-set-names  相当于 --skip-set-charset.

--opt  等同于–add-drop-table,  –add-locks, –create-options, –quick, –extended-insert, –lock-tables,  –set-charset, –disable-keys 该选项默认开启,  可以用–skip-opt禁用.

–order-by-primary  根据主键进行排序，如果没有主键就根据第一个唯一键进行排序

--protocol=name  使用的连接协议，包括：tcp, socket, pipe, memory.

-q, --quick   不缓冲查询，直接导出到标准输出。默认为打开状态，使用–skip-quick取消该选项。

-Q, --quote-names     用反引号（`）引用表和列名。（默认为on;使用--skip-quote-names禁用。）

--replace           用REPLACE替代INSERT INTO.（REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。
注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。）

--add-drop-trigger  创建触发器之前加删除触发器语句

--add-drop-table  创建表之前加删除表语句（默认开启，用--skip-add-drop-table禁用）

--add-drop-database  创建数据库之前加删除数据库语句

–set-charset  添加'SET NAMES  default_character_set'到输出文件。默认为打开状态，使用–skip-set-charset关闭选项。

--skip-opt     禁用–opt选项.

--socket,-S   指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock

--dump-date   将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-date关闭选项。

--tables 覆盖--databases (-B)参数，指定需要导出的表名。

test是数据库名称，a,e是表的名称

 我们看上图的报错是找不到表test2，正常来说-B参数是会把test,test2都识别成数据库名称的，所以这里-B参数不起作用，即--tables 覆盖--databases (-B)参数

--triggers 导出触发器。该选项默认启用，用--skip-triggers禁用它。

--tz-utc  在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。(默认开启，用--skip-tz-utc禁用)

--verbose, --v  输出多种平台信息。

--version, -V  输出mysqldump版本信息并退出

--where, -w  只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。

 上图的写法报错了，包含了空格等，所以需要用引号括起来，如下图写法

--xml, -X  导出XML格式.

--plugin_dir 客户端插件的目录，用于兼容不同的插件版本。

--default_auth 客户端插件默认使用权限。

--routines, -R  导出存储过程以及自定义函数。

--all-tablespaces , -Y   导出全部表空间。（向表转储添加创建NDB表使用的任何表空间所需的所有SQL语句。 此信息不包含在mysqldump的输出中。 此选项目前仅与MySQL Cluster表相关，MySQL表中不支持这些表。）

--no-tablespaces , -y   不导出任何表空间信息。

--apply-slave-statements  在'CHANGE MASTER'前添加'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。防止主从环境导致数据变化

--delete-master-logs  备份后删除主服务器上的日志。 自动启用--master-data。（这个参数不太理解，测试发现日志并没有删除，而且备份文件里也没有建表和插入数据的脚本，不过从日志的刷新来看确实是启用了--master-data）

--dump-slave[=#]

该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，在命令前增加说明信息。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。（在从执行）

要求备份用户额外具备 SUPER, LOCK TABLES, REPLICATION CLIENT权限

--master-data[=#]  该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。（在主执行）

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

--set-gtid-purged[=name]  用于主从备份，存在on,off,auto三个值，具体情况后续补充，这个参数不是很懂