[ale] MySQL sync

[Chuck Huber]

On Wed, Feb 19, 2003 at 04:24:22PM -0500, cfowler wrote:
> I've got 2 MySQL engines on seperate networks.  Is it possible to sync
> certain databases for backup purposes?

Yes it is possible with 3.x and I'm doing it right now.
You need to setup replication as a master slave.  I use stunnel
to create an encrypted link between the master and slave.  The slave
network is protected by a firewall, and I use iptables to forward the
port to the slave host's stunnel port.  MySql.com has some good docs
on this.  But in a nutshell, here's how to do it:

The "master" database is defined to be the source of information.
The "slave" is defined to be the recipient - the one that tries to
keep up with the master.

MASTER SETUP:
For the purposes of this discussion, the master will listen for
connections on TCP port 1234.  In /etc/my.cnf make sure the [mysqld] section
contains the following parameters:

    [mysqld]
    port=1234
    ...
    log-bin
    server-id=1

Add a user of your choice that has read and FILE access to all the databases
you wish to backup.  For this purposes of this discussion, we'll call it
"repluser".

After you set these parameters, you'll need to bounce the mysql server.

We'll need to setup a stunnel to listen.  In /etc/rc.d/init.d/mysql
setup a stunnel listener.  For the purposes of this discussion, we'll use 1235:

    #!/bin/sh
    #
    # Establish a secure tunnel for mysql.  This will listen
    # on port 1235 for SSL connections, then forward them to
    # port 1234.  The firewall has been setup to allow only
    # the slave machine to hit port 1235 and it forwards the
    # connection request to this machine.
    #
    case "$1" in
        'start')
            # Start the SSL tunnel
            /usr/sbin/stunnel -s mysql -d 1235 -r localhost:1234
            ;;
        'stop')
            # Stop the SSL tunnel
            kill $(cat /var/run/stunnel.localhost.1234.pid)
            ;;
    esac

    #
    #  Change to user mysql and execute the startup script
    #
    su - mysql -c "./rc $*"

FIREWALL SETUP
Since the slave will be initiating contact with the master, we'll need
to forward the connection request at the firewall to this master machine.
We'll want to forward the encrypted link since stunnel is already running
on the master.  Substitute in the IP address of the master.

    ${IPTABLES} -t nat -A PREROUTING -p tcp --dport 1235 -j DNAT --to-destination 192.168.?.?:1235

SLAVE SETUP
Setup stunnel.  In /etc/rc.d/init.d/mysql, add:
    ...
    case "$1" in
        'start')
            # -c = SSL Client mode.  Remote end is server
            # -s = suid after connecting
            # -d = daemon listen address/port
            # -r = remote list
            /usr/sbin/stunnel -c \
                -s mysql \
                -d 1235 \
                -r remotehost.domain.com:1235
            ;;
        'stop')
            kill $(cat /var/run/stunnel.remotehost.domain.com.1235.pid)
            ;;
    esac
    su - mysql -c "~mysql/mysql.server $*"

Setup mysql.  In ~mysql/.my.cnf (or /etc/my.cnf), add the following parameters:

    [mysqld]
    port=1234
    ...
    master-host=127.0.0.1
    master-port=1235
    master-user=repluser
    master-password=replpassword
    replicate-do-db=dbtorepl
    service-id=3

Set the master-user and master-password appropriate for your system.  Also set
replicate-do-db to the database you wish to replicate.
NOTE: Don't use "localhost".  That has special meaning to mysqld and will cause
it to use unix sockets to connect to /tmp/mysql (a pipe).  It must be 127.0.0.1.

Make sure ~mysql/data/master.info looks like:
    sched-bin.003
    1
    127.0.0.1
    repluser
    replpassword
    1235
    60

There are mysql commands you can use to adjust these parameters, but this will
give you a good start.

Bounce the slave server and use tcpdump to look for connections going to 
127.0.0.1:1235, and watch the mysql logs for errors.

Enjoy,
    - Chuck

-- 
"The purpose of encryption is to protect good people
from bad people, not to protect bad people from the government."
     Scott McNealy, CEO Sun Microsystems
"The best way for government to control people is to remain in
   a constant threat of war." ---Karl Marx
"They that can give up essential liberty to obtain a little temporary
   safety deserve neither liberty nor safety." -- Benjamin Franklin

 PGP signature