Backing up your Database (with GoDaddy and Cron)

In short, this is  a guide to setting up a cron job in GoDaddy so that your databases can be backed up (and later restored). It’s generally a good idea to regularly backup your databases, whether you think you need to or not.

This guide assumes that you are using the Linux hosting of GoDaddy.

First, get familiar with mysqldump. It’s the utility we’re going to use to do the backing up. Do you have access to your sites shell? It’s that thing you get when you log into yourdomain.com through SSH. I’ll assume you’ve got access to the command line to try this yourself and if you don’t then just follow along and trust me.

I’ll paste a facsimile of the mysqldump command that I use in my account, and then explain what it does. Keep in mind, the following command is continuous, all on one line, but I’ve broken it down to fit on this page.

mysqldump
-h DBHOSTADDRESS
-u DBNAME
-pDBPASSWORD
DBNAME |
gzip >
$HOME/html/somefolder/DBNAME_`date '+%m-%d-%Y_%H-%M'`.sql.gz

I’d like to point out the workings of that “date” thing first. Simply put, that gives the backup a name like “MyDB_03-18-2009_02-15.sql.gz” which tells us that the database, MyDB, was backed up March 18, 2009 at 2:15am.

DBNAME is whatever you named the database. As we all know by now, GoDaddy shares the database name and the database username, which is why it shows up so often.

DBPASSWORD is worth noting. There is no space between the -p and the password. That is not a typo. Just keep it as -pNoSpace or it will not work (of course, use the database password in place of “NoSpace”).

DBHOSTADDRESS can be found in the GoDaddy control panel. The control panel is found at

GoDaddy.com >> Hosting >> My Hosting Account >> Manage Account >> Databases >> MySQL

Then click the “Edit/View Details” Action for the database you want. The host address can be found in the Host Name row of the MySQL Database Information.

GZIP is what zips up the backup for us. It keeps the backup small (or at least smaller than the raw backup file).

$HOME is the full path to the backup directory. You do not need to worry about the path, just keep $HOME as it is.

And now for the easy part.

Create a file and write #!/bin/bash for the first line. Then on the second line, paste in the mysqldump command for your database. Remember to change the variables in the command to suit your account and database configuration.

Now change the permission of the script you just created to be executable by you. From the command line the command is

chmod 744 WhateverTheScriptIsCalled

You could probably change the permission from within an FTP program, like FileZilla, by right clicking and changing the permission.

Next, open Cron Manager. It is located in the Control Panel under the Content tab.

Give the cron job a name and set the frequency. Next, in the command box, browse to the location of the script you just created. Select it. Save. Done.

14 Responses to “Backing up your Database (with GoDaddy and Cron)”

  1. […] This is another useful tidbit to use with the article, Backing up your Database (with GoDaddy and Cron). […]

  2. hzzg6y says:

    How do I restore it back in case of any problem.

  3. kdnewton says:

    Restoring is done manually. Log in to your GoDaddy control panel, then select “My Hosting Account” from the “Hosting” tab.

    Next, select “Manage Account” for your hosting account, found halfway down the page.

    This opens a new window/tab. From there, select “MySQL” from the “Databases” tab.

    Select the appropriate database name that you want to restore and click the “Manage via phpMyAdmin” link beside it. This will open up the phpMyAdmin window for that database.

    Log in with the database name and password.

    Once logged in, you’ll find yourself on the “Home” phpMyAdmin page for that database. You’ll want to “Import” the backed up file, but before you do that you may need to wipe the database clean.

    I’d advise that you export whatever data is currently there, in case you want to recover anything from the database at a later date.

    You could probably find some more in-depth instructions for importing databases with phpMyAdmin with a Google search.

    http://www.google.ca/search?hl=en&q=phpMyAdmin+import&btnG=Search&meta=

  4. fleshins says:

    I can’t for the life of me get cron working w/ godaddy. I’m not able to get any emails about cron jobs completing – nothing. Would you mind pasting what your crontab file looks like? Here’s what I’ve got in mine:

    $ cat crontab
    # canary
    MAILTO=”xxxxxxx@gmail.com”
    # 2 test2
    0 * * * * /bin/touch $HOME/cron_test
    # 1 test1
    1 * * * * /web/cgi-bin/php $HOME/html/gdform.php

  5. kdnewton says:

    @fleshins
    # canary
    MAILTO=”xxxxxx@yyyyyy.zzz”
    # 1 Backup n3wt0n_01 MySQL
    15 2 * * 1 “$HOME/PATH/TO/SCRIPT”


    And the script is

    #!/bin/bash

    BACKUPFILE=”$HOME/PATH/TO/NEW/FILENAME.sql.gz”

    mysqldump -h DBHOSTADDRESS -u DBNAME -pDBPASSWORD DBNAME | gzip > $BACKUPFILE

    sh $HOME/PATH/TO/EMAIL/SCRIPT $0


    And the email script is

    #!/bin/bash

    to=”someaddress@wherever.com”
    from=”xxxxxx@yyyyyy.zzz”
    subject=”Receipt of Backup: $1″
    message=”Running backup script $1\n\nScript completed at `date`\n\n-The Cron Job at n3wt0n.com”

    touch OutboundMessage
    echo Subject: $subject > OutboundMessage
    echo -e $message >> OutboundMessage
    /usr/sbin/sendmail.real -f $from $to < OutboundMessage #cat OutboundMessage rm OutboundMessage

  6. fleshins says:

    thanks, it turned out to be a problem w/ the godaddy server I was on – f*cking ridiculous .

    thx for the post 🙂

  7. Rob C says:

    Thanks… this was extremely helpful in navigating through some of the GoDaddy quirkiness. Simple too!

  8. selcuk says:

    Hi
    I from Turkey

    I am using this command and got error email.

    mysqldump –opt -Q -h jadde.db.xxxxxx.hostedresource.com -uUSERNAME -pPASSWORD DNNAME| gzip > /home/content/xx/xxxxxx/daily/jadde/`date +%m`/jadde.`date +%d.%m.%Y.%H:%M`.sql.gz

    This command work on SSH but not working cron job.
    Please help.
    /bin/sh: -c: line 0: unexpected EOF while looking for matching “’
    /bin/sh: -c: line 1: syntax error: unexpected end of file

  9. selcuk says:

    Hello again
    I am fixed my problem.
    using \ before % charackter.

  10. crspencer says:

    I’m using this script:
    #!/bin/bash
    mysqldump -h isq1127601060863.xxxxxxxxxxx -u USERNAME -pPASSWORD isq1127601060863 | gzip > $home/html/dbbackups/isq1127601060863_`date ‘+%m-%d-%Y_%H-%M’`.sql.gz

    and I get the following results:
    ./sqlbackup: line 2: /html/dbbackups/isq1127601060863_10-10-2011_16-06.sql.gz: No such file or directory
    mysqldump: Got errno 32 on write

  11. hallidayny says:

    Hi – thanks so much for the advice. Just wondering, is this correct?

    #!/bin/bash
    mysqldump -h dbname.db.0000000.hostedresource.com -u dbname -pPW dbname | gzip > $HOME/content/00/0000000/html/mnseatank_`date ‘+%m-%d-%Y_%H-%M’`.sql.gz

  12. kyle says:

    Sorry, I don’t use GoDaddy for my host provider any longer.

  13. Hawkey says:

    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
    /var/chroot/home/content/78/11618278/html/_db_backups/backupmydatabases.sh: line 3: -h: command not found
    /var/chroot/home/content/78/11618278/html/_db_backups/backupmydatabases.sh: line 4: -u: command not found
    /var/chroot/home/content/78/11618278/html/_db_backups/backupmydatabases.sh: line 5: -pI7fID6t@N: command not found
    /var/chroot/home/content/78/11618278/html/_db_backups/backupmydatabases.sh: line 7: syntax error near unexpected token `newline’
    /var/chroot/home/content/78/11618278/html/_db_backups/backupmydatabases.sh: line 7: `gzip >’
    Any Help Please?

Leave a Reply

 

Staypressed theme by Themocracy