Tutorial : how to backup your databases

Hi folks,

I've been playing with Cron in Cpanel in order to set up automatic backups of all my databases.

1- Go to Cpanel > Cron Jobs
2 - Select Standard or Advanced, that's up to you
3- In the command field, type this :

4- Now let's see what this all means : the date line formats the date so that we can append it to our backup filename. We then ask mysql to dump all databases into /home/LOGIN/public_html/backups/xbackup_$date.sql (where LOGIN is your Cpanel name) using your MySQL user details : "yourusername" as user (-u) and "yourpassword" as the password (-p). Notice the date variable inserted in the filename. Finally our SQL file is gzipped using the gzip directive.

5- You now have to set up your backup frequency. And relax !


Notes :

• I've created a user "backup" with all privileges in Cpanel > MySQL databases and associated him with all my databases so that I can backup everything in one single file with just one line of cron.
• Protect your backup directory so that people cannot download the whole of you SQL data. You can enable directory protection with Cpanel > Password protect directories > *select your backup directory* > *check the box and enter a username/password* > *validate*. Thank Robert for the suggestion

Hope this helps,

heartbreaker

Ps : this tutorial is available here too for future reference.


.

Heartbreaker,

You should consider changing your forum username to Lifesaver. :bounce:

This is precisely what the forums are for. Cheers for this.

Dougie.

.

Magic. Suggest you make this a sticky/announcement HU?

#Is this the username and password which you need the same as that for cpanel? (assume it is)
#Typically, how big would you say a 500 post 50 member forum would be in filesize once backed up?
#I was thinking of doing it once every 3 days, is that OK do you think or too frequent?

Cheers )

Lol Dougie !

I'm glad I can help out, your reply made my day

Hi Tom,

The username and password are the ones associated with your MySQL user, not Cpanel. They are needed to select the database and dump the records.

If compressed, your forum data would be around 2-3MB I reckon (assuming your members are not posting novels, lol !)

I've set the cron job to backup my DB every day at 5am. It's about 50kb compressed right now so I can afford it

heartbreaker

Excellent tutorial.

The only thing I'd suggest is thinking about protecting that backups directory from the outside world (cPanel's password protection will do). Or use the home directory (the one above public_html).

You don't wan't people stealing your backups by typing http://www.yourdomain.com/backups/

Very true Robert, thanks for pointing it ! I'm going to edit my post.
Thanks

Ah, but I think the user and passes for the DB's are different, will I have to therefore tell it so? If so, how ! Sorry for being a pain

Hmmm... you know you make that sound so easy!

OK so adding a new user for backing up sounds OK, adding it to every DB doesn't ! I feel the need for a further comprehensive tutorial...
...or am I being greedy !!?

That's perfectly clear and comprehensive BurningSnowman
I think the thread covers anything now

wopeeee

Can I just extend this slightly? With a username and password it is still possible for people to get access to this directory via their web browser (if they know/guess/discover the details).

If you are only ever going to ftp to/from here and never need web access to this folder then a better solutions would be a .htaccess file with the following contents:

Code:

order deny,allow
deny from all

Or if you wanted to allow access to just your static IP address (if you have one) you could do:

Code:

order deny,allow
deny from all
allow from xx.xx.xx.xx

Just offering my 2¢/pence/whatever.

>date=`date -I`;

1. Does anyone know why statements like date=`date %Y%m%d`; are mangled by cpanel? (the % get changed to number 3's)? Is there a workaround?

2. Does anyone know how to automatically email the resulting mysql backup? I have tried uuencode | mail but it does not seem to run at all. Also if you specify a subject using quote (") cpanel also chops off anything after the first ".

TIA

It converts them to the number 6 backslashes do not help? You can try by just putting a single % in a Cron - Standard item. Do not know if it still does it on Advanced I guess it does. Very wierd.

I use mysqldump with the --opt option and today tried to import the resulting file into a new database only to get syntax errors during the import.

I resolved the problem by adding --quote-names as an option to mysqldump. It causes all database, table, and column names to be quoted.

Without this option you will run in to trouble if any of your database, table, or column names are the same as SQL commands.

This option is on by default as of MySQL 4.1.1 but HU are only at 4.0.18 (on Hyperion) so give it some thought if you want to avoid the hassle of manually editing your dump file when everything goes pear shaped...

Hello all,

How do i backup just one database?

I noticed this:

date=`date -I`; mysqldump -uyourusername -pyourpassword --all-databases > /home/LOGIN/public_html/backups/xbackup_$date.sql; gzip /home/LOGIN/public_html/backups/xbackup_$date.sql

says "all-databases". I only want to backup my main (forum) database.

Also you mentioned saving the backup to a folder above public_html in order to protect it. Do i need to create that folder first?

Cheers,

Mark

Hello TripleOX,

According to the mysqldump doc , what you want should look like :

date=`date -I`; mysqldump -uyourusername -pyourpassword --databases forum > /home/LOGIN/xbackup_$date.sql; gzip /home/LOGIN/xbackup_$date.sql

I've put the modifications in bold. Explanations :

• --databases forum : it will backup the database called 'forum'
• /home/LOGIN/xbackup_$date.sql : this path will place the file in your Cpanel directory and cannot be accessed from a browser. You'll need to retrieve them via FTP.

Hope that helps