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.
How do I restore it back in case of any problem.
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=
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
@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
thanks, it turned out to be a problem w/ the godaddy server I was on – f*cking ridiculous .
thx for the post
good stuff here
Thanks… this was extremely helpful in navigating through some of the GoDaddy quirkiness. Simple too!
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
Hello again
I am fixed my problem.
using \ before % charackter.
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