Graphika Studioz – Web Design, Development, Hosting and SEO Services – Parksville, Qualicum Beach

Here’s a way for you to backup your mysql databases automatically. You can configure it to do it when ever you like. Code was found on dreamhosts wiki written by Matttail

First log on to your home directory with an ftp client or ssh. Create a backups dir and a backups/archives dir.

ssh yourdomain.com

If you have a problem logging in it’s probably because you don’t have shell access, which you should be able to set up with your host from their panel)

Run these commands from the command line in your home dir:

mkdir backups
mkdir backups/archives

Then open your favorite text editor and create a file with this code. Replace the User, pass mysqlA.domain.com db_nameA with your info. If you want more then just copy paste the mysqldump lines and fill them in accordingly. Save this file as mysql.sh and then upload it to the backups dir.

#!/bin/bash
cd /home/username/backups/
mkdir mysql
suffix=$(date +%y%m%d)
mysqldump --opt -uUser -ppass -h mysqlA.domain.com db_nameA > mysql/db_nameA.$suffix.sql
mysqldump --opt -uUser -ppass -h mysqlB.domain.com db_nameB > mysql/db_nameB.$suffix.sql
tar -cf archives/mysql_backup.$suffix.tar mysql/*
rm -r mysql/

Then you will need to set up a cronjob so from the command line:

crontab -e

Place the following in this file:

MAILTO="you@yourdomain.com"
27 23 * * 3 /home/username/backups/mysql.sh

You can takeout the MAILTO if you don’t want it to email you errors if there are any when the script is ran. The 27 23 * * 3 is the date the script is ran, which is 11:27pm on the 3 day of the week. Edit these if you like. Mine is set to 0 2 * * 1, which is 2:00am Monday. Type CTRL O to save the file and CTRL X to exit the crontab.

That’s it! Your done! If you want to test the script run this command under the backups dir:

./mysql.sh

Mike Nixon sent me this link that does a pretty sweat job of describing how the different types of joins work when playing with a database.