mysqldump主要参数探究
在数据库的日常维护中，对于数据量小的备份，我们常常采用的是逻辑备份，也就是使用mysqldump导出。数据量比较大的备份会使用percona的xtrabackup，关于xtrabackup工具的使用以及原理请参考我前面的文章xtrabackup详解，当然还有其他的方法。现在我们主要来深入看看mysqldump几个比较常用参数的原理。要需要了解mysqldump各种参数做了什么，我们需要打开查询日志来分析，打开查询日志很简单，在[mysqld]段落添加如下参数：
general_log=1 general_log_file=/data/mysql/general.log
重启mysql服务器，然后我们所有的操作都会记录日志了(线上繁忙的服务器不建议开启)
两张表结构如下，一个是MyISAM,一个是InnoDB的
mysql> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> show create table tb2\G *************************** 1. row *************************** Table: tb2 Create Table: CREATE TABLE `tb2` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql>
1.不加参数备份tb2表，该表是MyISAM引擎
[root@MySQL-01 mysql]# mysqldump test tb2 > /tmp/a.sql
5 Init DB test 5 Query SHOW TABLES LIKE 'tb2' 5 Query LOCK TABLES `tb2` READ /*!32311 LOCAL */ 5 Query show table status like 'tb2' 5 Query SET SQL_QUOTE_SHOW_CREATE=1 5 Query SET SESSION character_set_results = 'binary' 5 Query show create table `tb2` 5 Query SET SESSION character_set_results = 'utf8' 5 Query show fields from `tb2` 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 5 Query SET SESSION character_set_results = 'binary' 5 Query use `test` 5 Query select @@collation_database 5 Query SHOW TRIGGERS LIKE 'tb2' 5 Query SET SESSION character_set_results = 'utf8' 5 Query UNLOCK TABLES 5 Quit
可以看见不加任何参数，自动加上了LOCK TABLES READ LOCAL锁，，该锁不会阻止读，也不会阻止新的数据插入。所以不加参数的dump是非常danger。
2.--lock tables
[root@MySQL-01 mysql]# mysqldump --lock-tables test tb2 > /tmp/a.sql
6 Query SHOW TABLES LIKE 'tb2' 6 Query LOCK TABLES `tb2` READ /*!32311 LOCAL */ 6 Query show table status like 'tb2' 6 Query SET SQL_QUOTE_SHOW_CREATE=1 6 Query SET SESSION character_set_results = 'binary' 6 Query show create table `tb2` 6 Query SET SESSION character_set_results = 'utf8' 6 Query show fields from `tb2` 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 6 Query SET SESSION character_set_results = 'binary' 6 Query use `test` 6 Query select @@collation_database 6 Query SHOW TRIGGERS LIKE 'tb2' 6 Query SET SESSION character_set_results = 'utf8' 6 Query UNLOCK TABLES 6 Quit
跟默认不加参数是一样的，了LOCK TABLES READ LOCAL锁，，该锁不会阻止读，也不会阻止新的数据插入。
3.--lock-all-tables
[root@MySQL-01 mysql]# mysqldump --lock-all-tables test tb2 > /tmp/a.sql
7 Query FLUSH TABLES 7 Query FLUSH TABLES WITH READ LOCK 7 Init DB test 7 Query SHOW TABLES LIKE 'tb2' 7 Query show table status like 'tb2' 7 Query SET SQL_QUOTE_SHOW_CREATE=1 7 Query SET SESSION character_set_results = 'binary' 7 Query show create table `tb2` 7 Query SET SESSION character_set_results = 'utf8' 7 Query show fields from `tb2` 7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 7 Query SET SESSION character_set_results = 'binary' 7 Query use `test` 7 Query select @@collation_database 7 Query SHOW TRIGGERS LIKE 'tb2' 7 Query SET SESSION character_set_results = 'utf8' 7 Quit
可以发现执行了flush tables（关闭所有已打开的表），它请求发起一个全局的读锁（FLUSH TABLES WITH READ LOCK）会阻止对所有表的写入操作，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。
4.--local-all-tables --master-data=2 这里master-data=2是比较常用的，当然也可以使用1，使用1后，备份记录里面日志偏移相关的提示没有注释。
[root@MySQL-01 mysql]# mysqldump --lock-all-tables --master-data=2 test tb2 > /tmp/a.sql
10 Query /*!40100 SET @@SQL_MODE='' */ 10 Query /*!40103 SET TIME_ZONE='+00:00' */ 10 Query FLUSH /*!40101 LOCAL */ TABLES 10 Query FLUSH TABLES WITH READ LOCK 10 Query SHOW MASTER STATUS 10 Init DB test 10 Query SHOW TABLES LIKE 'tb2' 10 Query show table status like 'tb2' 10 Query SET SQL_QUOTE_SHOW_CREATE=1 10 Query SET SESSION character_set_results = 'binary' 10 Query show create table `tb2` 10 Query SET SESSION character_set_results = 'utf8' 10 Query show fields from `tb2` 10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 10 Query SET SESSION character_set_results = 'binary' 10 Query use `test` 10 Query select @@collation_database 10 Query SHOW TRIGGERS LIKE 'tb2' 10 Query SET SESSION character_set_results = 'utf8' 10 Quit
可以发现没什么变化，只是多执行了SHOW MASTER STATUS,我们看看备份出来的sql
[root@MySQL-01 mysql]# grep 'CHANGE MASTER TO' /tmp/a.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=959; [root@MySQL-01 mysql]#
可以看见有记录二进制，以及日志的偏移量，这种用法在做主从的时候很实用。
5.--local-all-tables --master-data=2 --flush-logs
[root@MySQL-01 mysql]# mysqldump --lock-all-tables --master-data=2 --flush-logs test tb2 > /tmp/a.sql
17 Connect root@localhost on 17 Query /*!40100 SET @@SQL_MODE='' */ 17 Query /*!40103 SET TIME_ZONE='+00:00' */ 17 Query FLUSH /*!40101 LOCAL */ TABLES 17 Query FLUSH TABLES WITH READ LOCK 17 Refresh Id Command Argument 17 Query SHOW MASTER STATUS 17 Init DB test 17 Query SHOW TABLES LIKE 'tb2' 17 Query show table status like 'tb2' 17 Query SET SQL_QUOTE_SHOW_CREATE=1 17 Query SET SESSION character_set_results = 'binary' 17 Query show create table `tb2` 17 Query SET SESSION character_set_results = 'utf8' 17 Query show fields from `tb2` 17 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb2` 17 Query SET SESSION character_set_results = 'binary' 17 Query use `test` 17 Query select @@collation_database 17 Query SHOW TRIGGERS LIKE 'tb2'
可以看见添加多一个参数flush-logs日志里面并没有明显变化，但是该命令会刷新binlog，从新产生一个新的binlog。
下面我们看看备份表tb1，该表是innodb引擎的，众所周知，innodb实现了mvcc，多版本并发控制，那么我们看看一个非常重要的参数
6. --single-transaction
mysqldump --single-transaction test tb1 > /tmp/a.sql
19 Connect root@localhost on 19 Query /*!40100 SET @@SQL_MODE='' */ 19 Query /*!40103 SET TIME_ZONE='+00:00' */ 19 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 19 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 19 Query UNLOCK TABLES19 Init DB test 19 Query SHOW TABLES LIKE 'tb1' 19 Query show table status like 'tb1' 19 Query SET SQL_QUOTE_SHOW_CREATE=1 19 Query SET SESSION character_set_results = 'binary' 19 Query show create table `tb1` 19 Query SET SESSION character_set_results = 'utf8' 19 Query show fields from `tb1` 19 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1` 19 Query SET SESSION character_set_results = 'binary' 19 Query use `test` 19 Query select @@collation_database 19 Query SHOW TRIGGERS LIKE 'tb1' 19 Query SET SESSION character_set_results = 'utf8' 19 Quit
InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性，可以实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ。然后启动了一个快照，实现一致性非锁定读。下面是官方给出的解释：
START TRANSACTION WITH CONSISTENT SNAPSHOT;
The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from anyInnoDB table. See Section 14.2.7.2, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT option does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits consistent read (REPEATABLE READ or SERIALIZABLE).
7. --single-transaction and --master-data
[root@MySQL-01 mysql]# mysqldump --single-transaction --master-data=2 test tb1 > /tmp/a.sql
22 Connect root@localhost on 22 Query /*!40100 SET @@SQL_MODE='' */ 22 Query /*!40103 SET TIME_ZONE='+00:00' */ 22 Query FLUSH /*!40101 LOCAL */ TABLES 22 Query FLUSH TABLES WITH READ LOCK 22 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 22 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 22 Query SHOW MASTER STATUS 22 Query UNLOCK TABLES22 Init DB test 22 Query SHOW TABLES LIKE 'tb1' 22 Query show table status like 'tb1' 22 Query SET SQL_QUOTE_SHOW_CREATE=1 22 Query SET SESSION character_set_results = 'binary' 22 Query show create table `tb1` 22 Query SET SESSION character_set_results = 'utf8' 22 Query show fields from `tb1` 22 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1` 22 Query SET SESSION character_set_results = 'binary' 22 Query use `test` 22 Query select @@collation_database 22 Query SHOW TRIGGERS LIKE 'tb1' 22 Query SET SESSION character_set_results = 'utf8
由于增加了选项 --master-data，因此看见提交一个快速的全局读锁。难道这里是为了正确的记录日志偏移量?（知道的童鞋请告知一声）
总结一下:
备份MyISAM表的参数推荐如下：
mysqldump --lock-all-tables --master-data=2 --flush-logs db table > /data/backup/table.sql
备份InnoDB表的参数推荐如下：
mysqldump --single-transaction --master-data=2 --flush-log db table > /data/backup/table.sql
当然还有很多参数，比如设置字符集等，童鞋们自行help，你懂的！
参考资料：
