本文深入探讨了mysqldump的各种选项及其工作原理，特别是–single-transaction和–lock-all-tables的区别，以及如何保证数据的一致性和完整性。
常用选项
1. -q
很简单，什么都不做，只是导出时加了一个 SQL_NO_CACHE 来确保不会读取缓存里的数据。
081022 17:39:33 7 Connect root@localhost on
7 Query /*!40100 SET @@SQL_MODE='' */
7 Init DB yejr
7 Query SHOW TABLES LIKE 'yejr'
7 Query LOCK TABLES `yejr` READ /*!32311 LOCAL */
7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query show create table `yejr`
7 Query show fields from `yejr`
7 Query show table status like 'yejr'
7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
7 Query UNLOCK TABLES
7 Quit- –lock-tables
跟上面类似，不过多加了一个 READ LOCAL LOCK，该锁不会阻止读，也不会阻止新的数据插入。
081022 17:36:21 5 Connect root@localhost on
5 Query /*!40100 SET @@SQL_MODE='' */
5 Init DB yejr
5 Query SHOW TABLES LIKE 'yejr'
5 Query LOCK TABLES `yejr` READ /*!32311 LOCAL */
5 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
5 Query show create table `yejr`
5 Query show fields from `yejr`
5 Query show table status like 'yejr'
5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
5 Query UNLOCK TABLES
5 Quit- –lock-all-tables
这个就有点不太一样了，它请求发起一个全局的读锁，会阻止对所有表的写入操作，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。
081022 17:36:55 6 Connect root@localhost on
6 Query /*!40100 SET @@SQL_MODE='' */
6 Query FLUSH TABLES
6 Query FLUSH TABLES WITH READ LOCK
6 Init DB yejr
6 Query SHOW TABLES LIKE 'yejr'
6 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
6 Query show create table `yejr`
6 Query show fields from `yejr`
6 Query show table status like 'yejr'
6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
6 Quit- –master-data
除了和刚才的 –lock-all-tables 多了个 SHOW MASTER STATUS 之外，没有别的变化。
081022 17:59:02 1 Connect root@localhost on
1 Query /*!40100 SET @@SQL_MODE='' */
1 Query FLUSH TABLES
1 Query FLUSH TABLES WITH READ LOCK
1 Query SHOW MASTER STATUS
1 Init DB yejr
1 Query SHOW TABLES LIKE 'yejr'
1 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
1 Query show create table `yejr`
1 Query show fields from `yejr`
1 Query show table status like 'yejr'
1 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
1 Quit- –single-transaction
InnoDB 表在备份时，通常启用选项 –single-transaction 来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了的数据。
081022 17:23:35 1 Connect root@localhost on
1 Query /*!40100 SET @@SQL_MODE='' */
1 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
1 Query BEGIN
1 Query UNLOCK TABLES
1 Init DB yejr
1 Query SHOW TABLES LIKE 'yejr'
1 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
1 Query show create table `yejr`
1 Query show fields from `yejr`
1 Query show table status like 'yejr'
1 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
1 Quit- –single-transaction and –master-data
本例中，由于增加了选项 –master-data，因此还需要提交一个快速的全局读锁。在这里，可以看到和上面的不同之处在于少了发起 BEGIN 来显式声明事务的开始。这里采用 START TRANSACTION WITH CONSISTENT SNAPSHOT 来代替 BEGIN 的做法的缘故不是太了解，可以看看源代码来分析下。
081022 17:27:07 2 Connect root@localhost on
2 Query /*!40100 SET @@SQL_MODE='' */
2 Query FLUSH TABLES
2 Query FLUSH TABLES WITH READ LOCK
2 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2 Query SHOW MASTER STATUS
2 Query UNLOCK TABLES
2 Init DB yejr
2 Query SHOW TABLES LIKE 'yejr'
2 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
2 Query show create table `yejr`
2 Query show fields from `yejr`
2 Query show table status like 'yejr'
2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
2 Quit常用操作
1 导出指定数据库（包括表结构和数据）
mysqldump -uroot -proot tennis > tennis.sql2 导出指定数据库（表结构）
mysqldump -uroot -proot -d tennis > tennis.schema.sql3 导出指定表（包括表结构和数据）
mysqldump -uroot -proot tennis players > tennis.players.sql4 导出指定表（表结构）
mysqldump -uroot -proot -d tennis players > tennis.players.sql5 备份多个数据库
mysqldump --databases mydatabase1 mydatabase2 mydatabase3 > test.dump
mysqldump --all-databases> test.dump6 建表语句包含 drop table if exists tableName
insert之前包含一个锁表语句lock tables tableName write，insert之后包含unlock tables
mysqldump -uroot -proot --opt tennis > tennis.schema.sql7 跨主机备份
使用下面的命令可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库。
-C指示主机间的数据传输使用数据压缩
mysqldump --host=host1 --opt sourceDb | mysql --host=host2 -C targetDb8 还原
mysql -u username -p test_db < test_db.sql
mysql> source test_db.sql
9 每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式
在/etc/crontab配置文件中加入下面代码行：
30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz10 mysqldump全量备份+mysqlbinlog二进制日志增量备份
备份：
mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql用–flush-logs选项，mysql就会关闭原来的二进制日志文件，开启一个新的二进制日志文件。 比如，新开启的二进制日志文件为 mysql-bin.000003。 那么在进行数据恢复的时候，
你可以利用backup.sql进行全量恢复+ mysql-bin.000003进行增量同步。
在mysqldump过程中，之前其实一直不是很理解为什么加了–single-transaction就能保证innodb的数据是完全一致的，而myisam引擎无法保证，必须加–lock-all-tables，前段时间抽空详细地查看了整个mysqldump过程。
理解master-data和–dump-slave
–master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
–master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
–dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；
–dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；
注意:在从库上执行备份时，即–dump-slave=2，这时整个dump过程都是stop io_thread的状态
深入理解–single-transaction：
打开general_log，准备一个数据量较小的db，开启备份，添加–single-transaction和–master-data=2参数，查看general_log，信息如下，每一步添加了我的理解
整个dump过程是同一个连接id 32，这样能保证在设置session级别的变量的时候不影响到其他连接
thread_id: 32
argument: ucloudbackup@localhost on
***************** 14. row *****************
thread_id: 32
argument: /!40100 SET @@SQL_MODE=” /
***************** 15. row *****************
thread_id: 32
argument: /!40103 SET TIME_ZONE=’+00:00’ /
***************** 16. row *****************
thread_id: 32
argument: FLUSH /!40101 LOCAL / TABLES
***************** 17. row *****************
thread_id: 32
argument: FLUSH TABLES WITH READ LOCK
批注：因为开启了–master-data=2，这时就需要flush tables with read lock锁住全库，记录当时的master_log_file和master_log_pos点
***************** 18. row *****************
thread_id: 32
argument: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
批注：–single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，然而这个还不够，还需要看下一条
***************** 19. row *****************
thread_id: 32
argument: START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /
这时开启一个事务，并且设置WITH CONSISTENT SNAPSHOT为快照级别（如果mysql版本高于某一个版本值，我还不大清楚40100代表什么版本）。想象一下，如果只是可重复读，那么在事务开始时还没dump数据时，这时其他线程修改并提交了数据，那么这时第一次查询得到的结果是其他线程提交后的结果，而WITH CONSISTENT SNAPSHOT能够保证在事务开启的时候，第一次查询的结果就是事务开始时的数据A，即使这时其他线程将其数据修改为B，查的结果依然是A，具体的测试看我下面的测试结果
***************** 20. row *****************
thread_id: 32
argument: SHOW MASTER STATUS
这时候执行这个命令来记录当时的master_log_file和master_log_pos点，注意为什么这个时候记录，而不是再18 row和19 row之间就记录，个人认为应该都是可以的，这里是测试结果，start transaction并不会产生binlog的移动，而18 row和19 row的动作也在同一个thread id中
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 1690 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
***************** 21. row *****************
thread_id: 32
argument: UNLOCK TABLES
等记录完成后，就立即释放了，因为现在已经在一个事务中了，其他线程再修改数据已经无所谓，在本线程中已经是可重复读，这也是这一步必须在19 rows之后的原因，如果20 rows和21 rows都在19 rows之前的话就不行了，因为这时事务还没开启，一旦释放，其他线程立即就可以更改数据，从而无法保证得到事务开启时最准确的pos点。***************** 22. row *****************
thread_id: 32
argument: SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘UNDO LOG’ AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA=’mysql’ AND TABLE_NAME IN (‘user’))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
***************** 23. row *****************
thread_id: 32
argument: SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA=’mysql’ AND TABLE_NAME IN (‘user’)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
***************** 24. row *****************
thread_id: 32
argument: mysql
***************** 25. row *****************
thread_id: 32
argument: SHOW TABLES LIKE ‘user’
***************** 26. row *****************
thread_id: 32
argument: show table status like ‘user’
dump表以前都需要show一下各自信息，确保表，视图等不损坏，可用，每一步错了mysqldump都会报错并中断，给出对应的错误码，常见的myqldump错误请参考我的另外一篇blog http://blog.youkuaiyun.com/cug_jiang126com/article/details/49359699
***************** 27. row *****************
thread_id: 32
argument: SET OPTION SQL_QUOTE_SHOW_CREATE=1
***************** 28. row *****************
thread_id: 32
argument: SET SESSION character_set_results = ‘binary’
***************** 29. row *****************
thread_id: 32
argument: show create table user
***************** 30. row *****************
thread_id: 32
argument: SET SESSION character_set_results = ‘utf8’
***************** 31. row *****************
thread_id: 32
argument: show fields from user
***************** 32. row *****************
thread_id: 32
argument: SELECT /!40001 SQL_NO_CACHE / * FROM user
这就是我们show processlist时看到的信息，而数据是怎么通过一条select语句就dump到本地文件里的呢，并且还转成成相应的create和insert语句，这就是mysqldump这个客户端工具的工作了，这里不做讨论
***************** 33. row *****************
最后并没有看到commit，因为在整个事务中，其实并没有修改任何数据，只是为了保证可重复读得到备份时间点一致性的快照，dump完成后提交不提交应该无所谓了。
myisam引擎为什么无法保证在–single-transaction下得到一致性的备份？
因为它压根就不支持事务，自然就无法实现上述的过程，虽然添加了–single-transaction参数的myisam表处理过程和上面的完全一致，但是因为不支持事务，在整个dump过程中无法保证可重复读，无法得到一致性的备份。而innodb在备份过程中，虽然其他线程也在写数据，但是dump出来的数据能保证是备份开始时那个binlog pos的数据。
myisam引擎要保证得到一致性的数据的话，他是如何实现的呢？
它是通过添加–lock-all-tables，这样在flush tables with read lock后，直到整个dump过程结束，断开线程后才会unlock tables释放锁(没必要主动发unlock tables指令)，整个dump过程其他线程不可写，从而保证数据的一致性
如果我一定要在mysiam引擎中也添加–single-transaction参数，再用这个备份去创建从库或恢复到指定时间点，会有什么样的影响？
我个人的理解是如果整个dump过程中只有简单的insert操作，是没有关系的，期间肯定会有很多的主键重复错误，直接跳过或忽略就好了。如果是update操作，那就要出问题了，分几种情况考虑
1） 如果是基于时间点的恢复，假设整个dump过程有update a set id=5 where id=4之类的操作，相当于重复执行两次该操作，应该问题不大
2） 如果是创建从库，遇到上面的sql从库会报错，找不到该记录，这时跳过就好
3）不管是恢复还是创建从库，如果dump过程中有update a set id=id+5 之类的操作，那就有问题，重复执行两次，数据全变了。
深入理解–lock-all-tables
打开general_log，准备一个数据量较小的db，开启备份，添加–lock-all-tables(其实也是默认设置)和–master-data=2参数，查看general_log，信息如下，理解–lock-all-tables怎么保证数据一致性
mysql> select thread_id,argument from general_log where thread_id=185\G
***************** 1. row *****************
thread_id: 185
argument: ucloudbackup@10.10.108.15 on
***************** 2. row *****************
thread_id: 185
argument: /!40100 SET @@SQL_MODE=” /
***************** 3. row *****************
thread_id: 185
argument: /!40103 SET TIME_ZONE=’+00:00’ /
***************** 4. row *****************
thread_id: 185
argument: FLUSH /!40101 LOCAL / TABLES
***************** 5. row *****************
thread_id: 185
argument: FLUSH TABLES WITH READ LOCK
这里flush tables with read lock之后就不会主动unlock tables，保证整个dump过程整个db数据不可更改，也没有事务的概念了
***************** 6. row *****************
thread_id: 185
argument: SHOW MASTER STATUS
同样记录主库的位置
***************** 7. row *****************
thread_id: 185
argument: SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘UNDO LOG’ AND FILE_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
***************** 8. row *****************
thread_id: 185
argument: SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
***************** 9. row *****************
thread_id: 185
argument: SHOW DATABASES
***************** 10. row *****************
thread_id: 185
argument: jjj
***************** 11. row *****************
thread_id: 185
argument: SHOW CREATE DATABASE IF NOT EXISTS jjj
测试可重复读和快照读(WITH CONSISTENT SNAPSHOT )
准备工作3.1（测试可重读）
session 1:
mysql> select * from xx;
+——+
| id |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
+——+
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
设置事务隔离级别为可重复读
mysql> START TRANSACTION ;
Query OK, 0 rows affected (0.00 sec)
我们先不开快照读观察现象
session 2:
mysql> insert into xx values (5);
Query OK, 1 row affected (0.00 sec)
session 1:
mysql> select * from xx;
+——+
| id |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+——+
5 rows in set (0.00 sec)
批注：这时因为没有设置快照读，所以当session 2有数据更新时，可查到该数据，接
下来我们继续在session 2 插入数据
session 2:
mysql> insert into xx values (6);
Query OK, 1 row affected (0.00 sec)
这时再观察session 1的数据
session 1
mysql> select * from xx;
+——+
| id |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+——+
5 rows in set (0.00 sec)
查询发现还是只有5条，表示可重复实现了。
准备工作3.2（测试快照读）
session 1
mysql> select * from xx;
+——+
| id |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /;
Query OK, 0 rows affected (0.00 sec)
这时我们在session 2插入数据
session 2：
mysql> insert into xx values (2);
Query OK, 1 row affected (0.00 sec)
这时我们再观察session 1的结果
session 1：
mysql> select * from xx;
+——+
| id |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
发现还是只有一条数据，证明实现了快照读
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from xx;
+——+
| id |
+——+
| 1 |
| 2 |
+——+
2 rows in set (0.00 sec)
事务1 提交后方可看见第二条记录
顶
0
踩
0
恢复
cat backup.sql | mysql -uroot -ppassword
mysqlbinlog mysql-bin.000003 | mysql -uroot -ppassword我们在用mysqldump备份数据时，有个选项是 –where / -w，可以指定备份条件，这个选项的解释是：
-w, –where=name Dump only selected records. Quotes are mandatory
我们可以做个测试，例如：
?
1
mysqldump –single-transaction -w ’ id < 10000 ’ mydb mytable > mydump.sql
这时候就可以备份出mytable表中 id< 10000 的所有记录了。假设我们还想加一个时间范围条件，例如：
?
1
mysqldump –single-transaction -w ” id < 10000 and logintime < unix_timestamp(‘2014-06-01’)” mydb mytable > mydump.sql
在这里，一定注意单引号和双引号问题，避免出现这种情况：
?
1
mysqldump –single-transaction -w ’ id < 10000 and logintime < unix_timestamp(‘2014-06-01’) ’ mydb mytable > mydump.sql
这样的话，结果条件会被解析成：
?
1
WHERE id < 10000 and logintime < unix_timestamp(2014-06-01)
眼尖的同学会发现，时间条件变成了：
?
1
WHERE id < 10000 and logintime < unix_timestamp(2014-06-01)
也就是变成了：
?
1
unix_timestamp(2007)
这和我们原先的设想大相径庭，因此一定要谨慎
详细参数
mysqldump Ver 10.13 Distrib 5.5.40, for Win32 (x86)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf D:\Program Files\phpStudy\MySQL\my.ini D:\Program Files\phpStudy\MySQL\my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use --skip-add-drop-table to disable.)
--add-locks Add locks around INSERT statements.
(Defaults to on; use --skip-add-locks to disable.)
--allow-keywords Allow creation of column names that are keywords.
--apply-slave-statements
Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START
SLAVE' to bottom of dump.
--character-sets-dir=name
Directory for character set files.
-i, --comments Write additional information.
(Defaults to on; use --skip-comments to disable.)
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset.
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
-a, --create-options
Include all MySQL specific create options.
(Defaults to on; use --skip-create-options to disable.)
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.
--delayed-insert Insert rows with INSERT DELAYED.
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-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.)
--dump-slave[=#] This causes the binary log position and filename of the
master to be appended to the dumped data output. Setting
the value to 1, will printit as a CHANGE MASTER command
in the dumped data output; if equal to 2, that command
will be prefixed with a comment symbol. This option will
turn --lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.
-E, --events Dump events.
-e, --extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists.
(Defaults to on; use --skip-extended-insert to disable.)
--fields-terminated-by=name
Fields in the output file are terminated by the given
string.
--fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
--fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
--fields-escaped-by=name
Fields in the output file are escaped by the given
character.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs.
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an SQL error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-h, --host=name Connect to host.
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
--ignore-table=database.table.
--include-master-host-port
Adds 'MASTER_HOST=<host>, MASTER_PORT=<port>' to 'CHANGE
MASTER TO..' in dump produced with --dump-slave.
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the output file are terminated by the given
string.
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
(Defaults to on; use --skip-lock-tables to disable.)
--log-error=name Append warnings and errors to given file.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.
--max-allowed-packet=#
The maximum packet length to send to or receive from
server.
--net-buffer-length=#
The buffer size for TCP/IP and socket communication.
--no-autocommit Wrap tables with autocommit/commit statements.
-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.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Same as --skip-set-charset.
--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.
--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.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-W, --pipe Use named pipes to connect to server.
-P, --port=# Port number to use for connection.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --quick Don't buffer query, dump directly to stdout.
(Defaults to on; use --skip-quick to disable.)
-Q, --quote-names Quote table and column names with backticks (`).
(Defaults to on; use --skip-quote-names to disable.)
--replace Use REPLACE INTO instead of INSERT INTO.
-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.
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
(Defaults to on; use --skip-set-charset to disable.)
--shared-memory-base-name=name
Base name of shared memory.
--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.
--dump-date Put a dump date to the end of the output.
(Defaults to on; use --skip-dump-date to disable.)
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name The socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags).
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-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.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table.
(Defaults to on; use --skip-triggers to disable.)
--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.)
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records. Quotes are mandatory.
-X, --xml Dump a database as well formed XML.
--plugin-dir=name Directory for client-side plugins.
--default-auth=name Default authentication client-side plugin to use.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
apply-slave-statements FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
dump-slave 0
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
include-master-host-port FALSE
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max-allowed-packet 25165824
net-buffer-length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 3306
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
shared-memory-base-name (No default value)
single-transaction FALSE
dump-date TRUE
socket (No default value)
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-verify-server-cert FALSE
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)
plugin-dir (No default value)
default-auth (No default value)
参考
http://imysql.cn/2008_10_24_deep_into_mysqldump_options
http://blog.youkuaiyun.com/cug_jiang126com/article/details/49824471
http://www.jb51.net/article/51417.htm这里写链接内容
扫一扫
830
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
