A review of Tungsten Replicator: Part 1 – Installation

THE WORLD DISCUSSES #PYTHIAN ON TWITTER. HAVE A QUESTION? USE OUR HASHTAG AND ASK AWAY.A review of Tungsten Replicator: Part 1 – InstallationPosted by Ben Mildren on Sep 1, 2011

I’ve been following the development of Tungsten Replicator for quiet some time now, and recently was fortunate enough to find the time to take a look at the product in more detail.

If you haven’t heard of Tungsten Replicator yet, it’s an open source database replication engine that can be used to complement or completely replace native MySQL Replication.  In addition to providing standard replication functionality, Tungsten Replicator introduces exciting new features such as global transaction IDs, heterogeneous replication from MySQL to Oracle and Postgres, parallel replication, and the ability to replicate from multiple masters to a single slave.

Giuseppe Maxia and Robert Hodges have been writing some excellent blog posts on Tungsten, providing great detail on both the architecture and functionality of the product.  One of Giuseppe’s recent posts (here) detailed a new simplified installation procedure so this seemed like a natural place to begin…

Just as an aside, I’m a big fan of VirtualBox when it comes to testing on my laptop.  I’ve created several base images which I select from depending on the task, and simply create a new virtual machine by cloning one of these images.  I used to do this using a combination of VBoxManage clonehd and the VirtualBox gui, but with the release of VirtualBox 4.1 this has been simplified with the introduction of the command clonevm, and it can now be achieved through a simple gui wizard or by executing the following command:

VBoxManage clonevm VirtualMachineName --name NewVMName --register

Additionally VirtualBox provides functionality to take a snapshot of a virtual machine, which makes testing different scenarios and rolling back changes very simple.

Anyway back to installing Tungsten.. ..I looked at Giuseppe’s instructions, and began working through the list of prerequisites.

The first prerequisite was to select a Unix-like operating system, using VirtualBox, I chose a 64bit install of CentOS 5.5 as my base image, and cloned and fired up three vm’s.  The image already had MySQL 5.5 installed, and to simplify testing I set SELinux to permissive and turned off iptables, also for ease, I assigned each vm a hostname (tungsten1, tungsten2, & tungsten3) and added the references to the /etc/hosts file.

The second and third prerequisites specify some required packages, I downloaded and installed the 64bit jre rpm from www.java.com/en/download/, and installed ruby and ruby-libs using the CentOS updates repository.

Next on the list was to create a user account to install and run Tungsten.  The prereq’s specify the user needs sudo access, ssh access to the other hosts involved, and read access to the MySQL binary logs.

For the purposes of the test, as the root user I created an additional user on each machine with access to the required group as follows:

[root@tungsten1 ~]# useradd tungsten -Gmysql
[root@tungsten1 ~]# passwd tungsten

I then edited /etc/sudoers and added:

tungsten ALL=(ALL)    NOPASSWD: ALL

Then logging in as the user “tungsten”, I setup the ssh access:

[tungsten@tungsten1 ~]$ ssh-keygen
[tungsten@tungsten1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub tungsten2
[tungsten@tungsten1 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub tungsten3

Finally I created the mysql user and ensured the binary log was enabled.

mysql> create user tungstenmysql identified by 'tungsten';
mysql> grant all privileges on *.* to tungstenmysql with grant option;
mysql> flush privileges;
mysql> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

With the prerequisites complete, I downloaded and extracted the Tungsten Replicator installation files.

[tungsten@tungsten1 ~]$ wget http://tungsten-replicator.googlecode.com/files/tungsten-replicator-2.0.3.tar.gz
[tungsten@tungsten1 ~]$ tar -xzvf tungsten-replicator-2.0.3.tar.gz

I then tried to complete the installation using the command mentioned in Giuseppe’s post:

[tungsten@tungsten1 ~]$ cd tungsten-replicator-2.0.3
[tungsten@tungsten1 tungsten-replicator-2.0.3]$ ./tools/tungsten-installer \
--master-slave \
--master-host=tungsten1 \
--datasource-user=tungstenmysql \
--datasource-password=tungsten \
--service-name=rep1 \
--home-directory=/opt/continuent/ \
--cluster-hosts=tungsten1,tungsten2,tungsten3 \
--start

Take 1: The moment of truth:

[tungsten@tungsten1 ~]$ cd /opt/continuent/
[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/trepctl services

NAME              VALUE
----              -----
appliedLastSeqno: -1
appliedLatency  : -1.0
role            : master
serviceName     : rep1
serviceType     : unknown
started         : true
state           : OFFLINE:ERROR
Finished services command...

Hmmm time for some research.. ..the Tungsten Replicator project is hosted on Google code here, and documentation can be found here. By taking a look through the documentation and exploring the filesystem, I found there’s a replicator log called trepsvc.log that can be found in the <home-directory>/tungsten/tungsten-replicator/log/ folder. At first glance the log appears a bit noisy, but a simple grep on ERROR and then WARN showed there was an issue reading the binary log files. It seemed to be looking for mysql-bin.index even though I knew from the my.cnf I’d used, the binary logs took the format of mysqld-bin.

Taking a look at the tungsten-installer tool on the project wiki here, I found the extended list of options, and saw I could specify a –datasource-log-pattern option (the default for which was as expected, mysql-bin). Additionally I can see options available to specify the –master-log-file and –master-log-pos, which would obviously be useful if setting this up in an already running production environment.

Take 2: I removed the current installation and reran the install with the additional –datasource-log-pattern option.

[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/replicator stop
[tungsten@tungsten1 continuent]$ rm -Rf /opt/continuent/*

mysql> drop database tungsten_rep1;
mysql> reset master;

[tungsten@tungsten1 continuent]$ ~/tungsten-replicator-2.0.3/tools/tungsten-installer \
--master-slave \
--master-host=tungsten1 \
--datasource-user=tungstenmysql \
--datasource-password=tungsten \
--datasource-log-pattern=mysqld-bin \
--service-name=rep1 \
--home-directory=/opt/continuent/ \
--cluster-hosts=tungsten1,tungsten2,tungsten3 \
--start

Checking the master:

[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/trepctl services

NAME VALUE
---- -----
appliedLastSeqno: 1
appliedLatency : 0.901
role : master
serviceName : rep1
serviceType : local
started : true
state : ONLINE
Finished services command...

Success!

Checking the slaves:

[tungsten@tungsten2 continuent]$ ./tungsten/tungsten-replicator/bin/trepctl services

NAME VALUE
---- -----
appliedLastSeqno: -1
appliedLatency : -1.0
role : slave
serviceName : rep1
serviceType : unknown
started : true
state : OFFLINE:ERROR
Finished services command...

Hmmm time for some more research.. ..checking the log I could see there was an issue with setting the NO_ENGINE_SUBSTITUTION sql_mode, it looked like it was actually trying to set it to MODE_NO_ENGINE_SUBSTITUTION, this sounded a little buggy. I decided to search all issues listed on the Google code project site, and sure enough I found issue number 112 which described the behavior exactly – it actually looks like there’s a fix been submitted already. For now, just so I could proceed with the installation, I decided to remove the NO_ENGINE_SUBSTITION sql-mode option from the my.cnf.

Take 3: I removed the current installation and reran the install with the additional –datasource-log-pattern option and without NO_ENGINE_SUBSTITUTION.

[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/replicator stop
[tungsten@tungsten1 continuent]$ rm -Rf /opt/continuent/*

mysql> drop database tungsten_rep1;
mysql> reset master;

[tungsten@tungsten1 continuent]$ ~/tungsten-replicator-2.0.3/tools/tungsten-installer \
--master-slave \
--master-host=tungsten1 \
--datasource-user=tungstenmysql \
--datasource-password=tungsten \
--datasource-log-pattern=mysqld-bin \
--service-name=rep1 \
--home-directory=/opt/continuent/ \
--cluster-hosts=tungsten1,tungsten2,tungsten3 \
--start

Checking the master:

[tungsten@tungsten1 continuent]$ ./tungsten/tungsten-replicator/bin/trepctl services

NAME VALUE
---- -----
appliedLastSeqno: 1
appliedLatency : 0.602
role : master
serviceName : rep1
serviceType : local
started : true
state : ONLINE
Finished services command...

Success!

Checking the slaves:

NAME VALUE
---- -----
appliedLastSeqno: 1
appliedLatency : 0.0
role : slave
serviceName : rep1
serviceType : local
started : true
state : ONLINE
Finished services command...

Success!

Whilst it wasn’t totally seamless, the installation and troubleshooting were both pretty straightforward, especially considering this was literally the first time I’ve looked at Tungsten in any detail. I’m now able to create tables, and insert test data on the master, and see it replicate to the slaves. Watching replication in action and seeing data actually move from a to b, is truly one of those things that’s a joy to see!

Having explored the basics of setting up Tungsten Replicator, in the next post, I’ll look to explore some of the commands are available to administer the engine.

Category: Group Blog Posts, MySQL, Technical Blog
Tags: MySQL, Replication, tungsten


5 Responses
Robert Hodges says:
September 1, 2011 at 4:43 pm
Welcome to Tungsten! I use VirtualBox to test Tungsten–especially multi-master where you need lots of hosts to do anything. Your guidelines for set-up are a lot nicer than my gnarly dev procedures. Thanks for a handy post. Cheers, Robert Hodges

Reply
George says:
September 1, 2011 at 9:07 pm
Thanks for the guide very helpful especially for a fellow virtualbox user like myself

Reply
Ben Mildren says:
September 5, 2011 at 1:32 pm
Thanks guys! Viva la VirtualBox :-)

Reply
Henrik Ingo says:
September 2, 2011 at 3:36 pm
Hi Ben

Nice to finally see you in action. I’ve been waiting for the real Ben Mildren to finally post something MySQL related on Planet MySQL :-)

So this is handy. I check out Galera, you check out Tungsten, then we can compare notes. Seamless collaboration continues…

Reply
Ben Mildren says:
September 5, 2011 at 1:34 pm
Yeah me too! :-)

That would be cool, Ive been reading your Galera posts with interest.. ..maybe we can catch up at Percona Live in London.

Reply