mysqldump的几个主要选项探究

来源：互联网发布：软件系统架构说明书编辑：程序博客网时间：2024/06/06 10:43

0、前言

本文主要探讨 mysqldump 的几种主要工作方式，并且比较一下和 mk-parralel-dump的一些差异，为备份方式的选择提供更多的帮助。

1、mysqldump

首先来看下 mysqldump 的几个主要参数的实际工作方式。

mysqldump 几个主要选项
1. -q
很简单，什么都不做，只是导出时加了一个SQL_NO_CACHE 来确保不会读取缓存里的数据。

081022 17:39:33       7 Connect     root@localhost on7 Query       /*!40100 SET @@SQL_MODE='' */7 Init DB     yejr7 Query       SHOW TABLES LIKE 'yejr'7 Query       LOCK TABLES `yejr` READ /*!32311 LOCAL */7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=17 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 TABLES7 Quit

2. --lock-tables
跟上面类似，不过多加了一个 READ LOCAL LOCK，该锁不会阻止读，也不会阻止新的数据插入。

081022 17:36:21       5 Connect     root@localhost on5 Query       /*!40100 SET @@SQL_MODE='' */5 Init DB     yejr5 Query       SHOW TABLES LIKE 'yejr'5 Query       LOCK TABLES `yejr` READ /*!32311 LOCAL */5 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=15 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 TABLES5 Quit

3. --lock-all-tables
这个就有点不太一样了，它请求发起一个全局的读锁，会阻止对所有表的写入操作，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。

081022 17:36:55       6 Connect     root@localhost on6 Query       /*!40100 SET @@SQL_MODE='' */6 Query       FLUSH TABLES6 Query       FLUSH TABLES WITH READ LOCK6 Init DB     yejr6 Query       SHOW TABLES LIKE 'yejr'6 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=16 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

4. --master-data
除了和刚才的 --lock-all-tables 多了个 SHOW MASTER STATUS 之外，没有别的变化。

081022 17:59:02       1 Connect     root@localhost on1 Query       /*!40100 SET @@SQL_MODE='' */1 Query       FLUSH TABLES1 Query       FLUSH TABLES WITH READ LOCK1 Query       SHOW MASTER STATUS1 Init DB     yejr1 Query       SHOW TABLES LIKE 'yejr'1 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=11 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

5. --single-transaction

InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了的数据。

081022 17:23:35       1 Connect     root@localhost on1 Query       /*!40100 SET @@SQL_MODE='' */1 Query       SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ1 Query       BEGIN1 Query       UNLOCK TABLES1 Init DB     yejr1 Query       SHOW TABLES LIKE 'yejr'1 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=11 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

6. --single-transaction and --master-data
本例中，由于增加了选项 --master-data，因此还需要提交一个快速的全局读锁。在这里，可以看到和上面的不同之处在于少了发起 BEGIN 来显式声明事务的开始。这里采用 START TRANSACTION WITH CONSISTENT SNAPSHOT 来代替 BEGIN的做法的缘故不是太了解，可以看看源代码来分析下。

081022 17:27:07       2 Connect     root@localhost on2 Query       /*!40100 SET @@SQL_MODE='' */2 Query       FLUSH TABLES2 Query       FLUSH TABLES WITH READ LOCK2 Query       SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2 Query       START TRANSACTION WITH CONSISTENT SNAPSHOT2 Query       SHOW MASTER STATUS2 Query       UNLOCK TABLES2 Init DB     yejr2 Query       SHOW TABLES LIKE 'yejr'2 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=12 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

关于隔离级别可以看手册 13.2.10.3. InnoDB and TRANSACTION ISOLATION LEVEL，或者本站之前的文章：[InnoDB系列] - 实例解析Innodb的隔离级别以及锁模式。

关于 START TRANSACTION WITH CONSISTENT SNAPSHOT 的说明可以看下手册描述：

The WITH CONSISTENT SNAPSHOT clause 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 any InnoDB  table. See Section 13.2.10.4, “Consistent Non-Locking Read”. The WITH CONSISTENT SNAPSHOT clause does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that allows consistent read (REPEATABLE READ or SERIALIZABLE).

12.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax

2. mysqldump 和 mk-parralel-dump 的比较

mk-parralel-dump 是开源项目 Maatkit 中的一个工具，主要由 Baron Schwartz 维护。
mk-parralel-dump 是由 perl 开发的，可以实现并发的导出数据表。具体的功能不细说，自己去看相关文档吧。这里只列出在我的环境下和 mysqldump 的对比数据。

2.1 mysqldump 常规使用

#导出耗时time mysqldump -f --single-transaction -B yejr --tables yejr | gzip > /home/databak/yejr.sql.gzreal    10m15.319suser    6m47.946ssys     0m38.496s#文件大小608M /home/databak/yejr.sql.gz#导出期间系统负载05:00:01 PM       all      0.71      0.00      0.61      7.33     91.3605:10:02 PM       all     13.93      0.00      2.21      4.64     79.22

2.2 mysqldump + gzip --fast

#导出耗时time mysqldump -f --single-transaction -B yejr --tables yejr | gzip --fast > /home/databak/yejr_fast.sql.gzreal    9m6.248suser    4m21.467ssys     0m37.604s#文件大小815M Oct 21 17:33 /home/databak/yejr_fast.sql.gz#导出期间系统负载05:20:01 PM       all     11.94      0.00      2.43      5.69     79.9405:30:01 PM       all      6.46      0.00      1.57      3.95     88.02

2.3 mk-parallel-dump 常规使用

time ./mk-parallel-dump --database yejr --tables yejr --basedir /home/databak/default:             25 tables,    25 chunks,    25 successes,  0 failures, 404.93 wall-clock time, 613.25 dump timereal    6m48.763suser    4m20.724ssys     0m38.125s#文件大小819M    /home/databak/default/yejr/#导出期间系统负载05:10:02 PM       all     13.93      0.00      2.21      4.64     79.2205:20:01 PM       all     11.94      0.00      2.43      5.69     79.94

可以看到，mk-parallel-dump 尽快确实实现了并发导出，速度相对快多了，却有个致命伤：那就是它不支持InnoDB的一致性备份，目前已经有人提交相关代码了，不过还没实现，期待中。