cPanel: Automatic MySQL Backups for the Novice

DMB

vBulletin User
Joined
Jul 5, 2005
Messages
1,698
DMB submitted a new Article:

cPanel: Automatic MySQL Backups for the Novice

cPanel: Automatic MySQL Backups for the Novice

The ease of which users can create a message board or online community is becoming easier every day. With the help of Fantastico, which is provided with most hosts, a user can create a message board in three clicks. The problem with this become apparent when the word "backup" comes into play.

Those who are not tech savvy tend to believe that their hosting company will take care of backing up their information. While this may be true, the hosts explicitly state they can not guarantee the quality, or lack of corruption, of said backups.

For those with cPanel, I have an easy + automatic solution.

Navigate to "Cron Jobs" and choose the "Advanced (Unix Style)" layout.

You will be given the following options:

Minute: 0
Hour: 3 // This represents 3AM, which is usually an inactive time
Day: * // Means the script is run every day of the week
Month: * // Means the script is run every month of the year
Weekday: 0 // Means the script is run every sunday
Command: mysqldump --opt -Q -u dbusername --password=dbpassword dbname > /pathto/backups/sunday.sql

In the command prompt, you will need to replace the red variables that correspond to your information. Remember, if you are on shared hosting, your dbname and dbusername probably have a prefix.

Also note that it is asking for the backup path, not the backup URL.

After doing the first script repeat it six times but change the weekday from 0 to 1 (then 1 to 2, until you reach 6) and the filename from sunday to monday (then monday to tuesday, until you reach saturday).

After you have completed the above step, you are finished!!!

With this method, you are backing up everyday, BUT you have copies from the past seven days instead of rewriting the same copy everyday. This is advantageous because you may want to restore to a certain point or your only backup may have been corrupted.

Enjoy.

Read more about this article here...
 
Last edited by a moderator:

Graham

I Like It Here :)
Joined
May 27, 2005
Messages
1,120
I have always been reluctant to get involved with this. i have made database and site backups to my home computer but to be honest I would not know what to do with them and cpanel says reinstoring them needs an admin ( at the host I assume )

Automating would be good but where does this back it up to ? if it is doing it to the same webspace isn't there a danger of losing the whole lot together ?
 

DMB

vBulletin User
Joined
Jul 5, 2005
Messages
1,698
I back mine up to /home/username/backupss which can't be accessed by the public. Any time I have needed a backup it was because of upgrade problems or something along those lines. Hard drive crashes have never been a problem for me but you should still download copies regularly to your computer.
 

Teknomancer

Tekno Sorcerer
Joined
Dec 24, 2005
Messages
260
This is great. But only one problem. This will simply backup the database on the server itself right? Is there a way to simply EMAIL the backup (database.gz for example) to an email address everyday/week ??

That would be very helpful as I miss the point of creating backups in the server itself (other than restoring on the event of spamming etc.) ....
 

DMB

vBulletin User
Joined
Jul 5, 2005
Messages
1,698

CodeRed

Aspirant
Joined
Jan 5, 2006
Messages
48
interesting...

will this script still allow people to access the DB via the forum posts etc while its backing it up?

just curious
thanks!
 

DMB

vBulletin User
Joined
Jul 5, 2005
Messages
1,698
Yes. It may slow it down a little bit but you get to set when it backs up.
 

Teknomancer

Tekno Sorcerer
Joined
Dec 24, 2005
Messages
260

DMB

vBulletin User
Joined
Jul 5, 2005
Messages
1,698
Teknomancer said:
Excellent I'm going to try it out.

For all of you who want to e-mail their backups from cPanel to any mail address everyday here is the code:
Code:
mysqldump -Q -uuser_name -ppassword db_name | gzip > /path/to/backup/`date +%A`.gz && mutt -s "`date +%A`'s backup" -a /path/to/backup/`date +%A`.gz someone@nowhere.com < /dev/null
You must have mutt installed to use the e-mail command. To check, type in "mutt" in shell and if no errors/etc show up, you do.
 

DMB

vBulletin User
Joined
Jul 5, 2005
Messages
1,698
If your host provides Shell, there will be a button in cPanel that brings up a Java-run Shell.
 

simsim

means seasme
Joined
Nov 3, 2005
Messages
148
Great tutorial... thanks man.
Newmanium said:
Yes. It may slow it down a little bit but you get to set when it backs up.
Well, why it may slow down the forum?
 

Guardsman

Enthusiast
Joined
Apr 10, 2005
Messages
108
I am in the UK but my host is in the US. If I created this cron job for 3am would it run at 3am local time or US time?
 

ShadowLink64

Participant
Joined
Apr 18, 2006
Messages
78
If you want to save space, you could also just gzip decompress your SQL dumps by adding a "| gzip" to the cron command:
Code:
mysqldump --opt -Q -u dbusername --password=dbpassword dbname | gzip > /pathto/backups/sunday.gz

We also just have the cron job execute a PHP script that executes the shell command and makes sure there are at least three backups on file. :p

We also having it running at 3:30 AM EST because that seems to be the time when server load is the lowest. :p
 
Last edited:

Gary Bolton

Aspirant
Joined
Aug 31, 2006
Messages
20
thanks, trying this now. But I have a question. Does this overwrite the same database at the same time everyday.

Works a treat, thanks
 
Last edited:

Gary Bolton

Aspirant
Joined
Aug 31, 2006
Messages
20
Thats funny, I followed the instructions and it works fine except one thing, it created a database to my folder called "friday.sql" no problem, but it's only 1KB large. Anybody got any idea why that is? :bonk:


Do I also put the database name here:

-u dbusername --password=dbpassword dbname
 
Last edited:
Top