How to Have Automatic Backups of Your mySQL Database
The following guide provides a walk-through for setting up automatic backups of your mySQL databases using the AutoMySQLBackup file bash script. After reading the guide, it should be quick and easy to implement. This is an indispensable tool for webmasters. Once the backup system is in place you'll have daily, weekly and monthly backups of your mySQL databases neatly organized in a private folder on your web server. They are automatically rotated so that they don't eat up your storage space. Anytime you download a backup of your home directory all of the database backups will be included. This is much better than creating manual backups and can be a huge time saver if you have multiple databases.
Prologue
If you already know why you should be backing up your databases and want to skip ahead to the actual tutorial, click here.
Websites based on any type of content management system are pretty much guaranteed to be database driven. It is assumed that most people running their websites on virtual or dedicated servers already have a backup system in place and know how to set this up. If you are reading this tutorial, chances are you are using a shared hosting provider with a LAMP configuration (Linux Apache MySQL PHP). It is also likely that you have CPanel for site administration.
Reasons why automatic database backup is a good idea
Getting a backup system in place may require a little extra time and effort in the beginning, but this can prevent a future catastrophe. If you don't want to lose all of the hard work you have put into building your website, you need a backup system. Most of the actual content, i.e articles and posts, comments, member data, statistics, settings etc. are stored in the database. It is also important to backup the files related to your site, but the critical backup is the database because that is where most of the content is being added and updated.
What can go wrong?
- Hardware Failure:Over the past six months I have been through two hardware failures with one of my hosting providers. The first one was due to a hard drive crash. Although my provider has a backup system, after the server was taken back online many of my files were not restored. The only thing that saved the affected websites was having my own local backup. This may have been an unlucky anomaly, but I have first-hand experience why it is important to have regularly updated site backups on my own computer.
- Hacked Website: No need to get into the semantics of whether a site was "hacked" or "cracked". If your website was compromised either by somebody who cracked your site or by somebody who gained access through another cracked site on the shared server, there is not way to know exactly how much damage was done. It is necessary to start over with a fresh backup of your site (and if it was your site that got cracked, determine where the security hole was.) This is the great thing about having daily, weekly and monthly backups. If it is a few days or a few weeks before you find out that your site was hacked, you can always go back to the most recent backup that was not affected. This also ties in to the next situation where things can go wrong.
- The OOPS! Factor: Maybe it is getting late at night, you are working on a website and you accidentally overwrite some important files. Or maybe you decide to install a new module that screws up your database. There are a number of ways to create havoc for yourself. If this occurs and you have been making frequent backups, you can always revert to the last working backup to undo the damage.
The beauty of setting up AutoMySQLBackup is that you can download all of your files and database backups with one click once the setup is complete. Let's get started:
- Get the script: AutoMySQLBackup is a wonderful tool that can be downloaded for free. The first thing you need to do is get yourself a copy. Download AutoMySQLBackup.
- Configure the script: The next step is configuring AutoMySQLBackup before uploading it to your server. You can edit the script using any text editor. Take note - Windows Notepad has terrible formatting for these kinds of files. It is better to use a dedicated text editor. If you don't have a decent text editor, check out the freeware program PSPad, which also has some really cool tools for working with other files such as CSS, XTHML, PHP etc.
Once you have the AutoMySQLBackup script open for editing, you'll need to set the parameters. There are a lot of configuration options, but I am just going to focus on the important settings that will be applicable to most users.
- Enter your database username and password. The place to do this is USERNAME=dbuser and PASSWORD=password, just replace dbuser and password with your actual info. Pretty self-explanatory. If you have multiple databases on your hosting account, go ahead and create a separate mySQL user specifically for doing the backup in the database administration for your website. Then you can update all of the databases at once using that user.
- Next replace DBNAMES="DB1 DB2 DB3" with DBNAMES="all". This tells the script to update all of your databases. (Make sure you have assigned the mySQL backup user to each database.)
- Tell the script where you want the backups stored. This needs to be entered as an absolute path. For my hosting provider it looks like this: BACKUPDIR="/home/myaccount/mysql_backups". You will need to replace myaccount with the account name assigned by your hosting provider (this is usually the same as the username for your primary FTP account, just ask your provided if you need help). All of the backups will be stored in a folder called mysql_backups which will be automatically created in your home directory the first time AutoMySQLBackup runs. It is important to have the files stored in your home directory which is private. If they are stored in a web-accessible folder such as public_html or htdocs, anyone will be able to download the backups, and that is not good :P
- Upload the script: Save all changes and upload the script to the home directory for your website. Once it is uploaded,use your FTP program to change the file permissions / CHMOD for the script to 744. The user permissions need to be read, write and execute, otherwise the script won't run.
- Configure Cron: In a previous article I wrote a tutorial for using Cron to automate tasks. Now we need to setup a Cron job so that the databases are backed up on a regular schedule. Go to the Cron control panel for your website and enter the absolute path to the script. It should look something like this: /home/myaccount/automysqlbackup.sh.2.5. Then decide on a schedule. If you'd like to have the backup run daily at 11:00 pm enter this for the Minute, Hour, Day, Month, Weekday: 00 23 * * *. You may want to set this to run every minute while you verify that it is actually working. To do that enter: */1 * * * *. The next step is optional but necessary for trouble-shooting while you make sure that the script is working. Enter your email address to receive a message from Cron with details each time the backup is run. Now submit the Cron job and get ready for some Cron spam.
- Verify that it is working: After a minute, check your email. There will be a detailed report about the Cron job that was just run. If there are any errors, make the necessary adjustments. If the report is error-free, go back to the Cron setup and change the schedule to run once a day, once a week, or however often you would like to have a backup made. Also, check your home directory to make sure that the backup folder was created. To be on the ultra-safe side: download the backup, unpack the file and make sure that the .sql file can be restored to an empty database.
- Download a full site backup: Now that all of your databases are being backed up, all you need to do is download the site backup. If your hosting provider is using CPanel, there should be a place where you can download "today's home backup". There you have it! A complete backup of your site files and databases in one nicely packed file. If the files for your site rarely change and you just need the database backups, you can use your FTP program to download the mysql_backups folder whenever you like.
I hope this article is useful and informative. Once you get the hang of it, it only takes 5 or 10 minutes to set the whole thing up.