- 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.
[…] Read the next post “How to install AutoMySQLBackup“ […]
thanks for the tutorial but it seems that the DBNAMES=”all” options doen’t work in my environment … I’ve also found a 2007 open bug and a proposed solution but still no luck :-/
any ideas on what else to try / verify ??
instead of all you can write “all your” db names inside DBNAMES and every time you add a db add also the name in the DBNAMES file.
[…] found this site yesterday and it looks like a winner How to install AutoMySQLBackup | Databases | Lirent.net Hacks | News, Reviews, Resouces and Hacks! I used the PSPad they recommended to edit the file before uploading to my server and had no […]
I have been using AutoMySQL backup for sometime. At first everything was well and good but today when checked the error log I found the following in it.
mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘dost”: Cannot load from mysql.proc. The table is probably corrupted (1548)
I use a backup user for it with all previleges. I tried root too but still cant understand the problem. I also tried to import the backup created by it and it was successfully imported, any help will be much appreciated 🙂
Thank you so very much… This did the trick.. 🙂