Shulou(Shulou.com)05/31 Report--

Editor to share with you what is the difference between the comparative use of mydumper and mysqldump in mysql, I believe that most people do not know much about it, so share this article for your reference, I hope you will gain a lot after reading this article, let's go to know it!

Compare mydumper with mysqldump in mysql. 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 with its superior features. Let's verify the backup efficiency between them by testing.

Compared with innobackup, mysqldump and mydumper are more convenient for backing up a few tables or individual libraries, but mydumper adds relatively many features, such as multithreaded backup, regular matching backup, grouping and self-test. In addition, mydumper and mysqldump are essentially the same export logic data, do not support online hot backup innodb. Of course, we can also use innobackup to back up part of the table data, but it is not the same backup method as mydumper and mysqldump, so the following article only tests the use between mydumper and mysqldump.

Mydumper export

Use the mydumper tool to export test (9.4G) data in 8 threads and compress it, 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 a table definition and data file.

The overall execution time is as follows, with a total time of 123s.

# 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: 10:50:03

Mysqldump export

Use the default mysqldump tool to export the library 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.gzEnter password: real 3m19.805suser 4m47.334ssys 0m10.395s

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

Summary

On the whole, due to the lack of data, the time difference between mysqldump and mydumper is not big, and 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 lower version of mydumper may have export errors due to changes in the syntax of the higher version of MySQL, 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 a high version of mydumper, if the high version still has this problem, you can refer to the official github code to modify the code accordingly.

The above is all the content of this article entitled "what is the difference between the comparative use of mydumper and mysqldump in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.