MySQL 5.7.8 – mysqlpump caveat

MySQL 5.7.8-rc2 was released today, and features a new server utility called mysqlpump. This utility contains a number of major improvements over mysqldump including:

  • Parallel processing of databases, and of objects within databases, to speed up the dump process
  • For dump file reloading, faster secondary index creation for InnoDB tables by adding indexes after rows are inserted
  • Better control over which databases and database objects (tables, views, stored programs, user accounts) to dump
  • Dumping of user accounts as account-management statements (CREATE USER, GRANT) rather than as inserts into the mysql system database
  • Capability of creating compressed output
  • Progress indicator

What I wanted to caution however, is that mysqlpump is not currently consistent. That is to say that currently each of the dump threads lack a synchronization point before they start backing up the data. This makes it currently unsafe as a general purpose backup replacement.

The mysqlpump developers are aware of this limitation, and are busy adding this feature into the utility.

Edit: This functionality was added to MySQL 5.7.11.

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Domas Mituzas

    Meanwhile, mydumper had consistent dump threads back in 2009. Teehee!

  • Pavel Katiushyn

    The utility is really interesting. Did anyone benchmark it already?
    Why do you say it is not consistent? Could you give more details?
    It has the same single-transaction option as mysqldump.
    http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_single-transaction

    • Hi Pavel, “A –default-parallelism value greater than 1 and –single-transaction are mutually exclusive.” Since the default parallelism is 2, single-transaction won’t apply.

      Mysqlpump should be much faster to re-import data because it delays secondary key index creation.

      • Pavel Katiushyn

        Thank you Morgan. I missed that, while I was looking through the docs.
        I’ve just made mysqldump of 1Gb table and tested reimport with keys before data import and with later keys creation: the second import was 20% faster in my case.

        I also tested mydumper year ago. Don’t remember the time for dump, but load was 50% faster than with mysqldump.

        Do mysql developers have plans for parallel load utility?