Posts tagged ‘database’

Backup Mysql Database on GoDaddy

Assumptions: You are trying to back up MySQL Databases on a GoDaddy Shared Linux package where you have access to SSH and know what your credentials are. If you know either of these, but are unsure of how to get to it, check out this article first: Enabling SSH on your Godaddy Account.

Here are the steps we will use to back up a MySQL database on GoDaddy:

  1. SSH To your GoDaddy Account
  2. Make a directory to store your script
  3. Write the script
  4. Change the script to executable
  5. Execute the script
  6. Verify your backup was created

It’s easy to know that you should run backups on a normal basis, and as my web presence grows and I continue gaining more information (i.e. this example here), then it becomes more important to make sure I don’t lose this information. If you are here, you probably have or know the reasons for completing these steps.

SSH’ing to your GoDaddy Account:
I complete this step with my Unix Mac, but if you are on Windows, you may want to try Putty or some other interface for SSH’ing. For Mac’s, it’s as easy as:

ssh username@mydomain.com

If prompted to accept a certificate, type yes and hit enter, and then put in the password for your GoDaddy Shared Hosting SSH Login.

Make a directory to store your scripts:
This is debatable as to how you want to do this, I created a directory for my scripts and a directory for my backups.

mkdir -p site_backups/scripts
mkdir -p site_backups/databases

Note: Some of this upcoming tasks depend on the location of the database directory, so make sure you remember where that is.

Write the script:
This you can do however you want, I chose to use an ftp editor and save it in my local git repository and then I upload it to the server. Here are the contents of the script that I create. To complete this script, you will want to log into your GoDaddy hosting account and and go to Databases -> MySQL, and click the little pencil button. That should give you everything you need except your password. Here is what you need:

  • Database Name: DBNAME
  • Database Host: DBHOST
  • Database Password: DBPASSWORD

Below is the contents of the bash script. This script generates a database backup in the previously created directory, and down one more directory to the DBNAME, with a time stamp that drills down to the minute. I have somewhat modulated this script because I have several backups. It could even be more modular, and as you mature this script, you should keep that in mind. Additionally, telling me what you did as a comment here may help others, so feel free to coment.

site_backups/scripts/backup_database.sh

#!/bin/bash

function create_directory {
	if [ ! -d "$1" ]; then
	    mkdir -p "$1"
	fi
}

function execute_command {
	$1 | gzip > $2`date '+%m-%d-%Y_%H-%M'`.sql.gz
}

# hurlme_main
DBNAME="demodatabase"
DBHOST="$DBNAME.db.YOUR.hostedresource.com"
DBPASSWORD="your_password"
BACKUPLOCATION="$HOME/site_backup/databases/$DBNAME/"

COMMAND="mysqldump --add-drop-table -h $DBHOST -u $DBNAME -p$DBPASSWORD $DBNAME"

echo "Creating directory if it doesn't exist: $BACKUPLOCATION"
create_directory "$BACKUPLOCATION"
echo "Executing $DBNAME backup..."
execute_command "$COMMAND" "$BACKUPLOCATION"

Make this script above executable:
You can do this via FTP or SSH, pick your flavor and change the script permissions to 700. Via ssh, you can do it:
IMPORTANT: If you put your passwords and locations in this file, be weary to make sure you can’t access it via the public web. It is helpful to make sure this ISN’T in your HTML directory

ssh my_user@myresource
cd site_backup/scripts
chmod 700 backup_database.sh

Execute and verify your script ran properly:
Here, I am just chaining off of the above, so I am ssh’ed into my directory and executing the script directly:

./backup_database.sh
ll ../databases/DBNAME
-->  inetuser  2388 Mar 27 15:01 03-27-2011_15-01.sql.gz

The 2388 symbolizes the size of the database, If this is very small, like 20, then you probably haven’t got a good back up. For sakes sake, I would restore this to a local database or manually view the SQL to make sure it has done exactly what you wanted it to do.

Now, we must look at our next steps. Some people use CRONTAB to complete a schedule of scripts and you can do this easily right in your home directory. I, however, like to manage everything using Jenkins or any web based solution that makes this stuff easier to remember as I am very forgetful. We will look at this in an upcoming article. Until then, feel free to make suggestions and improve my solution and share it with all of us in the comments.

Drizzle Database

If you are interested in open source database options and prefer to stay away from those huge giants, then Drizzle might be the option for you. I moved to postgres for my current apps (from MySQL), but for those of you that may not have that option or really enjoyed the features on mysql, Drizzle might be the right option for you. Drizzle Project Home

I’ll definitely be giving this a shot in the coming months as I go to launch my next project that requires a relational database.

© 2012 - Jeff Ancel
Wordpress Themes
Scroll to Top