mysqldump主要参数探究

在数据库的日常维护中,对于数据量小的备份,我们常常采用的是逻辑备份,也就是使用mysqldump导出。数据量比较大的备份会使用percona的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

 

参考资料:

https://dev.mysql.com/doc/refman/5.0/en/commit.html

http://dev.mysql.com/doc/refman/5.0/en/flush.html 

mysqldump主要参数探究,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。