Bacula DokuWiki

This document, Yet another way to back up MySQL is copyrighted © 2009 by Kevin Keane. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.1 or any later version published by the Free Software Foundation; with no Invariant Sections, with no Front-Cover Texts, and with no Back-Cover Texts. A copy of the license is available at http://www.gnu.org/copyleft/fdl.html .

No liability for the contents of this document can be accepted. Use the concepts, examples and information at your own risk. There may be errors and inaccuracies which could damage to your system. Though this is highly unlikely, proceed with caution. The author(s) do not accept responsibility for your actions.

All copyrights are held by their respective owners, unless specifically noted otherwise. Use of a term in this document should not be regarded as affecting the validity of any trademark or service mark. Naming of particular products or brands should not be seen as endorsements.

Various people have proposed ways to back up MySQL databases. This is an amalgam of many of these ideas.

Most proposals for backing up MySQL databases tell you to use mysqldump. This is simply a script that neatly packages this idea, and also handles a root password neatly.

One requirement I had is to be able to back up and restore individual databases.

Any questions? Please post to the bacula-users mailing list, or visit http://www.4nettech.com and use the Contact Us form.

The underlying idea is to call mysqldump once for each database, and create a separate file for each. The following script will first query MySQL to get a list of all databases, and then automatically create a backup of each one.

With this script, you will get one file per database in the /var/local/mysqlbackups directory. The file name will be <databasename>.mysql

This allows you to easily restore individual databases, rather than MySQL as a whole.

To store the MySQL root password and make it available securely, create the following file as /root/.my.cnf :

[client]
user=root
password=xxxxx
protocol=tcp

Now change the permissions to keep it private:

chmod 400 /root/.my.cnf

Create the following script as /usr/local/sbin/backupdbs

#!/bin/bash

BACKUPLOCATION=/var/local/mysqlbackups

mkdir -p $BACKUPLOCATION

# back up all the mysql databases, into individual files so we can later restore
# them separately if needed.
mysql --defaults-extra-file=/root/.my.cnf -B -N -e "show databases" | while read db
do
   BACKUPFILE=$BACKUPLOCATION/$db.mysql
   echo "Backing up $db into $BACKUPFILE"
   /usr/bin/mysqldump --defaults-extra-file=/root/.my.cnf $db > $BACKUPFILE
done

Change its permission to be executable:

chmod 500 /usr/local/sbin/backupdbs
chown root:root /usr/local/sbin/backupdbs

Create the directory that we use to dump the databases into. Make sure that you have enough space on that partition:

mkdir -p /var/local/mysqlbackups

On the bacula server, you need to do a few things. Since they are standard bacula techniques, I am not going into detail here.

• You need to call the /usr/local/sbin/backupdbs script as a Client Run Before Job in the job that backs up your database server
• You need to make sure that /var/local/mysqlbackups is in your file set.
• Exclude /var/lib/mysql from your file set - since this is where mysql stores its raw database, you do not want it backed up directly.

To actually restore a database, do the following:

• Log on to mysql
• Drop the database you want to restore
• Create the database you want to restore. It will be empty at this point.
• Feed the backup file into mysql