Comparison of mydumper and mysqldump in mysql

If you only want to back up a few tables or a single database, mysqldump and mydumper are more convenient than innobackup. However, mydumper has relatively more features, such as multi-threaded backup, regular matching backup, grouping and self-checking. In addition, mydumper and mysqldump are essentially the same in exporting logical data, and do not support online hot backup of 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 tests 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 shown below:
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 database is saved as two files: table definition and data.

The overall execution time is as follows, 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 repository and compress it, as follows:

# 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

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

Summarize

In general, due to the small amount of data, the time difference between mysqldump and mydumper is not large. Most of the time is spent on data transmission. If the database is large enough, the advantage of mydumper can be reflected. In addition, due to the changes in the syntax of the higher version of MySQL, the lower version of mydumper will have 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 solve this problem, you can use a higher version of mydumper. If this problem still exists in the higher version, you can refer to the official code on github and make corresponding code modifications.