MySQL multithreaded data import and Export tool Mydumper

http://afei2.sinaapp.com/?p=456

Today, using mysqldump to import data tables from one library to another, the results are particularly slow, with the impression that there is a multi-threaded data import and Export tool Mydumper, so simply investigate and test it.
In the course of the afternoon data, this table is not updated, so there is no need to ensure consistency between multiple data, simply write a shell script to start multiple mysqldumper to guide the data, there are several problems:

1. Need to deal with the uneven size of the table data, some will soon end, and some will be relatively slow.
2. If you need to guarantee consistency between multiple exports, there is no guarantee.

Mydumper is a multi-threaded export Import tool written in C and can guarantee consistency across multiple tables. Mydumper already several blog in discussion: Mydumper performance test, mydumper use and source code analysis. Through the stronghearted test, we see that not more threads, the better, 6 threads at the time of the fastest (this is definitely related to the configuration of the machine and so on, only as an empirical value rather than absolute, the machine is good, the more threads the better). First, principle mydumper how to ensure the consistency of data? Here is the official solution, excerpt from the following, mainly using flush tables with read lock and start transaction with consistent snapshot, when flush tables with read lock open All threads are restarted, and the current position is obtained through show master status and show slave status (facilitates the use of mydumper to reconstruct slave and ensure data consistency between multiple tables).

1. Global write Lock is acquired ("FLUSH TABLES with READ lock")
2. Various metadata is read ("Show SLAVE Status", "Show MASTER status")
3. Other threads connect and establish snapshots (' START TRANSACTION with consistent SNAPSHOT '), on pre-4.1.8 it creates dummy InnoDB table, and reads from it.
4. Once all worker threads announce the snapshot establishment, master executes "UNLOCK TABLES" and starts queueing jobs.

Second, installation

1. Download source code, Https://launchpad.net/mydumper
2. Read the Readme, depending on the OS, install different dependencies
3. CMake.
4. Make

Third, the use

Mydumper cannot read/ETC/MY.CNF configuration file, need to manually develop user name, password, etc.

Mydumper-p 3306-u admin-p ' * * * * db23-b meituan-t test1,test2,test3,test4,test5-o./myloader-v 3--threads=6-p 3306-u admin-p ' * * * ' h 127.0.0.1-s/opt/tmp/mysql3306.sock-b test-d.

Iv. Testing

A simple test was made for mysqldump and Mydumper, and the results were as follows (the test results were affected by the environment and the results are for reference only): 5 tables per table 600M. Export:Remote, export two times, take the minimum value. Mysqldump 37sMydumper 21s Import:Mysqldump 14m4smydumper 9m4s Five, conclusion: Mydumper in the export import process because it can be multithreaded, so the speed is definitely better than mysqldump, can be used to replace mysqldump;mydumper cannot read/etc /MY.CNF in the configuration file, this is quite troublesome and must be specified. 0 You may also like:

• MySQL data file--. frm file (table structure recovery)
• MySQL uses the event to back up data as a single table every day
• MySQL Replication filtering options replicate* filtering rules
• Change InnoDB data page size optimization MySQL

MySQL multithreaded data import and Export tool Mydumper