Multi-Master Replication using Tungsten and Mysql-Proxy For Mysql High Availability

January 11, 2012

Multi-Master Replication using Tungsten and Mysql-Proxy For Mysql High Availability

This tutorial is based on my experience setting up Tungsten Replicator and Mysql-Proxy for a client's production setup. Initially the plan was to have a master and a slave server, but with my research and testing on Tungsten I believe I have a found a solid solution for providing a multi-master mysql 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. Setup Web Server

$ sudo su
$ tasksel

select LAMP server and click OK

3. Configuring Mysql-Proxy 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 resolve 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 setup 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 setup 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
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...

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. On you 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;

Search This Blog

My Commuting and Computing Misadventures

Multi-Master Replication using Tungsten and Mysql-Proxy For Mysql High Availability

1. My Setup

2. Setup Web Server

3. Configuring Mysql-Proxy on the web server

4. Configure Mysql Servers

5. Preparing Mysql Servers for Tungsten Replicator Installation

MYSQL

MYSQL User Permissions

Ruby

JAVA Virtual Machine

How to set your JAVA_HOME

Network

SSH

6. Installing Tungsten Replicator

7. Start Mysql-Proxy

8. Test Replication

Comments

Post a Comment

Popular Posts

AdBlocking with Pi-Hole and Ubuntu 14.04 on VirtualBox

The process has been signaled with signal "6"