Tutorial : how to backup your databases

Discussion in 'Tutorials / How To?' started by heartbreaker, May 28, 2004.

  1. TripleOx What an ox!!

    Thanks Heartbreaker,

    I'm nearly there...

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

    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.
  3. TripleOx What an ox!!

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

    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
  5. Adam Administrator

    Hi Rob,

    date=`date -I`;

     date=`date -u +%e`;
    This gives the day of the month.

  6. cyberdyne Member

    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:
    /bin/sh: line 1: gzip/home/LOGIN/xbackup_2005-06-08.sql: No such file or directory
    My cron command is:
    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?
  7. heartbreaker Power User

    Hello LondonAllstars :)

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

    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.

    Actualy, theres no need, it wouldnt give you any more info, lol :)
  9. heartbreaker Power User

    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 ?
  10. cyberdyne Member

    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.

    Just did another test resulting in the following email report:

    /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 ?

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

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

    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.
  12. cyberdyne Member

    Lovely jubbely :) HeartBreaker, that worked perfectly.

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

  13. heartbreaker Power User

    You're very welcome LondonAllstars ;) - I was starting to worry ! LOL
  14. cyberdyne Member

    One last thing.

    When i do a full backup using
    date=`date -I`; tar -zcf backup_$date.tgz ./
    I get this message via email.

    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.
  15. heartbreaker Power User

    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 :cool:
  16. cyberdyne Member

    Ok, great, thanks for that :)
  17. lynx New Member

    date=`date -I`;

    What time does it create the backup, or run the cron
  18. heartbreaker Power User

    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.
  19. lynx New Member

    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)
  20. heartbreaker Power User

    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.

Share This Page