The FreeBSD Diary

The FreeBSD Diary (TM)

Providing practical examples since 1998

As an Amazon Associate I earn from qualifying purchases.
 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:

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):


echo "----------------"
echo "--msqyl-rsync---"
echo "----------------"
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 /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"
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 /var/db/mysql

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

 User Login
 User Name:
 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