[In-depth understanding of MySQL series]-several main options of mysqldump

Author/Translator: ye Jinrong (Email: imysql@imysql.cn), source: http://imysql.cn, reproduced please indicate/translator and source, and cannot be used for commercial purposes, offenders must investigate.

0. Preface

This article mainly discussesMysqldumpAnd compare themMk-parralel-dumpTo provide more help for the selection of backup methods.

1. mysqldump

First, let's take a look.MysqldumpThe actual working method of several main parameters.

Main mysqldump options
1.-q

It is very easy to do nothing, just adding a SQL _NO_CACHE during export to ensure that the data in the cache will not be read.

081022 17:39:33 7 Connect root @ localhost on
7 Query /*! 40100 SET @ SQL _MODE = ''*/
7 Init DB yejr
7 Query show tables like 'ejr'
7 Query lock tables 'yejr' READ /*! 32311 LOCAL */
7 Query set option SQL _QUOTE_SHOW_CREATE = 1
7 Query show create table 'ejr'
7 Query show fields from 'ejr'
7 Query show table status like 'ejr'
7 Query SELECT /*! 40001 SQL _NO_CACHE */* FROM 'ejr'
7 Query UNLOCK TABLES
7 Quit

2. -- lock-tables

Similar to the above, but with a read local lock added, the LOCK will not stop reading or new data insertion.

081022 17:36:21 5 Connect root @ localhost on
5 Query /*! 40100 SET @ SQL _MODE = ''*/
5 Init DB yejr
5 Query show tables like 'ejr'
5 Query lock tables 'yejr' READ /*! 32311 LOCAL */
5 Query set option SQL _QUOTE_SHOW_CREATE = 1
5 Query show create table 'ejr'
5 Query show fields from 'ejr'
5 Query show table status like 'ejr'
5 Query SELECT /*! 40001 SQL _NO_CACHE */* FROM 'ejr'
5 Query UNLOCK TABLES
5 Quit

3. -- lock-all-tables

This is a bit different. When it requests a global read lock, it will block write operations on all tables to ensure data consistency. After the backup is complete, the session is disconnected and automatically unlocked.

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 'ejr'
6 Query set option SQL _QUOTE_SHOW_CREATE = 1
6 Query show create table 'ejr'
6 Query show fields from 'ejr'
6 Query show table status like 'ejr'
6 Query SELECT /*! 40001 SQL _NO_CACHE */* FROM 'ejr'
6 Quit

4. -- master-data

Except for the show master status added to -- lock-all-tables, there is no change.

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 'ejr'
1 Query set option SQL _QUOTE_SHOW_CREATE = 1
1 Query show create table 'ejr'
1 Query show fields from 'ejr'
1 Query show table status like 'ejr'
1 Query SELECT /*! 40001 SQL _NO_CACHE */* FROM 'ejr'
1 Quit

5. -- single-transaction

InnoDB tables are usually enabled during Backup.-- Single-transactionTo ensure the consistency of the backup. In fact, the working principle is to set the isolation level of this session to repeatable read to ensure that during this session (dump, data submitted by other sessions is not displayed.

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
In this example-- Master-dataTherefore, you need to submit a quick global read lock. Here, we can see that the difference from the above is that the launch is missing.BEGINTo explicitly declare the start of the transaction. Here we useSTART TRANSACTION WITH CONSISTENT SNAPSHOTTo replaceBEGINFor the sake of understanding, you can look at the source code for analysis.

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

For more information about the isolation level, see the manual 13.2.10.3. InnoDB and transaction isolation level, or the previous article: [InnoDB series]-instance parsing Innodb isolation level and lock mode.

AboutSTART TRANSACTION WITH CONSISTENT SNAPSHOTYou can refer to the following manual description:

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. Comparison between mysqldump and mk-parralel-dump

Mk-parralel-dumpIt is a tool in Maatkit, an open-source project, and is mainly maintained by Baron Schwartz.
Mk-parralel-dumpIt is developed by perl and can be used to export data tables concurrently. For more information about the specific functions, see the relevant documentation. Here only list andMysqldump.

2.1 General Use of mysqldump

# Export time mysqldump-f -- single-transaction-B yejr -- tables yejr | gzip>/home/databak/yejr. SQL .gz real 10m15. 319 suser 6m47. 946 ssys 0m38. 496 s # file size 608 M/home/databak/yejr. SQL .gz # system load during export 05:00:01 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

# Export time mysqldump-f -- single-transaction-B yejr -- tables yejr | gzip -- fast>/home/databak/yejr_fast. SQL .gzreal 9m6. 248 suser 4m21. 467 ssys 0m37. 604 s # file size 815 M Oct 21 05:20:01/home/databak/yejr_fast. SQL .gz # system load during export PM all 11.94 0.00 2.43 5.69 79.9405: 30: 01 PM all 6.46 0.00 1.57 3.95

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. 763 suser 4m20. 724 ssys 0m38. 125 s # file size 819 M/home/databak/default/yejr/# system load during export 05:10:02 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

We can see that mk-parallel-dump achieves concurrent export as soon as possible, with a relatively high speed, but a fatal flaw: it does not support consistent backup of InnoDB, someone has already submitted the relevant code, but it has not been implemented yet. We are looking forward to it.