Shulou(Shulou.com)06/01 Report--

Mysqlpump is a unique logical backup tool after mysql5.7.8 version. It has more features than mysqldump and mysqldumper,mysqlpump. The official documents are described as follows:

Mysqlpump features include: Parallel processing of databases, and of objects within databases, to speed up the dump process Better control over which databases and database objects (tables, stored programs, user accounts) to dump Dumping of user accounts as account-management statements (CREATE USER, GRANT) rather thanas inserts into the mysql system database Capability of creating compressed output Progress indicator (the values are estimates) For dump file reloading, faster secondary index creation for InnoDB tables by adding indexes after rows are inserted

1. Support multi-thread backup of database and database objects to speed up the backup process.

2. Have better control strategies to back up databases and database objects (tables, stored procedures, accounts).

3. Backup users manage SQL (create user,grant) through their account instead of inserting records into the mysql system library

4. Ability to create compressed files

5. Show progress (estimate)

6. Reload (restore) the backup file, build the table first, then insert the data and finally establish the index, which reduces the index maintenance overhead and speeds up the restore speed.

Note: after the mysql5.7.11 version, mysqlpump solves the problem of consistent backup. If it is earlier than this version, it is best to use mysqldump for logical backup to solve the problem of consistent backup.

Before MySQL 5.7.11, use of the-- single-transaction option is mutuallyexclusive with parallelism. To use-single-transaction, disable parallelismby setting-default-parallelism to 0 and not using any instances of-parallel-schemas:shell > mysqlpump-single-transaction-default-parallelism=0

In order to test the mysqlpump performance, I did a little test on my virtual machine.

Test the virtual machine environment:

Host:

CPU:Intel (R) Core (TM) i5-6400 CPU @ 2.70GHz 4 cores

Memory: 1G

Disk: SCSI hard disk 10G

Database:

Version: 5.7.16

Prepare test data with sysbench. Here is the test data preparation script:

[root@mysql1 shell] # cat test_sysbench.sh #! / bin/shsysbench-- test=oltp-- mysql-host=192.168.110.100-- mysql-port=3307-- mysql-user=root-- mysql-password=root-- mysql-db=sbtest2-- oltp-num-tables=4-- oltp-table-size=500000-- report-interval=100-- max-requests=0-- oltp-test-mode=nontrx-- oltp-nontrx-mode=select-- oltp-read-only=off-max-time=30-- num-threads=16 prepare sysbench-- test=oltp-- mysql-host=192. 168.110.100-- mysql-port=3307-- mysql-user=root-- mysql-password=root-- mysql-db=sbtest3-- oltp-num-tables=4-- oltp-table-size=500000-- report-interval=100-- max-requests=0-- oltp-test-mode=nontrx-- oltp-nontrx-mode=select-- oltp-read-only=off-- max-time=30-- num-threads=16 prepare sysbench-- test=oltp-- mysql-host=192.168.110.100-mysql-port=3307-mysql-user=root-mysql-password=root-- mysql-db=sbtest4-- oltp -num-tables=4-oltp-table-size=500000-- report-interval=100-- max-requests=0-- oltp-test-mode=nontrx-- oltp-nontrx-mode=select-- oltp-read-only=off-- max-time=30-- num-threads=16 prepare

The test results are as follows: (note that the mysql service needs to be restarted for each test to avoid memory buffering affecting the test results)

Shell > time mysqlpump-uroot-proot-S / mysqldata/mysql/mysql3307/mysql3307.sock-A-- single-transaction-- default-parallelism=1 > pump.sqlreal 0m46.317suser 0m13.602ssys 0m3.949sshell > time mysqlpump-uroot-proot-S / mysqldata/mysql/mysql3307/mysql3307.sock-A-- single-transaction-- default-parallelism=2 > pump.sqlreal 0m48.707suser 0m15.087ssys 0m3.965sshell > time mysqlpump-uroot-proot-S / mysqldata/mysql/mysql3307/mysql3307.sock-A-- single-transaction-- default -parallelism=4 > pump.sqlreal 0m55.529suser 0m14.783ssys 0m4.143sshell > time mysqldump-uroot-proot-S / mysqldata/mysql/mysql3307/mysql3307.sock-- set-gtid-purged=OFF-A-- single-transaction > dump.sqlreal 0m57.089suser 0m8.870ssys 0m3.441sseam + split line + shell > time mysqlpump-uroot-proot-S / mysqldata/mysql/mysql3307/mysql3307.sock-A-- single-transaction-- default-parallelism=1-- compress_output=LZ4-- skip-watch-progress > pump.sqlreal 1m6 .681suser 0m19.521ssys 0m4.041sshell > time mysqlpump-uroot-proot-S / mysqldata/mysql/mysql3307/mysql3307.sock-A-- single-transaction-- default-parallelism=1-- compress_output=ZLIB-- skip-watch-progress > pump.sql real 1m8.877suser 0m27.315ssys 0m4.693sshell > time mysqldump-uroot-proot-S / mysqldata/mysql/mysql3307/mysql3307.sock-- set-gtid-purged=OFF-A-- single-transaction | gzip > dump.sql.gzreal 1m8.567suser 0m20.797ssys 0m8.082s

From the test results, we can find that:

1. Mysqlpump is indeed more efficient than mysqldump.

2. However, due to the limited disk IO of the test machine, when the number of concurrent threads is increased from 1 to 2 and 4, the backup time increases because of the bottleneck of IO.

3. As the tested database table data is small and the test data is distorted, it is recommended to prepare more database and large table data for testing, the effect will be more obvious.

4. The compression efficiency of mysqlpump LZ4 compression algorithm is higher than that of ZLIB compression algorithm.

The usage of mysqlpump is highly consistent with that of mysqldump, and the most interesting thing is:

1. Support table-based multi-threaded export function (--default-parallelism, default is 2mai Meri parallelMeischemas, which controls parallel export libraries)

2. Export with a progress bar (--watch-progress, enabled by default)

3. Support direct compression export import (parameter-compress-output, and support ZLIB and LZ4)

Detailed usage can be found by looking up the official document or mysqlpump-help.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.