Mysqldump and Mydumper comparison test

1 Introduction

Mysqldump is the official MySQL Backup tool, is a very useful MySQL data transfer tool, with compatible strong, cross-version and other characteristics

Mydumper is a high-performance multi-threaded backup and recovery tool for MySQL that provides concurrent backup capabilities, greatly improved backup efficiency, and a more convenient table recovery by single table.

Mydumper Main features are:

• Lightweight C-language writing

• 10 times times faster execution speed than mysqldump

• Transactional and non-transactional table-consistent snapshots (for 0.2.2 or later)

• Fast File compression

• Export Binlog Supported

• Multi-threaded recovery (for 0.2.1 or later)

• Timed snapshots and sequential binary logs in the daemon's working mode (for 0.5.0 or later)

• Open Source (GNU GPLv3)

In this paper, two tools are tested in a simple comparison and the reference results are given.

2 Installation and Use method

Because mysqldump use more, more familiar, so here mainly introduce Mydumper installation use.

2.1 Mydumper Installation

Mydumper:https://launchpad.net/mydumper, the latest stable version is 0.6.2, after downloading, install as follows:

1#Yum InstallGlib2-devel Mysql-devel Zlib-devel Pcre-develGCC-c++2#wgethttp//launchpad.net/mydumper/0.6/0.6.2/+download/mydumper-0.6.2.tar.gz3#TarZXVF mydumper-0.6.2.Tar. GZ4# CD mydumper-0.6.25 # CMake.6# Make7# Make Install

2.2 Mydumper Common parameters

After the Mydumper installation is complete, include the exported Mydumper tool and the imported Myloader, the main parameters are as follows:

mydumper parameter Description:-B,--Database needs to be backed up-T,--tables-list the tables that need to be backed up, separated by-O,--OutputDir Output Directory-S,--statement-size attempted size of INSERT statementinchBytes, default1000000-R,--rows tries to split into many block tables-C,--Compress compressed output file-E,--build-empty-Files Even if the table has no data, it produces an empty file-X,--regex supports regular expressions-I.,--ignore-engines ignored by the storage engine, separated by-M,--no-schemas do not export table structure-K,--no-locks does not perform a temporary shared read lock warning: This will result in inconsistent backups-L,--Long-query-Guard Length query, default 60s--Kill-Long-queries kill long executed queries (instead of aborting)-B,--binlogs Export Binlog-D,--Daemon Enable daemon mode-I.,--snapshot-interval Dump snapshot interval, default 60s, required in daemon mode-L,--logfile log Files-H,--Host-U,--User-P,--Password-P,--Port-S,--Socket-T,--threads number of threads used, default 4-C,--compress-protocol using compression on MySQL connection-V,--version-V,--verbose more output,0= Silent,1= Errors,2= Warnings,3=Info, default2myloader parameter Description:-D,--Directory import Backup catalog-Q,--queries-per-transaction The number of queries executed per execution, the default-O,--overwrite-tables If a table exists to delete a table-B,--library that database needs to restore-E,--enable-binlog Enabling binary recovery data-H,--Host-U,--User-P,--Password-P,--Port-S,--Socket-T,--threads number of threads used, default 4-C,--compress-using compression on protocol connections-V,--version-V,--verbose more output,0= Silent,1= Errors,2= Warnings,3=Info, default2     

2.3 Using example 2.3.1 to export a database backup

Mydumper you need to specify a backup directory to backup, you cannot back up the entire library to a specific SQL file like mysqldump:

1 mydumper-u root-p ${mysqlpasswd}  4

The exported file looks like the following:

Each SQL file that indicates that the schema is followed is a table structure file, and the other is the table data, if the table has no data by default there is only one schema file, which can be added with the-e parameter to force the generation of an empty data file. If you add the-c parameter, each exported file is compressed and packaged as shown in:

2.3.2 Import Database Backup

1 myloader-u root-p ${mysqlpasswd}-D ${backupdir}-o-b ${db_name}

3 Contrast test

Test method: Using Mysqldump Table Backup method and Mydumper method, mydumper default 4 threads, to backup single or multiple libraries, mainly observe the backup time and the CPU usage of the machine during the backup.

A backup of a single database is backed up by default of 4 threads:

 	200m	4g	7g	30g
mysqldump	8s	2m58s	5m50s	13m13s
mydumper	4s	1m38s	2m56s	7m59s

Continuous backup of multiple databases with a total of approximately 8G of library size

Way	Time
Mysqldump	5m14s
Mydumper	2m05s

During the testing process, the CPU usage of the machine is observed, the CPU usage of mysqldump is about 98%, and the CPU usage is about 200%-400% in the case of Mydumper. So for the CPU-consuming game, the adoption of mydumper will undoubtedly increase the CPU consumption burden, and may have an impact on the game, so it is not recommended to use Mydumper, and if it is a dedicated database and CPU more idle situation, can be used, appropriate increase the number of threads, Backups can be done faster.

In addition, when tested on a standard single-disk machine, when the number of threads is set to 2, the IO is full, with a significant increase in speed compared to the number of threads 1, but after the IO is full, the number of threads is 4, 8, 10, the larger the number of threads, the greater the CPU consumption However, the overall backup time does not have much impact, so when the CPU is idle enough, Io is the bottleneck that affects mydumper performance, and when used, it is reasonable to set the number of threads according to the actual IO situation.

Mysqldump and mydumper comparison test