How-To: Auto-Backup Your Database Part I: cPanel
Date: August 15th, 2010
This instruction set is intended for those using a shared server account with HostGator. If you're not using a HostGator account, or have a different type of account, this may not work exactly as-is. Use with discretion, and remember your server host's support ticket system is here to help!
Alright, here is how we create a fancy auto-backing up database thing. You can do this without the SH file, but it's the easiest way to do it if you're backing up multiple databases because you can just edit the file to add more. I've decided to create a nonexistant database for this purpose, say... ocdt_books, with a database username of ocdt_books1 and a password of Dewey. And let's say my server's username is ocdt.
First, we are going to create our mysql.sh file, like below. Everything that is italicized is something you will have to change to get this to work for your account. Don't forget that your master username goes in that URL of where to place the file, because if you're like me, you don't have a dedicated server, and hence need to drop it in the mysql folder within your personal HostGator account.
mysqldump -u ocdt_books1 -pDewey ocdt_books > /home/ocdt/mysql/ocdt_books.`date +%F`.sql
The mysqldump line can be reused in this file for as many different databases as you want to back up. Another important thing about this file, before we move on, is that if your password has an exclamation point in it, it won't work, because it interferes with the bash script. Additionally, note that there is no space between -p and the database password; it won't work if there is. Finally, your database user must have LOCK TABLES permissions. I discovered these problems myself.
Now we upload this file to your /mysql folder on your server, and then we'll move on to setting up the Cron job. In your cPanel, go to the Advanced box, and look for Cron Jobs
We're going to set our backup to run every week. Now, with a shared account, you can get in a lot of trouble for running automated scripts during peak hours, so we want to run this on a Sunday morning at like the crack of dawn, when it's not going to do a lot of harm to web users. My server is set to run the process at 4:22 AM every Sunday, so we'll use that as an example.
In order to do that, we set the Minute to 22, the Hour to 4, the Day to *, the Month to *, and the Weekday to 0. This means that on the 0th Weekday (Sunday), on the 22nd Minute of the 4th Hour (4:22 AM), run this command. Set the command to /home/ocdt/mysql/mysql.sh, remembering once again to substitute ocdt with the master username for your account, and then press Add New Cron Job.
That's it! Your server now backs itself up! Your MySQL backups can be easily downloaded from the /mysql folder of your server at any time.