Posts tagged: sql

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

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


I just know I’m going to forget this if I don’t write it down. Within VBScript/ASP and SQL, this is how I insert a row to a table and retrieve the ID (key) of the row just inserted.

dim dbs
dim rst
set dbs = server.createobject("adodb.connection")
set rst = server.createobject("adodb.recordset") your DB connection information

Dim rowKey
		"theTable (" &_
			"ModifiedDate, " &_
			"CreatedDate, " &_
			"otherStuff" &_
		") " &_
	"VALUES (" &_
		"'" & now & "', " &_
		"'" & now & "', " &_
		"'" & SomeData & "'" &_
	");" &_
rst = dbs.execute(strSQL)
rowKey = rst("recID")

The variable, rowKey, now holds the record ID of the inserted row. Hopefully I look here first next time…

Staypressed theme by Themocracy