Tutorial : how to backup your databases

Thanks Heartbreaker,

I'm nearly there...

Why are there two locations mentioned. Is it going to back up the database twice?

Nope, the first time it creates the sql dump, the second routine gzip it so that it's all compressed. Useful when you're dealing with big DB

You end up with a single file : the gzipped one with the sql dump.

I've finally set up my cron job! Now i just gotta cross my fingers.

thanks to everyone thats posted...loads of useful stuff. However, as always, theres always anothing request.....

I would like to create daily backups, but in a monthly cycle. So instead of having the full date in the file name, i'd just like the "date part" (i.e. 1-31). "backup_1.sql.gz" , "backup_2.sql.gz"..... "backup_31.sql.gz"
So this way, the backup files will just overwrite again after a month.

Secondly (and this is following Kevins post from June Last year)...Kevin says he has posted a script to email the backup, but unforyunatly I cant trace the post. Any ideas anyone? Or perhaps an alternative to automatically transfer the file to somewhere else. (via ftp for example)

Many thanks
Rob

Hi all,
I have set this up and it seems to be working well. All db's are in one .sql file. I presume this is correct?
Also, i received an error in the email notification:

Code:

 
/bin/sh: line 1: gzip/home/LOGIN/xbackup_2005-06-08.sql: No such file or directory

My cron command is:

Code:

date=`date -I`; mysqldump -uMYDBUSERNAME -pMYDBPASSWORD --all-databases >/home/LOGIN/xbackup_$date.sql; gzip/home/LOGIN/xbackup_$date.sql 

Any ideas why I get the errors since im not actually asking it to write to a folder please, but have used Kevins suggestion of writing to the root, above the public_html?
Thankyou

Hello LondonAllstars

"LOGIN" refers to you Cpanel name : you have to change it to reflect your account setting.

Sorry HB, maybe I should have said - I have changed the values 'LOGIN' / 'MYDBUSERNAME' + 'MYDBPASSWORD' just so that theyre not on public view. They are indeed correct in my actual cron command

I'll PM it to you.

EDIT::
====
Actualy, theres no need, it wouldnt give you any more info, lol

lol - you're right, I don't need them

Considering the error message, it looks like the dump file is not created in the first place. Did you create a MySQL user and assign him to all your databases ?

Yes. Created a new user, added them to all my db's (except one which I do not need a backup of) with full permissions.
The .sql file is saving correctly in my root folder and upon checking it, it looks correct, but its just not gzip-ing.
Thank you.

EDIT::
====
Just did another test resulting in the following email report:

Code:

 
/bin/sh: line 1: gzip/home/MY-CPANEL-LOGIN/xbackup_2005-06-08.sql: No such file or directory 

Would trying the latter part of this command make any difference ?

Code:

 date=`date -I`;[b] [color=red]tar -zcf backup_$date.tgz[/color] [/b]./

Okay, this may work better : I've changed the function some time ago adding a pipe ("|"). Here's what I use on my domain :

Code:

date=`date -I`; mysqldump -uDBUSER -pDBPASS --all-databases | gzip > /home/CPANEL/hb_xbackup_$date.sql.gz

The gzip is created on the fly with this cron line.

Lovely jubbely HeartBreaker, that worked perfectly.

Thanks very much for all your help and indeed for the script in the first place.

:hail:

You're very welcome LondonAllstars - I was starting to worry ! LOL

One last thing.

When i do a full backup using

Code:

date=`date -I`; tar -zcf backup_$date.tgz ./

I get this message via email.

Code:

 
tar: .[i]/public_ftp/[/i].ftpquota: Cannot open: Permission denied
tar: ./backup_2005-06-09.tgz: file changed as we read it
tar: Error exit delayed from previous errors 

Will this make any difference to my backup copy upon upload should i ever need to use it do you think?

Finally... (ok, thats 2 things, lol).... should i receive a cron email if there were no errors with the cron job? Or are the emails just specifiacally to report errors?
Thank you again.

I haven't played with full backups yet so I can't really tell... will make some tests later tonight
As for emails, I only get them when there are errors. Otherwise, no emails : no news is good news

Ok, great, thanks for that

date=`date -I`;

What time does it create the backup, or run the cron

Hello lynx,

The date variable is only used for the filename creation : a typical filename would be "backup_2005-06-10.sql.gz" for instance. You set the time below the Command field in Cpanel > Cron Jobs. The choice is yours, I have mine set at night.

Sorry I forgot all about that, anyway with sql databases will it 'add drop tables' and add 'allow keywords' during the dump

Also It appeared to not backup for me using your method above, not the one on page one. I have a file like this in my home directory (hb_xbackup_2005-06-12.sql.gz) and all it contains is a sql file with:

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help


Also while I'm here I'm looking for a cron command to backup your whole account everything under your username, does anyone know what I would use. (the same short of backup as when you 'Backup' your account)

Hello folks,

I've updated the tutorial here : Backup files and databases easily with cron. This should address the latest gzip issues.

Lynx : it does not add the drop table statements so you'll have to drop your tables first and then populate the tables with your backup data. Also there are "CREATE DATABASE IF NOT EXISTS" statements.