Backup multiple MySQL Database Servers

At my current place we have several internal MySQL Databases used for vtiger CRM and Bugzilla. It is very critical that you must be doing backups for your MySQL Databases. When I started, I wrote a simple and yet pretty much effective bash script to automate and schedule that backing up procedures. It has been serving me quite nicely for now.

Basically, I needed to make a dump of MySQL Database, name it with today’s date, gzip and ftp to a different backup server. Of course I used crontabs to schedule backups.

In my example I will be using this script on one of those MySQL Servers, and get a dump from other two MySQL DB Servers in the network. So which means you must enable remote access to your MySQL.

Here’s my script:

#!/bin/sh
HOST=’backupserver.mydomain.com’
USER=’arstan’
PASSWD=’MyPassword’
db1=db1-backup-`date -I`.sql.gz
db2=db2-backup-`date -I`.sql.gz
db3=db3-`date -I`.sql.gz

# Backup DB1, this is local DB
mysqldump -u root -pMyPassword db1 | gzip > $db1

# Backup DB2, is remote MySQL DB Server
mysqldump -h db2.mydomain.com -u arstan -pMyPassword db2 | gzip > $db2

# Backup DB3, is remote MySQL DB Server
mysqldump -h db3.mydomain.com -u arstan -pMyPassword db3 | gzip > $db3

ftp -n $HOST <<EOF
quote USER $USER
quote PASS $PASSWD

binary
# Point to the backup folder on your backup ftp server
cd backups/office
put $db1
put $db2
put $db3

quit
EOF

# Remove all the local backup files
rm $db1 $db2 $db3

Save this file as backup.sh and put it onto one of the linux boxes you have. You can run this script manually or setup a daily cron. Here’s how you setup cron:

crontab -e

00 1 * * * root sh backup.sh

save and exit. Check with a command:

crontab -l

this will list you all the crontabs for the current user.

I used this to backup internal DB Servers, you can however use this script to do scheduled backup for your blog or website. You will need to have SSH access to your hosting provider though.

Article Sponsor:
Bloggerwave

April 26, 2007 | Filed Under Linux, Programming 

Comments

One Response to “Backup multiple MySQL Database Servers”

  1. synack on April 27th, 2007 3:32 am

    I’d have name production DBs with “prd” prefix, use MySQL command to list all databases, use some grep/awk/sed to take only those with “prd” prefix and do backup. This way, you don’t need to re-edit your backup script every time you add a new DB for production (which means schedule it for backups).

    Example below is based on PostgreSQL, but can customize for MySQL…

    postgres@deimos ~$ psql -l -t | cut -d”|” -f1 | sed -e ’s/ //g’ | grep ^prd

    prd_system1
    prd_system2
    prd_system3

    Can improve this further to have a loop:
    postgres@deimos ~$ for DB in `psql -l -t | cut -d”|” -f1 | sed -e ’s/ //g’ | grep ^out`; do pg_dump $DB`date`_backup.dump; done

Leave a Reply

You must be logged in to post a comment.

Categories


Clicky Web Analytics