Login

Username:

Password:

Remember me



Lost Password?

Register now!

Related Sponsor




Author : John Seymour
Article ID : 101
Audience : Top News
Version 1.01.03
Published Date: 2005/6/3 23:38:29
Reads : 15317

Click to see original Image in a new windowThe 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.

Printer Friendly Page Send this Article to a Friend
The comments are owned by the author. We aren't responsible for their content.
Author Thread
gibaphp
Published: 2005/6/27 3:58  Updated: 2005/6/27 3:58
Just popping in
Joined: 2005/2/8
From:
Comments: 4
 Re: Regular Database Backup with Cron(Updated)
Bello article, very good, congratz
Guest
Published: 2005/7/1 9:20  Updated: 2005/7/1 9:20
 Re: Regular Database Backup with Cron(Updated)
Yes. As many of webmasters, in our case xoops users, forget this point, it's a goog thing to talk about it.

Hosts usually have backups and they use them in case of server crash/hack. We did experiment the use of our self backup one day on our portal. It was a big day with many solutions found on our support forum.
In the middle of the day, hour host server went down (hacked). Less than a couple of hours, our site was online again... but a the last backup state : the day before !
Imagine what to tell to the people who worked arround our forums....
Lucky, we had just found this solution...
We where able to restaure our backup in 5 minutes and we only lost a couple of topics. Now, the scripts are modularised.

Conclusion : Backup your bases yourself and test script with high volumes...

++ R&B from http://www.WDForge.org
Guest
Published: 2006/1/8 17:40  Updated: 2006/1/8 17:40
 Re: Regular Database Backup with Cron(Updated)
However the only problem is if you're host doesn't support cron jobs. In which case use one of the backup modules.
Guest
Published: 2006/1/16 10:25  Updated: 2006/1/16 10:25
 Re: Regular Database Backup with Cron(Updated)
can you get the cron job to send backup to another server?
jseymour
Published: 2006/1/16 12:39  Updated: 2006/1/16 12:39
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
Yes, you may need to edit the SQL file to reflect the new mysql server name. But it can be done with no troubles.
Guest
Published: 2006/3/24 2:18  Updated: 2006/3/24 2:18
 Re: Regular Database Backup with Cron(Updated)
Hi John

Thanks for your article. I am new to this cron stuff and would like to know - once I have set up the script and saved it in a file, how can I test it?

Thanks
jseymour
Published: 2006/3/24 10:40  Updated: 2006/3/24 10:40
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
usually, you would create a test site (either on your server or on your local computer) and the test will duplicate your production site.
JimKarvo
Published: 2006/4/7 21:36  Updated: 2006/4/7 21:36
Just popping in
Joined: 2006/2/13
From:
Comments: 13
 Re: Regular Database Backup with Cron(Updated)
I paste your code!

Where is my file?

I gave my e-mail! It will be sent me by e-mail?
jseymour
Published: 2006/4/7 23:57  Updated: 2006/4/7 23:57
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
It depends on your server setup as to where the file is. Should by default be in the /home folder for the user.
JimKarvo
Published: 2006/4/8 8:33  Updated: 2006/4/8 8:33
Just popping in
Joined: 2006/2/13
From:
Comments: 13
 Re: Regular Database Backup with Cron(Updated)
I get that error:

mysqldump: Got error: 1130: Host '***.***.***.***' is not allowed to connect to this MySQL server when trying to connect

What I can do?
jseymour
Published: 2006/4/8 8:39  Updated: 2006/4/8 8:39
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
Is mysql on a separate server? Or on localhost?

If on localhost (same server as the web server) take out the "host" section of the script, it is localhost by default.
JimKarvo
Published: 2006/4/8 12:43  Updated: 2006/4/8 12:43
Just popping in
Joined: 2006/2/13
From:
Comments: 13
 Re: Regular Database Backup with Cron(Updated)
OK, done!

But I have a question!

Can it did send my sql file by e-mail?
jseymour
Published: 2006/4/8 15:26  Updated: 2006/4/8 15:26
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
No, synchronize to your computer via ftp.
JimKarvo
Published: 2006/4/8 16:02  Updated: 2006/4/8 16:02
Just popping in
Joined: 2006/2/13
From:
Comments: 13
 Re: Regular Database Backup with Cron(Updated)
How?
jseymour
Published: 2006/4/8 16:13  Updated: 2006/4/8 16:13
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
My FTP programs allows me to synchronize a folder on the server to a folder on my computer. Otherwise just download it from the server with an FTP program of your choice.

I use Directory Opus 8 for FTP. But any should work.
JimKarvo
Published: 2006/4/8 16:38  Updated: 2006/4/8 16:38
Just popping in
Joined: 2006/2/13
From:
Comments: 13
 Re: Regular Database Backup with Cron(Updated)
hm.. and if filename already exist in my PC, How I can rename it [I will use yous programm]
jseymour
Published: 2006/4/8 16:43  Updated: 2006/4/8 16:43
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
You can either rename it on the server, or do as I do. I create 7 cron jobs, one runs each day with 7 different file names. That way I keep one weeks backup before overwriting the files again. One week (for me) is enough to be sure I do not miss any errors and gives me a chance to restore.

So my backups are good for 7 days then are overwritten with new the next time around.
JimKarvo
Published: 2006/4/13 15:31  Updated: 2006/4/13 15:31
Just popping in
Joined: 2006/2/13
From:
Comments: 13
 Re: Regular Database Backup with Cron(Updated)
If I want to make a backup all of my databaze, what changes I must do?
jseymour
Published: 2006/4/14 4:02  Updated: 2006/4/14 4:02
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
To backup all databases on your server modify the script in the following structure:

mysqldump [OPTIONS] --all-databases [OPTIONS]

Just where you specify the database, put "--all-databases".
JimKarvo
Published: 2006/4/14 9:06  Updated: 2006/4/14 9:06
Just popping in
Joined: 2006/2/13
From:
Comments: 13
 Re: Regular Database Backup with Cron(Updated)
No,

I want to make a full backup of my database!
jseymour
Published: 2006/4/14 9:25  Updated: 2006/4/14 11:09
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: Regular Database Backup with Cron(Updated)
The original script makes a full backup of a single database. What kind of problems are you having?

Let's continue this discussion in This Forum, just create a new thread. Comments starting to get long here.

Related Sponsor

Bookmark and Share