WarPigW2

The most important item to backup with any CMS with dynamic content is the database. Almost all data for these sites are stored in the tables within the database. Because of this it is good practice to backup the database reliably and often.

I have recently chosen to do a complete database backup everyday. Depending on the activities of your web site this may be too often or not enough. Some very heavily used sites, with lots of purchasing activities may do a full backup once a day, and incremental backups every 30 minutes or so. In this model the most that could be lost due to a catastrophic failure is 30 minutes worth of information. For me, 24 hours is enough to recover my site almost fully.



My favorite type of hosting package offers cPanel for the control panel. After using this for a while I will never go back to Plesk hosting or others. They do not give me the control I have with cPanel.

In cPanel there is a section for creating Cron jobs. Cron runs a command at your defined intervals. So to create a Cron Job for a MySQL database backup, we must first create a command to be run. I chose the mysqldump command because it is a native MySQL command and runs fast and clean with the database. Plus I can add options to make for an easy full restore. This is also helpful with large databases as it is run from the command line instead of from phpmyadmin and limited with a PHP 300 second time-out.

The structure of the script looks like this:

mysqldump --host=IP of Database --user=username --password=Password --database database_name --opt --add-drop-table -Q >filename.sql

Lets go through each section of this command.

1. mysqldump - This is the application that this script runs.

2. --host - Here you enter the IP of your MySQL database server. If the server is on localhost then you can leave this part out, default is localhost.

3. --user - Enter the username of the database user with full privileges here.

4. --password - Enter the password for the above user here.

5. --database - Enter the full database name here.

6. --opt - This command is for adding options to the command line script.

7. --add-drop-table - This is an option that I added. If the table already exist, it will drop it from the database before re creating the table and inserting data. As I stated earlier this script is for a full database restore.

8. -Q - I have found through trial that this option should be used. The -Q option places single quotes around all table and column names.

9. >filename.sql - this part of the command directs the script to send the output to a filename you specify. The *.sql extension is a standard, uncompressed database file. Easy and fast for MySQL to handle for a restore.

Just fill in your information in the script, create a filename that you want to save it to. Copy the script and past it into the command area of the Cron form. Set the intervals for the script to run, then it will run automatically from then on. The file will be located in the directory above the root site directory, away from non logged in user access.

As with any backup procedure, make sure you test the restore functionality on a test site/database, before you actually need to use it (for a repair) on your production site.

If you have any comments, or questions please post in the forum. I will be happy to help in any way I can.