How To Set Up Multi-Master Replication Using Tungsten And MySQL-Proxy For MySQL High Availability On Ubuntu 10.04.3 LTS

This tutorial is based on my experience setting up Tungsten Replicator and MySQL-Proxy for a client's production setup.

1. My Setup

For the tutorial I'll be using 3 virtual machines

Web Server 1: web1 IP Address 192.168.56.101
Master 1: master1 IP Address 192.168.56.10
Master 2: master2 IP Address 192.168.56.11

2. Set Up Web Server

$ sudo su
$ tasksel

Select LAMP server and click OK.

3. Configuring MySQLProxy On The Web Server

$sudo apt-get -y install mysql-proxy

$vi /etc/default/mysql-proxy

ENABLED="true"
OPTIONS="--defaults-file=/root/mysql-proxy.cnf"

The defaults-file option should point to where you have saved /root/mysql-proxy.cnf.

[mysql-proxy]
daemon = true
proxy-address = 127.0.0.1:3305
proxy-skip-profiling = true
keepalive = true
event-threads = 50
pid-file = /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log
log-level = debug
proxy-backend-addresses = 192.168.56.10:3306,192.168.56.11:3306
proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/balance.lua

We don't need to start mysql-proxy yet as we still need to configure our backend.

4. Configure MySQL Servers

I actually only configured one and just cloned it after I have setup everything and just changed the /et/hostname, /etc/hosts and /etc/network/interfaces to match the settings for the second server.

sudo su apt-get install -y mysql-server

5. Preparing MySQL Servers For Tungsten Replicator Installation

The host requirements can be found here https://s3.amazonaws.com/releases.continuent.com/doc/replicator-2.0.4/html/Tungsten-Installation-Guide-mysql/content/ch05.html however I've prepared a short list of what I had to configure to get my setup working.

MySQL

$ cat /etc/mysql/my.cnf

[mysqld]
# Master replication settings.
server-id=1
# set increment for up to 4 servers
auto_increment_increment = 4
# increment offset for this server, next server would be 2
auto_increment_offset = 1
log-bin=mysql-bin
# Required InnoDB parameter settings for Tungsten.  Buffer pool size may be 
# larger but should not be smaller for production deployments. 
innodb_buffer_pool_size = 512M
# Recommended InnoDB settings for Tungsten.  
default-table-type=InnoDB
innodb_flush_log_at_trx_commit=2
sync_binlog=0
# Recommended general settings.  max_allowed_packet must be greater than
# the size of the largest transaction. 
max_allowed_packet=48m

MySQL User Permissions

Tungsten uses this account to recreate transactions:

$ mysql -u root -p

mysql> grant all on *.* to tungsten@'%' identified by 'secret' with grant option;

Ruby

$ apt-get install -y ruby libopenssl-ruby

To test:

  $ echo "p 'hello'" | ruby -ropenssl

"hello"

JAVA Virtual Machine

$ apt-get install openjdk-6-jre
$ echo $JAVA_HOME # Should point to Sun JDK install location
$ java -version

How To Set Your JAVA_HOME

Edit /etc/bash.bashrc and append the following at the end of the file.

JAVA_HOME=/usr/lib/jvm/java-6-openjdk
export JAVA_HOME

Reload bash settings:

$source /etc/bash.bashrc

Network

uname -n

should resolve to unique name of host.

  hostname --ip-address

resolves to real IP, private IP accepted.

  cat /etc/hosts

... 
192.168.56.10	master1 
192.168.56.11	master2 
...

SSH

Certificate based ssh login for account used to run tungsten, I used the root account. The machine you're using to set up Tungsten must be able to ssh without a password to the other machine.

$ sudo su
$ ssh-keygen
$ cat .ssh/id_rsa.pub

On the other machine copy the contents of id_rsa.pub to .ssh/authorized_keys.

$ chmod 0600 .ssh/authorized_keys

6. Installing Tungsten Replicator

You only need to install tungsten on one machine and from there you can install additional services to other nodes.

Binary Build Download:

$ wget http://tungsten-replicator.googlecode.com/files/tungsten-replicator-2.0.4.tar.gz
$ tar -zxvf tungsten-replicator-2.0.4.tar.gz
$ cd tungsten-replicator-2.0.4

I used the following script to configure the master servers.

$ cat setup-masters.sh

#! /bin/bash
TUNGSTEN_HOME=/opt/replication
MASTER1=master1
MASTER2=master2
./tools/tungsten-installer \
    --master-slave \
    --master-host=$MASTER1 \
    --datasource-user=tungsten \
    --datasource-password=secret \
    --service-name=zoid \
    --home-directory=$TUNGSTEN_HOME \
    --cluster-hosts=$MASTER1 \
    --start-and-report
  ./tools/tungsten-installer \
    --master-slave \
    --master-host=$MASTER2 \
    --datasource-user=tungsten \
    --datasource-password=secret \
    --service-name=linus \
    --home-directory=$TUNGSTEN_HOME \
    --cluster-hosts=$MASTER2 \
    --start-and-report

After running the above script the tungsten home directory will be populated, this is in /opt/replication/. Inside this folder execute the script to set up the slave services for each master. Our setup is basically like this:

master1
master service - master1
slave service - master2 - copies events from master1 and transfers it to master2 master service

master2
master service - master2
slave service - master1 - copies events from master2 and transfers it to master1 master service

$ cd /opt/replication/tungsten
$ cat setup-slaves.sh

#! /bin/bash
MASTER1=master1
MASTER2=master2
TUNGSTEN_TOOLS=tools
$TUNGSTEN_TOOLS/configure-service \
   --host $MASTER1 \
   -C -q \
   --local-service-name=zoid \
   --role=slave \
   --service-type=remote \
   --datasource=$MASTER1 \
   --master-thl-host=$MASTER2 \
   --svc-start linus
$TUNGSTEN_TOOLS/configure-service \
  --host $MASTER2 \
  -C -q \
  --local-service-name=linus \
  --role=slave \
  --service-type=remote \
  --datasource=$MASTER2 \
  --master-thl-host=$MASTER1 \
  --svc-start zoid

Now let's check if our services are running.

root@master1:/opt/replication/tungsten# tungsten-replicator/bin/trepctl services

Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 2296
appliedLatency  : 2.178
role            : slave
serviceName     : linus
serviceType     : remote
started         : true
state           : ONLINE
NAME              VALUE
----              -----
appliedLastSeqno: 1611
appliedLatency  : 0.953
role            : master
serviceName     : zoid
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

And on master2:

root@master1:/opt/replication/tungsten# tungsten-replicator/bin/trepctl services

7. Start MySQL-Proxy

$ /etc/init.d/mysql-proxy start

Verify that it is running:

$ netstat -tulnap | grep 3305

8. Test Replication

Let's test it using phpmyadmin, download and install phpmyadmin and configure it. In your browser go to http://192.168.56.101/phpmyadmin/ and on the list of servers you should be able to connect to mysql-proxy and the two other nodes. You can now test and see if your replication is working properly, you can shutdown one node by unplugging of its ethernet connection, write data to the other node and then turn the other node back on.

For more information regarding:

Tungsten visit their Wiki.
Mysql Proxy visit their Homepage.

$i++;
/* Authentication type */ 
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3305'; 
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
$i++;
/* Authentication type */ 
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = '192.168.56.10';
$cfg['Servers'][$i]['port'] = '3306'; 
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
$i++;
/* Authentication type */ 
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = '192.168.56.11';
$cfg['Servers'][$i]['port'] = '3306'; 
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;