Friday, January 23, 2009

Tungsten Replicator Presentation on 2 Feb in San Francisco

If you want to learn about Tungsten Replicator first hand, I'll be doing a presentation at the February 2nd MySQL Meet-up in San Francisco. With luck I'll be able to demo not only the replicator but also some cool Amazon/RightScale integration we will be introducing in February. Teaser: It's possible to set up database clusters faster in Amazon than on a laptop.

Advance thanks to Mike Tougeron and Rich Nigra for the invite. For people suffering from obsessive curiosity about MySQL replication or database clustering in general, this talk is for you. Hope to see lots of you at the meet-up.

Wednesday, January 21, 2009

Encrypting Replicated Data

Open source projects have a great way of pulling in original ideas. Take encrypting replicated SQL: it's a big deal when you are are not sure about network security. This problem comes up very quickly when you transmit data over WAN links or in shared/cloud environments.

I have been procrastinating on implementation of encryption in the Tungsten Replicator because it felt as if we were going to have to do some surgery for it to work correctly. (Another hypothesis is that I'm lazy.) However, this morning I was talking via Skype to Mark Stenbäck, an engineer from Finland whom we met through a recent conference. Mark had a great idea. Why not just write an event filter? An excellent thought...Here's how it could work.

Filters transform or alter SQL events in the Tungsten Replicator. Let's say that you are replicating using MySQL 5.0. All SQL events contain SQL statements, which look like the following in our transaction history storage. The field 'SQL(0)' is the statement. Everything else is metadata.
SEQ# = 97
- TIME = 2009-01-12 10:01:46.0
- EVENTID = 000282:20633
- SOURCEID = centos5d
- STATUS = 2
- SCHEMA = mats
- SQL(0) = DROP PROCEDURE IF EXISTS updateRow
The SQL statement is contained in a simple data structure that is easy to manipulate within filter code. Now suppose you write the following Java classes:
  • StatementEncryptionFilter -- Looks at the event and encrypts the statement, using secret key (2-way) encryption.
  • StatementDecryptionFilter -- Looks at the event and decrypts the statement using 2-way decryption.
Implementing encryption itself is not very difficult in Java. You can follow the cryptographic examples in the Java documentation to get something working quickly. You then add the filters into the Tungsten replicator.properties file. The configuration might look like the following:
# Pre-storage filter(s).  Runs on events before they are stored
# in the transaction history log.
replicator.prefilter=logger,encrypt

# Post-storage filter(s). Runs on events after pulling them from
# storage and before handing off for application.
replicator.postfilter=decrypt,logger
...

# Encrypt/decrypt filter definitions.
replicator.filter.encrypt=\
org.foo.tungsten.StatementEncryptionFilter
replicator.filter.encrypt.password=!encrypt?!
replicator.filter.decrypt=\
org.foo.tungsten.StatementDecryptionFilter
replicator.filter.decrypt.password=!encrypt?!
There are some subtleties here--when there are multiple filters you need to make sure that the encryption filter is the last one called and similarly that the decryption filter is called first. Otherwise the SQL statements will look like garbage when other filters try to read them.

Another subtlety is row replication, which uses lists of objects to contain replicated values. These are harder to encrypt. In this case it might make sense to alter the Tungsten Replicator architecture to add a configurable storage filter that serializes and deserializes events in the transaction history log. This would be a nice feature addition. It's now JIRA TREP-199--we should be able to implement it within a couple of builds.

To get back to the topic of open source, Mark's suggestion is an excellent example of the power of working with a diverse group of people with similar interests. His idea seems straightforward to implement and also leads to a simple but powerful improvement to the replicator design.

Do you have any ultra-cool replication ideas? Post them on the Tungsten Replicator mailing list or just implement them directly. I'm going to set up a project for extensions to the replicator. Contributions are welcome--they will be open source and free to everyone. We'll gladly extend the Replicator to help make your ideas work. Meanwhile I'll keep posting other neat ideas for extensions. Please come on down and join the fun.

Tuesday, January 13, 2009

Another Tungsten Replicator Build Is Out...

...Beta-4 to be precise. Downloads are available on the Continuent Forge. You can get more information about Tungsten in general from our community pages.

The Beta-4 build has a number of nice improvements. The best new feature is a utility to look and manage at events in the transaction history log. It's our version of mysqlbinlog but without any funny options to look at row updates.

Speaking of row updates, we now support all standard datatypes used in MySQL 5.1 row events. Error handling now works on the "WALL-E model"--if there's a serious error the Replicator goes into a restartable state called OFFLINE:ERROR and waits for you to bring it back on-line. Recovering from errors is therefore very straightforward and works over the network. Finally, we have several new event filters including one that prints SQL event contents into the system log. That's very handy for debugging. There is also the time delay filter I discussed in the last post on this blog.

For our Beta-5 build we will be improving statement replication, adding full support for heartbeat events, and add some SQL case-mapping filters to make MySQL to Oracle replication work easily. I guess that means I finally need to write a blog article about replicating from Oracle to MySQL.

Please stop by our community site and give the new build a spin.

Monday, January 5, 2009

Using Tungsten Replicator Filters to Implement MySQL Time-Delayed Replication

Time-delayed replication is a useful feature that allows a slave to lag a fixed amount of time behind a master, thus providing a time window to recover from disasters like deleting a 10 million line table by accident. You just run over to the slave, turn off replication, and recover lost data, as the delayed updates mean it has yet to see your deadly mistake. It's a simple way to protect your administrative honor as well as your job.

Time-delayed replication has been on the MySQL to-do list since at least 2001. It's currently scheduled for release 6.0 and the fix is included in recent OurDelta builds. However, there's a very simple way to get the feature with Tungsten Replicator filters. This works for unadulterated MySQL 5.0 and 5.1 releases.

I wrote about filters in a previous post on the pluggable Tungsten Replicator architecture. Filters are hooks into the replicator that allow your code to examine, change, and drop events before applying them to another database. Here's the Java interface:
public interface Filter extends ReplicatorPlugin
{
public ReplDBMSEvent filter(ReplDBMSEvent event)
throws ReplicatorException, InterruptedException;
}
Is there a simple way to use a filter for time-delayed replication? Actually, there is. It turns out that the ReplDBMSEvent passed into the filter includes a "source timestamp" that tells when the event was pulled from the log. Tungsten reads binlog events almost instantaneously, so we can use the source timestamp to approximate the time at which the update was written on the master. If we also assume that clocks are synchronized between master and slave hosts, it's a matter of minutes to write a simple filter that implements time-delayed replication.

The filter itself is surprisingly short, because all we have to do is compute how long until the SQL should be applied and then go to sleep for that period of time. There are no complex API calls or management gyrations because our filter runs inside the replicator. This took about 20 minutes to write in Eclipse. (By contrast, this blog article is now 2 hours and counting.) Here is all the code you need.
package com.continuent.tungsten.replicator.filter;

import java.sql.Timestamp;
import org.apache.log4j.Logger;
import com.continuent.tungsten.replicator.ReplicatorException;
import com.continuent.tungsten.replicator.event.ReplDBMSEvent;
import com.continuent.tungsten.replicator.plugin.PluginContext;

/**
* Filter to delay a transaction until a particular point in time has passed.
* The time delay filter uses the originating timestamp from the replication
* event to just time delays. We assume that clocks are synchronized to within
* some reasonable precision between event producers and consumers.
*/
public class TimeDelayFilter implements Filter
{
private static Logger logger =
Logger.getLogger(TimeDelayFilter.class);
private long timeDelayMillis = 0;

/**
* Sets the time delay in seconds.
*/
public void setDelay(long timeDelaySeconds)
{
timeDelayMillis = timeDelaySeconds * 1000;
}

public ReplDBMSEvent filter(ReplDBMSEvent event)
throws ReplicatorException, InterruptedException
{
// Compute the interval that we should delay.
Timestamp sourceTstamp = event.getSourceTstamp();
long futureTime = sourceTstamp.getTime() + timeDelayMillis;
long intervalMillis = futureTime - System.currentTimeMillis();

// Sleep until it is time to deliver this event. We let
// InterruptedException flow through or the replicator will not
// be able to shut down.
if (intervalMillis > 0)
Thread.sleep(intervalMillis);

return event;
}

public void configure(PluginContext context) throws ReplicatorException {
logger.info("Time delay filtering: event delivery delay set to "
+ (timeDelayMillis / 1000) + " seconds");
}

public void prepare(PluginContext context) throws ReplicatorException {
}

public void release(PluginContext context) throws ReplicatorException {
}
}
User-written code lives in a directory called lib-ext. If you write your own filters you'll need to compile them into a JAR file using javac/jar. You need to put the JAR into the lib-ext directory of any slave replicator that uses the filter. Then you tell the replicator to run your filter by updating file replicator.properties, which contains all the configuration settings for Tungsten Replicator.

First, we tell the replicator that there is an "applier-side" filter that will run on events that are applied to the slave database. The property setting looks like this--it specifies that a filter nicknamed "delay" that processes events before we apply them.
# Post-storage filter selection.  Value must be one or more comma-separated
# logical filter names.
replicator.postfilter=delay
We now need to define the "delay" filter, which requires two more entries in replicator.properties, so that our filter will delay events by 5 minutes (300 seconds). Tungsten uses a very simple protocol for configuring filters--it reads the 'delay=300' property and assigns this automatically using the corresponding variable setter on the filter class.
# Time delay filter.  Should only be used on slaves, as it delays storage
# of new events on the master. The time delay is in seconds.
replicator.filter.delay=\
com.continuent.tungsten.replicator.filter.TimeDelayFilter
replicator.filter.delay.delay=300
If you now restart the replicator as a slave, it will read the new property values and start running. So does it work? You can test by inserting a row on the master database as follows:
mysql> create table foo (id int primary key, data varchar(25),
mysql> creation_date timestamp);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into foo(id, data) values(9, 'HELLO');
Query OK, 1 row affected (0.01 sec)
Now go over to the slave and try to select the row:
mysql> select * from foo;
ERROR 1146 (42S02): Table 'mats.foo' doesn't exist
It's not there. In fact, the table has not arrived yet either. However, if you try again in 5 minutes, you will get the following:
mysql> select * from foo;
+----+--------+---------------------+
| id | data | creation_date |
+----+--------+---------------------+
| 9 | HELLO | 2009-01-05 16:13:33 |
+----+--------+---------------------+
1 row in set (0.00 sec)
As this post shows, filters are a very flexible way to bend Tungsten replication to your will without writing a lot of code. I did a demo of this filter to a friend and we thought of a number of variations on the time-delay idea--one of the more interesting ones is to call somebody to approve SQL statements that look dangerous. The filter would hold them up until this occurs.

The main objection to the Tungsten filter approach is that it requires you to implement using Java. This does not bother me especially, but a lot of otherwise quite nice people break into hives at the thought of writing Java code. Linas Virbalas, a colleague of mine, is working on a generic filter that connects to JavaScript. I'm really looking forward to see this work. It will open up a lot of interesting doors to extend replication flexibly and easily using scripts.

p.s., You don't have to write this filter yourself. The time-delay filter is checked into our SVN repository and will be released in Tungsten 1.0 beta-4, due out on or around January 9th, 2009. Stop by our community website learn more. Downloads are located on the Continuent Forge.

Scaling Databases Using Commodity Hardware and Shared-Nothing Design