MySql -> Oracle Replication with Tungsten Replicator

So, at the beginning of a few words, a la preface. This manual does not claim to truth in the first instance and on the line-by-line manual. Scripts can be written much better. Commands - at the time of reading may sound differently (even at the time of writing, the documentation on the site may differ from real commands). Much of the scripts have been done under the root, which is also not correct in general, but for “what would have worked and then corrected” - for the time being, it’s so. You will find answers to basic setup questions in the documentation on the site tungsten (http://code.google.com/p/tungsten-replicator/).

Task:

There was a need to replicate with MySql (5.5) to Oracle (11.2) on a server running CentOS 5.5. What is more, everything, but only large tables, which are very, very quickly filled and related to statistics. Add to this that the server MySql there are problems with the place, and as a conclusion - filtering replication should occur on it. Well, and if necessary - immediately clear all possible temporary files, again because of the space on both servers.
')


So, let's begin.

Installation:

On MySql you need to put binlog in RAW format. This is necessary only for filtering scripts and was part of business logic. If you do not need to filter, or write your own script, it is quite possible not to change the format of binlog.

We download the tungsten replicator itself (http://code.google.com/p/tungsten-replicator/), and install it on the wizard (MySql server) and on the slave (Oracle). It will require ruby ​​and java. All put from distributions without any problems.

Tungsten will work with us according to the following scheme:



Installation script for the wizard:

./tools/tungsten-installer --master-slave -a --cluster-hosts = 127.0.0.1 \
--master-host = 127.0.0.1 \
--user = root \
--home-directory = / opt / repl \
--datasource-port = 3306 \
--datasource-user = \
--datasource-password = \
--service-name = oracle \
--rmi-port = 10,000 \
- thl-port = 2112 \
--mysql-enable-enumtostring = true \
--mysql-use-bytes-for-string = false \
--skip-validation-check = MySQLNoMySQLReplicationCheck

In general, I think everything is clear, “service-name” - how the service will be called, and the scheme will appear in the muscle - tungsten_ <service-name>. She still useful to us.

The user under which only turns in the root example, then or immediately rearrange who is more convenient for you. The examples will use root.

On the slave:

./tools/tungsten-installer --master-slave -a --cluster-hosts = localhost \
--user = root \
--master-host = <IP master> \
--home-directory = / opt / repl \
--datasource-type = oracle \
--datasource-oracle-service = \
--datasource-user = \
--datasource-password = \
--service-name = frommysql \
--rmi-port = 10,000 \
--master-thl-port = 2112

So far, it's simple. After performing these not tricky operations - we will work only with a copy in the folder where we installed (/ opt / repl / ...).

We are not in a hurry to start the replicator, so right away it will not start correctly anyway - it will swear in the logs for a lack of configs, since Config names will be desired depending on the schema name. Rename replicator.properties to static- <schema name> .properties, in my case - static-oracle.properties.

In the config (/ opt / repl / tungsten / tungsten-replicator / conf / ...) on the wizard we indicate which filters will be used:

replicator.stage.binlog-to-q.filters = dropcomments, filtertables, dbupper

and the path to the filter itself, and accordingly the tables that will be replicated:

replicator.filter.filtertables = com.continuent.tungsten.replicator.filter.JavaScriptFilter
replicator.filter.filtertables.script = / opt / repl / tungsten / tungsten-replicator / filtertables.js
replicator.filter.filtertables.include = idp.abyrvalg, idp.transactions

In this case, the idp schema and the abyrvalg and transactions tables.

Make sure that the master is listening to the port that the slave will access to it to collect thl logs

replicator.master.listen.uri = thl: //0.0.0.0: 2112 /

In the replicator.source_id = specify any unique name. The easiest is server IP. In my case - 192.168.40.3

At the end of the story will be attached to the configs and of course the filtering script.

We start the replicator on the master ./replicator start
Check its transition to online - ./trepctl status, if offline - ./trepctl online

If the status is online, then everything is fine, he started storing the MySql binlogs (by default, when they exceed gigabytes - he rubs them) and thl files - in fact, he will send a slave, this will be discussed below. There should be no problems.

On the slave, respectively, the config file is renamed to static- <schema name> .properties, in my case - static-frommysql.properties.

We are convinced that in the value of replicator.master.connect.uri = thl: // <master IP>: 2112 the IP master is set correctly, attendances and passwords are correct.

replicator.source_id = - again, any unique value,
comment out replicator.store.thl.storageListenerUri.

We create in the slave specified in a config the table corresponding to replicable.

We start the slave - ./replicator start
If he moved from status to online status - everything is fine. If not, it will swear in logs and statuses.

You can go to the created schemes on the placer and slave, and pay attention - in the trep_commit_seqno table, the values ​​of seqno and epoch_number change.

If everything is ok, you can reduce the logging level by editing wrapper.conf

A bit of patient care.

On the thl wizard, logs are stored as specified in the config file, by default - the day. (replicator.store.thl.log_file_retention =), we change to the one we need, the same on the slave. But if we urgently needed to free up space, then we will have to do it manually. The main thing - do not wipe the last event.

The default wizard stores bin logs in the size of no more than 1 gigabyte (/ opt / repl / relay / ...), but to clean thl manually on the slave and wizard you need to look at the upper and lower values ​​of the events already recorded.

Actually - this curve script on the master and the slave and is engaged in this for me.

#! / bin / sh
cd / opt / repl / tungsten / tungsten-replicator / bin
MINVALUE = `. / Thl -service frommysql info | grep "min seq # =" | awk '{print $ 4;}' `
MAXVALUE = `. / Trepctl status | grep "appliedLastSeqno:" | awk '{print $ 3;}' `
MAXVALUENEW = `echo" $ MAXVALUE-1000 "| bc`
./trepctl -service frommysql offline
sleep 10
./thl purge -low $ MINVALUE -high $ MAXVAskyrimLUENEW -y
./trepctl -service frommysql online

A bit of troubling.

If we accidentally erased too much, or the network between the servers lay for a long time, or a number of reasons happened, but when connecting, the slave writes that alas and oh - but he wants the N position, but the master doesn't have it, something like:

INFO | jvm 1 | 2011/11/24 11:32:54 | 2011-11-24 11: 32: 54,432 [oracle - connector-handler-192.168.39.50] ERROR thl.ConnectorHandler Connector handler terminated by THL: segno = XXXXXXX found

(more accurately we look either in the status or in the logs) - then we will have to clean up a bit.

We stop replication on the slave. We wipe the copied thl logs. Pay attention to the trep_commit_seqno table. We overwrite the values ​​of seqno and epoch_number, or set the values ​​expected by the master, depending on what he wants. We start. Further, of course, the lost fragment will have to be distilled from MySql to Oracle (via csv).

It is also possible to read from a certain position, see the documentation on the official website, paragraphs 4.3.x

Wizard config, slave config and js-filtering script:

_http: //zalil.ru/32217860

In general - Tungsten replicator proved to be a very flexible and powerful, but not very well documented product, allowing replication between different databases for various tasks.