14

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?

  • That is just mean to name two similar tools almost identically. – Erik Oct 22 '15 at 2:46
  • @Erik seems like it is a "parallel dump" and it can pump the data faster.. but otherwise I agree. – jkavalik Oct 22 '15 at 7:01
  • 1
    You might also be interested in MyDumper. It appears that mysqlpump has taken a couple of ideas from it - not a bad thing, since the project hasn't moved in more than a year :-( – Vérace Oct 23 '15 at 2:47
  • I'd be interested in seeing some performance tests! – Ryan Foley Oct 23 '15 at 13:06
  • XtradbBackup is another serious contender. – Rick James Nov 3 '15 at 16:52
14

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.

  • 2
    Warning: mysqlpump is closed source software (uncertain future). An Open Source alternative to mysqlpump is github.com/maxbube/mydumper mydumper – Ivanov Aug 20 '18 at 6:43
10

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:

  • --compatible=
  • --tab, and its related options of:
    • --fields-terminated-by=
    • --fields-enclosed-by=
    • --fields-optionally-enclosed-by=
    • --fields-escaped-by=
    • --lines-terminated-by=
  • --quote-names
  • --add-drop-trigger
  • --allow-keywords
  • --apply-slave-statements
  • --comments
  • --compact
  • --compatible
  • --create-options
  • --delete-master-logs
  • --disable-keys
  • --dump-date
  • --dump-slave
  • --enable-cleartext-plugin
  • --flush-logs
  • --flush-privileges
  • --force
  • --ignore-error
  • --include-master-host-port
  • --lock-all-tables
  • --lock-tables
  • --master-data
  • --no-autocommit
  • --no-tablespaces
  • --opt
  • --order-by-primary
  • --pipe
  • --quick
  • --quote-names
  • --set-gtid-purged
  • --shared-memory-base-name
  • --skip-add-drop-table
  • --skip-add-locks
  • --skip-comments
  • --skip-compact
  • --skip-disable-keys
  • --skip-extended-insert
  • --skip-opt
  • --skip-quick
  • --skip-quote-names
  • --skip-triggers
  • --skip-tz-utc
  • --verbose
  • --where
  • --xml

On the other hand, mysqlpump has the following options that are not available in mysqldump

  • --compress-output
  • --default-parallelism
  • --defer-table-indexes
  • --exclude-databases=
  • --exclude-events=
  • --exclude-routines=
  • --exclude-triggers=
  • --exclude-users=
  • --include-events=
  • --include-routines=
  • --include-triggers=
  • --include-users=
  • --parallel-schemas=
  • --skip-definer
  • --users
  • --watch-progress

Renamed or similar:

  • --no-data in dump == --skip-dump-rows in pump
  • --no-set-names in 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-tables can specify all tables, separated by commas, and allows for wildcard characters (% and _)
  • --tables in dump is mostly --include-tables= in pump except --include-tables can specify all tables, separated by commas, and allows for wildcard characters (% and _). Also, --tables overrides --databases in dump, whereas --include-tables= in pump does not.
  • --databases in both dump and pump is mostly --include-databases= in pump except --include-databases can specify all databases, separated by commas, and allows for wildcard characters (% and _).
2

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

  • Progress Indicator

  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.