mysqldump vs mysqlbackup

Posted on

Question :

I am using MySQL v5.6.x. and using mysqldump takes a long time to dump and restore the db and does not seem practical for DB size larger than 50GB. It looks like the mysqlbackup utility that comes with the MySQL Enterprise Backup is very fast. Any other alternatives or ideas to speed up the dump/load time?

Answer :

Dump based backups (logical backups) and recommended for small-medium databases, however, you could speed up logical backup by using enhanced logical backup tools like mysqlpump or mydumper.

Raw backups are recommended for large databases and there are two major tools to explore: mysqlbackup (Enterprise backup) and Percona xtrabackup (free – only Linux based environments).

At my company, we use Percona XtraBackup on thousands of MySQL instances every night. Some are small, but some are multi-terabyte. It’s a physical backup solution that supports compression, encryption, streaming, partial backups, incremental backups, etc. Restoring is as fast as copying the files into the data directory (i.e. no long-running import is necessary). The only downside is that restoring requires you to shut down the MySQL instance briefly, whereas tools like mysqldump or mydumper allow importing into a running instance.

Leave a Reply

Your email address will not be published. Required fields are marked *