Backing up MySQL data

Backing up files and directories is relatively easy; databases, however, have some special quirks that you need to address. Our examples use MySQL, but the same principles apply to PostgreSQL and other relational databases.

This article is excerpted from O’Reilly’s recently published book Linux System Administration.

If your MySQL server does not need to be available 24×7, a fast and easy offline raw backup method is:

1. Stop the MySQL server:

   # /etc/init.d/mysqld stop

2. Copy MySQL’s data files and directories. For example, if your MySQL data directory is /var/lib/mysql and you want to save it to /tmp/mysql-backup:

   # cp -r /var/lib/mysql /tmp/mysql-backup

   Instead of cp, you can use rsync, tar, gzip, or other commands.

3. Start the server again:

   # /etc/init.d/mysqld start

Online backups are trickier. If you have mutually independent MyISAM tables (no foreign keys or transactions), you could lock each one in turn, copy its files, and unlock it. But you may have InnoDB tables, or someone could write a transaction involving multiple tables. Fortunately there are several reasonable noncommercial solutions, including mysqlhotcopy, mysqlsnapshot, replication, and mysqldump.

mysqlhotcopy is a Perl script that does online raw backups of ISAM or MyISAM tables. The manpage includes many options, but here’s how to back up a single database named drupal:

# mysqlhotcopy -u user -p password drupal /tmp
Locked 57 tables in 0 seconds.
Flushed tables ('drupal'.'access', 'drupal'.'accesslog', 'drupal'.'aggregator_
category', 'drupal'.'aggregator_category_feed', 'drupal'.'aggregator_category_item',
'drupal'.'aggregator_feed', 'drupal'.'aggregator_item', 'drupal'.'authmap', 'drupal'.
'blocks', 'drupal'.'book', 'drupal'.'boxes', 'drupal'.'cache', 'drupal'.'client',
'drupal'.'client_system', 'drupal'.'comments', 'drupal'.'contact', 'drupal'.'file_
revisions', 'drupal'.'files', 'drupal'.'filter_formats', 'drupal'.'filters',
'drupal'.'flood', 'drupal'.'forum', 'drupal'.'history', 'drupal'.'locales_meta',
'drupal'.'locales_source', 'drupal'.'locales_target', 'drupal'.'menu', 'drupal'.
'node', 'drupal'.'node_access', 'drupal'.'node_comment_statistics', 'drupal'.'node_
counter', 'drupal'.'node_revisions', 'drupal'.'permission', 'drupal'.'poll',
'drupal'.'poll_choices', 'drupal'.'poll_votes', 'drupal'.'profile_fields', 'drupal'.
'profile_values', 'drupal'.'role', 'drupal'.'search_dataset', 'drupal'.'search_
index', 'drupal'.'search_total', 'drupal'.'sequences', 'drupal'.'sessions', 'drupal'.
'system', 'drupal'.'term_data', 'drupal'.'term_hierarchy', 'drupal'.'term_node',
'drupal'.'term_relation', 'drupal'.'term_synonym', 'drupal'.'url_alias', 'drupal'.
'users', 'drupal'.'users_roles', 'drupal'.'variable', 'drupal'.'vocabulary',
'drupal'.'vocabulary_node_types', 'drupal'.'watchdog') in 0 seconds.
Copying 171 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 57 tables (171 files) in 1 second (1 seconds overall).

mysqlsnapshot is even easier. It backs up all the ISAM or MyISAM tables on your server to one tar file per database:

# ./mysqlsnapshot -u user -p password -s /tmp --split -n
checking for binary logging... ok
backing up db drupal... done
backing up db mysql... done
backing up db test... done
snapshot completed in /tmp

If you’ve set up MySQL replication for 24×7 availability, you can back up from a slave server using one of the methods just decribed. You’ll also need to save replication info (logs, configuration files, and so on).

For extra protection from hardware corruption (but not human error), set up replication and provide your slave (and/or master) with RAID 1 (mirrored) disks.

Many MySQL sites migrate data from MyISAM to InnoDB tables to get true database transactions and better write performance. The authors of the InnoDB module have a commercial product for online InnoDB backups named InnoDB Hot Backup.

The last method is usually the first mentioned in most documentation: mysqldump. Rather than a raw (verbatim) copy, mysqldump produces an ASCII dump of the specified databases and tables. It works with all MySQL table types, including InnoDB. It’s relatively slow, and the text files it produces are large, although they compress fairly well. It’s useful to create these dumps from time to time, because they contain a straightforward script for recreating your databases and tables from scratch. You can use editors, grep, and other text tools to search through or modify the dump files.

To lock all of your tables and dump them to a single file, enter:


# mysqldump -u user -ppassword -x --all-databases > /tmp/mysql.dump

You can pipe the output through gzip to save some time and space:


# mysqldump -u user -ppassword -x --all-databases | gzip > /
tmp/mysql.dump.gz

A new open source tool (free download, pay for support) called Zmanda Recovery Manager for MySQL provides a useful front end to many of these alternatives. Notable features:

• Has a command-line interface.

• Backs up local databases, or remote databases over SSL.

• Emails the status of the backup procedure.

• Handles all table types, including InnoDB.

• Does not provide any new backup methods. Instead, it chooses among mysqldump, mysqlhotcopy, MySQL replication, or LVM snapshots.

• Supports recovery to a particular transaction or point in time.

Zmanda provides .tar.gz and .rpm files for many Linux distributions. For an installation how-to for Debian, see HowtoForge.