Use mydumper to back up MySQL databases with multiple threads

Mysqldump: one of its features is that the list needs to be locked during processing. Therefore, if we need to perform backup during working hours, it will cause DML blocking. However, MySQL usually has master and slave databases, and most of the backups are performed from the master database. Therefore, you do not need to consider the lock issue. In this way, mydumper can better complete the backup task.
Mydumper is a high-performance multi-thread backup and recovery tool for MySQL and Drizzle. Developers mainly come from MySQL, Facebook, and SkySQL.

Copy codeThe Code is as follows:
1: lightweight C Language
2: The execution speed is 10 times faster than mysqldump
3: consistent snapshots for transactional and non-transactional tables (applicable to versions 0.2.2 and later)
4: Fast File compression
5. binlog export is supported.
6: multi-thread recovery (applicable to versions 0.2.1 and later)
7: Working with daemon, scheduled snapshots, and continuous binary logs (applicable to versions 0.5.0 and later)
8: open-source (GNU GPLv3)

Download and install: Environment: Ubuntu 12.04

Copy codeThe Code is as follows:
Wget https://launchpad.net/mydumper/0.5/0.5.2/+download/mydumper-0.5.2.tar.gz

Installation: After unzipping, installation instructions are provided.

Copy codeThe Code is as follows:
Apt-get install libglib2.0-dev libmysqlclient15-dev zlib1g-dev libpcre3-dev g ++
Cmake.
Make

Copy codeThe Code is as follows:
Root @ dd :~ Mydumper-0.5.2 # cmake.
-- The CXX compiler identification is GNU
-- Check for working CXX compiler:/usr/bin/c ++
-- Check for working CXX compiler:/usr/bin/c ++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info-done
-- Using mysql-config:/usr/bin/mysql_config
-- Found MySQL:/usr/include/mysql,/usr/lib/x86_64-linux-gnu/libmysqlclient_r.so;/usr/lib/x86_64-linux-gnu/libpthread. so;/usr/lib/x86_64-linux-gnu/libz. so;/usr/lib/x86_64-linux-gnu/libm. so;/usr/lib/x86_64-linux-gnu/librt. so;/usr/lib/x86_64-linux-gnu/libdl. so

CMake Warning at docs/CMakeLists.txt: 9 (message ):
Unable to find sphsf-documentation generator

--------------------------------------------------
-- MYSQL_CONFIG =/usr/bin/mysql_config
-- CMAKE_INSTALL_PREFIX =/usr/local
-- BUILD_DOCS = ON
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake-D <Variable >=< Value>
--------------------------------------------------
--
-- Processing ing done
-- Generating done
-- Build files have been written to:/root/mydumper-0.5.2
Root @ dd :~ Mydumper-0.5.2 # make
Scanning dependencies of target mydumper
[2, 20%] Building C object CMakeFiles/mydumper. dir/mydumper. c. o
[40%] Building C object CMakeFiles/mydumper. dir/binlog. c. o
[2, 60%] Building C object CMakeFiles/mydumper. dir/server_detect.c.o
[2, 80%] Building C object CMakeFiles/mydumper. dir/g_unix_signal.c.o
Linking C executable mydumper
[2, 80%] Built target mydumper
Scanning dependencies of target myloader
[2, 100%] Building C object CMakeFiles/myloader. dir/myloader. c. o
Linking C executable myloader
[2, 100%] Built target myloader

Generate two tools: mydumper (Backup) and myloader (import)

Parameters:
Mydumper:
Copy codeThe Code is as follows:
Root @ dd :~ /Mydumper-0.5.2 #./mydumper -- help
Usage:
Mydumper [OPTION...] multi-threaded MySQL dumping

Help Options:
-?, -- Help Show help options

Application Options:
-B, -- database to be backed up
-T, -- tables-list tables to be backed up, separated by commas
-O, -- outputdir directory of the output file
-S, -- statement-size: the number of bytes of the insert statement. The default value is 1000000.
-R, -- rows split into many row block tables
-C, -- compress compressed output file
-E, -- build-empty-files: even if the table has no data, an empty file is generated.
-X, -- regex Regular Expression: 'db. table'
-I, -- storage engine ignored by ignore-engines, separated by commas
-M, -- no-schemas does not export the table structure
-K, -- no-locks do not execute the shared read lock warning: this will cause inconsistent backups
-L, -- long-query-guard: Set the long query time. The default value is 60 seconds.
-- Kill-long-queries kill the query that has been executed for a long time
-B, -- binlogs export binlog
-D, -- daemon enable daemon mode
-I, -- snapshot-interval dump snapshot interval. The default value is 60 s. It must be in daemon mode.
-L, -- logfile Log File
-H, -- host The host to connect
-U, -- user Username with privileges to run the dump
-P, -- password User password
-P, -- port TCP/IP port to connect
-S, -- socket UNIX domain socket file to use for connection
-T, -- the number of threads used by threads. The default value is 4.
-C, -- compress-protocol use the compression protocol on the mysql connection
-V, -- version Show the program version and exit
-V, -- verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Myloader:
Copy codeThe Code is as follows:
Root @ dd :~ /Mydumper-0.5.2 #./myloader -- help
Usage:
Myloader [OPTION...] multi-threaded MySQL loader

Help Options:
-?, -- Help Show help options

Application Options:
-D, -- directory where the backup file is located
-Q, -- queries-per-transaction: number of queries executed each time. The default value is 1000.
-O, -- overwrite-tables: delete a table if the table exists. Note that you need to back up the table structure when using this parameter. Otherwise, a problem may occur.
-B, -- database to be restored
-E, -- enable-binlog enable binary data recovery
-H, -- host The host to connect
-U, -- user Username with privileges to run the dump
-P, -- password User password
-P, -- port TCP/IP port to connect
-S, -- socket UNIX domain socket file to use for connection
-T, -- the number of threads used by threads. The default value is 4.
-C, -- compress-protocol use the compression protocol on the connection
-V, -- version Show the program version and exit
-V, -- verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Test: Test basic usage

1: Backup
Copy codeThe Code is as follows:
./Mydumper-u zjy-p #####-h 192.168.220.245-P 3306-B chushihua-o/home/zhoujy/bak/

Back up the analyzedxy database to the/home/zhoujy/bak/directory to check whether multithreading is required:

Copy codeThe Code is as follows:
| 4937639 | zjy | 192.168.200.25: 34781 | NULL | Query | 0 | NULL | show processlist |
| 4937677 | zjy | 192.168.200.25: 34791 | NULL | Query | 10 | Writing to net | SELECT /*! 40001 SQL _NO_CACHE */|
| 4937678 | zjy | 192.168.200.25: 34792 | NULL | Query | 5 | Writing to net | SELECT /*! 40001 SQL _NO_CACHE */|
| 4937679 | zjy | 192.168.200.25: 34793 | NULL | Query | 10 | Writing to net | SELECT /*! 40001 SQL _NO_CACHE */|
| 4937680 | zjy | 192.168.200.25: 34794 | NULL | Query | 10 | Writing to net | SELECT /*! 40001 SQL _NO_CACHE */|

The above shows that there are indeed 4 threads (default) in the backup, view the backup file:

Copy codeThe Code is as follows:
Root @ zhoujy:/home/zhoujy/bak # ls-lh
-Rw-r -- 1 root 322 chushihua. dba_hospital_all_name-schema. SQL
-Rw-r -- 1 root 16 M 2013-11-14 17:59 chushihua. dba_hospital_all_name. SQL
-Rw-r -- 1 root 221 chushihua. dba_hospital-schema. SQL
-Rw-r -- 1 root 658 chushihua. dba_hospital. SQL
-Rw-r -- 1 root 198 chushihua. dba_jobTitle-schema. SQL
-Rw-r -- 1 root 300 chushihua. dba_jobTitle. SQL
-Rw-r -- 1 root 261 chushihua. dba_locatedCity-schema. SQL
-Rw-r -- 1 root 202 K 2013-11-14 17:59 chushihua. dba_locatedCity. SQL

Analysis: mydumper backs up data and table structures separately, and backs up binary logs separately in a file.

Copy codeThe Code is as follows:
Metadata: the metadata records the backup start and end times, and the location of the binlog log file.
Table data: one file for each table
Table schemas: table structure file
Binary logs: After the -- binlogs option is enabled, binary files are stored in the binlog_snapshot directory.
Daemon mode: In this mode, there are five directories: 0, 1, binlogs, binlog_snapshot, and last_dump.
The backup directory is 0 and 1. Backup is performed at intervals. If mydumper fails for some reason, there will still be a good snapshot. After the snapshot is complete, last_dump points to the backup.

2: Restore: to restore to another server, first create the database to restore (chushihua)

Copy codeThe Code is as follows:
./Myloader-u root-p 123456-h 192.168.200.25-P 3307-B chushihua-d/home/zhoujy/bak/

Check whether multiple threads are supported like backups:

Copy codeThe Code is as follows:
| 19 | root | NULL | Query | 0 | init | show processlist |
| 30 | root | chushihua | Query | 5 | update | insert into |
| 31 | root | chushihua | Query | 5 | update | insert into |
| 32 | root | chushihua | Query | 5 | update | insert into |
| 33 | root | chushihua | Query | 5 | update | insert into |

The preceding figure shows that four threads (default) are restored.
Further test: test some common parameters
1): backup the specified table (-T) and do not export the table structure (-m)

Copy codeThe Code is as follows:
. /Mydumper-u root-p 123456-h 192.168.220.252-P 3306-m-B test-T B, a, c, d, e, g, f, h, i-o/home/zhoujy/bak/

Zhoujy @ zhoujy :~ /Bak $ ls-lh
-Rw-r -- 1 zhoujy 3.4 K 2013-11-14 test. a. SQL
-Rw-r -- 1 zhoujy 1.6 M 2013-11-14 test. B. SQL
-Rw-r -- 1 zhoujy 7.8 M 2013-11-14 test. c. SQL
-Rw-r -- 1 zhoujy 1.7 M 2013-11-14 test. d. SQL
-Rw-r -- 1 zhoujy 303 K 2013-11-14 test. e. SQL
-Rw-r -- 1 zhoujy 517 K 2013-11-14 test. f. SQL
-Rw-r -- 1 zhoujy 646 K 2013-11-14 test. g. SQL
-Rw-r -- 1 zhoujy 394 K 2013-11-14 test. h. SQL
-Rw-r -- 1 zhoujy 34 K 2013-11-14 20:57 test. I. SQL
-Rw-r -- 1 zhoujy 75 2013-11-14 20:57 metadata

2) compress the backup file (-c), back up binlog (-B), and back up the table using the regular expression (-x)

 Copy codeThe Code is as follows:
../Mydumper-u root-p 123456-h 192.168.200.25-P 3306-m-c-B -- regex = tmp. *-B test-o/home/zhoujy/bak/

Drwx ------ 2 zhoujy 4.0 K binlog_snapshot
-Rw-r -- 1 zhoujy 133 metadata
-Rw-r -- 1 zhoujy 94 K 2013-11-14 21:16 test.tmp_0808. SQL .gz
-Rw-r -- 1 zhoujy 75 K 2013-11-14 21:16 test.tmp_0809. SQL .gz
-Rw-r -- 1 zhoujy 25 K 2013-11-14 21:16 test.tmp_0813. SQL .gz
-Rw-r -- 1 zhoujy 208 K test.tmp_0826. SQL .gz
-Rw-r -- 1 zhoujy 915 test.tmp_0827. SQL .gz
-Rw-r -- 1 zhoujy 901 test.tmp_0912. SQL .gz
-Rw-r -- 1 zhoujy 2.1 K test.tmp_0916. SQL .gz
-Rw-r -- 1 zhoujy 622 K 2013-11-14 test.tmp_0918_a. SQL .gz
-Rw-r -- 1 zhoujy 28 M 2013-11-14 21:16 test.tmp_0918_ff. SQL .gz
 

As shown above, the backup file is compressed (decompressed with gzip-d) and tmp is backed up. * binary logs of all matched tables are also backed up to the binlog_snapshot file and compressed.
3) restore the table. Delete the table first (-o). Note that using this parameter, the backup file with the table structure is required in the backup directory.

Copy codeThe Code is as follows:
./Myloader-u root-p 123456-h 192.168.200.25-P 3306-o-B test-d/home/zhoujy/bak/

Test more parameters by yourself.
Last test: Use mysqldump and mydumper for comparative test.

Copy codeThe Code is as follows:
#! /Usr/bin/env python
# Coding = UTF-8
Import MySQLdb
Import OS
Import sys
Import time

Backup = OS. system ('')

Def mysqldump_data ():
T1 = time. time ()
Backup = OS. system ('mysqldump -- no-defaults-uroot-p123456-h192.168.200.25 -- default-character-set = utf8 test>/home/zhoujy/test. Bak ')
T2 = time. time ()
T = round (t2-t1)
Print "mysqldump Cost Time % s" % t

Def mydumper_data ():
T1 = time. time ()
Backup = OS. system ('mydumper-u root-p 123456-h 192.168.200.25-P 3306-B test-o/home/zhoujy/bak /')
T2 = time. time ()
T = round (t2-t1)
Print "mydumper Cost Time % s" % t

If _ name _ = '_ main __':
Mysqldump_data ()
Mydumper_data ()

Two databases were tested:

1:

Mysqldump Cost Time: 162 s
Mydumper Cost Time: 61 s

2:

Mysqldump Cost Time: 483 s
Mydumper Cost Time: 337 s

From the above time point of view, mydumper indeed improves the backup data and restores the data.

Summary:
From the test analysis above, we can see that mydumper can improve the efficiency of backup and restoration, which is limited by the disk I/O capability. we can evaluate the disk I/O before using this tool.