Getting started with Tungsten Replicator and Tungsten Sandbox

We have been busy, at Continuent. In addition to our usual work with high performance replication, we have addressed usability issues, since we know that a hard-to-use problem, no matter how powerful, has low adoption. Thus, is with some personal satisfaction that I can announce the release of Tungsten Replicator 2.0.3, which comes with several huge improvements in matter of user friendliness. The new installation procedure is so user friendly, in fact, that I was able to build a sophisticated tungsten-sandbox with a 150-line shell script. (The corresponding features for MySQL Sandbox required 4,500 lines of Perl).

Enough self celebration, though. Let's get started, as the title of this post suggests, with the practical steps.

Requirements

Before we begin, there are a few requirements to meet.

1. You need to be on a Unix-like operating system. Our main choice is Linux. If you want to test on Mac OSX, it works, but we won't recommend it.
2. Java JRE must be installed. And it must be the original one, not the Open JDK. Update: The requirement against OpenJDK has been lifted. It works fine in my tests.
3. Ruby 1.8 must be installed. This is mainly needed during the installation phase only, but it is required nonetheless.
4. The user account that will install and run Tungsten must have ssh access to the other hosts involved in the cluster
5. The above mentioned user must have sudo access. This is only needed if you want to use Tungsten Replicator to run backups that involve root access (like xtrabackup). We may lift this requirement later, but for now you need to enable it, at least during the installation, and remove the access when you are done.
6. This user must also have read access to MySQL binary logs. Usually you achieve this by making sure that the binary logs are readable by users belonging to the "mysql" group, and by adding such group to your user.
7. There must be a MySQL users for Tungsten replication. This user must have full access to the database server, with grant option.
8. The MySQL server must have binary logging enabled.
9. If you have MySQL native replication running, you must stop it.

Getting the code and install

The code is released in the downloads section of Tungsten's home. The current recommended version is 2.0.3, but if you like to be really up to date, we also publish a list of recent builds from our build server, which you can use to have a go at the replicator. For this simple installation, I will use four servers from our server farm. The servers are named R1, R2, R3, and R4. The first good news of the new installation process is this: you need to install in one server only!. More details follow. First off, create a directory where you want to install. Use a non-root account. Just make sure that it's the same user in all the servers, and that such user can access the directory where you want to install. I am going to call this directory planet.

cd $HOME
for N in 1 2 3 4 ; do ssh r$N mkdir planet ; done
cd planet 
wget http://tungsten-replicator.googlecode.com/files/tungsten-replicator-2.0.3.tar.gz
tar -xzf tungsten-replicator-2.0.3.tar.gz
cd tungsten-replicator-2.0.3

I have already a MySQL user named tungsten with password "mypwd" (but it can be anything you like, as long as it has the required privileges). Now we have all the components. If you have read the Tungsten documentation, please ignore the ./configure script. That is left for compatibility reasons, and will be deprecated soon. Instead, to install the cluster of our 4 servers, let's do the following:

export TUNGSTEN_BASE=$HOME/planet

./tools/tungsten-installer \
    --master-slave \
    --master-host=r1 \
    --datasource-user=tungsten \
    --datasource-password=mypwd \
    --service-name=dragon \
    --home-directory=$TUNGSTEN_BASE \
    --cluster-hosts=r1,r2,r3,r4 \
    --start

Some comment on this command: --master-slave is the installation mode (see below for more info). --service-name can be anything you want. --home-directory is where all the installation sub directories will go. --cluster-hosts is the list of servers you want to install, and finally, --master-host is the host that will be installed as a master, while all the others will be slaves of that one. If you have followed the instructions carefully, the installer will bring up the Tungsten cluster without any fuss, Unix style. If you hate silent installations, you can get the full monty by adding some options:

./tools/tungsten-installer \
    --verbose \
    --master-slave \
    --master-host=r1 \
    --datasource-user=tungsten \
    --datasource-password=mypwd \
    --service-name=dragon \
    --home-directory=$TUNGSTEN_BASE \
    --cluster-hosts=r1,r2,r3,r4 \
    --start-and-report

If you run the installer in verbose mode, you will see an extremely long list of validation checks that the installed does on your current servers and on the ones that are listed in the --cluster-hosts option. If everything went well, you will find the following directories in $HOME/planet (for all servers in your cluster):

• configs, containing the configuration file created by the installer. This file describes your cluster
• releases, containing the Tungsten binaries.
• thl, containing Tungsten's Transaction History Logs. These logs are like MySQL binary logs, but with much more metadata, including a global transaction ID, which is missing in MySQL native replication.
• relay, which should be empty, unless you install in "direct" mode (see below.)
• tungsten, which is a symlink to the Tungsten directory inside releases.

In addition to the above mentioned directories, Tungsten Replicator creates a database for each service. Since we have only one service in this topology, you will find a database named "tungsten_dragon". (If you have called your service "bunny", you will instead find "tungsten_bunny"). Inside this database there is the replication metadata necessary for making the servers fault tolerant. Only a small amount of data is kept on that database. It's roughly corresponding to what you get from the .info files in MySQL native replication. To test that the system is OK, let's find our tools. The first one is trepctl, which, among other things, can give us an overview of the running services.

cd $HOME/planet
./tungsten/tungsten-replicator/bin/trepctl services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 1.152
role            : slave
serviceName     : dragon
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

./tungsten/tungsten-replicator/bin/trepctl -host r1 services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 0.936
role            : master
serviceName     : dragon
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

mysql -h r1 -e 'create schema if not exists test'
mysql -h r1 -e 'create table test.t1 (i int)'

./tungsten/tungsten-replicator/bin/trepctl services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 2
appliedLatency  : 0.155
role            : slave
serviceName     : dragon
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

./tungsten/tungsten-replicator/bin/trepctl -host r1 services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 2
appliedLatency  : 0.044
role            : master
serviceName     : dragon
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

After the installation, trepctl reported the last applied sequence number (appliedLastSeqno) as 0. Following the execution of two commands in the master, such number became 2. If you want to know more of what was happening, you can use the thl command. This corresponds roughly to using mysqlbinlog with MySQL native replication logs.

/tungsten/tungsten-replicator/bin/thl -service dragon list |less
SEQ# = 1 / FRAG# = 0 (last frag)
- TIME = 2011-06-09 14:51:23.0
- EVENTID = 000002:0000000000000514;197609
- SOURCEID = qa.r1.continuent.com
- STATUS = COMPLETED(2)
- METADATA = [mysql_server_id=10;service=dragon;shard=test]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- OPTIONS = [##charset = ISO8859_1, createOrDropDB = , autocommit = 1, sql_auto_is_null = 1, foreign_key_checks = 1, unique_checks = 1, sql_mode = '', character_set_client = 8, collation_connection = 8, collation_server = 8]
- SQL(0) = create schema if not exists test /* ___SERVICE___ = [dragon] */
SEQ# = 2 / FRAG# = 0 (last frag)
- TIME = 2011-06-09 14:51:30.0
- EVENTID = 000002:0000000000000601;197610
- SOURCEID = qa.r1.continuent.com
- STATUS = COMPLETED(2)
- METADATA = [mysql_server_id=10;service=dragon;shard=test]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- OPTIONS = [##charset = ISO8859_1, autocommit = 1, sql_auto_is_null = 1, foreign_key_checks = 1, unique_checks = 1, sql_mode = '', character_set_client = 8, collation_connection = 8, collation_server = 8]
- SCHEMA = 
- SQL(0) = create table test.t1 (i int) /* ___SERVICE___ = [dragon] */

Once we are satisfied that replication is working, we can clean up the cluster and try other installation experiments. To clean up a cluster, you need to do the following:

1. stop the replicator in all servers.
   for N in 1 2 3 4; do $PWD/tungsten/tungsten-replicator/bin/replicator stop; done
2. remove the thl files from all servers.
3. remove the tungsten_SERVICE_NAME database from all mysql servers
4. run a "reset master" in the master database
5. remove the directories created by the installer in all servers

Installation types

The procedure described above was, until a few months ago, the only thing you could do with Tungsten. Now you can broaden your horizons with a wider range of possibilities.

Master/slave is of course the main option, and it's the one that you have seen in the previous section. This method gives you the full set of Tungsten features and performance. It is the recommended method for production use and for benchmarking. In this scenario, the Tungsten replicator on the master will extract transactions from the binary log, transfer them to the THL, and share it with the slaves. The slaves will read from the THL and apply the transactions to the database. There are a few steps more in between, but for the sake of brevity I will skip them You can have a look at Robert Hodges blog for more info.
Slave "direct" is the alternative that you can use in production, and it's been designed to satisfy users who only want some particular benefits on the slave side, and don't care about global transaction IDs. If you are looking at parallel apply, this is probably a setup that you want to try. In this scenario, there is no replicator on the master. The slave pulls data remotely from the binary logs, copies them locally, and extracts data to the THL. Here's an example of how to start a slave-direct system:

./tools/tungsten-installer \
    --direct \
    --master-host=r1 \
    --slave-host=r4 \
    --master-user=tungsten \
    --slave-user=tungsten \
    --master-password=secret \
    --slave-password=secret \
    --service-name=Castor \
    --thl-port=21124 \
    --rmi-port=10104 \
    --channels=5 \
    --home-directory=$TUNGSTEN_BASE \
    --start-and-report

If your purpose is testing Tungsten, probably the Tungsten Sandbox is what you should try. This system is based on MySQL Sandbox, a framework that lets you install more than one MySQL server in the same host. Building on top of MySQL Sandbox, and leveraging the new flexibility in Tungsten installer, tungsten-sandbox allows you to build a master/slave system inside a single host. Let's give it a try. You need to have MySQL Sandbox installed, and at least one MySQL tarball expanded under $HOME/opt/mysql/X.X.XX (where X.X.XX is the MySQL version, such as 5.5.12).

cd $HOME/planet
mkdir sb
cd tungsten-replicator-2.0.3
wget http://tungsten-replicator.googlecode.com/files/tungsten-sandbox
./tungsten-sandbox -h
USAGE: ./tungsten-sandbox [flags] args
flags:
  -n,--nodes:  how many nodes to install (default: 3)
  -m,--mysql_version:  which MySQL version to use (default: '5.1.56')
  -t,--tungsten_base:  where to install the sandbox (default: '/home/tungsten/tsb2')
  -d,--group_dir:  sandbox group directory name (default: 'tr_dbs')
  -s,--service:  how the service is named (default: 'tsandbox')
  -P,--base_port:  port base for MySQL sandbox nodes (default: 710)
  -l,--thl_port:  port for the THL service (default: 1211)
  -r,--rmi_port:  port for the RMI service (default: 1010)
  -v,--[no]version:  show Tungsten sandbox version (default: false)
  -h,--[no]help:  show Tungsten sandbox help (default: false)

In my server, I have already expanded MySQL 5.5.10, and I want to install inside $HOME/tsb. So, here is what I do:

./tungsten-sandbox -m 5.5.10 -t ~/tsb

This command installs three instances of MySQL under $HOME/sandboxes and three of Tungsten under $HOME/tsb. Inside this directory, in addition to the running instances, we find some more goodies:

• clear_all send_kill_all start_all status_all stop_all use_all : symlinks to the corresponding commands in MySQL sandbox
• db1 db2 db3: these are the tungsten instances.
• n1 n2 n3: quick links to access each MySQL node
• replicator_all trepctl_all: utilities that run "replicator" or "trepctl" for each node with the arguments provided on the command line

Additionally, there are a few scripts inside each Tungsten instance in the sandbox:

• trepctl: a link to the deep down trepctl
• thl: a link to the thl utility
• show_log: a quick way of showing the replicator log

Since this is a tool for testing, removing it is going to be easy.

~/tsb/replicator_all stop
~/tsb/clear_all
rm -rf ~/tsb/*

A final method of installing is a sandbox with ths slave-direct method. There is no dedicated script for this method, but thanks to the new installer, you can get the job done quite easily:

export NODE_OPTIONS='-c innodb_flush_log_at_trx_commit=2 -c max_allowed_packet=48M'
make_multiple_sandbox --group_directory=tr_dbs --sandbox_base_port=7100 5.5.10

TUNGSTEN_BASE=$HOME/tinstall/tsb/
./tools/tungsten-installer \
    --direct \
    --master-host=127.0.0.1 \
    --master-port=7101 \
    --slave-host=db2 \
    --slave-port=7102 \
    --master-user=root \
    --slave-user=root \
    --master-password=msandbox \
    --slave-password=msandbox \
    --master-log-directory=$HOME/sandboxes/tr_dbs/node1/data \
    --service-name=Castor \
    --thl-port=12112 \
    --channels=5 \
    --rmi-port=20000 \
    --home-directory=$TUNGSTEN_BASE \

./tools/tungsten-installer \
    --direct \
    --master-host=127.0.0.1 \
    --master-port=7101 \
    --slave-host=db3 \
    --slave-port=7103 \
    --master-user=root \
    --slave-user=root \
    --master-password=msandbox \
    --slave-password=msandbox \
    --master-log-directory=$HOME/sandboxes/tr_dbs/node1/data \
    --service-name=Pollux \
    --thl-port=22112 \
    --channels=1 \
    --rmi-port=20000 \
    --home-directory=$TUNGSTEN_BASE \
    --relay-directory=$TUNGSTEN_BASE/relay --start-and-report

This script creates two services (Castor and Pollux), with only one instance of Tungsten replicator, with all the servers (MySQL and Tungsten ones) in the same host.

Conclusions

There should be much more to say, but I will leave it for the coming days. In the meantime, I encourage everyone to try the new Tungsten and submit bug reports when things don't work as expected. As always, happy hacking!
P.S. Today at 10am PT there is a webinar on this very topic!

Who's afraid of MySQL forks?

There is much talk about MySQL forks and how they are going to replace MySQL, or take over MySQL user base, or become more powerful/profitable/popular/you-name-it than MySQL itself. Let's clear some air on this topic. There is more about forks than meets the eye, especially if you think about a few obvious facts. What's a fork? According to Wikipedia a project fork happens when developers take a legal copy of source code from one software package and start independent development on it, creating a distinct piece of software.

By this definition, when someone who doesn't work at the MySQL project distributes a package that is based on MySQL code but differs from the original, it's a fork.
Why am I approaching the issue from this angle? Because, apart from Windows users, who mostly download MySQL from the official site, the majority of users get MySQL through a Linux distribution or some other project. And most of the time such packages are different from the ones built by the MySQL team. There is nothing wrong with that. The differences are sometimes minimal packaging changes done to adapt MySQL to the specific distribution, and sometimes they are a cherry-picking application of patches to an old version that needs to be maintained so that the package is unlike any other MySQL version that you may find in the wild. Even if the version is the same, depending on the distribution and the age of the server, the code beneath could be wildly different from the official versions.
Thus, it turns out that many users, possibly the majority, are using a MySQL fork, albeit a very minor one.
But when people talk about forks, they often refer to three main projects:

• The Percona distribution. This is a collection of a few distinct patches in the server, coupled with a fork of the InnoDB plugin, named XtraDB, and an independent tool for backup (XtraBackup). This fork has a solid business background. Every patch has been developed to meet user requests, and the engineers at Percona maintain them appropriately.
• Then we have the MariaDB fork, which is a series of changes to the MySQL core, motivated by the desire of the developers to build a rich set of feature enhancements while being backward compatible to the main distribution. The business model is thus a fast track of new features and bug fixes to customers.
• And then there is Drizzle, which has even less business traction than MariaDB, but a very well defined goal of creating a lightweight database by re-engineering a bare bones stripped down version of MySQL that is now very distant from its origins.

What I said in the above descriptions is just the synopsis of what these three forks are. In recent mythology, it is fabled that, if MySQL ceases to exist (because it goes bankrupt, or Oracle kills it, or a major accident happens to the project, whatever) users can replace MySQL with one fork, and live happily ever after.
Not so fast. There is something that few people take into account when listening to this too often repeated tale.
What most observers miss is that the forks' original code (with the exception of Drizzle) is very marginal. The bulk of the distribution is still the code produced by the MySQL team, which is merged at every minor release, and integrated with the patches produced by Percona and MariaDB. So, while technically they are forks of MySQL, they can't live independently from the official MySQL distribution. Both Percona and MariaDB don't have the manpower to maintain the server by handling the huge amount of bugs that the MySQL team is fixing every month.
There is also a matter of skill set. Percona has talented InnoDB experts, while MariaDB has mostly core server experts (and some are among the top ones, I may add). They could complement each other, although it seems that cooperation between the two projects is not as good as it used to be. (Could be my personal impression.)
The bottom line, though, is if both projects are able to survive should the main project become unavailable. I am not suggesting that Oracle wants to make MySQL scarce. On the contrary, all the information at my disposal suggest that Oracle will keep MySQL publicly available for long time.
This state of affair seems to indicate that Drizzle is, instead, a true fork that does not depend on MySQL health. To some extent, this is true. However, the main storage engine in Drizzle is InnoDB. Therefore, at least today, Drizzle is as dependent on Oracle as Percona and MariaDB.
What would happen tomorrow, if the disaster depicted by doomsday advocates comes true and MySQL actually disappears? I don't honestly know, but I would love to have a public commitment from the major players, about what they are prepared to do in terms of maintaining that huge chunk of code that today they take from Oracle releases on a monthly basis.
This is all matter of thought for MySQL users.

About adoption of the forks today, I have seen five types of arguments in favor of a MySQL fork:

1. I need the feature provided by Percona or MariaDB, or I need a quick bug fix that I can't get from the slow roadmap at Oracle. I trust that this handful of people are able to maintain that little code that differs from MySQL and matters to me. So I don't care if they don't have 100 developers on the task.
2. Given Oracle's track record in other Open Source projects, I don't trust them to deliver MySQL according to FOSS principles, so let's go for true Open Source alternatives.
3. Most MySQL developers have now left Oracle, and so the forks have more chances of being higher quality.
4. Cool! MariaDB/Percona has a bunch of features more than MySQL. It must be better. Let's use it.
5. I like new technology. Let's plunge into them!

Argument #1 is a solid business backed reason for adopting some software. The risk is often well calculated, especially if the evaluation can be backed by performance and functional tests.
Argument #2 is frivolous, as it mixes subjective feelings into business matters. And so is argument #4. Yet, these two types of advocacy are quite popular and spread much faster than the more reasonable approach seen at #1.
Argument #3 is debatable. MySQL developers at Oracle outnumber all forks easily. The idea that the departure of a few core developers can alter the system in such a way that the whole project crumble has been already negated by facts: MySQL 5.5 is an excellent release, with enthusiastic appreciation from power users. While I agree that top MySQL talents work at the forks, I consider the MySQL team to be still in excellent shape.
Argument #5 is reasonable, if it is followed by cool judgment and backed by facts. I am one who is always ready to try new solutions, and love experimenting with cool technology. But adoption is different from proof of concept. I am happy to see that Drizzle can replace MySQL in some applications, but would I trust it in its present beta stage? Certainly not. So, I am happy to test, but I trust my valuable data to more stable solutions.

What's for you, the final user? My personal advice is: don't adopt blindly because of some enthusiastic advertising. But test the product thoroughly, and if it fits your needs, by all means, go for it. But if you don't have a specific reason, I recommend staying with the official branch, because, despite the change in affiliation, there is still a well experienced team behind it.

Protocol, the GPL, and how Bazaar can help

Mark Callaghan asks Can a protocol be GPL?, after finding a disturbing comment in a source file:

Any re-implementations of this protocol must also be under GPL, unless one has got an license from MySQL AB stating otherwise.

I recall talking with one of the company lawyers about this matter, and he assured me that the GPL can't be used for a protocol, and that's why this notice was dropped from MySQL.com site a few years ago, even before the Sun acquisition.
This is thus an embarrassing piece of ancient history (which will hopefully be removed soon) that has been in our files for long time. For how long?
If we get the source trees from the public bazaar repository, we don't get a good answer.

$ bzr annotate --long --all  sql/net_serv.cc |  head -n 24 |tail -n 9
2476.648.3   cmiller@xxxxx   20071011 | /**                   
2476.648.3   cmiller@xxxxx   20071011 |   @file
2476.648.3   cmiller@xxxxx   20071011 | 
1616.1722.3  joerg@xxxxx     20050307 |   This file is the net layer API for the MySQL client/server protocol,
1616.1722.3  joerg@xxxxx     20050307 |   which is a tightly coupled, proprietary protocol owned by MySQL AB.
2476.648.3   cmiller@xxxxx   20071011 |   @note
1616.1722.3  joerg@xxxxx     20050307 |   Any re-implementations of this protocol must also be under GPL
1616.1722.3  joerg@xxxxx     20050307 |   unless one has got an license from MySQL AB stating otherwise.
1616.1722.3  joerg@xxxxx     20050307 | 

Inspecting revision 1616.1722.3, we learn that it was just a merge. This thing was much older. A comment in Mark's blog from Venu Anuganti put me on the right track. He was a MySQL employee in 2003, and thus I needed to find older annotations.
A few years ago I showed how you could get back in time using Bazaar .
Using this technique, I resuscitated MySQL 4.1.2

$ bzr branch -r tag:mysql-4.1.2 lp:mysql-server/5.1 branch4.1.2
$ cd branch4.1.2
$ bzr annotate --long --all  sql/net_serv.cc |  head -n 24 |tail -n 9
2           bk@xxxxxxx  20000731 |                             
1098.3.1    monty@xxxx  20020723 | /*
1538.19.1   monty@xxxx  20030604 |   This file is the net layer API for the MySQL client/server protocol,
1538.19.1   monty@xxxx  20030604 |   which is a tightly coupled, proprietary protocol owned by MySQL AB.
1538.19.1   monty@xxxx  20030604 |   Any re-implementations of this protocol must also be under GPL
1538.19.1   monty@xxxx  20030604 |   unless one has got an license from MySQL AB stating otherwise.
1538.19.1   monty@xxxx  20030604 | */
1538.19.1   monty@xxxx  20030604 | 
1538.19.1   monty@xxxx  20030604 | /*

$bzr log -r 1538.19.1
------------------------------------------------------------
revno: 1538.19.1
committer: monty@xxxx
timestamp: Wed 2003-06-04 18:28:51 +0300

So, this looks like a commit, not a merge, and thus we have found the origin of the offending message.
However, there is a problem.
In a comment on a recent Brian's blog post, Monty said
"I have never said or claimed that the GPL affects you over the protocol."
I am sure there is a reason for this quote, but unfortunately Bazaar doesn't have an answer.