This document is written for Tungsten Replicator version 1.0.3.
Copyright © 2008-2009 Continuent
The trademarks, logos, and service marks in this Document are the property of Continuent or other third parties. You are not permitted to use these Marks without the prior written consent of Continuent or such appropriate third party. Continuent, Tungsten, uni/cluster, m/cluster, p/cluster, uc/connector, and the Continuent logo are trademarks or registered trademarks of Continuent in the United States, France, Finland and other countries.
All Materials on this Document are (and shall continue to be) owned exclusively by Continuent or other respective third party owners and are protected under applicable copyrights, patents, trademarks, trade dress and/or other proprietary rights. Under no circumstances will you acquire any ownership rights or other interest in any Materials by or through your access or use of the Materials. All right, title and interest not expressly granted is reserved to Continuent.
All rights reserved.
Table of Contents
List of Figures
List of Tables
Tungsten Replicator provides master/slave replication. It has a pluggable architecture and it supports a multiplicity of database management systems. The following chapters provide more detailed information on Tungsten Replicator.
The basic concepts of the Tungsten Replicator system are explained in the following chapters.
Database replication is a highly flexible technology for copying updates automatically between databases. The idea is that if you make a change to one database, other database copies update automatically. Replication occurs at the database level and does not require any special actions from client applications.
Propagating updates automatically is a simple idea, but it helps solve a surprisingly large number of problems. See below for a summary figure of solution examples. The examples are also explained into more detail - clockwise - in the list below the figure.
Availability. Keeping multiple copies of data is one of the most effective ways to avoid database availability problems. If one database fails, you can switch to another local copy or even to a copy located on another site.
Cross-site database operation. Applications like credit card processing use multiple open databases on different sites, so that there is always a database available for transactions. Replication can help transfer copies between distributed databases or send updates to a centrally located copy.
Scaling. Replicated copies are live databases, so you can use them to distribute read traffic. For example, you can run backups or reports on a replica without affecting other copies.
Upgrades. Replication allows users to upgrade a replica, which can then be switched over to the master copy. This is a classic technique to minimize downtime as well as provide a convenient back-out in the event of problems.
Heterogeneous database integration. It is quite common for data to be entered in one database type, such as Oracle, and used in another, such as MySQL. Replication can copy data between databases and perform transformations necessary to ensure proper conversion.
Data warehouse loading. Replication applies updates in real-time, which is very useful as databases become too large to move using batch processes. Data warehouse loading is much easier with capabilities such as transforming data or copying updates to a central location.
Geographic distribution. Replication allows users to place two or more clusters in geographically separated location to protect against site failure or site unreachability.
It is not surprising that database replication is considered essential technology to build and operate a wide variety of business-critical applications. Tungsten Replicator is designed to solve the problems described above as well as many others.
Tungsten Replicator uses master/slave replication. In master/slave replication, updates are handled by one database server, known as the master, and propagated automatically to replicas, which are known as slaves. This is a very efficient way to make database copies and keep them up to date as they change.
Master/slave replication is based on a simple idea. Let's assume that two databases start with the same initial data, which we call a snapshot. We make changes on one database, recording them in order so that they can be replayed with exactly the same effect as the original changes. We call this a serialized order. If we replay the serialized order on the second database, we have master/slave replication.
Master/slave replication is popular for a number of reasons. First, databases can generate and reload snapshots relatively efficiently using backup tools. Second, databases not only serialize data very quickly but also write it into a file-based log that can be read by external processes. Master/slave application is therefore reasonably tractable to implement, even though the effort to do it well is not small.
Master/slave replication has a number of benefits for users. It runs very quickly, places few limitations on user SQL, and works well over high latency network connections typical of wide area networks (WAN). Also, as Tungsten Replicator demonstrates, this type of replication does not require any changes to the database server itself, which means that it works with off-the-shelf databases.
Master/slave replication also has some disadvantages. First, the master is a single point of failure. Special procedures are necessary to handle this and keep systems available. Second, slaves tend to lag the master. This is due to the fact that masters can typically process updates faster than they can be replicated and applied to slaves.
Tungsten Replicator is designed to minimize drawbacks of the approach, for example by handling master failover correctly or providing mechanisms to help boost speed of updates on replicas. It also has features like data filtering and transformation, which make it better able to handle problems like heterogeneous data integration for which master/slave replication is uniquely suited.
Replication technology is so important that most established databases offer it as a built-in feature. Built-in replication has the advantage that it tends to work well for replication between databases of the same type. However, there are commonly restrictions that prevent using it between database versions or across different operating systems. In addition, for commercial databases replication tends to be a complex and expensive add-on.
Tungsten Replicator by contrast operates outside the database and can be viewed as external replication. This approach has a number of advantages. It handles problems like availability and scaling but also imposes fewer limitations such as version or platform restrictions and is ideally suited for replicating between databases of different types.
Log-based replication reads SQL updates from the database recovery log, which contains the serialized list of changes that the database uses for its own recovery in the event of a restart. Log-based replication has the lowest performance overhead of any replication method, handles a wider set of changes, and has the least management impact. It is, however, harder to implement because it requires reading log formats which are complex and rarely documented completely.
Trigger-based replication, on the other hand, installs triggers to capture table updates. This approach is often easier to implement but in most other respects is almost always a "second choice" for users. Triggers tend to slow the master, add to management complexity, and have limited ability to handle data definition language (DDL).
Tungsten Replicator uses log-based replication due to the greatly improved performance and flexibility. Not all log readers are available in open source; some are commercial extensions.
Tungsten Replicator is a process that runs on every host in the cluster and implements replication as described in the previous sections. The figure below depicts the replication architecture:
The components in the figure are:
Master DBMS - The Database Management System (DBMS), which acts as the master for the replication system. The master role can change, and any DBMS can be potentially elected as the master for the replication.
Slave DBMS - The slave DBMS receives replication events from the master DBMS and applies them. There can be any number of slaves in the replication system. Slaves are also commonly known as replicas.
Replication Event Extractor - The replication event extractor extracts replication events from the master DBMS logs. Events are either SQL statements or rows from the replicated database.
Transaction History Log - The transaction history log provides persistent storage for replication events and communications with other transaction history logs in the cluster.
Replication Event Applier - The replication event applier applies the replication events into the slave DBMS.
Node Manager - Node manager refers to the manager for Tungsten components running either on the slave or master node. Node manager connects to the Tungsten service manager at the upper level.
Tungsten Replicator architecture is very flexible and allows addition of new extractors and appliers. Addition of new databases is quite straightforward. It also allows users to implement creative new uses for replication, such as reading data from a database and applying it to an application rather than a database, or replicating from an application to a database. Extending Tungsten Replicator is discussed in Chapter 7, Extending the Tungsten Replicator System.
Tungsten Replicator can extract events and hence replicate from any MySQL 5.0 or later database that has binary logs (binlogs) enabled. Binary logs capture data for MySQL's native replication. Tungsten Replicator tails binlog files, parsing and extracting new events as the MySQL server writes them. These events are then stored in the Transaction History Log and propagated to other Tungsten Replicator instances.
The following diagram shows the Tungsten Replicator architecture for MySQL replication.
Tungsten Replicator has a number of advantages over native MySQL replication, including the following:
Proper handling of master failover in the presence of multiple slaves. Tungsten Replicator puts global sequence numbers on all SQL requests, which allows slaves to be promoted the master and then distribute events easily to the remaining slaves.
Replication from newer to older versions of MySQL, for example from MySQL version 5.0 to version 4.1.
Handling flexible replication designs including one master to many slaves (fan-out), many masters to a single slave (fan-in) and circular replication between two or more masters.
Ability to replicate to and from other database types, with transformation and filtering of replicated data.
This chapter explains how to install and configure Tungsten Replicator for MySQL. It is assumed that each Tungsten Replicator instance runs on a separate database node.
Tungsten Replicator is written in Java and requires Sun JDK 1.5 or above. Before installing the replicator, you should obtain and install the JDK from http://java.sun.com. Download and install the full JDK.
When the JDK is correctly installed you should be able to run java -version from the command line and see output like the following:
Java version "1.5.0_12" Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_12-b04) Java HotSpot(TM) Client VM (build 1.5.0_12-b04, mixed mode, sharing)
RedHat Linux and distributions derived from them may include the GNU Java, which has very different command line flags and behaves differently from Sun JDK versions. This version of Java can cause serious confusion if it somehow gets in the execution path. We recommend you remove it unless there is a compelling reason for it to be present on your hosts.
We recommend setting up a non-privileged host account to run Tungsten Replicator.
This account is called
the remainder of the documentation; however you may choose any name.
continuent account must be able to
read the MySQL binlog directory. On Linux and Solaris systems,
you may accomplish this by adding the
account to the
mysql group, which permits
read access to MySQL database files.
In addition, the Tungsten Replicator release directory must be owned by the
Prepare the database nodes as follows:
Install MySQL on the database nodes.
my.cnf configuration file
to enable binlogging and to disable logging of the
tungsten database. Use, for example,
my.cnf configuration file below:
[mysqld] # Master replication settings. log-bin=mysql-bin server-id=1 max_allowed_packet=16m
Start (or restart) MySQL so that the new settings take effect.
Create a Tungsten Replicator database user and a corresponding
database. By default this user is named
The following example shows a typical setup.
mysql> grant all on *.* to tungsten@'localhost' identified by 'secret' with grant option; mysql> create database tungsten;
To install Tungsten Replicator, proceed as follows:
Login with the
continuent account use to run
the Tungsten Replicator. This will ensure that all files are owned by
the correct login. You must also use this account to start the
Copy the distribution archive to the database nodes and unpack at the location of your choice. In the following code examples, we will use this location as the default directory.
On Linux, Solaris, MacOS X, and other Unix variants we recommend
installing in directory
Windows, use for example the
If you use Windows and cannot unpack the .zip distribution archive, try installing another file compression program, such as 7-zip. You can also use the jar program distributed with the Java JDK.
Configure Tungsten Replicator instances.
In the unpacked distribution,
cd to the
and copy file
replicator.properties. Here are sample
commands for Linux and Solaris.
cd conf cp replicator.properties.mysql replicator.properties chmod 700 replicator.properties
replicator.properties and set
properties required by the replicator. The following list
summarizes the main properties that need to change.
replicator.role must be set to
depending on whether the replicator is extracting
from a master database or applying on a slave.
replicator.auto_enable may optionally
be set to
true. If so set the replicator
will automatically go into the online state and begin
replicating data on startup.
replicator.source_id must be a unique name
for each Replicator. The host name is a good value if you
have one Tungsten Replicator per host.
replicator.thl.remote_uri must contain
the host name of the master. For the master replicator
this value can be
Properties beginning with
replicator.thl must have the
correct host, port, login, and password values
for the MySQL database.
The standard database name for storing THL data is
Similarly, properties beginning with
have the correct login, and password values for
the MySQL database.
The extractor binlog directory
must be the same name as
defined with the
log_bin option in the
my.cnf MySQL configuration file.
The sample value works for many Linux distributions.
Windows file names must either use forward slash characters (/) or double backslash characters (\\). Single backslash characters are interpreted as escape characters and will be removed leaving an invalid file name.
The built-in backup/restore capability is configured
For more on backup configuration see Section 3.2.2, “Backup Configuration”. You
should configure this at configuration time.
The following sample shows standard parameters for the
If you have followed these instructions so far and have MySQL installed, you should not need to make any other changes.
This chapter explains how to start Tungsten Replicator and set up a simple master/slave configuration.
In Linux, Solaris, and Mac OS X, the login used to
run Tungsten Replicator must have permissions to read
MySQL binlog files. Add the Tungsten Replicator login to the
Tungsten Replicator is run and configured by using shell scripts
residing in the
In Linux, Solaris, and Mac OS X, use the scripts below:
trepsvc is used to
start Tungsten Replicator as a service. This is the preferred way to
run the replicator process on Linux hosts.
trepstart is used to
start Tungsten Replicator.
trepctl is used to
control Tungsten Replicator.
In Windows, use the scripts below:
trepstart.bat is used to
start Tungsten Replicator.
trepctl.bat is used to
control Tungsten Replicator.
To start Tungsten Replicator in Linux, Solaris, or Mac OS X, proceed as follows:
Dump the master database and upload it to all slaves in the cluster. For example, issue the following command on the master:
--all-databases > mydump.sql
On the slave:
slave_host < mydump.sql
On Debian based distributions, you may have to copy the password
Slaves may also be provisioned using the built-in backup capability of Tungsten Replicator. See Section 3.4, “Provisioning New Slaves” for more information.
On master and all slaves, start the Tungsten Replicator process:
trepstart (or) trepsvc start
To start Tungsten Replicator in Windows, proceed as follows:
On master and all slaves, start the Tungsten Replicator process:
In a separate command window, start replication.
If you set the
This is all it takes to start Tungsten Replicator master and slaves. You should now have your master and slave Tungsten Replicators running and you can check the replication by making some data changes in the master database and verifying that the changes are reflected in the slave databases.
The use of trepctl/trepctl.bat command is documented in Chapter 6, Command Reference Guide. See also Section 6.2, “Running Tungsten Replicator as an Operating System Service”.
Tungsten Replicator has certain requirements specific to MySQL that must be met for replication to function correctly. This section provides an overview of administrative settings and other information that will ensure smooth operation. For more information please refer to MySQL server documentation at http://www.mysql.com.
Tungsten Replicator has minimal dependencies on MySQL server parameters
but does have a few required standard settings. Server parameters
are normally set in
my.cnf. Location of this
by OS platform and distribution; on Linux systems it is commonly
/etc/my.cnf. Here is an
example of the minimum my.cnf parameter setttings for Tungsten Replicator.
[mysqld] # Master replication settings. log-bin=mysql-bin server-id=1 max_allowed_packet=16m
The following table summarizes recommended usage of the foregoing parameters.
|Parameter Name||Usage Notes||Recommended Setting|
Prefix of MySQL binlog files. Must match the value of
||Any value is OK|
|server-id||Not currently used by Tungsten Replicator. However, we recommend using a different value per server in accordance with standard MySQL usage.||Follow MySQL recommendations|
|max_allowed_packet||Determines the largest transaction that can be stored in the Tungsten Replicator transaction history log. If using statement replication this is approximately the number of bytes in all statements of the largest transaction plus about 10%. If using row replication, it is approximately twice the total bytes of the row changes in the largest transaction. Insufficiently large values will cause replication to fail with a MySQL "Packet for query is too large" error.||16m|
Table 2.1. Minimum MySQL Server Parameters for Tungsten Replicator
Additional optional parameters are discussed succeeding sections. For all other parameters please follow standard MySQL recommendations.
MySQL releases from version 3.23 to 5.0 used statement replication only. MySQL 5.1 offers a choice between statement replication and row replication, which affects how SQL changes are recorded in the binlog. In general row replication is more flexible. However, row replication is newer and in some cases generates much large amounts of data in the binlog.
MySQL 5.1 users can select row vs. statement replication using the
binlog_format variable. The default value can be
for the server using the my.cnf file or directly using a
SET SQL command. The following example
illustrates how to enable row replication globally through SQL.
mysql> set @@global.binlog_format='ROW';
The following example shows how to enable statement replication for a single SQL session.
mysql> set @@session.binlog_format='STATEMENT';
Row replication and statement replication have trade-offs and special requirements in order to work properly. In general, these conditions apply to Tungsten Replicator as well, since they affect how SQL changes are written to the binlog. You should read these carefully in the MySQL product documentation.
MySQL permits variations in SQL syntax using the server
variable. These variations can lead to problems on slaves which must match
SQL Mode settings to apply updates correctly using JDBC, which is the Java
connectivity interface used by Tungsten Replicator
Tungsten Replicator allows users to set JDBC options required to handle non-strict
SQL mode settings using the
setting in the sample
replicator.properties.mysql file. These
settings work for all MySQL servers against which Tungsten Replicator is certified and
do not normally need to be changed. Be aware, however, that if you do alter these
options it may lead to errors as the slave may not be able to handle non-strict
SQL updates on the master.
MySQL binlogs accumulate until deleted or otherwise archived by users. With MySQL native replication this means you must wait until all slaves have received events from binlog files, at which point they may be deleted. With Tungsten Replicator binlog management is much simpler. As soon as binlog file contents have been completely stored in the Tungsten Replicator Transaction History Log (THL) you may delete the file.
The THL stores the binlog position in the THL catalog table named
history. You can query this table to find out the
current position of the THL with respect to the binlogs as follows:
mysql> select seqno, eventid from history order by seqno desc limit 1; +-------+-----------------+ | seqno | eventid | +-------+-----------------+ | 9402 | 000041:10428573 | +-------+-----------------+ 1 row in set (0.08 sec)
In this example, the THL has reached byte 10428573 of binlog file mysql-bin.000041. (Your prefix may differ; this is a standard form when running MySQL out of the box.) Files mysql-bin.000040 and before may be safely deleted.
Deleting a binlog that Tungsten Replicator is currentlying reading may cause unexpected failures or data inconsistencies.
MySQL binlogs are subject to problems in which the binlog content gets out of sync with the database content, typically following a crash. When this happens the binlog may contain events that were not committed in the database or the binlog may contain events that were not committed to the database. Tungsten Replicator in this case may read the corrupt files leading to data inconsistencies in slaves.
Binlog corruption problems can be minimized by the following:
Use MySQL 5.0 or greater with InnoDB rather than MyISAM and/or older releases. InnoDB performs a two-phase commit to the binlog that helps avoid inconsistencies.
Set innodb_flush_lot_at_trx_commit=2 and sync_binlog=1 with battery-backed cache to allow InnoDB to operate quickly but with minimal risk of lost writes to disk.
There are also many excellent recommendations in the MySQL community concerning this problem.
The basic operating principles of the Tungsten Replicator are explained in this chapter. Understanding these principles will allow you to set up and manage individual replicator instances.
A Tungsten Replicator instance is an operating system process that manages extracting or applying SQL updates. The Tungsten Replicator instance can function in either of two roles: as a master or as a slave. The master extracts SQL changes from a log and stores them for distribution as replication events. The slave receives replication events and applies them to a target, which is usually but not always a SQL database.
At any given time, the Tungsten Replicator instance usually is either a master or a slave. To perform a particular role the replicator must be correctly configured and must be in the correct state.
To configure a replicator to function as a master, the
replicator.thl.remote_uri property in the
replicator.properties file should be configured
to point to the localhost and provide a port on which it listens
as shown below.
To configure a replicator to function as a slave, the
replicator.thl.remote_uri property should be configured
to point to the host and port of the master transaction history log
(THL). If the master in the previous example were running on host
centos5d, then each slave would need to be
configured as shown below.
These settings must be correctly defined for master and slave to be able to communicate properly.
Slaves can also function as masters to other slaves. For example, you can set up a configuration in which a slave points to another slave which in turn points to the real master. The slave in the middle is known as a relay slave.
Tungsten Replicator states determine whether the instance functions
as a master, slave, or neither. Tungsten Replicator states follow
the simple state model shown below. The boxes are states
while the arrows are transitions. Transitions are triggered
by issuing commands through the
The replicator states and commands to change them are as follows.
START. Tungsten Replicator processes automatically
enter this state on start-up. They remain in this state until a
user issues a
OFFLINE. In this state, the Tungsten Replicator is
idle and neither applies nor extracts replication events. The
Tungsten Replicator enters this state after successful configuration or
following a successful
offline command. Users
can issue a
online command to start
replication. The Tungsten Replicator may also enter this state
automatically following a fatal replication error.
OFFLINE has four sub-states, which are listed
OFFLINE:BACKUP. This state indicates that Tungsten Replicator is performaing a database backup.
OFFLINE:ERROR. This state indicates that Tungsten Replicator is off-line following an error of some kind. The error message that caused this is easily accessible and is preserved until a successful administrative command causes it to exit the error state.
OFFLINE:NORMAL. This state indicates that Tungsten Replicator is off-line following a normal administrative operation or following a normal start-up.
OFFLINE:RESTORE. This state indicates that Tungsten Replicator is performing a database restore.
SYNCHRONIZING. The Tungsten Replicator enters
this state whenever it is catching up with a master. This normally
occurs when a user issues a
It also occurs in response to internal events such as when a slave
loses its connection to the master. The Tungsten Replicator transitions
automatically to the
ONLINE when it detects that
it is synchronized with the master and ready to begin applying
ONLINE has two sub-states, which are listed
ONLINE:SLAVE. In this state Tungsten Replicator
is synchronized with the master defined in the
replicator.thl.remote_uri property and actively
applying events or ready to apply events. Users can
promote the Tungsten Replicator to a master only by changing the
replicator.role property. This must be
done while the replicator is in the OFFLINE state.
ONLINE:MASTER. In this state Tungsten Replicator is
extracting events using a properly configured and functioning
extractor and delivering them to slaves through the port defined
Tungsten Replicator enters this state only if the
replicator.role property is set to
Tungsten Replicator can go offline from this state either in response to
shutdown command or due to an internal error.
Due to the asynchronous nature of the replication, there is some delay before changes made on the master node will be reflected in the slave nodes. The latency depends on hardware, communication network, and the SQL load profile and should be measured separately for each installation.
Replication catalogs are database tables that the Tungsten Replicator uses to keep track of replication events and manage the replication process. Catalogs are normally stored in the same database server for which the Tungsten Replicator is handling replication, as shown in the following diagram.
Replication catalogs include tables for storing replicated events, consistency checks, and any other information required by the Tungsten Replicator. The Tungsten Replicator creates them automatically at start-up time.
The catalog database is the database that
contains the catalog tables. Catalog tables may be stored in any
database. By convention they are stored in the
tungsten database. The catalog database should
be different from the database used to store application data and
should not be replicated. Replicating the catalog database will
quickly result in corrupt data on slaves as well as replication
The catalog database contents must be coordinated with the contents of tables that are currently being replicated. For example, the THL tables described in Section 3.1.6, “Transaction History Log” must match application data or replication will either miss SQL updates or try to apply them twice. When transferring a snapshot to provision a new Tungsten Replicator instance, the catalog tables are normally included.
The Tungsten Replicator catalog tables are described fully in Appendix A, Tungsten Replicator Catalogs.
Create the catalog database beforehand when first setting up the master. Depending on which DBMS you are using, a missing database may result in a replicator failure on start-up. Once the database is correctly set up it can be replicated to slaves along with application databases.
replicator.properties file contains
static configuration information for Tungsten Replicator. By
static we mean that the properties are read once
and do not change again unless the file changes and is reread when
the replicator restarts or receives a configure
command while in the OFFLINE state. The
replicator.properties file is located by
default in directory
conf and must be
properly configured for the replicator process to run.
Configuration parameters have a well-defined form that allows for global parameters that apply to the replicator as a whole as well as parameters that are specific to individual plug-ins. The rules are as follows.
All replication properties start with the prefix
replicator. Property names
consist of multiple parts separated by periods.
Global replicator properties have two parts. They are values that apply to the replicator process as a whole. Here are two examples.
Active plug-ins are selected by specialized global replicator properties. Each plug-in has a logical name that is used to find further properties for that plug-in. There are four such properties names as shown below:
# Defines the extractor logical name. replicator.extractor=mysql # Defines comma-separated list of 0 or more pre-storage filter names. replicator.pre_storage_filters=dbswitcher,casemapper # Defines comma-separated list of 0 or more post-storage filter names. replicator.pre_storage_filters=ddl-suppressor,column-suppressor # Defines the applier logical name. replicator.applier=mysql
Extractors and appliers have only a single logical name as only one of these can be active at any given time. There may be zero or more filters active, so these permit multiple logical names separated by commas or whitespace.
The THL is technically a plug-in but does not use the logical name format. THL parameters are discussed in Section 3.1.6, “Transaction History Log”.
There is a 3-part property name for each plug-in that defines the Java class that implements the plug-in. This name has the following syntax:
replicator.[plug-in type].[logical name]=[java class]
Here are some example plug-in class definitions.
replicator.extractor.mysql= \ com.continuent.tungsten.replicator.extractor.mysql.MySQLExtractor replicator.extractor.oracle= \ com.continuent.tungsten.replicator.extractor.oracle.OracleReplicationEventExtractor replicator.filter.dbswitcher= \ com.continuent.tungsten.replicator.filter.DbNameSwizzler replicator.applier.mysql= \ com.continuent.tungsten.replicator.applier.MySQLApplier
Tungsten Replicator plug-in classes must be in the class path. Replicator configuration will fail if a plug-in class cannot be found and instantiated.
There is a 4-part property name for each configuration property on the plug-in itself. Each of these defines a plug-in property value that will be set when the plug-in is instantiated. The names of these parameters have the following syntax:
replicator.[plug-in type].[plug-in name].[property]=[value]
Here are some example plug-in property definitions.
replicator.applier.mysql.host=localhost replicator.applier.mysql.user=tungsten replicator.applier.mysql.password=secret
Plug-in properties must exist on the plug-in. Replicator configuration will fail if any property cannot be found and set on the plug-in to which it applies.
Certain Tungsten Replicator properties may be set dynamically using JMX or through the trepctl program. Dynamic properties are remembered across restarts of the replicator process. Dynamic properties are designed to allow remote configuration changes necessary to manage Tungsten Replicator and perform critical operations like failover without physical access to the host on which the replicator process is executing.
Tungsten Replicator stores dynamic properties in file
conf/dynamic.properties. When starting or processing a configurecommand , the replicator process first reads static properties in
replicator.propertiesfollowed by dynamic properties. In this way, dynamic values override the original static values.
Dynamic properties are set using the trepctl set program, as illustrated in the following example. Note that the Tungsten Replicator must be in the OFFLINE state for changes to be accepted.
trepctl offline trepctl set -name replicator.thl.remote_uri -value thl://guppy/ trepctl online
Dynamic properties are cleared using the
trepctl clear. This command resets all
properties to their static values and deletes the
dynamic.properties file. Like the
set it may only run when the replicator process
is in the OFFLINE state.
trepctl offline trepctl clear trepctl online
There are two further ways to clear dynamic properties. You may
dynamic.properties file while the
replicator is stopped. You may also start the replicator using the
-clear. Both cause Tungsten Replicator to "forget"
current dynamic property values and start with original static
The trepctl show shows current values of all dynamically settable parameters. You may issue this command in any state.
The Transaction History Log or THL is a standard component of every Tungsten Replicator instance. The THL maintains a list of replication events in serial order, which means that if you apply them to a slave database it will result in the same contents as the master.
THL contents are stored in catalog tables. The catalog
location is defined by the
These properties are illustrated below.
# Parameters for configuring THL. These must be fully filled out. replicator.thl.storage=com.continuent.tungsten.replicator.thl.JdbcTHLStorage replicator.thl.url=jdbc:mysql://localhost/tungsten replicator.thl.user=tungsten replicator.thl.password=secret
The THL storage plug-in is generic and works the same way for
all supported database types. The storage implementation class is
as seen in the preceding example. The THL plug-in properties are
described full in Section C.1, “Transaction History Log (THL) Storage”.
Replication events are stored in the
Each event is characterized by a sequence number (column
seqno), which increases with each new transaction
that is added to the THL. Sequence number values are identical
across all THL copies, which means that if a master and slave have
the same maximum sequence number then the slave's THL is fully
up to date with the master.
The database or schema name for the THL and other catalog tables
is given by the
Each Tungsten Replicator instance must have a configured extractor to operate as a master. The extractor is not used when operating as a slave and may be omitted in this case.
The extractor is selected using the
replicator.extractor property. This property
must supply a logical name as shown in the following example.
# Extractor selection. Value must be a logical name. replicator.extractor=mysql
Class name and properties for the extractor itself are configured according to the rules for plug-in configuration provided in Section 3.1.4, “Static Properties and the replicator.properties File”. The following example shows extractor property definition for the MySQL extractor.
replicator.extractor.mysql= \ com.continuent.tungsten.replicator.extractor.mysql.MySQLExtractor replicator.extractor.mysql.user=tungsten replicator.extractor.mysql.password=secret
Extractor properties vary by extractor type. Extractors are documented fully in Section C.2, “Extractors”.
Each Tungsten Replicator instance must have a configured applier
to operate as a slave. The extractor is not used when
operating as a master but must be configured because all
replicators first start in the
The applier is selected using the
replicator.applier property. This
property must supply a logical name
as shown in the following example.
# Applier selection. Value must be a logical name. replicator.applier=mysql
Class name and properties for the applier are configured according to the rules for plug-in configuration provided in Section 3.1.4, “Static Properties and the replicator.properties File”. The following example shows property definition for the MySQL applier. As with all plug-ins, unused properties may be omitted. They will assume default values.
# MySQL applier properties. replicator.applier.mysql= \ com.continuent.tungsten.replicator.applier.MySQLApplier replicator.applier.mysql.host=localhost replicator.applier.mysql.port=4306 replicator.applier.mysql.user=tungsten replicator.applier.mysql.password=secret
Applier properties vary by applier type. Appliers are documented fully in Section C.4, “Appliers”.
Each Tungsten Replicator instance may have 0 or more filters. Filters can drop or transform SQL events, which is very handy for a wide variety of replication use cases.
Filters can be configured and used in two different ways. Pre-storage filters execute on the master after events are extracted and before they are stored in the THL. Post-storage filters execute on the slave after events are retrieved from the THL and before they are applied to a target database. Filter implementation is identical regardless of the role in which they function. The only difference is in configuration.
Filters are selected using properties that correspond to pre-storage or post-storage roles. Each property has a list of 0 or more logical names. Names can be separated by commas or whitespace characters (e.g., a blank space or tab). The following example demonstrates filter configuration.
# Pre-storage filter selection. Value must be one or more comma-separated # logical filter names. replicator.prefilter=logger # Post-storage filter selection. Value must be one or more comma-separated # logical filter names. replicator.postfilter=logger,dbtransform
Class name and properties for each filter are set
according to the rules for plug-in configuration
provided in Section 3.1.4, “Static Properties and the replicator.properties File”. Note
that all filters use the same
prefix regardless of their actual role.
# Logging filter. replicator.filter.logger= \ com.continuent.tungsten.replicator.filter.LoggingFilter # Database transform filter. replicator.filter.dbtransform= \ com.continuent.tungsten.replicator.filter.DatabaseTransformFilter replicator.filter.dbtransform.from_regex=foo replicator.filter.dbtransform.to_regex=bar
Filter properties vary by filter type. Filters are documented fully in Section C.3, “Filters”.
Tungsten Replicator provides a built-in facility to backup and restore databases using simple commands. The backup facility uses two specialized types of plugins.
Backup agents implement the backup and restore operations using commands that are specific for a particular database type. The result of a backup is a file that can be stored. A restore operation a backup file and restores it. There may be multiple backup agents for a particular file type.
Storage agents manage backup files: storing, retrieving, and purging files that are no longer needed. There is a small number of storage agents that serve the needs of many different kind of backups. The default storage mechanism is file-based storage on a shared disk that is visible to all hosts using Tungsten Replicator.
Backups are an optional feature of Tungsten Replicator and do not need to be configured if you do not need them. If you do not enable at least one backup agent and at least one storage agent, the backup and restore commands will be disabled.
Backup configuration is controlled by
replication.properties. Backup and storage
agent plug-ins follow the same general configuration
pattern described in Section 3.1.4, “Static Properties and the replicator.properties File”.
Here are the main steps to observe when setting configuration values.
List one or more backup agents using the
and configure the corresponding agent properties. Backup
agent settings are specific to the type of database and
Select a default backup agent using the
This agent is used if you do not specify one on the backup
In like fashion select one or more storage agents using
configure properties, and select a default using the
All storage agents have a retention
property. This determines the maximum number of backups
retained until old backups are deleted.
When testing replication (for example before deploying into production) it is often useful to define two storage agents that use different locations. The default agent is used for for normal backup and restore. The other agent can hold a base backup to restore system state quickly at the beginning of tests.
Backup properties are not dynamic. You must reread
Before running a backup Tungsten Replicator must be in the OFFLINE state. Depending on the backup agent used, the database may also need to be fully quiesced with no active transactions.
The trepctl has options to run backup and restore tasks. The following example runs a backup using the default backup agent, stores it using the default storage agent, and prints the URI when the backup completes.
$ trepctl backup Backup completed successfully; \ URI=storage://file-system/store-0000000013.properties State: OFFLINE:NORMAL
The following example selects specific backup and storage agents and waits only 15 minutes before returning. If the backup is not done before the timeout expires the URI is not printed. Instead, you must check the log to find the backup URI.
$ trepctl backup -backup mysqldump -storage fs -limit 900 Backup is pending; check log for status State: OFFLINE:BACKUP
To restore data use the trepctl restore command. As with backups, Tungsten Replicator must be in the OFFLINE state to run a restore command.
The following example runs a backup using the latest backup stored with the default storage agent. Tungsten Replicator automatically determines the backup agent to use by reading the backup metadata and calling the correct agent.
$ trepctl restore Restore completed successfully State: OFFLINE:NORMAL
Similarly, the following example restores a specific backup by specifying its URI. We also wait up to 20 minutes before returning. As with backups, if the restore is not done before the timeout expires you will not know if the restore succeeded. Instead, you must check the replicator log.
$ trepctl restore -uri storage://file-system/store-0000000013.properties -limit 1200 Restore is pending; check log for status State: OFFLINE:RESTORE
|Both backup and restore operations return Tungsten Replicator to the OFFLINE:NORMAL if they succeed. In the event of an error Tungsten Replicator goes into the OFFLINE:ERROR state, which means that failures are easy to detect.|
Storage agents store three types of information. The first is the backup files themselves. The second is metadata describing each file, for example its size, the date on which it was generated, and the backup agent that produced it. Finally, there is information used to manage the store itself, such as the index number to assign the next backup.
Each storage agent may have a slightly different form of organization. The default file system storage agent provided by Tungsten Replicator stores backups and metadata on the file system. The following listing shows typical storage contents.
storage.index store-0000000012-mysqldump-6450242716624177955sql store-0000000012.properties store-0000000013-mysqldump-8628772972175094324sql store-0000000013.properties store-0000000014-mysqldump-6633515753745331096sql store-0000000014.properties
storage.index file has the index used
to generate new backup numbers. Each backup consists of the
backup file itself plus a properties file with metadata. You can
look at but should not edit property files.
You can purge backups by deleting the backup file and the matching
properties file. You can re-initialize storage fully by removing all
|You can increase the file storage availability by backing it up regularly to another file system using tools like rsync.|
Master failover is the process of switching from an existing master to a new one. Failover is not automatic. Instead, users must execute a series of commands to configure a new master and point slaves to the master. The following procedure describes how to perform a planned failover.
Commands shown in this section correspond to Unix conventions. Windows commands are analogous but use the Windows scripts.
Quiesce master database applications so that there are no transactions in progress. Use flush to ensure the master database state is synchronized with the log and collect the last sequence number in the transaction history log. Wait for the slave that is being promoted to master to catch up to that sequence number.
$ trepctl -host prodmast1 flush Master log is synchronized with database at sequence number: 376 State: ONLINE:MASTER Seqno Range: 0 -> 376 $ trepctl -host prodslave4 wait -applied 376
Send master and slave to the OFFLINE state.
trepctl -host prodmast1 offline trepctl -host prodslave4 offline
Configure the old slave to be a master by setting its remote THL URI to point to the same host.
trepctl -host prodslave4 set -name replicator.role -value master trepctl -host prodslave4 set -name replicator.thl.remote_uri -value thl://prodslave4/ trepctl -host prodslave4 online trepctl -host prodslave4 wait -state ONLINE:MASTER
Once the new master is up and running, configure the old master to point to it as shown below.
trepctl -host prodslave4 set -name replicator.role -value slave trepctl -host prodmast1 set -name replicator.thl.remote_uri -value thl://prodslave4/ trepctl -host prodmast1 online
On the remaining slaves, run the commands below.
trepctl offline trepctl set -name replicator.thl.remote_uri -value thl://prodslave4/ trepctl online
If you are switching masters due to a failover, do not enable the old master as a slave, as this can lead to data inconsistency errors due to transactions on the master that were not replicated to any slave. See Section 5.8, “Dealing with a Failed Master” for more information. A future release of Tungsten Replicator will provide mechanisms to identify automatically when a master can safely be reused as a slave.
You can add new slaves to a replication configuration at any time. The provisioning procedure has the following steps.
Install and configure. Install software and configure the
replicator.properties file. Ensure that
the database to which you are replicating is running and
has proper accounts set up.
Synchronize slave state. Load the slave with an initial copy of data that includes Tungsten Replicator catalogs. The initial copy must be transactionally consistent. This means that the data in the copy must match the sequence numbers recorded in the catalog tables, so that when replication starts it will start applying events at a point that correctly matches current data. The copy must be new enough so that the master can start replication at the slave's current position in the THL.
Enable replication. Issue commands to configure the slave
and bring it into the
The first time you provision a slave it will be necessary to stop the master completely in order to create a consistent data dump. However, once you have at least one slave available you can use a slave for provisioning instead. We call this kind of slave a donor slave. Donor slaves eliminate the need to stop the master.
The following diagram illustrates use of a donor slave to provision another slave without stopping the master.
Synchronizing data requires database-specific dump and load commands.
The following procedure is generic and assumes that you have backup
properly configured as described in Section 3.2, “Backup and Restore”.
You can also substitute a backup mechanism of your own. The host
Command the donor slave to the OFFLINE state, back it up, and bring it back on line again. If you have properly configured backups this looks like the following:
trepctl -host donor offline trepctl -host donor backup trepctl -host donor online
If backups are not configured, you would substitute appropriate database dump commands for trepctl backup. Once this step is complete the donor is not needed further.
For this procedure to work properly in all cases you must ensure that the backup is transactionally consistent, i.e., that there are no writes going to the database. This is not normally an issue with slaves but requires quiescing applications if the donor is a master.
Provision the new slave by starting and loading the backup. Again if backups are configured this is a relatively simple procedure as shown below.
trepstart trepctl -host recipient restore trepctl -host recipient online
If backups are not configured, you would substitute appropriate database restore commands. These must run before you start the replicator and bring it online.
The Tungsten Replicator does not automatically purge records from the
history catalog table, which comprises the
THL. This table therefore grows without bound unless manually truncated.
When purging THL records, it is important to avoid deleting rows corresponding to SQL events that have yet to be replicated to one or more slaves. This applies obviously to the current master database. However, it also applies to slaves used for failover. It is important to avoid a situation where a slave is promoted to master but does not have events required by other slaves that happen to be lagging behind.
The following procedure describes a safe procedure for checking and truncating THL records.
Find the current THL high water mark
on each slave database. The high water mark is the
highest event sequence number in each THL. Login
to each slave and issue the following
$ trepctl -host centos5d -port 10000 State: SLAVE Seqno Range: 2036 -> 6461
Note: you can also use thl info to find the same information.
Select the lowest high water mark value. For example, suppose there are two slaves and one has a high water mark of 6461 and the other has a high water mark of 7730. 6461 is the lowest value so we select this.
Truncate the master and slave THL tables to eliminate all records with sequence numbers less than the current low value. You can do this as follows using the thl utility.
$ bin/thl purge -high 6460 WARNING: The purge command will break replication if you delete all events or delete events that have not reached all slaves. Are you sure you wish to delete 101 events [y/N]? y Deleting events where SEQ# <=6460 Deleted events: 4424
You can also perform this operation directly using SQL as shown below.
DELETE from history WHERE seqno < 6461;
Do not delete all records in the THL or replication restart may fail. You should always leave at least one record.
Tungsten Replicator can compute checksums on SQL events before they are loaded into the THL and again whenever they are applied to a target database. Checksums protect against random errors due to data corruption.
Checksums are controlled by properties in the replicator.properties file. The following sample shows these properties.
# Event checksum algorithm. Possible values are 'SHA' or 'MD5' or # empty for no checksums replicator.event.checksum=md5 # How to react on consistency check failure Possible values are # 'stop' or 'warn'. replicator.applier.consistency_policy=stop
Checksum algorithms must be consistent across master and slave replicators. Also, if no value is selected, checksums are neither computed nor checked.
When checksum checking is in effect, Tungsten Replicator has two
possible responses to a failed checksum event based on the
consistency policy. If the policy is
replicator writes a warning to the replicator log. If the policy
stop, Tungsten Replicator issues an automatic
shutdown that puts the replicator into the
When Tungsten Replicator stops due to a checksum failure, it is very
important to track down the cause of the failure if this can be
done. It is possible to continue by setting the consistency
replicator.properties. This allows you to get
past a failure and continue replication.
Checksums are highly recommended for production settings as they prevent inscrutable data inconsistencies due to random data corruption during transport and storage, software failures, or administrative errors.
Tungsten Replicator offers a built-in consistency checking facility that makes it easy to run a consistency check on part or all of a table. The consistency check runs a checksum on the table on the master and then repeats the same checksum on the slave. If the checksum fails, it can generate either a warning or an error that sends the replicator to the OFFLINE:ERROR state.
Table consistency checks work by generating a special consistency check event that is replicated between the master and slave. When the event arrives on the slave Tungsten Replicator recomputes the checksum and compares the results with the previous values for the master. The check executes in serial order with other SQL updates, which means that the slaves should be in the same state as the master when the event runs. As a result, it is not necessary to stop the slave; also the comparison can be incremental.
Tables must have a primary key defined for consistency checks to work. Tungsten Replicator uses the primary key to order rows in order to generate consistent results on successive invocations. Checksums on tables without a primary key will fail.
To run a consistency check over a table, use the
trepctl command as shown in the following
example. In this case we are checking consistency on table
orders in database
trepctl check sample.orders
To check a part of the table, use the -limit flag. The limit parameters are offset, which the row on which to start, and the limit, which is the number of rows to check. The following example shows a check that starts on the 100th row and proceeds for 100 rows. (Row numbering starts at 0, not 1.)
trepctl check sample.orders -limit 99,100
There is also a -method flag, but for the time being the only value that is accepted is 'md5'. This flag is therefore typically omitted.
Tungsten Replicator provides properties to control the behavior of consistency checks. The following example shows these properties.
# How to react on consistency check failure. Possible values are 'stop' or 'warn'. replicator.applier.consistency_policy=stop # Should consistency check be sensitive to column names and/or types? Settings # on a slave must be identical to master's. Values are 'true' or 'false'. replicator.applier.consistency_column_names=true replicator.applier.consistency_column_types=true
consistency_policy is the same property that
controls the response to a failed event checksum, as described in
Section 3.6, “Event Checksums”. There are also two additional
parameters that are helpful for consistency checking across different
consistency_column_names - If true, the
consistency check ignores differences in the column name case.
For example, a column named "address" and a column named "ADDRESS"
will be considered equivalent.
consistency_column_types - If true, the
consistency check ignores differences in column types. For
example, this would would allow integer and long values to
Tungsten Replicator implements capable management and monitoring interfaces through JMX. JMX is a standard management API that allows Java processes to expose monitoring data, management commands, and notifications of state changes to external clients. For more information on JMX itself look at the Sun documentation, for example http://java.sun.com/javase/technologies/core/mntr-mgmt/javamanagement/.
Tungsten Replicator is designed to be a relatively simple network service. It does not attempt to make decisions about database state or failover, nor does it implement overall management procedures. Instead, Tungsten Replicator exposes JMX MBeans, which serve as an interface for other management tools. These tools use information from the MBean interfaces as well as operations to implement higher-level management procedures.
The following diagram depicts the management architecture.
Tungsten Replicator MBean interfaces expose only standard Java types. Clients
do not need to include replicator libraries to invoke and use the
replicator MBeans. This means that generic management tools like
jconsole can easily connect to and manage
Tungsten Replicator separates management and monitoring statistics into separate MBean interfaces.
Tungsten Replicator JMX interfaces often change, though we make every effort to ensure upward compatibility for clients. The Javadoc pages in binary builds or the Java interfaces in source code are the final reference for MBean interface behavior.
The ReplicatorManagerMBean is the principle management interface for Tungsten Replicator. Management operations like going online, rereading configuration, and stopping the replicator use this interface.
Attributes - Values include the
current state of the replicator (e.g., OFFLINE:NORMAL)
and pending error conditions, if any.
Operations - Operations correspond
to all management operations that affect or query
Tungsten Replicator state.
Notifications - Notifications
include state changes and error conditions.
For detailed documentation of ReplicatorManagerMBean
interfaces, please refer to the Javadoc page for interface
The ReplicatorMonitorMBean exposes replicator statistics. Management operations that examine Tungsten Replicator performance and throughput use this interface.
Attributes - Values include key statistics
and monitoring data for extraction, receipt, and application
of SQL events. Note that replicators in the MASTER state will
update extraction statistics, where as replicators in the SLAVE
state will update event receipt and application statistics. To
get a full picture of statistics you need to look at both
manager and slave replicators.
Operations - ReplicatorMonitorMBean has
a single operation to reset monitoring counters.
Notifications - ReplicatorMonitorMBean does
not currently generate notifications.
For detailed documentation of ReplicatorMonitorMBean
interfaces, please refer to the Javadoc page for interface
Any client that can manipulate JMX interfaces can manage and monitor Tungsten Replicator.
trepctl client distributed with the
replicator is a JMX client. It uses JMX interfaces to obtain
replicator data and to implement management operations. If you
can see an attribute or perform an operation via
trepctl you can do the same using JMX directly.
The Sun JDK distributes
jconsole, a very
flexible JMX client. All Tungsten Replicator attributes,
operations, and notifications are accessible through
also shows extensive Java Virtual Machine monitoring data, which
makes it an invaluable tool for monitoring.
The easiest way to connect with
to start jconsole on the same host as the replicator. You should
see the Tungsten Replicator listed in the local connections when
jconsole starts up. You can also connect
to remote replicator processes by specifying the host name and port.
It is relatively easy to write a custom JMX management agent. Consult the Sun JMX documentation for advice on writing your own client. When you start to write code itself, look at class com.continuent.tungsten.replicator.ReplicatorManagerCtrl in the Tungsten Replicator source code.
Tungsten Replicator depends on utility code from the Tungsten Commons project to connect to JMX. This code is downloadable from the Continuent Community Site.
This chapter provides guidance on overall strategy for using Tungsten Replicator in your environment. The techniques described here assume familiarity with Tungsten Replicator as described in Section 3.1, “The Tungsten Replicator Process”. Every system designer or DBA who uses replication should be familiar with these approaches.
|More detailed descriptions will be added in a future version of this manual.|
Automatic failover on master failure is a standard technique to ensure database high availability. Automation allows you to elect a new master whenever your current master fails without having to wait for a human to make a decision. It is key to ensuring that databases remain highly available at all times.
The simplest way to implement automated failover is using a master/slave pair with a shared virtual IP address (VIP). The current master host "owns" the virtual IP. Client applications access the database using the virtual IP address rather than connecting directly. This approach makes it easy to shift clients from one database host to another in the event of a failover.
Failover itself is controlled by an external program known
as a "cluster manager." The cluster manager is responsible
for deciding when it is time to do a failover. For a variety
of reasons Tungsten Replicator cannot make this decision for itself.
There are many programs that can function as a cluster manager.
One of our favorites is
is available from http://www.linux-ha.org/Heartbeat.
Heartbeat is easy to set up and quite reliable.
The following diagram illustrates failover using a master/slave pair. When Heartbeat on the slave detects that the master host is down or not responding, it takes over the VIP and promotes the local slave to be master using the procedure described in Section 3.3, “Master Failover”.
Master/slave pairs are a good choice for basic availability since there is no question which slave should be promoted. If there are multiple slaves you must check the slave positions before failing over as described in Section 5.8, “Dealing with a Failed Master”. For a seat-of-the-pants solution you can write a watch-dog process that monitors the master and then queries each slave to get its position. You then failover using the most advanced slave.
Promoting one of several slaves is actually a complex problem with special cases that can be quite subtle and hence difficult to handle correctly. Continuent is implementing a management framework designed to solve this problem using group communications. Check our community website for more information.
Whatever solution you choose for handling automated failure, be sure to test it thoroughly and regularly. This is the only way to ensure that failover will work correctly when you need it.
Tungsten Replicator has a number of features that make it well-suited for performing database server upgrades as well as application migrations. The basic idea is to upgrade a slave database that is in the OFFLINE state, let it catch up with any missed updates, and then promote it to master. This approach solves a number of difficult problems for DBAs.
If the upgrade fails for any reason, you can just discard the slave without any affect on the master.
Once the slave is fully upgraded and catches up with missed updates, the upgrade outage time is reduced to the time to do a failover.
You can make the old master a slave, which means that you can keep it around as a fallback in case the newly upgraded database has problems. In this case, you just fail back to the old slave.
Tungsten Replicator has a simple procedure for provisioning slaves and performing clean failover. It also can replicator from newer to older database versions such as MySQL 5.0 to 4.1. Finally, Filters allow users to replicate even back to databases that have schema changes. You can construct filters to drop new columns or even entire updates that will not work when replicating back to an old version.
The setup for upgrade is almost identical to automatic failover, except that a cluster manager is not required.
The basic upgrade procedure is described below.
Set up a master/slave pair and ensure replication is working correctly.
Take the slave
OFFLINE and perform the upgrade.
If upgrade fails discard the slave and start over.
Bring the slave back online and allow it to catch up with the
master. Once the slave is in the
it is fully caught up.
Failover from the existing master to the slave.
Bring the old master up as a slave.
You can upgrade the old master at leisure or simply discard it if you do not need it after the upgrade.
If there are also schema changes you may have to insert filters on the slave to alter or discard SQL events that go back to the old master. Depending on the extent of the changes, it may not be practical to replicate back to the master.
The simplest ways to handle the switch-over of client applications range from updating their connection information to using a VIP as described in Section 4.1, “Implementing Automated Failover”. Both of these techniques require applications either to stop or handle broken connections when the failover occurs.
You can also implement fully seamless upgrades though these are much harder and may place constraints on your application design. One technique is to build logic into applications so that they handle a database server reboot transparently. A better approach is to insert a middleware layer like the Tungsten Connector which provides an abstraction layer and makes database restart transparent. Continuent is actively working on solutions that enable seamless failover for master/slave configurations.
Avoid mixing too many things in a single upgrade. It is generally better to proceed by single steps that change only one thing at a time.
Always test upgrades carefully on real data! Using a slave for the upgrade allows you to test repeatedly on production data. It is great to have a backout for a failed upgrade but even better if you don't have to use it.
Tungsten Replicator supports replication between different database types as well as between databases and non-database entities like applications or even flat files. This section provides you with some ideas about how to set up different heterogeneous replication use cases.
Tungsten Replicator can replicate between different database types, since SQL events are essentially generic after they have been extracted. To replicate between different databases, set up a replicator for each server as if you were replicating between databases of the same type. The "from" database must run in the master role, while the "to" database acts as a slave.
For example, you can set up replication between MySQL and Oracle as follows. Install and configure Tungsten Replicator for the MySQL database and run it in the master role. Install and configure Tungsten Replicator for the MySQL database and run it in the slave role.
When replicating between different database types you must be careful what is being replicated. SQL INSERT, UPDATE, and DELETE statements tend to be quite portable. So, for example, you can replicate from a MySQL 5.0 instance using statement replication to an Oracle instance. Beware, however, that SQL functions as well as binary data types tend to be relatively non-portable. Also, DDL statements beyond the simplest CREATE TABLE expressions are rarely at all portable.
SQL portability issues can be solved in at least two ways.
Use row replication. Row replication moves data in a generic form that avoids SQL dialect dependencies.
Use filters. Implement filters to drop or transform SQL that causes problems. Filter implementation is described in Chapter 7, Extending the Tungsten Replicator System.
The current version of the replicator has some temporary limits that affect how easily it can replicate between different database types. The most significant of these is that there is no fully generic JDBC applier that works with any database type. Appliers are currently specific to one database. This limit and others will be removed shortly.
Replication to and from non-databases is not supported in the off-the-shelf replicator. However, such replication is quite easy to implement for anyone with a reasonable understanding of Java and the willingness to write a replicator plug-in, as described in Chapter 7, Extending the Tungsten Replicator System.
A simple example of non-database replication is to morph database changes into XML documents, one per update. To do this you would implement an applier plug-in that takes SQL updates and converts them into your preferred XML format. This applier just needs to be able to read the SQL event data structures and generate XML tags.
If you use row replication, the applier will be much easier to write. If using statement replication, you may need to parse SQL text, which can be a non-trivial undertaking.
When converting SQL events into XML you might wish to convert only certain changes to XML. You could build logic into your applier to skip events that do not interest you. However, a better way is to write a filter that drops uninteresting events. This approach results in two components that are simpler and can also be used independently.
Tungsten Replicator supports the ability to create large numbers of replica databases from one or two slaves. This is a very convenient solution for organizations that have highly variable database load over time and may need to scale up to high number of reads very rapidly.
One simple way to scale reads rapidly is to maintain a permanent donor slave that is just used for reading read-only copies. When your read load increases and you need more replicas, you can add virtual machines and provision new slaves on them from the donor as described in Section 3.4, “Provisioning New Slaves”. As soon as the replicas are no longer needed, you can just deactivate them.
Cloud environments like Amazon add some interesting twists to slave scaling. For example, Amazon Elastic Block Store supports point-in-time snapshots. You can use this feature to provision slave databases rapidly at the file system level without using database dump and restore, which tends to be slow and resource intensive.
A future revision of this manual will expand on using replication for fast scaling in virtual and cloud environments.
Replication provides a convenient way to extract data rapidly and efficiently from production databases and load them into reporting databases. Tungsten Replicator permits multiple masters to load data into a single slave server. This allows you to collect data from geographically dispersed databases into a single location.
A future revision of this manual will expand on how to load data warehouses and feed SQL events to ETL tools.
This chapter deals with Tungsten Replicator troubleshooting as well as tuning.
Tungsten Replicator uses an internal state machine that includes special
handling for errors. In the case of an unrecoverable error, the
replicator process automatically switches into the
OFFLINE:ERRORstate. The replicator preserves
information about the error that caused the problem until a user
fixes the problem and enters a command to bring the replicator
back online again.
The following command illustrates error handling on a slave if you attempt bring it on-line when the underlying database is not up.
$ trepctl online State: OFFLINE:ERROR Error: Replicator service start-up failed Exception Message: com.continuent.tungsten.replicator.ReplicatorException: com.mysql.jdbc.CommunicationsException: Communications link failure Last packet sent to the server was 1 ms ago. Seqno Range: -1 -> -1
If you restart the database server, you can then repeat the command as shown in the example below.
$ trepctl online State: SYNCHRONIZING Seqno Range: 101 -> 6461 $ trepctl State: SLAVE Seqno Range: 101 -> 6461
Note that error information is automatically cleared once a command brings the replicator out of the OFFLINE:ERROR sub-state.
Java virtual machines may run out of memory when processing large qualities of data. This results in a log message like the following:
2009-01-12 08:27:26,512 ERROR tungsten.replicator.ReplicatorManager Received error notification, shutting down services: Applier thread failure at event sequence number 25 java.lang.OutOfMemoryError: Java heap space
If this occurs you should raise memory by adding JVM options to set a higher heap size allocation. Java provides the following option to set the heap and stack frame sizes:
java.lang.StackOverflowError. 1024 is a good starting value if you see stack overflow problems.
The following example shows how to set the heap on Linux to 512M prior to starting the replicator from the command line.
export JVM_OPTIONS=-Xmx512m trep_start.sh
If you run Tungsten Replicator as a service, memory settings are in file
conf/wrapper.conf. You can update these as
shown in the following example.
# Maximum Java Heap Size (in MB) wrapper.java.maxmemory=512
Java memory tuning is a complex subject. For further information consult Java JDK documentation.
There are a number of performance trade-offs associated with replication. Tungsten Replicator includes several property settings that can help improve performance in specific situations.
Deserializing replicated events in order to apply them is one of the
most CPU-intensive operations that occurs in the replication process.
Tungsten Replicator supports a cache on the slave side that can hold events
as they are received from the master. This cache eliminates a
deserialization operation as events are read back out of the THL prior
to application. You can control the cache size using the
replicator.thl.cache_size property as shown
in the following example.
The cache consumes heap memory in the Tungsten Replicator slave process. You can assume that the additional memory required is roughly 2x the size of the SQL events held in the cache and adjust slave JVM heap size accordingly upwards.
Slave replicators by default apply events one as single transactions
and commit at the end of each one. As SQL commit is a
relatively expensive operation, you can improve performance
by enabling apply-side block commit. This feature applies events
up to a preset limit or until the apply-side cache is empty, then
commits. You can control the block commit limit with the
as shown in the following example.
Block commit does not consume additional memory but may raise latency for individual events as they must commit in a group. Values of 10 to 100 serve to amortize the commit cost nicely. Note that each event is actually a database transaction. Block commit therefore has less effect if the master is being updated through very large transactions.
Master connections normally write each event to the slave as an independently serialized object that has no references to previous events. It is possible to improve transfer speed by changing the connection reset period to a value greater than one, which allows events to contain references to previously written objects in order to avoid writing them again with each new event
The following example shows how to raise the reset period using the
Raising the reset period will increase the amount of memory consumed by the master. You can assume that the additional memory required is roughly 2x the average SQL event size times the reset period and adjust master JVM heap accordingly.
SQL may fail on the slave for reasons unrelated to replication. For example, dropping a database that exists on the master but not on the slave will result in a failure on the slave side. There are two ways to skip over the failed statement.
The first method is to use the thl utility to find the failing SQL statement and skip over it. This is the recommended procedure for dealing with most failures. Here is the procedure.
Find the sequence number of the failed statement. The slave replicator will typically print a message like the following if you run trepctl after a failure.
For output purposes, we use backslashes (\) to spread out long lines across several lines.
$ trepctl State: OFFLINE:ERROR Error: Applier thread failure at event sequence number 5 Exception Message: java.sql.BatchUpdateException: error occurred during\ batching: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself Seqno Range: -1 -> -1
Examine the failed event using thl list. This is not required but highly recommended to understand exactly which statement may have failed. In this example you can display the failed event using thl list -seqno 5.
Use the thl skip command to skip the event.
$ thl skip -seqno 5 WARNING: Skipping events may cause data inconsistencies with the master database. Are you sure you wish to skip 1 events [y/N]? y Skipping events where SEQ# = 5 Marked events as skipped: 1
Bring the slave back on-line using trepctl online . The slave will continue at the next event following the skipped event.
You can also configure slave replicators to skip over failed statements automatically. To do this, set the applier failure policy in replicator.properties as shown below.
It is your responsibility to ensure that there are no problems with data consisency due to skipped events. You should always ensure you understand the root cause for any applier failure. If you set the applier policy to 'warn', be aware that it applies to any event failure. This removes a fundamental check on replication--think carefully before using the 'warn' setting.
Replicator failures may occur if the catalog tables used by the Transaction History Log (THL) run out of space. This leads to a message like the following:
State: OFFLINE:ERROR Error: THL thread failed Exception Message: java.sql.SQLException: The table 'history' is full Seqno Range: -1 -> -1
The cure is to free up disk space on the affected file system. You can restart Tungsten Replicator once disk space is available again. To avoid problems in the first place, check disk space levels and purge the THL regularly using the thl utility.
It may not help to purge the THL after a disk full condition. Database engines do not necessarily return space from deleted rows immediately to the file system. Your best bet is to delete space or extend the file system if you can using a logical volume manager.
It is possible for a SQL update to execute on the master and fail on the slave. When the SQL update fails on the slave, the slave will automatically switch to the OFFLINE slave. When you see an update fail in this way, you should always check to ensure that the master and slave databases are consistent.
Tungsten Replicator requires databases master and slave databases to be in identical states when replicating. If not, SQL updates that work on the master may produce different results or fail outright in the slave. This assumption is fundamental to the design of data replication.
Data inconsistencies should be avoided by ensuring slaves are correctly provisioned. You should make slaves read-only to prevent accidental updates. Finally, you can use the data consistency checking described in Section 3.7, “Consistency Checking” to identify data consistency problems that arise after provisioning. In this release of Tungsten Replicator data consistency problems must be corrected by reprovisioning or direct reconciliation of data between databases.
If one of the Tungsten slave nodes fails, the master node and other slaves will keep on working without interruption. In other words, the cluster is functional despite the slave problem.
To recover from a slave node failure, it is essential to first analyze the reason for the node failure and fix any problem(s) that can prevent future slave operation.
There are two ways to recover a failed slave. In the event of a severe failure that causes the database to lose data, the simplest and best procedure is to re-provision the slave from scratch using the procedure described in Section 3.4, “Provisioning New Slaves”.
In most other cases it is sufficient to restart the Tungsten Replicator as well as the slave database, then bring the replicator to the ONLINE state. It will pick up replication where it left off.
If you observe replication errors when restarting the slave after a crash, you should re-provision the slave from scratch. Forcing replication to continue in these circumstances can lead to invalid data on the slave.
This chapter shows a simple failover scenario, where a master fails and must be replaced due to either a database server failure or a failure of the master replicator process.
To recover, proceed as follows:
Ensure the master is stopped. This may be unnecessary if it has crashed but in some cases may require killing the replicator process so that slave replicators lose their connection to the master.
Check the status on each slave by issuing the command below:
Wait until all slaves reach the SYNCHRONIZING state. Slaves automatically go into this state when they cannot contact the master.
Check the seqno ranges from all slaves by issuing the command below:
Select the slave with the greater max seqno as the new master.
From this point on, follow the master failover procedure described in Section 3.3, “Master Failover” to promote the slave with the highest sequence number to be the new master and redirect slaves to that master.
After you have failed over to another master, you should repair the failed master. The first step is to analyze and correct any problems that led to the failure. Once this is done you can recover the master.
The standard way to recover a master is to provision it as a slave using the procedure described in Section 3.4, “Provisioning New Slaves” and then perform another master failover.
Making a failed master a slave without first re-provisioning can lead to data inconsistencies if the master has unreplicated changes that were lost when failing over to a slave. Re-provisioning synchronizes database contents fully with the current master and avoids possible data problems.
If the database fails, the node is considered as failed and
the procedure for a failed master or failed slave is used,
accordingly. You will notice the database failure as the
replicator process will go into the
Sometimes it is necessary to reset the replicator from scratch in order to get past an error or set up quickly for testing. The following procedure shows how to initialize the system quickly.
Clear all tables used by the replicator by logging into the Tungsten Replicator catalog database and dropping all tables found there, as shown in the following example. You can also accomplish the same thing by dropping and creating the database.
drop table consistency; drop table history; drop table trep_commit_seqno;
After completing this procedure you can restart replication normally. Replication with start with the next update that is applied to the database. Any previous changes in database logs are ignored.
Tungsten Replicator can be run from the command line interface or run as an operating system process. The following commands are available for Tungsten Replicator.
To avoid file permission problems and possibility failures always use the
correct account to start and stop Tungsten Replicator. By convention this
account is |
The Tungsten Replicator
bin directory contains scripts
that can be used to start and stop the Replicator from the command
Use these commands to start and stop Tungsten Replicator from the command line prompt in the Linux, Solaris, and Mac OS X operating systems.
This command starts the Tungsten Replicator process if it is not
already running. The replicator process will load static
replicator properties automatically from
conf/replicator.properties and then read
dynamic properties stored in
-clear command clears dynamic properties
so that the replicator process starts from static values only. You
can also remove the dynamic.properties file to achieve the same
This command is used to stop Tungsten Replicator in an orderly manner. The process will clean up and exit. This command must be run from the START or OFFLINE state.
This command stops the Tungsten Replicator process if it is running. It is used to halt a replicator process that does not respond to trepctl stop.
Use these commands to start and stop Tungsten Replicator from the command line prompt in the Windows operating system.
This command starts the Tungsten Replicator process if it is not already running. It functions identically to the Unix trepstart
This command is used to stop Tungsten Replicator in an orderly manner. It functions identically to the Unix trepctl stop.
There is no equivalent for trepstop on Windows. You should use operating system tools to terminate the process if required.
These instructions are only applicable for the Linux, Solaris, and Mac OS X operating systems.
Tungsten Replicator includes trepsvc which is based on the Java Service Wrapper (http://wrapper.tanukisoftware.org). This allows you to run Tungsten Replicator as a service that has protection against signals and it also implements the standard interface used by Unix Services. The service implementation also restarts Tungsten Replicator in the event of a crash.
The Tungsten Replicator service implementation supports services on 32-bit and 64-bit versions of Linux, and on Mac OS X platforms.
You can adjust the Tungsten Replicator service configuration by editing the
conf/wrapper.properties configuration file.
Please read the comments in the file for information on legal settings.
For most installations, the included file should work out of the box.
On Linux hosts you can add
trepsvc as a system
service that will start and start automatically, using the
chkconfig command, as shown in the following example:
ln -s /opt/tungsten/tungsten-replicator/bin/trepsvc \ /etc/init.d/trepsvc chkconfig --add trepsvc
If you are using the
tungsten account as recommended,
you should edit the
trepsvc script and change the
RUN_AS_USER to the correct account.
The trepsvc is a replacement for the
trepstart and trepstop
trepsvc commands are summarized below:
trepsvc start - This command starts the Tungsten Replicator
service if it is not already running. Logs are written to log
trepsvc status - This command prints out the status of the Tungsten Replicator service, namely whether it is running and if it is, on which process number.
trepsvc stop - This command stops the Tungsten Replicator service if it is currently running.
trepsvc restart - This command restarts the Tungsten Replicator service, stopping it first if it is currently running.
trepsvc console - This command runs the Tungsten Replicator service in a Java console program that allows you to view log output in a GUI shell.
trepsvc dump - This command sends a 'kill -quit' signal to the Java VM to force it to write a thread dump to the log. This command is useful for debugging a stuck process.
For maximum ease of use, set the
The commands in the sections below change Tungsten Replicator state.
trepctl script allows you to
submit commands to Tungsten Replicator. These commands change the
Tungsten Replicator state. The general syntax is as follows:
trepctl [global_options] command [command_options]
The following global options are supported.
-host host - Specifies the replicator host. Defaults to "localhost".
-port port - Specifies the replicator port. Defaults to 10000.
-verbose - Prints verbose error messages on failures.
Commands and their options are described below.
trepctl backup [-backup backupAgent] [-storage storageAgent] [-limit
Starts a backup of the database and stores the resulting backup file in a designated storage location. The backup commands returns the URI of the backup. This command may only be executed when Tungsten Replicator is in the OFFLINE state.
Backup command behavior can be altered by the following optional parameters:
-backup - Provides the name of a
backup agent defined in
If omitted Tungsten Replicator will use the default backup agent name.
If no default is specified, the backup will fail.
-storage - Provides the name of a
storage agent defined in
If omitted Tungsten Replicator will use the default storage agent name.
If no default is specified, the backup will fail.
-limit - Specifies the amount of time in seconds to wait for the backup to complete before returning. If not specified the command will wait until the backup finishes.
This command clears dynamic properties. It must be entered from the OFFLINE state.
trepctl configure [
This command refreshes the Tungsten Replicator properties.
configure command may only be used
when the node is in the OFFLINE state. If the file is omitted
the replicator process will reread its own static properties file
followed by any currently set dynamic properties.
Tungsten Replicator reads the
conf/replicator.properties file by
default. An alternative property file can be specified by giving
the configuration property file name as an argument.
Synchronizes the state of the log with the state of the database. Flush inserts a heartbeat into the master database and returns the sequence number of that heartbeat event in the transaction history log.
This command is used for planned failover as it allows users to ensure that the transaction history log contains all master updates and also provides the sequence number that a slave must reach before it can safely be promoted to a master. The flush command may only be used when the replicator process is in the ONLINE:MASTER state.
The heartbeat command inserts a SQL update into the heartbeat table, which is automatically maintained in all master and slave replicators. The master update contains a timestamp showing when the update arrived in the master database. When the heartbeat arrives on the slave, Tungsten Replicator automatically adds another timestamp and computes latency since the first update on the master.
This command is convenient for assessing true end-to-end latency as it forces an update to be processed. The heartbeat command may only be used when the replicator process is in the ONLINE:MASTER state.
trepctl kill [-y]
Kills the replicator process immediately without any clean-up of current activities. This command can be used to terminate a wedged replicator or as part of replication tests. For all other situtations the 'stop' command is preferred.
Normally users will be prompted to be sure they wish to kill the replicator. The -y option suppresses the prompt. This command may be issued in any replicator state.
This command puts the replicator into the OFFLINE state if it is not there already. This command must be used to stop the replicator cleanly. It is also required when reconfiguring a slave or master as part of a failover procedure. It may only be issued in the SLAVE, SYNCHRONIZING, or MASTER state.
This command is used to command the slave Tungsten Replicator to enter the online state. Depending on the replicator.role property the replicator will go online as a slave or a master. This command may only be issued from the OFFLINE state.
Resets monitoring counters as well as the origin time used to compute averages. This command may be issued in any replicator state.
trepctl restore [-uri backupUri] [-limit
Restores a backup of the database, where the backup is a file stored in configured backup storage. The restore command prints a message indicating whether the restore task completed successfully. The restore command may only be executed when Tungsten Replicator is in the OFFLINE state.
Restore command behavior can be altered by the following optional parameters:
-uri - Provides the URI of a backup to load from storage. If not specified the restore command will load the last available backup in the default storage facility. If no such backup exists, the restore will fail.
-limit - Specifies the amount of time in seconds to wait for the restore to complete before returning. If not specified the command will wait until the restore finishes.
trepctl set -name name -value value
This command sets a dynamically settable property value. The
-name argument provides the property name.
-value argument provides the value. The
command may only be issued when in the OFFLINE state. An error
will result if the property name does not exist or is not a
This command shows current values of dynamic properties.
Values that have not been explicitly set using the
set command will show static values. The
command may be issued from any replicator state.
Prints full state and monitoring data for the replicator. This command may be issued from any state.
This command cause the replicator process to clean up and exit. It may only be issued from the START or OFFLINE state.
trepctl wait [-state state] [-limit seconds]
Waits for the replicator to reach a particular state, returning immediately if the replicator is in this state. This command also returns with an error if the replicator goes into the error state. This command may be issued in any replicator state.
The state name must be a fully qualified name like OFFLINE:NORMAL. You can specify a parent state such as OFFLINE, in which case the command will return when the replicator reaches OFFLINE or any substate. The optional -limit parameter provides a number of seconds to wait. It must have a value between 0 and 1800 seconds. The limit defaults to 1800 if you do not enter a value.
trepctl wait [-applied seqno] [-limit seconds]
Waits for the replicator to apply a particular sequence number on a slave. This command is only valid on slave replicator processes and may only be entered when Tungsten Replicator is in the ONLINE:SLAVE state.
The command returns when the sequence number is applied or the timeout expires. Timeout behavior is the same as for the trepctl wait -state command.
Run the trepctl command without arguments to check the Tungsten Replicator state.
The Replicator THL Utility (
users to view and manipulate events in the of the Transaction
History Log. Events are serialized into a platform-independent
format that is not decipherable. The
utility not only prints events in an easy-to-read format but also
support operations to purge or skip THL events.
thl utility resides in the
bin directory. The command syntax is as follows:
The following sections provide details of commands and options.
thl utility supports the following global
Path to replicator.properties file. The value defauilts to the file in conf/replicator.properties in the Tungsten Replicator release directory.
thl invocation contains a command that
specifies the management operation to perform.
Print help information about
Display summary information about the THL including the minimum and maximum sequence numbers.
Dump one or more events starting at the low and high numbers and paging by the number of events given in the -by option. The high and low event entries default to the beginning and end of the THL respectively.
Dump a specific event with the indicated sequence number.
Deletes events within the given range. This operation is useful for clearing the log to prevent it from building up over time. The purge command prints a prompt before continuing. The prompt can be suppressed with the -y option.
Use caution when purging events. You should not purge events until they have reached all slaves. Also, you must leave at least one event in the THL at all times or replication may fail to restart if you turn Tungsten Replicator off.
Deletes a specific THL event. Caveats are identical to the more general form given previously.
Marks a range of events to be skipped. This function allows users to skip over events that may be causing errors.
Use caution when skipping events. Skipping an event can lead to data inconsistencies between replicas. This command is normally used to get around errors that cause replicator failures.
Skips a specific THL event. Caveats are identical to the more general form given previously.
The Tungsten Replicator implements extractors, filters, and appliers as plug-ins. Users can write their own plug-ins to add specialized capabilities to the Tungsten Replicator, such as supporting new databases, replicating from databases into applications or message processing systems, or performing custom transformations on replicated data.
This chapter describes how plug-ins work and provides guidelines for writing new plug-ins.
All replicator plug-ins derive from the
ReplicatorPlugin interface. This interface defines
the general contract for all plug-ins. Each specific type of
replicator plug-in extends the ReplicatorPlugin interface. There are
three main plug-in types, as shown in the following table.
|Applier||Applies SQL events to a replication target|
|Extractor||Extracts SQL events from a source like a database recovery log|
|Filter||Transforms or drops a SQL event|
Table 7.1. Replicator Plug-In Types
ReplicatorPlugin methods like configure(), prepare(), and release() accept a PluginContext as an argument. The context provides a means to issue call-backs into the replicator in a portable and efficient manner.
The THL is also a plug-in, but for the time being alternate implementations and/or extensions are not supported.
ReplicatorPlugin implementations have the following life cycle.
Configuration. Tungsten Replicator instantiates plug-ins and assigns their properties when processing a 'configure' administrative operation. All plug-ins are configured at this time. Configuration includes the following steps.
Instantiation. Tungsten Replicator creates an instance of the plug-in class using its default constructor. If the plug-in cannot be instantiated, configuration fails.
Property Assignment. Set plug-in properties. The properties are mapped to setters on the plug-in instance. For example, replicator.applier.mysql.host maps to setHost(String host). If a setter fails or cannot be found, configuration fails.
Complete Configuration. Finally, Tungsten Replicator calls the plug-in's configure() method. This method is responsible for ensuring that configuration is complete and all required properties are correctly specified.
The configuration stage should not allocate resources.
Preparation. The Tungsten Replicator calls the plug-in prepare() method to allow the plug-in to allocate resources for operation. At this point the plug-in should login to databases, open files, and perform other operations to get ready for actual work.
Appliers and post-storage filters are prepared when Tungsten Replicator goes into the SLAVE state. Extractors and pre-storage filters are prepared when Tungsten Replicator goes into the MASTER command.
Release. The Tungsten Replicator calls the plug-in release() method when the plug-in is about to be de-allocated. The plug-in is responsible for cleaning up all resources at this time.
Between preparation and release the plug-in is active and handles calls specific to that plug-in type. Plug-in-specific calls are never called at any other time.
replicator.properties are mapped
to setters on the plug-in instance according to the following rules.
Any prefix on the property name is removed so that a property like replicator.extractor.mydbms.foo_bar becomes simply foo_bar.
The first letter of the property is capitalized, so foo_bar becomes Foo_bar.
If an underscore ("_") occurs in the property name, it is omitted and the following character, if any, is capitalized. Foo_bar becomes FooBar.
The prefix "set" is added to the result. FooBar becomes setFooBar.
|It is tempting to try to process properties directly by calling PluginContext.getReplicatorProperties() rather than using setter methods. You must resist this temptation! Setters are type-safe and allow Tungsten Replicator to perform automatic validity checks of property assignments. Also, processing properties directly can result in complex or brittle configuration that is likely to fail if the property file format changes.|
The Tungsten Replicator uses Log4j to generate log messages. Plug-in authors can assume that Log4j is always available in the class path. The convention in the Tungsten Replicator is to use the class name rather than artificial log names when writing log messages.
To write your own plug-in, implement a Java class that implements one
of the plug-in interfaces and follows the life-cycle as well as
conventions for setting properties. Built your plug-in into a JAR
file and place it in the Tungsten Replicator
along with any other JAR files on which your plug-in depends.
Serious plug-in development is easiest if you have access to the Tungsten Replicator source. Download the source code and build the replicator yourself. This makes debugging easier, as you can see the full context of calls to your plug-on. Also, you can use existing plug-ins as examples for your own plug-in development.
Plug-ins can be debugged using any Java IDE that supports remote debugging. Most Tungsten development is done using Eclipse, which has excellent remote debugging support. You can enable remote debugging by uncommenting lines in the Tungsten Replicator start scripts.
Tungsten Replicator uses the database schema described in the table descriptions below.
history table is described
in the table below:
|seqno||bigint(20)||Primary Key||Unique transaction ID|
|fragno||smallint||Fragment number for transactions that are broken into multiple events|
|last_frag||char(1)||Marker for last fragment in the transaction|
|source_id||varchar(32)||Source ID of master that produced this event|
|type||tinyint||Event type (0=DBMS Update, 1=Start Master, 2=Stop Master, 3=Heartbeat)|
|epoch_number||bigint(20)||Unique number that increments following each Start Master event|
|source_tstamp||timestamp||When event was extracted from master log|
|local_enqueue_tstamp||timestamp||When event was stored in the slave THL|
|processed_tstamp||timestamp||When event was processed on slave|
|Status||timestamp||Event status (0=Pending, 1=In Process, 2=Completed, 3=Failed, 4=Skip, 5=Skipped)|
|comments||varchar(128)||Error message associated with processing, if any|
|event||longblob||Serialized replication event (binary data)|
consistency table is described
in the table below:
|ts||timestamp||Defaults to current timestamp|
Tungsten Replicator uses the global properties described in the table below.
This appendix describes the Tungsten Replicator plug-ins.
Plug-in properties use short names. When actually using a property you must prefix it as described in Section 3.1.4, “Static Properties and the replicator.properties File”.
THL storage is implemented using a single generic plug-in.
This plug-in implements a generic JDBC storage for plug-ins. It requires a JDBC URL and has identical properties regardless of the database.
|Java Implementation Class|
Table C.1. JDBC THL Storage Implementation Class
|Property Name||Description||Syntax||Required||Default Value|
|password||Password to login used for THL||String||Yes||none|
|storage||THL storage implementation name. Must be set to the THL storage class name given above.||Class name||Yes||None|
|url||JDBC URL to connect to THL database||String||Yes||none|
|user||Login to THL database||String||Yes||none|
Table C.2. JDBC THL Properties
The extractor plug-ins are described in the chapters below.
The MySQL extractor extracts SQL events from the MySQL binlog. It supports both statement as well as row events. It is designed to support MySQL 4.1 and above. The MySQL extractor requires read-only access to the MySQL binlog directory. It also requires an administrative login to the MySQL server capable of running commands like 'FLUSH LOGS' and 'SHOW MASTER STATUS'.
|Java Implementation Class|
Table C.3. MySQL Extractor Implementation Class
|Property Name||Description||Syntax||Required||Default Value|
|binlog_dir||Location of mysql binlogs (MySQL data directory)||Directory||No||/var/log/mysql|
|binlog_file_pattern||Pattern of mysql binlog files (must match MySQL log_bin parameter)||String||No||mysql-bin|
|host||MySQL server host name||String||No||localhost|
|password||Password to administrative login||String||No||Empty String ("")|
|port||MySQL server TCP/IP port||Integer||No||3306|
|strictVersionChecking||Whether replicator should print warning for unsupported MySQL version||Boolean||No||true|
|user||Administrative login to MySQL server||String||No||root|
Table C.4. MySQL Extractor Properties
The filter plug-ins are described in the chapters below.
Transforms database, table and column names into upper or lower case. In the case of replicated statements, it transforms all values outside quoted strings.
|Java Implementation Class|
Table C.5. Database Transform Filter Implementation Class
|Property Name||Description||Syntax||Required||Default Value|
|toUpperCase||If true, transform to upper case; otherwise to lower case||Boolean||No||false (to lower case)|
Table C.6. Database Transform Filter Properties
The Database Transform filter maps the default database
name stored on a SQL event to a new name using regular expressions.
Any name that matches the
fromRegex expression is mapped using the
toRegex expression. The rules for matching are based on Java
regular expressions as implemented in the Java Pattern and Matcher
classes. Transforms work according to the
|Java Implementation Class|
Table C.7. Database Transform Filter Implementation Class
|Property Name||Description||Syntax||Required||Default Value|
|fromRegex||Java regular expression that matches against database names||String||Yes||None|
|toRegex||Transformation regular expression used to change matching Java database names||String||Yes||None|
Table C.8. Database Transform Filter Properties
The logger filter logs SQL events by calling their toString() methods. The result is written to the log4j log.
|Java Implementation Class|
Table C.9. Logger Filter Implementation Class
The Time-Delay filter implements simple time-delayed replication. The filter delays event application by a user-configurable period of time measured in seconds. Event application is blocked until the current time is equal to or greater than the time at which the event was extracted plus the delay. For this to work, master and slave hosts should use NTP (Network Time Protocol) to synchronize clocks.
The Time-Delay filter should only be used on slaves. It delays storage of events in the Transaction History Log (THL). On masters this can lead to data loss.
|Java Implementation Class|
Table C.11. Time Delay Filter Implementation Class
|Property Name||Description||Syntax||Required||Default Value|
|delay||Time delay in seconds||Integer||No||0 (No delay)|
Table C.12. Time Delay Filter Properties
The applier plug-ins are described in the chapters below.
The MySQL Applier is a custom applier that applies SQL events to MySQL databases. The MySQL applier supports application of statement and row events on all MySQL versions. The MySQL applier requires an administrative login to the MySQL server capable of running any SQL DDL statement or update that is replicated.
|Java Implementation Class|
Table C.13. MySQL Applier Implementation Class
|Property Name||Description||Syntax||Required||Default Value|
|host||MySQL server host name||String||No||localhost|
|password||Password to administrative login||String||No||Empty String ("")|
|port||MySQL server TCP/IP port||Integer||No||3306|
|user||Administrative login to MySQL server||String||No||root|
Table C.14. MySQL Applier Properties
This document uses the following typographic conventions:
This font is used for all ordinary text.
Uppercase letters on this font indicate SQL keywords and macro names.
These fonts indicate file names and path expressions.
update t1 set x = A.y FROM (SELECT * from t2) A where t1.id = A.id
This font is used for program code and program output. Example SQL statements also use this font.
This font is used for sample command lines.
This font is used for function names.
This font is used for parameter names and function arguments.
Words emphasised like this indicate information that the user or the application must provide.
Backup and recovery log management
This style is used for references to other documents, or chapters in the same document. New terms and emphasised issues are also written like this.
This style is used for syntax notation.
Table D.1. Typographic Conventions
For output purposes, we use backslashes (\) to spread out long lines across several lines. Long lines are used, for example, in configuration files.
If you noticed inaccuracies in this document, or have
suggestions on how the Continuent Tungsten documentation
set could be improved, please do not hesitate to send