Three simple tools for backing up MySQL databases
One of the most important responsibilities of any administrator is to make regular backups. But unlike backing up regular data, backing up databases isn't a straightforward affair. Designing an effective strategy for taking backups of production MySQL servers depends on a lot of factors. Depending on your situation, your best MySQL backup tool might be mysqldump, AutoMySQLBackup, or MySQL Workbench.
The easiest and safest way to back up a database is to shut down the MySQL server. This ensures you have a consistent copy of the data, since you don't have to worry about parts of the database being modified while it's being backed up. The backups themselves will be completed faster than on a production server, as the server isn't handling requests from other applications.
That said, taking a database server offline isn't a practical solution for most enterprise deployments. So you'll want to use a solution that lets you take backups of a production server without taking it offline.
Types of backups
- Hot backup – A hot backup is a backup of a running database. During a hot backup, neither reads nor writes to the database are blocked. This is the best option for huge databases that can take days or weeks to back up, as well as deployments that can't afford any downtime. However, it's risky, and if not implemented properly it can negatively affect performance.
- Warm backup – A warm backup is also a backup of a database that is still running, but during a warm backup, only read queries are allowed; write queries are blocked from modifying a database. This option works well for most deployments, and you can use a combination of tools to speed up the backup process for large databases.
- Cold backup – A cold backup is a backup performed while the database is shut down. This makes it easy to make a consistent copy of your data. The biggest disadvantage of this method is that the server is not accessible during the time the backup is performed. This approach works best for databases that hold mostly static information, such as a database of employees.
Then there's the question of how you want to back up the database. There are two major ways to back up MySQL's data: either by creating a logical backup or by copying the raw files. A logical backup saves information that represents the database structures. It's made up of SQL statements such as CREATE DATABASE and INSERT. The obvious advantage of such a backup is that you can make changes to the data with a text editor or using tools such as grep. A raw backup, on the other hand, is a backup of the disk partitions of the database server.
Both methods have advantages and disadvantages. Logical backups are simpler, you can restore them by simply piping them into the MySQL server, and logical backups are more compatible between different versions of MySQL. It's actually a standard practice to restore a database from a logical backup when upgrading from one MySQL release to another. But depending on the size and complexity of the database, taking a raw backup and restoring it can be much faster than working with a logical backup.
Take backups with mysqldump
The default backup tool that ships with MySQL server is the command-line mysqldump program. With it you can create a logical backup of all the databases on a server, or individual databases, or only particular tables.
The output dumped by the tool contains all the commands required to recreate the database, complete with tables filled with the original data.
Using the tool is pretty simple:
$ mysqldump --user=root --password --all-databases > dump.sql
This command creates a file named dump.sql in the current directory that contains all the SQL commands to recreate the database and all its tables and fill it with the original data. You can browse the contents of the file with the less command. Here's a brief snippet:
$ less dump.sql
-- MySQL dump 10.13 Distrib 5.5.29, for linux2.6 (i686)
-- Host: localhost Database:
-- Server version 5.5.29
-- Current Database: `drupal7`
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `drupal7` /*!40100 DEFAULT CHARACTER SET latin1 */;
-- Table structure for table `actions`
DROP TABLE IF EXISTS `actions`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `actions` (
`aid` varchar(255) NOT NULL DEFAULT '0',
`parameters` longblob NOT NULL,
`label` varchar(255) NOT NULL DEFAULT '0',
PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores action information.';
/*!40101 SET character_set_client = @saved_cs_client */;
The tool has an extensive list of options, some more commonly used than others. If you want to back up only particular databases, instead of the
--all-databases option use the
--databases option and specify the name of the databases you want to back up. To ensure the backups are consistent, use the
--lock-all-tables option to lock all tables across all databases.
The mysqldump tool works on all the MySQL storage engines. It can do a warm backup at acceptable backup speeds but drags its feet when restoring the database. It works best for smaller databases, and its biggest advantage is that it's scriptable.
Script backups with AutoMySQLBackup
If mysqldump works for your MySQL setup, you can either script it for taking automated backups, or use AutoMySQLBackup. It's a clever little script, based on mysqldump, that does a lot of heavy lifting. It can back up a single database or multiple databases and save them in separate compressed files. Using the script you can easily set up incremental backups that are automatically rotated.
To use the script, download and unpack it. It has an install.sh script that installs the script under /etc/automysqlbackup directory. Edit the configuration file under this directory as per your setup and requirements. The various configuration options are explained in the script itself, but here are the most important ones.
To make sure the script can connect to the MySQL server, make sure you customize the following variables as per your setup:
If you want you can also change the directory where the backups are stored by specifying the location in the
CONFIG_backup_dir variable. Similarly, if you want only a particular list of databases to be backed up instead of everything, specify them with the
CONFIG_db_names variables. Or, you can specify the databases and tables you wish to exclude with the
To set up a rotation policy, you need to tweak the appropriate variables. For example, if you want to do monthly backups on the 28th of every month, weekly backups on Sundays, and keep daily backups for seven days, weekly backups for 14 days, and monthly backups for 30 days, change the variables thusly:
Once the file has been configured, test it by invoking it manually with
# automysqlbackup /etc/automysqlbackup/dbserver.conf, assuming dbserver.conf is the name of our AutoMySQLBackup configuration file. It should create compressed logical backups of the databases in the appropriate directories.
When you're satisfied with the command, you can automate the process by creating a backup script and use cron to run that script daily. The README file bundled with AutoMySQLBackup includes a simple but effective backup script.
Backups with MySQL Workbench
Both mysqldump and AutoMySQLBackup rely on command-line tools to restore the dumps. If you prefer a graphical user interface, there's a GUI backup tool you can use to restore logical backups – among many other things.
If you work with MySQL databases, chances are you use the graphical MySQL Workbench database design tool. In addition to querying and designing databases, you can also use the tool to create (and restore) logical backups of the databases.
MySQL Workbench's main interface is divided into three categories based on function. The backup options are housed under the Server Administration section. To back up your databases, connect to the server from under this category. You will then be switched to a tab that lists all the server administration tools.
Click on the Data Export option in the left pane. Select the databases and tables you want to back up from the list shown in the right pane. When specifying the destination of the backups, MySQL Workbench lets you specify whether you want to export the backup to a folder (where each table is written to a separate file) or to a single self-contained file.
To restore databases, switch to the Data Import/Restore option, point to the location where you've stored the backup dumps, and Workbench will display the backed-up databases and tables. You can select the tables you wish to import and they will be restored.
Other backup options
These three options for creating warm logical backups of databases should work for most enterprise users, but if you need to create a consistent hot backup of your databases, you can turn to the snapshot functionality of your filesystem.
Filesystems such as FreeBSD's ZFS have native support for creating snapshots, and you can also create them using the Logical Volume Manager (LVM) on Linux.
But filesystem snapshots are still not an ideal solution for taking hot backups. For that, you'd need a commercial product such as MySQL Enterprise Backup or Zmanda Recovery Manager.
This work is licensed under a Creative Commons Attribution 3.0 Unported License