Detailed explanation on the comparison and use of mydumper and mysqldump in mysql

This article mainly shares with you the detailed comparison and use of mydumper and mysqldump in mysql. The MySQL database backup tool has its own mysqldump, which is an official backup tool of mysql. However, the third-party backup tool mydumper is used by more people due to its superior features. Next, we will verify the backup efficiency between them through testing.

If you only back up a few tables or a single library, mysqldump and mydumper are more convenient than innobackup, but mydumper adds relatively many features, such as multi-thread backup, regular matching backup, grouping and Self-test and other functions. In addition, mydumper and mysqldump are essentially the same export logical data and do not support online hot backup innodb. Of course, we can also use innobackup to back up some table data, but it is not the same backup method as mydumper and mysqldump, so the following Only test the use between mydumper and mysqldump.

mydumper export

Use the mydumper tool to export the test (9.4G) data with 8 threads and compress it, as follows Shown:
mydumper -B test --regex 'test.*' -c -e -G -E -R --use-savepoints -h 10.0.21.5 -u root -P 3301 -p xxxxxx -t 8 -o /data/mysql_bak/

In the directory /data/mysql_bak, each table in the library is saved as two files: table definition and data.

The overall execution time is as follows, it takes a total of 123s

# cat metadata 
Started dump at: 2017-05-19 10:48:00
SHOW MASTER STATUS:
  Log: mysql-bin.000406
  Pos: 2165426
  GTID:(null)

SHOW SLAVE STATUS:
  Host: 10.144.127.4
  Log: mysql-bin.000419
  Pos: 506000361
  GTID:(null)

Finished dump at: 2017-05-19 10:50:03

mysqldump export

Use the default mysqldump tool to export the library And compressed, as shown below:

# time mysqldump -B test -E -R -h 10.0.21.5 -u root -P 3301 -p | gzip >/data/test.sql.gz
Enter password: 

real  3m19.805s
user  4m47.334s
sys 0m10.395s

real One line shows that the entire running time of mysqldump is 199.8s

Summary

Overall, since there is not much data, the time difference between mysqldump and mydumper is not big. Most of the time is consumed at the data transmission level. If the library is large enough, the advantages of mydumper can be reflected. In addition, low Due to the syntax changes in higher versions of MySQL, the version of mydumper may cause export errors, such as the following error:

** (mydumper:18758): CRITICAL **: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' 
at line 1 (1064)

To deal with this problem, you can use higher versions Mydumper, if you still have this problem in higher versions, you can refer to the official github code to make corresponding code modifications.

Related recommendations:

mydumper installation details

Detailed introduction to the principle of mydumper

Introduction to the use of MySQL database backup tool Mydumper

The above is the detailed content of Detailed explanation on the comparison and use of mydumper and mysqldump in mysql. For more information, please follow other related articles on the PHP Chinese website!