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.
Serg said
Backup… It is very important…
Michael Clark said
You also need to create the tar directory under your $BACKUPPATH directory, for example /var/www/backup/tar
I keep my old backups, so I remove the rm command, and add the date to each tar file by changing the tar and gzip commands to be:
tar -cvf $BACKUPPATH/tar/${bbdd[$index]}.`date +%F`.tar ${bbdd[$index]}/* > /dev/null
gzip $BACKUPPATH/tar/${bbdd[$index]}.`date +%F`.tar > /dev/null
It’s also important to note that the mysqlhotcopy line has two double dashes in it, before password and allowold. WordPress is converting those into an em dash, which renders badly. Same problem with the quotes in the bbdd and let commands.
Cars for sale by owner said
I think MySQL is very useful service for database recovery and I read news about it on June 6, 2009 that, data recovery services and data protection tools, announces the release of updated MySQL Recovery product, Stellar phoenix database recovery For MySQL version 2.0. It now supports MySQL 4.x.
David James