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:
$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;
