Whether you’re working with MySQL, MySQL Cluster, or any other RDBMS, every database with a requirement for persistent data should always have a backup. As a Production DBA you’re the insurance policy to safeguard the data. Bad things do happen. Backups are your safety net to ensure you always have a way to recover should the worst happen and the database becomes irreparable.
There are many ways to produce a consistent backup of MySQL, I have listed a few of the options available below; Remember backups are your safety net, failing to retrieve a consistent backup when you need it most can be a very career limiting move, so no matter what backup method you choose always test your backups!
Logical Backups
The ever popular mysqldump is a backup and export utility provided with the MySQL binaries that can be used to make logical backups. With a logical backup, the database is exported to a flat file using a series of create and insert SQL statements, to restore the backup, the file is simply passed back to MySQL to recreate the database objects and data using the SQL code.
For small to medium sized databases a logical backup can be an excellent choice, however when working with larger databases, they can take a long time to both produce and recover. For this reason, logical backups aren’t usually the primary backup method when working with very large databases, however it’s still a great idea to try and use logical backups as part of a wider backup policy to provide an added layer of safety.
There are a number of tools available that can perform similar functions to mysqldump, one good example of these would be mk-parallel-dump available from the Maatkit toolkit, whilst not strictly a logical backup tool, by breaking tables into chunks and dumping the data on multiple concurrent threads mk-parallel-dump can significantly increase the speed of exporting data.
Database File Backups
A common way to backup MySQL is to make a copy of the database files on disk. In an environment that’s not operating 24/7 this can easily be done by shutting down MySQL and copying the files out of hours, however even in an environment that is running 24/7, where there are no obvious opportunities for downtime, there are still a number of options available to make a consistent copy of the database files.
mysqlhotcopy is a perl script provided with the MySQL binaries that can be used to automate copying the database files for both MyISAM and Archive tables. mysqlhotcopy is able to take a consistent copy of the files whilst MySQL’s running as it both flushes the tables and acquires a read lock prior to the copy. The main issue with this method is the tables are locked for the duration of the copy, so depending on the size of the database this can obviously have a noticeable impact.
By installing MySQL on a logical volume manager such as LVM or ZFS, it’s possible to take a consistent copy of the database files by taking a Snapshot of the filesystem that contains the files. This method isn’t limited to MyISAM and Archive tables, and can safely be used for InnoDB. Again when working with MyISAM it’s important to flush and lock the tables prior to the Snapshot, however as the lock is only required whilst the Snapshot is created, this doesn’t have the same impact as with mysqlhotcopy. mylvmbackup is a very useful perl script written by Lenz Grimmer that automates a LVM Snapshot and database file backup for both MyISAM and InnoDB tables. Snapshots can be a sound backup strategy for databases of any size, but they do require some additional technical knowledge, additionally they can have a performance overhead, so it’s important to benchmark a Snapshot to ensure the impact on the operation isn’t intrusive.
Another alternative to backup the database files without interrupting the live service would be to use MySQL Replication to replicate the database to a slave, the slave can be stopped to allow a backup of the database files offline. In most cases, introducing an additional slave, requires additional hardware – this obviously increases the cost and manageability of the overall solution, MySQL Replication is also asynchronous so it’s important to monitor a slave when using it for backups.
3rd Party Tools
There are a number of third party tools available for backing up MySQL, Zmanda Recovery Manager for MySQL provides an automated way of executing a backup using mysqldump or one of the various database file backup techniques mentioned above. ZRM for MySQL is available in both Community and Enterprise Editions, the commercial Enterprise Edition extends the number of backup options supported and ties in with a GUI to provide an added featureset.
InnoDB HotBackup is a tool commercially available from InnoBase (a subsidiary of Oracle) that allows a consistent backup for both MyISAM and InnoDB tables without interrupting the database or requiring additional architecture, since the Oracle takeover of Sun, InnoDB HotBackup has now been re-branded as MySQL Enterprise Backup and will be available as a feature of MySQL Enterprise. XtraBackup is an open source tool developed by Percona that adopts the same approach as InnoDB HotBackup and allows a consistent backup to be taken on MyISAM, InnoDB or Percona’s propriety engine XtraDB. Both InnoDB HotBackup and Xtrabackup are more than capable alternatives to database file backups. Recently both tools have also introduced a page level “incremental” backup, this can be used to reduce the capacity requirements of a backup policy, however it can also add some additional complexity to the backup and restore process.
MySQL Cluster
MySQL Cluster has a native backup tool that can be executed from the Management Node to take a consistent backup of the data on each Data Node.
mysqldump can be used in combination on the Application Nodes to backup the Database Schemas including any routines, triggers, and events, it can also be used to create a logical backup or data export on MySQL Cluster but to ensure consistency the Cluster has to be put into single user mode.