Creating files for MySql database backups

NOTE: These instructions are still under test -- they have been published to solicit comments


1. Introduction

These notes have been generated from my attempts to create a web server infrastructure for a collaborative project we are starting. The plan is to use a wiki, blog, mailing loist and other tools to creatre an environment in which reserachers can collaborate in accummulating information generated by the project.

The tools chosen for this include MediaWiki, Drupal and WebCalendar, which all use a MySql database for data storage.

Using a database for storage of valuable information raises the problem of how to back it up. Performing a backup of the database files used by a running database is not reliable, as changes may be made while the backup is being performed, potentially compromising data integrity. One approach might be to stop the database for the duration of the backup, but that means taking the service fully offline for an undetermined period, which is not desirable, or (I hope) necessary.

The approach described here uses a combination of poeriodic database exports to static files of SQL commands, combined with MySql's "binary logging" facility, which can capture incremental changes to the database as they occur.

2. System details

The instructions here have been applied to a Scientific Linux 4.4 (essentially, Redhat Enterprise) server running MySql 4.1.20, all running under VMWare. Some of the instructions depend on features introduced in MySql 4.1.8.

The instructions should be adaptable to other server platforms.

Note that I have chosen to backup database-by-database, rather than using the mysqldmp --all-databases option. My rationale is that (a) this results in more usable backup files, and (b) the periods of database lockout during backup may thereby be reduced.

3. Creating files for MySql database backup

First, enable binary logging from the MySql server: edit file /etc/my.cnf so that section [mysqld] contains the line:


Also create a directory /var/log/mysql/, and sets its owner to be mysql. Now restart mysqld.

Create a file /root/.my.cnf containing the following lines, where <password> is the MySql database root password:

password=<password> }}}

Ensure this file is set to be readable only by user root:

chmod 600 /root/.my.cnf }}}

Create a script file /usr/local/<host>-dump-mysql, where <host> is a name distinguishing this host or MySql instance:

# # Backup selected databases from running MySql # Assumes binary logging is enabled # tgtdir="/var/log/mysql/" host="delos" first="--flush-logs"

# Remember list of old log files oldlogs=echo /var/log/mysql/mysql-bin.[!i]*

for db in mysql wikidb drupaldb webcalendar # CHANGE THIS TO REFLECT LOCAL DATABASES do


# Now move old log files away if test -n "echo /var/log/mysql/oldlogs/mysql-bin.*" ; then

fi mv ${oldlogs} /var/log/mysql/oldlogs/

# # End. }}}

Finally, arrange to run this script as a weekly cron job, preferably at a time when the server can be expected to be lightly loaded - for the example below, 01:00AM on Sunday mornings. Create a file /etc/cron.d/mysqldump with the following contents:

0 1 * * sun root /usr/local/bin/delos-dump-mysql }}}

(Hmmm, the last bit doesn't seem to be working - the weekly cron job didn't run. I'll try '0' instead of 'sun'.)

4. Configuring backup

The live database files should be excluded from any regular backup; e.g. all files in directory and subdirectories of /var/lib/mysql.

Use the command lsof | grep mysqld while MySql is running to determine which files are held open by the database server.

5. Restoring a database


6. Other approaches

A number of other approaches have been suggested to me.

6.1. Single transaction

Use the '--single-transaction' option to get a consistent snapshot of the database. But this only works for transactional tables such as InnoDB and BDB.

6.2. Per-database table locking

Use --lock-tables on a per-database basis.

This option is automatically disabled by the --master-data option, which writes information about the binary log file position to the dump file.

Using --lock-tables instead of --master-data is a simpler option, but as far as I can tell it means that only complete snapshots of the dumped database can be used. I like the --master-data option as it creates the possibility of using relatively infrequent backups (e.g. weekly) in conjunction more frequent (e.g. daily) backups in the form of the binary log.

6.3. mysqlhotcopy

mysqlhotcopy performs the kind of dynamic database copying we try to achieve here. See for details.

But mysqlhotcopy does not support InnoDB, which is required for certain advanced database features used by some applications. Recent versions of Media Wiki apparently use OnnoDB. For more observations about InnoDB, see

6.4. MySQL backup

MySQL backup is a Perl script, available from

6.5. Replicate to a hot standy database

An interesting suggestion was to replicate the running database to a hot standby using the binary log facility. Then, come backup time, the replication facility can be suspended for as long as it takes the standby database to be backed up.

More details are here:

7. References

-- GrahamKlyne 2006-11-27 14:12:06

Creative Commons License
The content of this wiki is licensed under the Creative Commons Attribution-ShareAlike 2.0 England & Wales Licence.

OSS Watch is funded by the Joint Information Systems Committee (JISC) and is situated within the Research Technologies Service (RTS) of the University of Oxford.