A couple of our hosting customers recently posted on Twitter that they were looking for a solution to backup their MySQL databases. This is an excellent idea and is highly recommended. With the following script we’ll provide, you will have the capability backup one or more MySQL databases to either your FTP account or an email address.
- You will need to log into your cPanel account and create a new email address with the username of “backups” so that you have an email to send your DB backups to.
- While still in cPanel, go to your MySQL section and create a new database user. Attach that user to the database you want to backup.
- Open up your favorite FTP client and sign into your hosting account.
- While in your root directory (before public_htmll, create a new directory titled “backups” and then navigate to it.
- Download the file db_backup.php, and then open it in a code editor.
- Find line 33-34; This is where you will enter in the details for the database that you want to backup.
- Find line 27 & 45. Where you see “cpuser” enter in your cPanel username.
- If you want the backup sent to your email address, find lines 47-49, and fill out appropriately.
- If you want the backup uploaded via FTP, find lines 57-61, and like the email portion, fill it out appropriately.
- Once you’ve made those edits, save and then upload to the /backups/ directory you created. CHMOD the file to 644.
Hopefully you’re not too tired just yet! So far we’ve created a directory that is outside of your web directory, so that your backup script can sit comfortably. You’ve also added in the details for the database you want to backup, what email you want it sent to, and if you want it backed up to another (or the same) FTP account.
The next thing we’re going to do is set up a cron job. This essentially is going to automate the process of backing up your database to the schedule you set for it. So you’ll be able to let it sit without having to worry about manually backing things up yourself. Find line 27, and copy the snippet that looks like this:
Navigate to the Advanced tab in cPanel, and then click on Cron Jobs. You’ll see two different options to chose from, but you can go ahead and select Standard. Now you’re going to take the line 27 we copied, and paste that into the “Command to run:” input box. Now we have the choice of setting up the schedule for the cron job to run. I recommend leaving the settings at default. As it is right now the cron job will run a backup every month, every day at 3am. Of course you can change any of those options, but I would not backup more than every day. You don’t need backups every minute! Also, at the top it gives you an option to add an email so you can receive an email about the output; Leave that blank. We’re already going to be receiving a daily email with your backup, so this isn’t necessary.
So, go ahead and save your cron job. Now you’ve got a backup system set in place for your database :).
