Mydumper and mysqldump in mysql are compared and used, and mydumpermysqldump

Mydumper and mysqldump in mysql are compared and used, and mydumpermysqldump

If you only back up several tables or a single database, mysqldump and mydumper are more convenient than innobackup. However, mydumper adds many features, such as multi-thread backup and regular matching backup, group, self-check, and other functions. in addition, mydumper and mysqldump are essentially the same logical data export and do not support online Hot Standby innodb. of course, we can also use innobackup to back up some table data, but it is not a type of backup method with mydumper and mysqldump. Therefore, we will only test the use of mydumper and mysqldump.

Export mydumper

Use the mydumper tool to export and compress test (9.4 GB) data in 8 threads, 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/data/mysql_bak directory, each table in the database is saved as a table definition and data file.

The overall execution time is as follows, which takes 123 s in total

# cat metadata Started dump at: 2017-05-19 10:48:00SHOW 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

Export mysqldump

Use the default mysqldump tool to export and compress the database, as shown below:

# time mysqldump -B test -E -R -h 10.0.21.5 -u root -P 3301 -p | gzip >/data/test.sql.gzEnter password: real  3m19.805suser  4m47.334ssys 0m10.395s

The whole running time of mysqldump in a real row is 199.8 s.

Summary

In general, because there is not much data, the time difference between mysqldump and mydumper is not big. Most of the time is spent on data transmission. If the database is large enough, the advantage of mydumper can be reflected. in addition, the mydumper of earlier versions may cause export errors due to changes in MySQL Syntax of later versions. 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 solve this problem, you can use the mydumper of the higher version. If the higher version still has this problem, you can refer to the github official code to modify the corresponding code.