Tungsten-Replicator 2.1.1 with better installation and built-in security

UPDATE 2013-08-30: Tungsten 2.1.2 was released.

UPDATE 2013-08-23: We have found a few problems that happen when replicating with RBR and temporal columns. We will have to publish an updated bugfix release quite soon.

Tungsten Replicator 2.1.1 is out. Key features in this release are:

• A better installer, of which we have already given a preview in tpm, the multi-master composer. The new installer allows faster and more powerful deployments of both single and multiple masters topologies. And it also allows the next feature:
• Secured communication layer. Now the replicator data and administrative messages can be encrypted with SSL across nodes. The security layer, once installed, is transparent. All replication features will keep working as before, and the encryption is independent from the database. In fact, heterogeneous replication (e.g. MySQL to MongoDB, Oracle to MySQL, etc) can use it just as easily as MySQL to MySQL replication.
• Full support for MySQL 5.6 binary log improvements. Now you can have the best of two worlds, running MySQL 5.6 enhanced performance, and Tungsten advanced replication features, without compromises. Due to this improvement, we also have the first change in our transport layer (the Transaction History Logs, or THL) since we released parallel replication. This means that a full cluster upgrade is needed (first slaves, and then masters) if you want to use the new release.

For more information on Tungsten Replicator 2.1.1, see the Release notes.

What does this mean for the common user? Let’s see what you can experience, when installing Tungsten Replicator 2.1.1

$ tar -xzf tungsten-replicator-2.1.1-230.tar.gz
$ cd tungsten-replicator-2.1.1-230
$ export VERBOSE=1
$ ./cookbook/install_master_slave
## -------------------------------------------------------------------------------------
## Installation with deprecated method will resume in 30 seconds - Hit CTRL+C now to abort
## -------------------------------------------------------------------------------------
## WARNING: INSTALLATION WITH tungsten-installer and configure-service IS DEPRECATED
## Future versions of Tungsten Cookbook will only support tpm-based installations
## To install with tpm, please set the variable 'USE_TPM' and start again
## -------------------------------------------------------------------------------------
....5....^C

Installation with tungsten-installer, which has been used until now, is still available, but it is deprecated. We want to encourage everyone to use tpm, as we will stop supporting tungsten-installer from the next release (2.1.2).

The main reason for using tpm instead of tungsten-installer, is that you can now install with security. The Tungsten manual has an extensive section on how to create security certificates. If you are not used to this kind of tasks, you may get discouraged from the very beginning, as you will need to create two key stores, one encrypted password store, and one file with JMX access rules. Tungsten Cookbook to the rescue! It will be enough to state our intention to install using tpm, with security enabled, and the cookbook script will generate the needed files for you.

$ export USE_TPM=1
$ export WITH_SECURITY=1
$ ./cookbook/install_master_slave
Certificate stored in file </home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/client.cer>
Certificate was added to keystore
[Storing /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/truststore.ts]
Using parameters:
-----------------
password_file.location   = /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/passwords.store
encrypted.password   = true
truststore.location      = /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/truststore.ts
truststore.password      = cookbookpass
-----------------
Creating non existing file: /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/passwords.store
User created successfuly: cookbook
Using parameters:
-----------------
password_file.location   = /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/passwords.store
encrypted.password   = true
truststore.location      = /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/truststore.ts
truststore.password      = cookbookpass
-----------------
User created successfuly: cookbook
# ---------------------------------------------------------------------
# Options for tpm
\
--thl-ssl=true \
--rmi-ssl=true \
--rmi-authentication=true \
--rmi-user=cookbook \
--java-keystore-password=cookbookpass \
--java-truststore-password=cookbookpass \
--java-truststore-path=/home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/truststore.ts \
--java-keystore-path=/home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/keystore.jks \
--java-jmxremote-access-path=/home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/jmxremote.access \
--java-passwordstore-path=/home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/passwords.store
# ---------------------------------------------------------------------

Next, you will see the complete installation command using tpm, and the cluster will be built as smoothly as it would be without the security additions.

Notice that the paths that you see on the screen are created dynamically. Once installed, the security files will be deployed in a standard location, which will be easily picked up when you need to upgrade.

The difference that you will notice about the secure deployment is only in a few small differences. When using the cookbook tools, you will see a ssl label next to each secured node:

$ ./cookbook/show_cluster
--------------------------------------------------------------------------------------
Topology: 'MASTER_SLAVE'
--------------------------------------------------------------------------------------
# node host1 (ssl)
cookbook  [master]  seqno:          0  - latency:   0.681 - ONLINE
# node host2 (ssl)
cookbook  [slave]   seqno:          0  - latency:   1.397 - ONLINE
# node host3 (ssl)
cookbook  [slave]   seqno:          0  - latency:   1.683 - ONLINE
# node host4 (ssl)
cookbook  [slave]   seqno:          0  - latency:   1.684 - ONLINE

When using the traditional tools, you will notice one tiny difference in the master URI:

Processing status command...
NAME                     VALUE
----                     -----
appliedLastEventId     : mysql-bin.000008:0000000000000427;0
appliedLastSeqno       : 0
appliedLatency         : 0.681
channels               : 1
clusterName            : cookbook
currentEventId         : mysql-bin.000008:0000000000000427
currentTimeMillis      : 1377091602039
dataServerHost         : host1
extensions             :
latestEpochNumber      : 0
masterConnectUri       : thls://localhost:/    
masterListenUri        : thls://host1:2112/    
maximumStoredSeqNo     : 0
minimumStoredSeqNo     : 0
offlineRequests        : NONE
pendingError           : NONE
pendingErrorCode       : NONE
pendingErrorEventId    : NONE
pendingErrorSeqno      : -1
pendingExceptionMessage: NONE
pipelineSource         : /var/lib/mysql
relativeLatency        : 656.039
resourcePrecedence     : 99
rmiPort                : 10000
role                   : master
seqnoType              : java.lang.Long
serviceName            : cookbook
serviceType            : local
simpleServiceName      : cookbook
siteName               : default
sourceId               : host1
state                  : ONLINE
timeInStateSeconds     : 655.552
transitioningTo        :
uptimeSeconds          : 656.431
version                : Tungsten Replicator 2.1.1 build 230
Finished status command...

Instead of thl:// you see thls://. That’s the indication that the replicators are communicating using a SSL channel.

The same procedure works for multi-master and heterogeneous topologies. In fact, the very same mechanism is used in our commercial product, Continuent Tungsten, where it is installed using the same tools and the same tpm options.

For existing deployments we have a manual page dedicated to Upgrading from tungsten-installer to tpm-based installation. If you are a cookbook user, try

./cookbook/upgrade

There is a live webinar covering many Tungsten-Replicator 2.1.1 features. It is free, on Thursday, August 22nd, at 10am PT.

.

Getting started with replication from MySQL to MongoDB

As you probably know, Tungsten Replicator can replicate data from MySQL to MongoDB. The installation is relatively simple and, once done, replication works very well. There was a bug in the installation procedure recently, and as I was testing that the breakage has been fixed, I wanted to share the experience of getting started with this replication.

Step 1: install a MySQL server

For this exercise, we will use a MySQL sandbox running MySQL 5.5.31.

We download the binaries from dev.mysql.com and install a sandbox, making sure that it is configured as master, and that it is used row-based-replication.

$ mkdir -p $HOME/opt/mysql
$ cd ~/downloads
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.31-linux2.6-x86_64.tar.gz/from/http://cdn.mysql.com/
$ make_sandbox --export_binaries ~/downloads/mysql-5.5.31-linux2.6-x86_64.tar.gz -- --master
$ echo 'binlog-format=row' >> ~/sandboxes/msb_5_5_31/my.sandbox.cnf

$ ~/sandboxes/msb_5_5_31/use -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {root} ((none)) > grant all on *.* to tungsten identified by 'secret' with grant option;
Query OK, 0 rows affected (0.00 sec)


mysql [localhost] {root} ((none)) > set global binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

The above command will install an instance of MySQL 5.5.31 in the directory $HOME/sandboxes/msb_5_5_31. You can use any other MySQL version. In that case, you should change the DEPLOY.sh file below with the coordinates of your server.

Step2: install a MongoDB server

Get the binaries from MongoDB downloads, and unpack them.

$ cd ~/downloads
$ wget http://fastdl.mongodb.org/linux/mongodb-linux-x86_64-2.4.3.tgz
$ cd
$ tar -xzf ~/downloads/mongodb-linux-x86_64-2.4.3.tgz
$ mv mongodb-linux-x86_64-2.4.3 mongodb

Once you have unpacked the binaries, you can run the server, with a command such as this:

#!/bin/bash
MONGODB_HOME=$HOME/mongodb

if [ ! -d $MONGODB_HOME ]
then
    echo "$MONGODB_HOME not found"
    exit 1
fi

cd $MONGODB_HOME
if [ ! -d $MONGODB_HOME/data ]
then
    mkdir $MONGODB_HOME/data
fi

./bin/mongod \
   --logpath=$MONGODB_HOME/mongodb.log \
   --dbpath=$MONGODB_HOME/data \
   --fork \
   --rest

Now MongoDB should be ready to receive commands. If needed, you can see a tutorial on basic MongoDB operations. For now, it's enough to check that your system is running:

$ cd ~/mongodb
$ ./bin/mongo
MongoDB shell version: 2.4.3
connecting to: test
> show dbs
local 0.078125GB
>

Step 3: install a master replicator using MySQL

As I mentioned above, there was a bug in the installation. The bug was fixed yesterday, and thus you should use a recent build from http://bit.ly/tr20_builds. For this exercise, I am using tungsten-replicator-2.1.0-269.tar.gz.

Of course, you should also configure your host according to the system requirements for Tungsten.

We start by creating a few directories that we need for our deployment.

$ cd 
$ mkdir deploy
$ cd deploy
$ mkdir mysql     # here we install the master replicator
$ mkdir mongodb   # here we install the slave replicator
$ wget https://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.0-snapshots/tungsten-replicator-2.1.0-269.tar.gz
$ tar -xzf tungsten-replicator-2.1.0-269.tar.gz

We now create a defaults script that will be used by both master and slave installer.

$ cat DEPLOY.sh
MYSQL_PORT=5531
MYSQL_SANDBOX_BASE=$HOME/sandboxes/msb_5_5_31
MYSQL_BASEDIR=$HOME/opt/mysql/5.5.31
MYSQL_CONF=$MYSQL_SANDBOX_BASE/my.sandbox.cnf
MYSQL_BINLOG_DIRECTORY=$MYSQL_SANDBOX_BASE/DATA/
DEPLOY_HOME=$HOME/deploy
MYSQL_DEPLOY=$DEPLOY_HOME/mysql
MONGODB_DEPLOY=$DEPLOY_HOME/mongodb
MONGODB_PORT=27017
TUNGSTEN_BINARIES=$DEPLOY_HOME/tungsten-replicator-2.1.0-269
MASTER_THL_PORT=12500
SLAVE_THL_PORT=12600
MASTER_RMI_PORT=11500
SLAVE_RMI_PORT=11600

And finally the master installation script:

$ cat install_master.sh
. ./DEPLOY.sh

cd $TUNGSTEN_BINARIES

export PATH=$MYSQL_BASEDIR/bin:$PATH

./tools/tungsten-installer --master-slave -a \
  --datasource-type=mysql \
  --master-host=127.0.0.1  \
  --datasource-user=tungsten  \
  --datasource-password=secret  \
  --datasource-mysql-conf=$MYSQL_CONF \
  --datasource-log-directory=$MYSQL_BINLOG_DIRECTORY \
  --datasource-port=$MYSQL_PORT \
  --service-name=mongodb \
  --home-directory=$MYSQL_DEPLOY \
  --cluster-hosts=127.0.0.1 \
  --thl-port=$MASTER_THL_PORT \
  --rmi-port=$MASTER_RMI_PORT \
  --java-file-encoding=UTF8 \
  --mysql-use-bytes-for-string=false \
  --mysql-enable-enumtostring=true \
  --mysql-enable-settostring=true \
  --svc-extractor-filters=colnames,pkey \
  --svc-parallelization-type=none --start-and-report

We can run the installation command for the master.

$ ./install_master.sh
INFO  >> 127_0_0_1 >> Getting services list
INFO  >> 127_0_0_1 >> ..
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 1.218
role            : master
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

Step 4: install a slave replicator using MongoDB

Now we have a master database server with a replicator that can send data across for any slave to pick it up. We can install one or more MySQL slaves, but this is not the purpose of this exercise. So we skip any regular slave installation and will install a MongoDB slave. To do so, we create a third script, which will invoke the installer to run a replicator slave service that applies data to MongoDB.

$ cat install_slave.sh
. ./DEPLOY.sh

cd $TUNGSTEN_BINARIES

export PATH=$MYSQL_BASEDIR/bin:$PATH

tools/tungsten-installer --master-slave -a \
  --datasource-type=mongodb \
  --master-host=127.0.0.1  \
  --service-name=mongodb \
  --home-directory=$MONGODB_DEPLOY \
  --cluster-hosts=127.0.0.1 \
  --datasource-port=$MONGODB_PORT \
  --master-thl-port=$MASTER_THL_PORT \
  --thl-port=$SLAVE_THL_PORT \
  --rmi-port=$SLAVE_RMI_PORT \
  --java-file-encoding=UTF8 \
  --skip-validation-check=InstallerMasterSlaveCheck \
  --svc-parallelization-type=none --start-and-report

Compared to MySQL installation, we see that the 'datasource-type' is 'mongodb', and as such it does not need username and password. Let's run it.

$ ./install_slave.sh
WARN  >> 127.0.0.1 >> Currently unable to check for the THL schema in mongodb
INFO  >> 127_0_0_1 >> Getting services list
INFO  >> 127_0_0_1 >> Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 49.444
role            : slave
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

We get a warning, which should not worry us, as it is expected. Notice that the high latency reported by the replicator is due to the delay in installing the slave. The only event in the replication pipeline is the 'master online' broadcast, which was replicated after the installation was completed.

If you got to this point, with both the master and slave replicator in the 'ONLINE' state, then you can continue with checking that replication works.

Step 5: check replication

Replication from MySQL (a relational DBMS) to MongoDB (a key-value store) requires some understanding of what we are replicating and what limitations we can face.

In MySQL, we have tables, which are translated to MongoDB collections. What is very important to understand is that Tungsten Replicator DOES NOT REPLICATE DDL EVENTS. Any CREATE/DROP/ALTER events are ignored by the replicator.

However, the replicator is smart enough to convert every table record into a document within the relevant collection. Every insert will generate a document inside MongoDB. Let's see an example:

mysql [localhost] {msandbox} (test) > create table myfirst (id int not null primary key, name char(30), d date);
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into myfirst values (1, 'Harry Potter', '1997-06-30');
Query OK, 1 row affected (0.00 sec)

On the MongoDB side, we will see the corresponding object:

> show dbs
local 0.078125GB
test 0.203125GB
tungsten_mongodb 0.203125GB
> use test
switched to db test
> show collections
myfirst
system.indexes
> db.myfirst.find()
{ "_id" : ObjectId("51a2867d3004fd7959a5f5aa"), "id" : "1", "name" : "Harry Potter", "d" : "1997-06-30" }

Not only inserts, but also updates are recognised:

mysql [localhost] {msandbox} (test) > insert into myfirst values (2, 'Harry Potter 2', '1999-06-02');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > update myfirst set name = 'Harry Potter 1' where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Which will result in:

> db.myfirst.find()
{ "_id" : ObjectId("51a2867d3004fd7959a5f5aa"), "id" : "1", "name" : "Harry Potter 1", "d" : "1997-06-30" }
{ "_id" : ObjectId("51a287633004fd7959a5f5ab"), "id" : "2", "name" : "Harry Potter 2", "d" : "1999-06-02" }

And delete statements:

mysql [localhost] {msandbox} (test) > delete from myfirst  where id =2;
Query OK, 1 row affected (0.00 sec)

#####
> db.myfirst.find()
{ "_id" : ObjectId("51a2867d3004fd7959a5f5aa"), "id" : "1", "name" : "Harry Potter 1", "d" : "1997-06-30" }

This is all fine. But what happens when we insert quite a lot of data? Let's find out. We're going to use the test employees database to see if the system complains.

$ cd ~/data/employees/
$  ~/sandboxes/msb_5_5_31/use < employees.sql
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries

To see if replication is OK, we check both master and slave for their status, and then we count the records in MySQL and MongoDB.

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $MASTER_RMI_PORT services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 182
appliedLatency  : 1.152
role            : master
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $SLAVE_RMI_PORT services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 106
appliedLatency  : 93.469
role            : slave
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

So, we see that the slave is lagging, as the data is hard to digest (about 4 million records). Let's try again after a few seconds:

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $SLAVE_RMI_PORT services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 182
appliedLatency  : 89.99
role            : slave
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

Both the master and the slave have reached the same transaction number. The slave has some delay, due to the size of the transactions (which become even bigger because of the RBR). When the size of the transaction is small, the latency becomes quite acceptable:

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $MASTER_RMI_PORT heartbeat

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $SLAVE_RMI_PORT services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 183
appliedLatency  : 0.423
role            : slave
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

As a last operation, let's check the numbers of the biggest table in the employees database:

$ ~/sandboxes/msb_5_5_31/use  employees
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (employees) > show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)

mysql [localhost] {msandbox} (employees) > select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.22 sec)

mysql [localhost] {msandbox} (employees) > select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (0.28 sec)

mysql [localhost] {msandbox} (employees) > select count(*) from titles;
+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (0.25 sec)

mysql [localhost] {msandbox} (employees) > select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+
1 row in set (0.83 sec)

####

> use employees
switched to db employees
> show collections
departments
dept_emp
dept_manager
employees
salaries
system.indexes
titles
> db.employees.count()
300024
> db.dept_emp.count()
331603
> db.titles.count()
443308
> db.salaries.count()
2844047
>

This proves that replication works, and even large amount of data are replicated reasonably fast.

Here's the official documentation about MySQL to MongoDB replication: https://docs.continuent.com/wiki/display/TEDOC/Replicating+from+MySQL+to+MongoDB. There you will find more details on how to install and what to expect. Happy hacking!

Introducing the Flying Clusters, and more than MySQL replication

My Colleague Linas Virbalas has just crossed the boundary between real and virtual and has started a blog, titled Flying Clusters. Linas is a gifted developer who is taking care of the special projects. One of such projects is replication between MySQL and PostgreSQL, which works quite well.

Another project, which has just started, is about providing PostgreSQL with Advanced Logical Replication using Tungsten replicator. As you probably know, recent versions of PostgreSQL can do physical replication, which has its pros and cons. With this project, PostgreSQL users can also have the choice of using logical replication. Not only that: since Tungsten Replicator already supports MySQL, cross-DBMS replication clusters are not far away.
We are also more ambitious, and we are exploring ways of replicating to NoSQL entities. We will start with MongoDB at a dedicated SQL to NoSQL Replication Hackathon, where we will attempt the creation of n applier for MongoDB during the Open Database Camp conference.

Open Database camp 2011 - Travel logistics, and don't forget the party

The Open Database Camp 2011 is near. In 9 days, the welcome party starts, and then the conference itself gets going. If you are coming earlier than Friday, May 6th, you can either use public transportation or book a private seat with a volunteer in the car pooling page. Please help the organizers: post your arrival and departure dates and times, so we may be able to help you even outside the official conference days. About the conference itself, as everyone should know, it's a un-conference, where the talks will be decided on the spot. But you can book ideas and topics in the sessions page. Since we will have many participants from Italy, there will be dedicated sessions in Italian in addition to the ones in English, which is the official language of the conference.