I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? Are there scenarios where one of them might work better? Is mysqldump going to be phased out for mysqlpump?
Since I happened to be researching this myself, here's a summary of what I found.
According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further improvements.
However, the post cautions that mysqlpump is "currently unsafe as a general purpose backup replacement" because individual threads may generate their sections of the dump from different states of the database. It doesn't mention any plans to deprecate mysqldump.
Giuseppe Maxia played around with mysqlpump in 2015 and found the dumping speed-up to be fairly small. He appreciated the new ability to select precisely which objects to dump, but noted that loading the dump back in can only be done single-threaded and that (unlike mysqldump) dumps can only be loaded back into databases with the same name.
I have also been looking into the differences between these two utilities, and in addition to what is mentioned in @Neil's answer, it seems that mysqlpump also has fewer options than mysqldump, such as not allowing for the following:
--tab, and its related options of:
On the other hand, mysqlpump has the following options that are not available in mysqldump
Renamed or similar:
--no-datain dump ==
--no-set-namesin dump does not exist in pump, but its synonym --
--skip-set-charset-- exists in both
--ignore-table=in dump is mostly
--exclude-tables=in pump except
--exclude-tablescan specify all tables, separated by commas, and allows for wildcard characters (
--tablesin dump is mostly
--include-tables=in pump except
--include-tablescan specify all tables, separated by commas, and allows for wildcard characters (
--databasesin dump, whereas
--include-tables=in pump does not.
--databasesin both dump and pump is mostly
--include-databases=in pump except
--include-databasescan specify all databases, separated by commas, and allows for wildcard characters (
Here is the summary of what I understood between both the backup tools.
The definition for mysqldump given in manual page
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfers to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
Mysqldump is very easy to use and syntax is also very simple. When we execute this, it converts all rows to SQL queries and print that in standard output. We can redirect this output to an outfile(>) or using an option — result-file.
By default, mysqldump does not dump information_schema tables and it never takes performance_schema. The major drawback of mysqldump is it uses only one thread while taking backup and restore.
Mysqlpump is another utility which was introduced in MySQL 5.7 with a few more features when compared with mysqldump
The definition for mysqlpump given in manual page
The mysqlpump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfers to another SQL server.
The major features are
Parallel processing (multi threaded) to speed up the backup
Dumping of user accounts as (create, grant statements not as inserts for MySQL system database)
By Default, mysqlpump does not take backup of system databases such as information schema, performance schema, and some MySQL system tables unless it mentioned explicitly.
It also have options –include-database, –exclude-database, –include-table, –exclude-table with pattern matching(%).
These options are more convenient for users who want to backup only a few objects from an entire dataset.
In general, mysqlpump divides its work into chunks and each is assigned to a multi-threaded queue.
This multithreaded queue is processed by N threads (By default it uses 2 threads).
We can define a number of threads by using this option –default-parallelism and –parallel-schemas.
while restoration, it uses a single thread which is the same as mysqldump , it lacks parallel restore.
As an add-on, we have one more tool which performs parallelisation in both backup and restoration (where mysqlpump and mysqldump lacks) which is called mydumper and myloader. This will be greatly used while restoring huge datasets.
You can find some more details and its useful options of these tool in this blog mysqldump vs mysqlpump vs mydumper.