Detailed explanation of the comparison between mydumper and mysqldump in mysql

MySQLThe 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 and regular matching backup, Grouping and self-test 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 as mydumper and mysqldump. Class backup method, so the following only tests the use between mydumper and mysqldump.

mydumper export

Use the mydumper tool to export test(9.4G) with 8 threads The data, and compressed, is as follows:
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 compress it, 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

On the whole, because there is not much data, mysqldump and mydumper The time difference 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, the lower version of mydumper will have the problem of export errors due to the syntax changes of the higher version of MySQL. For example, the following error occurs:

** (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 a higher version of mydumper. If the higher version still has this problem, you can refer to github Official code for corresponding Code modification.

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