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.
