程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> [深入理解MySQL系列] - mysqldump的幾個主要選項探究

[深入理解MySQL系列] - mysqldump的幾個主要選項探究

編輯：MySQL綜合教程

作/譯者：葉金榮（Email: [email protected]），來源：http://imysql.cn，轉載請注明作/譯者和出處，並且不能用於商業用途，違者必究。

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 --tables 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 --tables 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的一致性備份，目前已經有人提交相關代碼了，不過還沒實現，期待中。