Automatic MySQL backup solution

[pioneerx01]

Hi,

 

I have several tatabases hosted on a server and I would like to be able to back them up atomatically (on schedule) without going to each one independently and exporiting a backup. I did some google searching and I see that there are few solutions out there. I would like hear the oppinions of this community and any good/bad experiences they had in doing something like this.

 

Any recommendatiions of software, prteferably free or unter $100 would be appreciated.

 

Thanks,

Pioneer

[jazzman1]

You don't have to pay extra money to approach that. I would recommend you using mysqldump via ssh tunnel to encrypt the dump data and a cron job to run the dump file on a schedule. Take a look at this my simple script written in BASH: 

#!/bin/bash

DBHOST="dbAddress"
DBUSER="dbUser"
DBPASS="dbPass"
DBNAME="dbName"
NOW=`/bin/date +%Y%m%d`
DIR=$(dirname $0)

if [ ! -d $DIR/$NOW ];then mkdir -p $DIR/$NOW;fi

ssh userName@domainName -p 22 mysqldump --host=$DBHOST --user=$DBUSER --password=$DBPASS --protocol=TCP \
--port=3306 --single-transaction ${DBNAME} > ${DIR}/${NOW}/$NOW.sql

tar zcf ${DIR}/db-$NOW.tgz ${DIR}/${NOW}/$NOW.sql

rm -rf ${DIR}/${NOW}

If you have more then one database hosted on the same DB sever you could use  a --all-databases flag.

You need to set up a trusted ssh connection between the client and server to allow logging into a host(server) without needing a password.

Edited November 6, 2013 by jazzman1