Tutorial : how to backup your databases

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

  1. heartbreaker Power User

    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,


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

  2. Dougie :


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

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


  3. tben2505 Tom

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

    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 :)

  5. Robert Moderator

    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/ :eek:
  6. heartbreaker Power User

    Very true Robert, thanks for pointing it ! I'm going to edit my post.
    Thanks :)
  7. tben2505 Tom

    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 :(
  8. BurningSnowman Moderator

    If you go to cPanel > MySQL Databases, you can enter the details towards the bottom to create a new MySQL user. If you create one exclusively for the purpose of backing up, then add it to every DB with full priveledges, you can back up using a cron job as described in heartbreaker's post with no problem.

    Great post indeed, by the way. Should come in handy. :)
  9. tben2505 Tom

    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 !!?
  10. BurningSnowman Moderator

    I doubt this will quite be up to the standards of the start of this thread, but it should be pretty simple to do anyway. *clears throat*

    Making a DB user:
    • Go to cPanel > MySQL Databases.
    • Scroll down to the section near the bottom with the heading Users:. You'll see a list of all the users you have, and below them a box for a UserName and Password.
    • Fill them in with appropriate values and hit Add User. There's your new user.

    Adding it to a DB (to be tediously repeated for each DB, in this case):
    • Find the bits of form just above the Users: section, immediately after the details of the last DB you have listed.
    • In the first drop down list, marked User, find the user you have created. Remember (particularly if you later set up a cron job) that the actual name of the user you've created will be preceded by your cPanel user name and an underscore.
    • Select the appropriate DB (these will also start with the user name and underscore) in the second drop down list, marked Db.
    • Check that priveledges are set to ALL, unless you have a good reason not to.
    • Hit Add User to Db to, um, add user to DB.

    Was that comprehensive (and probably patronizing) enough?
  11. heartbreaker Power User

    That's perfectly clear and comprehensive BurningSnowman ;)
    I think the thread covers anything now :)
  12. tben2505 Tom

  13. Saeka New Member

    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:

    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:

    order deny,allow
    deny from all
    allow from xx.xx.xx.xx
    Just offering my 2ยข/pence/whatever. :)
  14. pmk New Member

    >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 ".

  15. Adam Administrator

    Excellent script heartbreaker :)

    pmk - I've just posted a small script that e-mails the compressed database to you in the other thread.

  16. BurningSnowman Moderator

    pmk: It seems likely that a script in cPanel is escaping them and replacing them with the decimal value for the % sign. Did 37 feature prominently in the resulting commands? If that's what's happening, you could try escaping the percent signs by preceding them with a backslash.
  17. pmk New Member

    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.
  18. Mike New Member

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

    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?


  20. heartbreaker Power User

    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 ;)

Share This Page