MySQL multi-thread import and export tool Mydumper

Today, mysqldump is used online to import data tables from one database to another. The export speed is very slow. So I searched the internet and found this stuff. After the test, I found it was good. I will share it with you.

Brief Introduction
Mydumper is a multi-threaded export and import tool written in C language, and can ensure consistency between multiple tables. of course, the more threads, the better (this is related to the server configuration and many other factors. It can only be used as an experience value rather than an absolute value. When the machine is good, the more threads, the better ).

Principle
How can we achieve data consistency?
Below are official answers
It mainly uses flush tables with read lock and start transaction with consistent snapshot to enable all threads when flush tables with read lock, the current position is obtained through show master status and show slave status (it is easy to use Mydumper to reconstruct slave and ensure data consistency between multiple tables)
The original version is as follows:
This is all done following best MySQL practices and traditions:
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 ")
3.1. 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.
Installation:
Sudo yum install-y gcc-c ++ glib2-devel mysql-devel zlib-devel pcre-devel
Cmake
Make; make install
Two files will be generated after installation
[Mysql @ localhost ~] $ Ls/usr/local/bin/
Mydumper myloader
Example:
Export
Mysql> select count (*) from test;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 1048576 |
+ ---------- +
1 row in set (0.41 sec)
[Mysql @ localhost bin] $./mydumper-u root-p 'xxxxxxxx'-t 4-B test-T test-c -- less-locking-o/home/mysql/
[Mysql @ localhost bin] $ ls/home/mysql/
Test.test-schema. SQL .gz test.test. SQL .gz.
Parameter Details
-U "user"
-P "password"
-T "specifies the number of parallel rows. The default value is 4"
-B "specify DB"
-T "specify table"
-C "compression"
-- Less-locking "minimize lock table lock time (for InnoDB )"
-O "specify directory"

For example:
Set the maximum number of long queries. If a query is longer than this limit, exit mydumper. You can also disable this long query.
Mydumper-u root-p 'xxx' -- long-query-guard 400 -- kill-long-queries
To set regular expression through regex, you need to set the db name
Mydumper-u root-p 'xxxx' -- regex = test. name

Import
Mysql> drop table test;
Query OK, 0 rows affected (0.26 sec)
Mysql> exit
Bye
[Mysql @ localhost bin] $./myloader-u root-p 'xxxxx'-B test-d/home/mysql/
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Test |
+ ---------------- +
1 row in set (0.00 sec)

Mysql> select count (*) from test;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 1048576 |
+ ---------- +
1 row in set (0.39 sec)

Conclusion:
Mydumper is faster than mysqldump in the export and import process because it can be performed in multiple threads)
Note:
-- No-locks Parameter
This parameter is officially provided with an English comment "Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups", which will cause backup inconsistency.
There is a table lock when exporting the myisam table. Therefore, first process the myisam table, record the number of myisam tables, and unlock it immediately after all myisam tables are processed. Minimize the lock time.

Mydumper: MySQL multi-thread logical backup and recovery

Comparison of backup efficiency between MySQL backup tools mysqldump and mydumper