The FreeBSD Diary

The FreeBSD Diary (TM) Remember
I remember
[ HOME | TOPICS | INDEX | WEB RESOURCES | BOOKS | CONTRIBUTE | SEARCH | FEEDBACK | FAQ | FORUMS ]

 New Topic  |  Go to Top  |  Go to Topic  |  Search  |  Log In   Newer Topic  |  Older Topic 
 rsync'ing mysql
Author: Jeremy Reid 
Date:   10-05-02 02:46

At first I thought it would be a frigtening thing to mirror my mysql databases. Everything I had read involved using mysqldump for each database in mysql .. then transferring all the databases over and loading them into the backup database server.

I wanted the flexibility of being able to add a database at any time without having to modify my backup script.

For my solution I am using a primary server, which is live to the world, and a secondary server, which is tucked away waiting for the primary to fail (also on a seperate net connection just in case).

On the primary, I'm running rsyncd with the following entry for mysql:

[mysql]
path = /var/db/mysql/
comment = mysql databases


On the secondary (backup) server, I made a script that stops mysqld, rsyncs the databases, and brings mysql back up (with extra verbosity for the fun of it):

#!/bin/sh

echo "----------------"
echo "--msqyl-rsync---"
echo "----------------"
date
echo "----------------"


# stop mysql server
echo "stopping mysql server..."
/usr/bin/killall mysqld > /dev/null 2>&1 && echo " stop mysqld"
echo "----------------"

# run rsync
echo "starting rsync..."
rsync -avz --stats --delete 10.0.0.2::databases /var/db/mysql
echo "----------------"

# start mysql up again
echo "starting mysql server..."
if [ -x /usr/local/bin/safe_mysqld ]; then
/usr/local/bin/safe_mysqld --user=mysql > /dev/null & && echo " start mysqld"
fi
echo "----------------"

echo "script end"

This script is called by a cron job that runs nightly (and redirects output to an email to me) but can also, of course, be run manually when necessary.

So far, I haven't had any problems with this method, but if anyone sees any flaw in this, please let me know.

Reply To This Message
 
 Re: rsync'ing mysql
Author: Jeremy Reid 
Date:   10-05-02 02:48

Oops! I made a mistake

The rsync line of the script should read as follows:

rsync -avz --stats --delete 10.0.0.2::mysql /var/db/mysql

Reply To This Message
 Forum List  |  Threaded View   Newer Topic  |  Older Topic 


 Forum List  |  Need a Login? Register Here 
 User Login
 User Name:
 Password:
 Remember my login:
   
 Forgot Your Password?
Enter your email address or user name below and a new password will be sent to the email address associated with your profile.
How to get the most out of the forum

phorum.org