[ale] MySQL sync

[cfowler]

Chuck,

Does this make the whole server a slave or just the database?

Chris

On Thu, 2003-02-20 at 08:30, Chuck Huber wrote:
> 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


_______________________________________________
Ale mailing list
Ale at ale.org
http://www.ale.org/mailman/listinfo/ale