Mysql备份工具mysqldump--参数

原创

陈不成i

修改于 2021-06-11 14:18:35

1.5K0

修改于 2021-06-11 14:18:35

文章被收录于专栏：ops技术分享

参数

Mysqldump命令参数

#获得帮助
--help
#备份所有库，这样设置的账号密码什么的也会备份了
--all-databases
#不缓冲查询，直接导出到标准输出。默认为打开状态
--quick
#如果有3个数据库整体导出，每次只会对一个库添加只读锁，不会影响其它数据库。所以该参数只能保证各个schema自己的数据一致性快照。该参数默认打开。
--lock-tables
#不锁表，保证各个表具有数据一致性快照。这期间增删改查正常，但是alter table等对表结构发生更改的语句要被挂起。默认关闭。
#所以该参数明显不能保证各个表之间的数据一致性（特别是外键约束的父表和子表之间）
#一致性快照说的是如果4点开始备份，那对数据做一个快照，6点结束了，这期间只会保存4点前的，新的改变不进行同步保存，根据pos点。
--single-transaction
#从一开始就对整个mysql实例加global read lock锁，在这个导出过程中，数据库严格处于只读状态，数据强一致性，默认不开启，开启会导致数据库只能进行读，没法写入。
#使用该参数的话，也会自动将 --single-transaction 及 --lock-tables 参数置于 off 状态。
--lock-all-tables
#刷新binlog日志，没开启加这个参数也没关系
--flush-logs
#将会输出CHANGE MASTER命令用于从库的恢复，2的话会默认注释掉
--master-data=2
#在导出mysql数据库之后，发出一条FLUSH PRIVILEGES语句.为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
--flush-privileges
#导出触发器
--triggers
#导出存储过程以及自定义函数。
--routines
#导出事件
--events
#使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB
--hex-blob
#打印程序参数列表并退出，这个可以从my.cnf中[mysqldump]中读取一些信息。比如user=root，在执行mysqldump备份命令时候可以不用加--user了
--print-defaults
#已不从任何选项文件读取默认选项
--no-defaults
#只有给指定的文件读取默认选项，默认是/etc/my.cnf
--defaults-file=
#在全局下面读取这个文件，同上一个参数
--defaults-extra-file=
#备份所有的数据库
-A
#导出全部表空间
-Y
#不导出任何表空间
-y
#每个数据库创建之前添加drop数据库语句，这样在恢复的时候会先删除数据库，再新建。这样可以让每次恢复都是一致的，而不是中途导入一些。
--add-drop-database
#每个数据表创建之前添加drop数据表语句，默认打开。可以用–-skip-add-drop-table取消
--add-drop-table
#在每个表导出之前增加LOCK TABLES进行并且之后UNLOCK TABLE(为了使得更快地插入到MySQL)。默认开启。
--add-locks
#允许创建是关键词的列明子。这由表名前缀每个列名做到。看不懂
--allow-keywords
#从库在CHANGE MASTER前添加STOP SLAVE，并且在导出的最后添加START SLAVE。
--apply-slave-statements
#字符集文件的目录
--character-sets-dir=name
#附加注释信息。默认为打开，可以用--skip-comments取消
-i / --comments
#导出数据将和其他数据库和旧版本的MySQL 相兼容.值可以ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb等等。
--compatible=name
#导出更少的输出信息（用于调试）去掉注释和头尾等结构。可以使用选项
--compact
#使用完成的insert语句（包含列名称）,这么做能提高插入效率，但是可能会受到max_allowed_packer参数的影响而导致插入失败。
-c / --complete-insert
#在客户端和服务器之间启用压缩传递所有信息
-C / --compress
#在CREATE TABLE语句中包括所有MySQL特性选项。（默认为打开状态）
-a / --create-options
#导出数据里面添加创建数据库的语句和切入数据库
-B / --databases
#输出debug信息，用于调试。默认值为d：t:o,/tmp/mysqldump.trace
-# / --debug="d:t:o,/tmp/debug.trace"
#检查内存和打开文件使用说明并退出
--debug-check
#输出调试信息并退出
-debug-info
#设置默认字符集，默认值为utf8
--default-character-set=utf8
#采用延时插入方式（INSERT DELAYED）导出数据
--delayed-insert
#master备份后删除日志，这个参数将自动激活--master-data
--delete-master-logs
#对于每个表，用/*！40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*！40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT 语句。
#这样可以更快地导出dump出来的文件，因为它是在插入所有行后创建索引.该选项只适合MyISAM表,默认为打开状态.
-K / --disable-keys
#该选项导致主的binlog位置和文件名追加到导出数据的文件中。
#设置为1时，将会以CHANGE MASTER命令输出到数据文件；
#设置为2时,在命令前增加说话信息。该选项将会打开--lock-all-tables,除非--single-transaction被指定。该选项会自动关闭–-lock-tables选项。默认值为0
--dump-slave=0/1/2
#导出事件
-E / --events
#使用具有多个VALUES列的INSERT语句.并加速导入时的速度。默认为打开状态,使用--skip-extended-insert取消选项。
-e / --extended-insert
#导出文件中忽略给定字段。与--tab选项一起使用，不能用于--databases和--all-databases选项
#例子：mysqldump -uroot -p test test表 --tab="/home/mysql" --fields-terminated-by="#"
--fields-terminated-by=name
#输出文件中的各个字段用给定字符包括。与--tab选项一起使用,不能用于--databases和--all-databases选项
#例子：mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#"
--fields-enclosed-by=name
#输出文件中的各个字段用给定字符选项性包括。与--tab选项一起使用,不能用于--databases和--all-databases选项
#例子：mysqldump -uroot -p test test --tab="/home/mysql"--fields-enclosed-by="#" --fields-optionally-enclosed-by ="#
--fields-optionally-enclosed-by=name
#输出文件中的各个字段忽略给定字符.与–tab选项一起使用,不能用于--databases和--all-databases选项
#例子：mysqldump -uroot -p mysql user --tab="/home/mysql" --fields-escaped-by="#"
--fields-escaped-by=name
#开始导出之前刷新日志
#加入一次导出多个数据库（使用选项--databases或者--all-databases）将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所有表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables或者--master-data和--flush-logs
-F / --flush-logs
#导出mysql数据库之后，发出一条FLUSH PRIVILEGES语句.为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
--flush-privileges
#在导出过程中忽略出现的SQL错误
-f / --force
#不导出指定表，指定忽略多个表时，需要重复多次。每次一个表，每个表必须同时指定数据库和表名
#例子：mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
--ignore-table=name
#在--dump-slave产生的CHANGE MASTER TO ....语句中增加'MASTER_HOST=<host>,MASTER_PROT=<prot>'
--include-master-host-port
#在插入行使用INSERT IGNORE语句
--insert-ignore
#输出文件的每行用定字符串划分.与--tab选项一起使用,不能用于--database和--all-databases选项。
#例子：mysqldump -uroot -p --host=localhost test test --tab="/tmp/mysql" --lines-terminated-by="##"
--lines-terminated-by=name
#提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭--single-transaction和--lock-tables选项
-x / --lock-all-tables
#开始导出前，多订所有表。用READ LOCAL锁定表以允许MyISAM表并行插入.对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
#请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件的表在数据库之间的逻辑一致性。不同数据库表的导出状态完全不同。
-l / --lock-tables
#附加警告和错误信息到给定文件
--log-error=/tmp/err_log
#该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项打开--lock-all-tables 选项，除非--single-transaction也被锁定。
#（在这种情况下,全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables
--master-data=0/1/2
#服务器发送和接受的最大包长度
--max-allowed-packet=100240
#TCP/IP和socket连接的缓存大小
--net-buffer-length=10024
#使用autocommit/commit语句包裹表
--no-autocommit
使用autocommit/commit语句包裹表。
52、-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that
normally is output for each dumped database if
--all-databases or --databases is given.
例子：mysqldump -uroot -p --host=localhost --all-databases --no-create-db
只导出数据，而不添加CREATE DATABASE语句
53、-t, --no-create-info
Don't write table creation info.
例子：mysqldump -uroot -p --host=localhost --all-databases --no-create-info
只导出数据，而不添加CREATE TABLE 语句
54、-d, --no-data No row information.
例子：mysqldump -uroot -p --host=localhost --all-databases --no-data
只导出数据，而不添加CREATE TABLE语句
55、-N, --no-set-names Same as --skip-set-charset.
例子：mysqldump -uroot -p --host=localhost --all-databases --no-set-names
等同于--skip-set-charset
56、--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--------
例子：mysqldump -uroot -p --host=localhost --all-databases --opt
　　等同于--add-drop-table，--add-locks，--create-options，--quick，--extended-insert，--lock-tables，--set-charset，--disable-keys 该选项默认开启，可以用--skip-opt禁用
57、--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
例子：mysqldump -uroot -p --host=localhost --all-database --order-by-primary
如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。
58、-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
连接数据库密码
59、-P, --port=# Port number to use for connection.
连接数据库端口号
60、--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
例子：mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
使用的连接协议，包括：tcp、socker、pipe、memory
61、-q, --quick Don't buffer query, dump directly to stdout.
(Defaults to on; use --skip-quick to disable.)
------
例子：mysqldump -uroot -p --host=localhost --all-database
mysqldump -uroot -p --host=localhost --all-database --skip-quick
不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。
62、-Q, --quote-names Quote table and column names with backticks (`).
(Defaults to on; use --skip-quote-names to disable.)
-------
mysqldump -uroot -p --host=localhost --all-database
mysqldump -uroot -p --host=localhost --all-database --skip-quote-names
使用（`）引起表和列名。默认为打开状态，使用--skip-quote-names取消该选项。
63、--replace Use REPLACE INTO instead of INSERT INTO.
----------
mysqldump -uroot -p --host=localhost --all-database --replace
使用REPLACE INTO 取代INSERT INTO.
64、-r, --result-file=name
Direct output to a given file. This option should be used
in systems (e.g., DOS, Windows) that use carriage-return
linefeed pairs (\r\n) to separate text lines. This option
ensures that only a single newline is used.
----------------
mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt
直接输出到指定文件中。该选项应该用在使用回车换行对（\\r\\n）换行的系统上（例如：DOS,Windows）。该选项确保只有一行被使用
65、-R, --routines Dump stored routines (functions and procedures).
------
mysqldump -uroot -p --host=localhost --all-databases --routines
导出存储过程以及自定义函数。
66、--set-charset Add 'SET NAMES default_character_set' to the output.
(Defaults to on; use --skip-set-charset to disable.)
-----------------
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-database --skip-set-charset
添加'SET NAMES default_character_set'到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。
67、--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables
------------------
mysqldump -uroot -p --host=localhost --all-database --single-transaction
改选项在导出数据之前提交一个BEGIN SQL语句，BEGIN不会阻塞任何应用程序且能保证导出时的数据库的一致性状态，他只使用与多版本存储引擎，仅InnoDB 本选项和--lock-tables 选项是互相互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick选项。
68、 --dump-date Put a dump date to the end of the output.
(Defaults to on; use --skip-dump-date to disable.)
例子：mysqldump -uroot -p --host=localhost --all-database
mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date
将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-data关闭选项
69、 --skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
---------------
mysqldump -uroot -p --host=localhost --all-databases --skip-opt
禁用-opt选项
70、-S, --socket=name The socket file to use for connection.
---------
mysqldump -uroot -p --host=localhost --all-databases --skip-opt
指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
71、 -T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"
为每个表在给定路径创建tab分隔的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上，
72、--tables Overrides option --databases (-B).
---------------
mysqldump -uroot -p --host=localhost --database test --tables test
覆盖--database （-B）参数，需要导出的表名。
73、--triggers Dump triggers for each dumped table.
(Defaults to on; use --skip-triggers to disable.)
----------------
mysqldump -uroot -p --host=localhost --all-database --trigger
导出发出器。该选项默认启用，用--skip-trigger禁用它。
74、--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
(Defaults to on; use --skip-tz-utc to disable.)
--------------
mysqldump -uroot -p --host=localhost --all-databases --tz-utc
在导出顶部设置时区TIME_ZONE='+00：00' 以保证不同时区导出的TIMESTAMP 数据或数据被移动其他时区时的正确性。
75、-u, --user=name User for login if not current user.
指定连接的用户名
76、-v, --verbose Print info about the various stages.
输出多种平台信息
77、-V, --version Output version information and exit.
输出mysqldump版本信息并退出
78、-w, --where=name Dump only selected records. Quotes are mandatory.
-----------------
mysqldump -uroot -p --host=localhost --all-databases --where="user='root'"
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来
79、-X, --xml Dump a database as well formed XML.
mysqldump -uroot -p --host=localhost --all-database --xml
导出XML格式
80、--plugin-dir=name Directory for client-side plugins.
-------------------
mysqldump -uroot -p --host=localhost --all-databases --plug_dir="/usr/local/lib/"
客户端插件的目录，用于兼容不同的插件版本
81、--default-auth=name Default authentication client-side plugin to use.
---------------
mysqldump -uroot -p --host=localhost --all-database --default-auth="/usr/local/lib/plugin/<PLUGIN>"
客户端插件默认使用
82、--enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
启用/禁用 明文的身份验证插件

Mysqldump执行过程详解

* 留意unlock tables语句的位置，是在show master status语句获取了binlog pos之后立即执行
' #修改session级别的sql_mode为空，避免可能有些sql_mode值对备份产生影响'
2017-07-01T17:42:17.779564+08:00 6 Query /*!40100 SET @@SQL_MODE='' */ 2017-07-01T17:42:17.779695+08:00 6 Query /*!40103 SET TIME_ZONE='+00:00' */
' #强制刷新表缓存到磁盘并关闭表（但已经加表锁的表会阻塞该语句）'
2017-07-01T17:42:17.779889+08:00 6 Query FLUSH /*!40101 LOCAL */ TABLES
' # 对整个实例加全局读锁，如果存在表锁将阻塞加全局读锁语句'
2017-07-01T17:42:17.780047+08:00 6 Query FLUSH TABLES WITH READ LOCK
' #在session级别修改隔离级别为RR'
2017-07-01T17:42:17.780201+08:00 6 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
'# 开启一个一致性快照事务，必须在隔离级别RR下才能开启一个快照事务'
2017-07-01T17:42:17.780326+08:00 6 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
'#查看是否开启GTID'
2017-07-01T17:42:17.780452+08:00 6 Query SHOW VARIABLES LIKE 'gtid\_mode'
'#如果开启GTID则查看当前的事务GTID集合'
2017-07-01T17:42:17.781867+08:00 6 Query SELECT @@GLOBAL.GTID_EXECUTED
'#查看当前数据的binlog pos'
2017-07-01T17:42:17.781999+08:00 6 Query SHOW MASTER STATUS
'#释放全局读锁，留意解锁的位置，下文会专门提到这个'
2017-07-01T17:42:17.782113+08:00 6 Query UNLOCK TABLES
......
2017-07-01T17:42:17.786315+08:00 6 Init DB luoxiaobo
'#在一个数据库开始备份之前，设置一个保存点（回滚点）'
2017-07-01T17:42:17.786428+08:00 6 Query SAVEPOINT sp
'#查看库下有哪些表'
2017-07-01T17:42:17.786539+08:00 6 Query show tables
' #查看这个表的状态'
2017-07-01T17:42:17.786710+08:00 6 Query show table status like 't\_luoxiaobo'
'# 给每个表的每个字段加个反引号'
2017-07-01T17:42:17.786908+08:00 6 Query SET SQL_QUOTE_SHOW_CREATE=1
'#表结构的备份都是binary格式，所以要先改这个'
2017-07-01T17:42:17.787023+08:00 6 Query SET SESSION character_set_results = 'binary'
' #查看这个表的定义语句'
2017-07-01T17:42:17.787137+08:00 6 Query show create table `t_luoxiaobo`
'# 修改session的数据结果返回字符集，备份数据需要使用数据原本的字符集，这里是utf8'
2017-07-01T17:42:17.787329+08:00 6 Query SET SESSION character_set_results = 'utf8'
' #查看这个表的字段信息'
2017-07-01T17:42:17.787450+08:00 6 Query show fields from `t_luoxiaobo`
2017-07-01T17:42:17.787715+08:00 6 Query show fields from `t_luoxiaobo`
' #查询表中的数据，结合show fields from `t_luoxiaobo`的字段信息生成insert into语句'
2017-07-01T17:42:17.787967+08:00 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t_luoxiaobo`
2017-07-01T17:42:17.788285+08:00 6 Query SET SESSION character_set_results = 'binary'
2017-07-01T17:42:17.788411+08:00 6 Query use `luoxiaobo`
2017-07-01T17:42:17.788535+08:00 6 Query select @@collation_database
'#查看是否有这个表的触发器'
2017-07-01T17:42:17.788668+08:00 6 Query SHOW TRIGGERS LIKE 't\_luoxiaobo'
2017-07-01T17:42:17.788926+08:00 6 Query SET SESSION character_set_results = 'utf8'
' #t_luoxiaobob表备份结束，回滚到保存点sp，以释放select *...语句产生的MDL锁，如果不回滚到sp，后续整个备份过程中无法对该表执行DDL操作'
2017-07-01T17:42:17.789043+08:00 6 Query ROLLBACK TO SAVEPOINT sp
2017-07-01T17:42:17.789191+08:00 6 Query show table status like 't\_luoxiaobo2'
2017-07-01T17:42:17.789399+08:00 6 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-07-01T17:42:17.789510+08:00 6 Query SET SESSION character_set_results = 'binary'
2017-07-01T17:42:17.789625+08:00 6 Query show create table `t_luoxiaobo2`
2017-07-01T17:42:17.789753+08:00 6 Query SET SESSION character_set_results = 'utf8'
2017-07-01T17:42:17.789871+08:00 6 Query show fields from `t_luoxiaobo2`
2017-07-01T17:42:17.790123+08:00 6 Query show fields from `t_luoxiaobo2`
'#备份表t_luoxiaobo2'
2017-07-01T17:42:17.790486+08:00 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t_luoxiaobo2`
2017-07-01T17:42:17.790689+08:00 6 Query SET SESSION character_set_results = 'binary'
2017-07-01T17:42:17.790806+08:00 6 Query use `luoxiaobo`
2017-07-01T17:42:17.790923+08:00 6 Query select @@collation_database
2017-07-01T17:42:17.791053+08:00 6 Query SHOW TRIGGERS LIKE 't\_luoxiaobo2'
2017-07-01T17:42:17.791378+08:00 6 Query SET SESSION character_set_results = 'utf8'
'#备份t_luoxiaobo2表过程与t__luoxiaobo表完全一样'
2017-07-01T17:42:17.791497+08:00 6 Query ROLLBACK TO SAVEPOINT sp
'#整个luoxiaobo库备份完成之后，释放该保存点'
2017-07-01T17:42:17.791606+08:00 6 Query RELEASE SAVEPOINT sp
' #查看是否有相关的events'
2017-07-01T17:42:17.791717+08:00 6 Query show events
2017-07-01T17:42:17.792065+08:00 6 Query use `luoxiaobo`
2017-07-01T17:42:17.792323+08:00 6 Query select @@collation_database
2017-07-01T17:42:17.792489+08:00 6 Query SET SESSION character_set_results = 'binary'
'#查看luoxiaobo库是否有存储函数'
2017-07-01T17:42:17.792617+08:00 6 Query SHOW FUNCTION STATUS WHERE Db = 'luoxiaobo'
' #查看luoxiaobo库是否有存储过程'
2017-07-01T17:42:17.793967+08:00 6 Query SHOW PROCEDURE STATUS WHERE Db = 'luoxiaobo'
2017-07-01T17:42:17.794952+08:00 6 Query SET SESSION character_set_results = 'utf8'
'#备份结束，退出连接'
2017-07-01T17:42:17.805746+08:00 6 Quit