Backing up MySQL databases

November 22, 2006 at 1:40 pm · Filed under Development, MySQL

We all know how important it is to backup our servers and personal computers… but we all avoid doing it!! We just went through a major crisis in our servers and for some hours we thought we had lost all the info in them… and our last backup of the databases was a few weeks old!! The rest of the content of the server was recoverable (one of the many good things of using CVS is that you keep many copies of the code in different computers), but the databases are critical.

In the end we managed to solve the problem and everything is up and running now, with no data loss. But as you can imagine we are now setting up a backup system for our MySQL databases. Here’s how we did it, in case it may be helpful for anyone.

We have decided to use mysqlhotcopy, which seems to be a fairly standard tool for the job. First we created a new user for backups, with read only permissions:


CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT , RELOAD , LOCK TABLES ON *.* TO 'backupuser'@'localhost';


Of course you need to change “backupuser” and “password“. After that create a folder to hold all the backups, we used /var/www/backup:


mkdir /var/www/backup


Then we created this script to backup all the databases:


#!/bin/bash

USER=backupuser
PASS=password
BACKUPPATH=/var/www/backup

bbdd=( "db1" "db2" "db3" )
numbbdd=${#bbdd[@]}
index=0

while [ "$index" -lt "$numbbdd" ]
do
mysqlhotcopy -u $USER --password=$PASS --allowold ${bbdd[$index]} $BACKUPPATH > /dev/null
rm -f $BACKUPPATH/tar/${bbdd[$index]}.tar.gz > /dev/null
cd $BACKUPPATH
tar cvf $BACKUPPATH/tar/${bbdd[$index]}.tar ${bbdd[$index]}/* > /dev/null
gzip $BACKUPPATH/tar/${bbdd[$index]}.tar > /dev/null
let "index = $index + 1"
done

You need to change USER, PASS and BACKUPPATH to the ones you are using. You also need to change “bbdd” with the list of databases you want to backup. Save this script as dbbackup.sh and then set execution permissions with “chmod +x dbbackup.sh”.

You can now run the script and you will see that will create a copy of your MySQL databases on your BACKUPPATH (mysqlhotcopy just copies the folder of each database) and you will also see a BACKUPPATH/tar folder with one .tar.gz file for each database.

This is still a very rudimentary backup system (the files are in the same disk, there is no rotation, etc), but it’s a start. We will now setup a system to copy the backup files to all our three servers, probably using rsync.

Advertisement

Permalink