Hi there!

I didn’t like SloniuPL’s method of backing up MySQL database so I wrote my own. Simpler. Nicer. Easier.

I wrote a script: /root/.bin/MySQL_BackUP.sh.

Which contains:

date &&
echo "Compressing /var/lib/mysql/ into a /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar file" &&
tar cfP /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar /var/lib/mysql/ &&
echo "What's the size of the /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar file?" &&
du -h /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar &&
date &&
echo "Compressing echo /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar into /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar.xz file." &&
xz -f --compress -9 /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar &&
date &&
echo "What's the size of the /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar.xz file?" &&
du -h /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar.xz &&
echo "Mailing the /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar.xz file to andrzejl@icsserver.loc." &&
date &&
echo "MySQL Database Backup `date +%m-%d-%Y`!" | /bin/mail -s "MySQL Database Backup `date +%m-%d-%Y`!" -a /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar.xz andrzejl@icsserver.loc &&
echo "Removing the /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar.xz file." &&
rm /home/andrzejl/MySQL_Database_Backup_`date +%m-%d-%Y`.tar.xz &&
echo "MySQL Database Backup finished!" &&
date

and it does exactly what it says on the box…

Wed Jun 11 07:14:45 IST 2014
Compressing /var/lib/mysql/ into a /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar file
What’s the size of the /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar file?
155M /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar
Wed Jun 11 07:14:52 IST 2014
Compressing echo /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar into /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar.xz file.
Wed Jun 11 07:17:01 IST 2014
What’s the size of the /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar.xz file?
7.1M /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar.xz
Mailing the /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar.xz file to andrzejl@icsserver.loc.
Wed Jun 11 07:17:02 IST 2014
Removing the /home/andrzejl/MySQL_Database_Backup_06-11-2014.tar.xz file.
MySQL Database Backup finished!
Wed Jun 11 07:17:02 IST 2014

And then I have edited my crontab so it looks like this:

0 */6 * * * /root/.bin/MySQL_BackUP.sh

This way I have a fresh and funky database snapshot every 6 hours…

After tiny modifications this script can be used to create a snapshot of any folder and send it to as many recipients and as often as You wish…

That’s what I call backup… and yes I am aware of the fact that it WOULD be best to stop the MySQL server before You will create a backup in case there is some writing going on*… and yes I am aware that mysqldump will create the .tar.gz file for me too… but this is a low profile home server and not some huge corporate database… You like it? Great. You want to use it? Copy script. Modify it. Use it. You have my blessing. I don’t give any warranties. It works for me. I don’t provide support so don’t even ask ;).

Cheers.

Andrzej

PS. * IF * the database is being written to while the backup starts it will not finish backing up:

tar: /var/lib/mysql/ib_logfile0: file changed as we read it

so broken / partially written database in the backup file _shouldn’t_ happen.