Replicating from MySQL to NuoDB Using Tungsten Replicator

philip_stoev's picture

Tungsten Replicator is a software package that allows replication to be established between MySQL and another database product.  This blog post describes how to configure replication between MySQL and NuoDB.  The benefits of doing this are to:

1. Offload realtime analytics from an overloaded operational MySQL instance.  Both NuoDB and MySQL would run side by side with NuoDB managing operational analytics.

2. Completely migrating an operational data store to NuoDB, to reap the benefits of elastic scalability without sharding, simple to manage redundancy, and built-in support for active-active geo-redundancy.

Here is how to configure replication from MySQL to NuoDB:

Step #1. Configure MySQL

MySQL must be configured to use row-based replication with no checksums. To achieve that, make sure /etc/my.cnf has the following entries:

[mysqld]
binlog-format=row
log-bin=mysql-binlog
server-id=1
loose-binlog-checksum=NONE
loose-log-bin-use-v1-row-events=1

and then restart MySQL.  If you are running MySQL 5.6 , the loose-binlog-checksum and log-bin-use-v1-row-events options will make MySQL write the replication log in the 5.5 format that is compatible with Tungsten.

Step #2. Obtain and compile Tungsten Replicator (requires Ant and Ruby)

git clone https://github.com/tazija/tungsten-replicator
cd tungsten-replicator/builder/
./build.sh

Step #3.  Initiate Replication using Tungsten

These commands start the necessary Tungsten Replicator processes that communicate with MySQL and with NuoDB. You may need to modify the username and password arguments to match your environment.

./build/tungsten-replicator-2.1.0/tools/tungsten-installer \
--master-slave \
--advanced \
--master-host=127.0.0.1 \
--datasource-type=mysql \
--datasource-port=3306 \
--datasource-user=<MYSQL_USER> \
--datasource-password=<MYSQL_PASSWORD> \
--service-name=replicator \
--cluster-hosts=127.0.0.1 \
--home-directory=/opt/tungsten/extractor \
--rmi-port=10001 \
--thl-port=12001 \
--skip-validation-check=MySQLPermissionsCheck \
--skip-validation-check=InstallerMasterSlaveCheck \
--java-file-encoding=UTF8 \
--mysql-enable-settostring=true \
--mysql-enable-enumtostring=true \
--mysql-use-bytes-for-string=false \
--svc-parallelization-type=none \
--start-and-report

./build/tungsten-replicator-2.1.0/tungsten-replicator/bin/trepctl -port 10001 status
./build/tungsten-replicator-2.1.0/tools/tungsten-installer \
--master-slave \
--advanced \
--master-host=127.0.0.1 \
--master-thl-port=12001 \
--datasource-type=nuodb \
--nuodb-database=test \
--datasource-user=dba \
--datasource-password=goalie \
--service-name=replicator \
--cluster-hosts=127.0.0.1 \
--home-directory=/opt/tungsten/applier \
--rmi-port=10011 \
--thl-port=12002 \
--skip-validation-check=InstallerMasterSlaveCheck \
--svc-parallelization-type=none \
--property=replicator.applier.failure_policy=warn \
--java-file-encoding=UTF8 \
--start-and-report
./build/tungsten-replicator-2.1.0/tungsten-replicator/bin/trepctl -port 10011 status

Step #4. Migrate the existing data

The following commands will export both the schema and the data from MySQL into NuoDB using the NuoDB Migrator tool – see http://dev.nuodb.com/techblog/2013/06/26/migrating-a-mysql-application-to-nuodb/.  The migrator tool will create an equivalent schema to the source MySQL database in NuoDB and populate the NuoDB database with a copy of the data – essentially doing a one time synchronization of NuoDB with the base data in MySQL.  We recommend doing this step when the source database is idle.

export CLASSPATH=/usr/share/java/mysql-connector-java.jar:$CLASSPATH
/opt/nuodb/bin/nuodb-migrator schema \
--source.driver=com.mysql.jdbc.Driver \
--source.url=jdbc:mysql://localhost/<MYSQL_SCHEMA> \
--source.username=<MYSQL_USERNAME> \
--source.password=<MYSQL_PASSWORD> \
--target.url=jdbc:com.nuodb://localhost/test \
--target.username=dba \
--target.password=goalie \
--target.schema=test

/opt/nuodb/bin/nuodb-migrator dump \
--source.driver=com.mysql.jdbc.Driver \
--source.url=jdbc:mysql://localhost/<MYSQL_SCHEMA> \
--source.username=<MYSQL_USERNAME> \
--source.password=<MYSQL_PASSWORD> \
--output.type=csv \
--output.path=/tmp/mysql-dump

/opt/nuodb/bin/nuodb-migrator load \
--input.path=/tmp/mysql-dump \
--target.url=jdbc:com.nuodb://localhost/test \
--target.username=dba \
--target.password=goalie \
--target.schema=test

 

That is it, Tungsten will keep NuoDB synchronized as changes are made to the MySQL source database.  You can check that any updates made on the MySQL server are propagated to the NuoDB database with your app or favorite SQL editing tool.  These steps can also further be orchestrated to reduce/eliminate downtime.

What’s next?

This post gave a great overview of how to replicate a MySQL database.  In an upcoming post we’ll focus on a migration using a active mysql instance and walk through the details around a live cutover with no downtime or data loss.

Our current solution currently doesn’t support DDL statement being issued on the MySQL side that contains MySQL-specific syntax.  We’re also looking to add support for that.

We wish to thank our friends at Continuent for creating Tungsten, it is an excellent piece of infrastructure for managing the complexity of replication.  It has been very easy to work with.

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Go to top