mysqldump的几个主要选项探究

0、前言

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

1、mysqldump

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

mysqldump 几个主要选项

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

2. –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

3. –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

4. –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

5. –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

6. –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

    关于隔离级别可以看手册 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 yejr | gzip > /home/databak/yejr.sql.gz
real    10m15.319s
user    6m47.946s
sys     0m38.496s

#文件大小
608M /home/databak/yejr.sql.gz

#导出期间系统负载
05:00:01 PM       all      0.71      0.00      0.61      7.33     91.36
05: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 yejr | gzip --fast > /home/databak/yejr_fast.sql.gz
real    9m6.248s
user    4m21.467s
sys     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.94
05: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 time
real    6m48.763s
user    4m20.724s
sys     0m38.125s

#文件大小
819M    /home/databak/default/yejr/

#导出期间系统负载
05:10:02 PM       all     13.93      0.00      2.21      4.64     79.22
05:20:01 PM       all     11.94      0.00      2.43      5.69     79.94

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

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>