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.
To actually restore a database, do the following: