Mysqldump Principle 2

This paper mainly discusses mysqldumpThe main ways of working, and compare and Mk-parralel-dumpSome of the differences that provide more help for the choice of backup methods.

First of all, look at the mysqldump of the main parameters of the actual mode of operation.

Mysqldump several main options
1.-Q
Very simple, do nothing, just add a when the export Sql_no_cacheTo ensure that the data in the cache is not read.

081022 17:39:33       7 Connect     [email protected] on7 Query       /*!40100 SET @ @SQL_MODE = ' */7 Init DB     yejr7 query< C4/>show TABLES like ' yejr ' 7 query       LOCK TABLES ' Yejr ' READ/*!32311 LOCAL */7 Query       SET OPTION Sql_quote_show_crea te=17 Query       Show create TABLE ' YEJR ' 7 query       show fields from ' YEJR ' 7 Query       Show table status like ' Yejr ' 7 Quer Y       SELECT/*!40001 sql_no_cache */* from ' YEJR ' 7 Query       UNLOCK TABLES7 Quit

2.--lock-tables
Similar to the above, but with the additional read LOCAL lock, the lock does not block reading, nor does it prevent new data from being inserted.

081022 17:36:21       5 Connect     [email protected] on5 Query       /*!40100 SET @ @SQL_MODE = ' */5 Init DB     yejr5 query< C4/>show TABLES like ' yejr ' 5 query       LOCK TABLES ' Yejr ' READ/*!32311 LOCAL */5 Query       SET OPTION Sql_quote_show_crea te=15 Query       Show create TABLE ' YEJR ' 5 query       show fields from ' YEJR ' 5 query       Show table status like ' Yejr ' 5 Quer Y       SELECT/*!40001 sql_no_cache */* from ' YEJR ' 5 Query       UNLOCK TABLES5 Quit

3.--lock-all-tables
This is a bit different. It requests that a global read lock be initiated, which prevents write operations on all tables to ensure data consistency. When the backup is complete, the session is disconnected and automatically unlocked.

081022 17:36:55       6 Connect     [email protected] 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
There is no change other than a SHOW MASTER STATUS with just--lock-all-tables.

081022 17:59:02       1 Connect     [email protected] 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 Que Ry       Show Table status like ' Yejr ' 1 Query       SELECT/*!40001 sql_no_cache */* from ' YEJR ' 1 Quit

5.--single-transaction

InnoDB table when backing up, the option --single-transaction is usually enabled to guarantee the consistency of the backup, in fact it works by setting the isolation level for this session to: repeatable READ to ensure this session (dump) , you will not see data that has been submitted by other sessions.

081022 17:23:35       1 Connect     [email protected] on1 query       /*!40100 SET @ @SQL_MODE = ' */1 query       SET SESSION TR Ansaction Isolation level repeatable READ1 query       BEGIN1 query       UNLOCK TABLES1 Init DB     yejr1 query       SHOW TAB  LES 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, a fast global read lock is required because of the addition of the option --master-data. Here, you can see that the difference from the above is that there is less initiation begin to explicitly declare the beginning of a transaction. Here the START TRANSACTION with consistent SNAPSHOT to replace the practice of BEGIN is not too well understood, you can look at the source code to analyze under.

081022 17:27:07       2 Connect     [email protected] 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
   

You can see the manual 13.2.10.3 about the isolation level. InnoDB and TRANSACTION isolation level, or previous post: [InnoDB Series]-instance resolution InnoDB isolation levels and lock mode.

A description of the START TRANSACTION with consistent SNAPSHOT can be seen in the manual:

The WITH consistent SNAPSHOT clause starts a consistent read for storage engines that is capable of it. This applies is 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 don't change the current transaction isolation level, so it provides a consistent s Napshot if the current isolation level is one, allows consistent read (repeatable read or SERIALIZABLE).

12.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax

2. Comparison of Mysqldump and Mk-parralel-dump

Mk-parralel-dump is a tool in the open source project Maatkit, which is maintained primarily by Baron Schwartz.
Mk-parralel-dump is developed by Perl and can be implemented in parallel with exported data tables. Specific functions do not elaborate, go to see the relevant documents yourself. This is a list of the comparison data in my environment and mysqldump .

2.1 Mysqldump General use

#导出耗时time mysqldump-f--single-transaction-b Yejr--tables Yejr | gzip >/home/databak/yejr.sql.gzreal    10m15.319suser    6m47.946ssys     0m38.496s# File Size 608m/home/databak/ yejr.sql.gz# during export system load 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# File Size 815M OCT 17:33/home/databak/yejr_fast.sql.gz# Export period system load 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 General use

Time./mk-parallel-dump--database yejr--tables yejr--basedir/home/databak/default:             tables, chunks    ,    successes,  0 failures, 404.93 wall-clock time, 613.25 dump timereal 6m48.763suser    4m20.724ssys     0m38.125s# File size 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

Can see, mk-parallel-dump as soon as possible did realize the concurrent export, the speed is relatively fast, but there is a fatal wound: that is it does not support InnoDB consistent backup, has been submitted by the relevant code, but has not been realized, look forward to.

Mysqldump Principle 2