- 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
- 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: 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.