MySQL Multithreaded Backup tool mydumper

Mydumper is a high-performance multi-threaded backup and recovery tool for MySQL and drizzle. The developer of this tool is from MySQL, Fackbook, Skysql, and there are already some large product business tests and use of the tool. We can also use the Myloader tool when recovering a database.

Key features of the mydumper include:

First, the use of lightweight C language written code.

Second, compared to mysqldump, its speed is nearly 10 times times faster (to be tested)

Iii. snapshot with transactional and non-transactional table consistency (applied to 0.2.2+)

Four, can be quickly file compression (files compression on-the-fly)

Five, support export Binlog

Six, can be multi-threaded recovery (for 0.2.1+)

Seven, you can use the Daemon working mode, regular scan and output continuous binary log

Test environment: 6 core, 6G memory, backup data is about 1.5G.

Install Mydumper, download Mydumper package on official website: https://launchpad.net/mydumper/

[Email protected] ~]#Yum InstallGlib2-devel mysql-devel zlib-devel pcre-devel cmake-Y[[email protected]~]#TarZXVF mydumper-0.6.2.Tar. gz [[email protected]~]# CD mydumper-0.6.2[email protected] mydumper-0.6.2]# CMake. [Email protected] mydumper-0.6.2]# Make&& Make Install

Description of main parameters in Mydumper: (Mydumper--help)

--host,-h:connect the MySQL server. --user,-u:the user backs up the linked user. --password,-P:the user's password. --port,-P:connection port. --socket,-S: connect the socket file. --database,-B:the database that needs to be backed up. --table-list,-T:tables that need to be backed up, separated by commas (,). --outputdir,-o:the entered directory. --built-empty-files, - e:the default number of data is a table structure file. --regex,-x: supports regular expressions, such as Mydumper-regex'^(?! (mysql|test))'. --ignore-engines,-I:ignores the storage engine. --no-schemas,-m:The table structure is not exported. --long-query-Guard:long query, default 60s. --kill-long-queries,-k: You can set the kill long query. --verbose,-v:0=silent,1=errors,2=warnings,3=info default is 2. --binlogs,-B:export Binlog. --daemon,-D: enable daemon mode. --snapshot-interval,-I:Dump snapshot interval time, default 60s. --logfile, - L:Mysqldumper log output, typically used in daemon mode.

Most of the Myloader parameters are the same as the Mydumper, as follows:

-D,--folder for directory backup files-Q,--queries-per-transaction The number of queries executed per object, the default is-O,--overwrite-Tables If the table you want to restore exists, drop the table first, use this parameter, and back up the table structure when you need to back up-B,--database to be restored-E,--enable-binlog Enabling binary logging of restored data-H,--Host hosts-U,--user-Restored users-P,--pass<a href="http://www.it165.net/edu/ebg/"target="_blank"class="Keylink">word</a>Password-P,--Port Ports-S,--Socket Socket File-T,--threads The number of threads used by the restore, default is 4-C,--compress-Protocol Compression Protocol-V,--version Display versions-V,--verbose output mode,0= Silent,1= Errors,2= Warnings,3=Info, the default is 2

Use the Mydumper and mysqldump backups below to see the backup time as follows:

 [[email protected] TPCC -mysql]#  mydumper-u root-p 123456 -s/data/mysql- 5.5 .    40 /mysql.sock-b tpcctest-o/root/tpcctestreal 0m16.463s  user 0m16.436ssys 0m2.205s[[email protected] TPCC -mysql ]# time /usr/local/mysql-40 /bin/mysqldump-uroot-p123456-s/data/mysql-40 /mysql.sock tpcctest >/root/tpcctest2.sql real 0m37.875s  user 0m28.847ssys 0m3.701s[[email protected] TPCC -mysql]# 

Mydumper back up is a folder, mysqldump back up is a. sql file, the size is as follows:

du -sh  tpcctest1. 5G    du -sh1 . 5G    Tpcctest2.sql

Mydumper is multithreaded, the default 4 threads, you can use the-t option to specify the number of threads, the following separate test results for different threads:

[Email protected] tpcc-mysql]# TimeMydumper-u root-p123456-s/data/mysql-5.5. +/mysql.sock-b Tpcctest-o-T6/root/tpcctest Real 0m16.014suser 0m16.671ssys 0m2.083s[[email protected] TPCC-mysql]# TimeMydumper-u root-p123456-s/data/mysql-5.5. +/mysql.sock-b Tpcctest-o-TTen/root/tpcctestreal 0m17.494suser 0m16.283ssys 0m2.771s[[email protected] TPCC-mysql]# TimeMydumper-u root-p123456-s/data/mysql-5.5. +/mysql.sock-b Tpcctest-o-T3/root/tpcctest Real 0m14.774suser 0m16.662ssys 0m2.814s[[email protected] TPCC-mysql]# TimeMydumper-u root-p123456-s/data/mysql-5.5. +/mysql.sock-b Tpcctest-o-T2/root/tpcctestreal 0m16.382suser 0m16.603ssys 0m2.692s

As you can see, the number of threads is not so much that the backup will be faster.

Restore Speed comparison:

Below are the speed comparisons of Myloader and MySQL direct restore:

 Time 123456 -H localhost-s/data/mysql-5.5. /mysql.sock-b tpcctest-d/root/tpcctest   real    4m55.778suser    0m3.733ssys     0m1.075s

 time mysql-uroot-p123456-s/data/mysql-5.5. /mysql.sock tpcctest </root/tpcctest2.sql     real    7m35.524suser    0m34.156ssys     0m2.032s

Example of backing up a single table with Mydumper:

123456 -H localhost-s/data/mysql-5.5. /mysql.sock-b tpcctest-t customer-o/root/~]# cd customer/ls  Metadata  tpcctest.customer-schema.sql  tpcctest.customer.sql

Mydumper also has a lot of features that interested friends can study under their own ^.^

One friend said he used Mydumper to back up 176G of data for about 21 minutes, but he backed it up with the maximum system resources (server configuration: RAID Ten RAID card does not cache Intel (R) Xeon (r) CPU e5-2670 0 @ 2.60GHz 32 core 32G memory)

Summarize:

Mydumper backup speed is much faster than mysqldump because Mydumper supports multi-threaded backup and recovery

Second, although can support multi-threaded backup, but not the more threads, the faster the speed

Third, the function of Mydumper is also more powerful, more parameters, can be flexibly configured according to demand

Four, mydumper backup and restore speed, and server hardware configuration has a great relationship, this experimental test is just to cite examples.

Reference book: MySQL Management: Performance tuning, high availability and monitoring

MySQL Multithreaded Backup tool mydumper