In part 1, I told you how to set up JungleDisk backup for your Linux server. In this part 2, I’ll tell you how to automatically have it dump and backup your MySQL databases (correctly)!
There are security implications if this permissions are not set correctly on the files, as you’re storing your MySQL password in the script, and you’re going to have complete database dumps sitting on your hard drive. However, I will attempt to explain as clearly as possible my solution, and I’m not responsible if it doesn’t work for you.
So, start out by deciding where you want your database-dumping script to run from. A few good example spots are /root (root users home directory), and /etc/jungledisk (with the configuration files). I’ve called my backup script prebackup.sh. You’ll understand the name further below, but I’m going to use that name the rest of the way through.
So create your prebackup.sh script as root, and set it to mode 700.
touch prebackup.sh && chmod 0700 prebackup.sh
This makes sure that root is the only user who can read, write, or execute it.
Now, using your favorite text editor, you can use the following sample script:
#!/bin/bash date=`date -I` dir=/var/backups file=sqlbackup-$date.sql touch $dir/$file && chmod 600 $dir/$file && mysqldump --all-databases -p'__MySQLPassword__' >> $dir/$file find $dir -ctime +7 -delete
Danny pointed out that creating gzipped MySQL dumps, as I had in my original script, is discouraged as it defeats the data de-dup feature of jungledisk. The above script has been changed from the original to make uncompressed dumps. Thanks, Danny!
The last line, with the find statement, is responsible for the cleanup of the directory. It will delete any file which is older than 7 days. If you want more or less time, simply change
+7 to your desired number of days (keeping the
Warning: There’s very little (read: none) sanity-checking in this script. Replace
__MySQLPassword__ with your root MySQL password.
Jay pointed out that there will likely be issues with handling special characters in the SQL password. If you have any suggestions, please feel free to post them in the comments below.
After saving, you should have a 183-byte (give or take) file with 0700 mode:
root@ve:/etc/jungledisk# ls -l prebackup.sh
-rwx------ 1 root root 183 Mar 24 17:08 prebackup.sh
You should make sure that the directory in $dir is owned by user and group root and mode 0700. This will make sure that noone else has access to your dumped databases. Now that you have your script, it’s time to automate it. You could schedule a cron job to run the script, but it’s easier to have JungleDisk run it for you at the start of every backup job.
Start your management-side program, login, and go to your server’s backup configuration. Under Backup Options, check Enable Pre and Post Backup Scripts. Now, click Configure, and in the Pre-Backup Script, enter the full path and filename of your newly created script, i.e. /etc/jungledisk/prebackup.sh.
Now, on your assigned schedule, the server engine will run your database dumping script, and start your backup job. Of course, make sure whatever directory you’re dumping your databases to is included in your backup set.
The last thing to note is this script is amazingly light; it doesn’t delete any old databases and it doesn’t do much else. You’re free to modify it, and I would greatly appreciate any feedback on your modifications.
Comments are welcome, as always.