MySql -> Oracle Replication Using Tungsten Replicator

So, in the beginning are a few words, a la preface. This manual does not claim to be true in the first instance or for line-by-line leadership. Scripts can be written much better. Teams - at the time of reading, they may sound different (even at the time of writing, the documentation on the site differs from real teams). Much of the scripts have been done under the root, which, on the whole, is also not correct, but for the time being it would work and then fix it — I left it for now. You will find answers to basic questions on configuration in the documentation on the tungsten website (http://code.google.com/p/tungsten-replicator/).

Task:

There was a need for replication from MySql (5.5) to Oracle (11.2) on a server with CentOS 5.5. Moreover, it’s not just everything, but just large tables that fill up very, very quickly and are associated with statistics. Add to this that there are problems with the place on the MySql server, and as a conclusion, replication filtering should occur on it. Well, and if necessary - immediately clean up all possible temporary files, again because of the place on both servers.



So, let's begin.

Installation:

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

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. I installed everything from distributions without any problems.

Tungsten will work with us according to the following scheme:

image

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 = 10000 \
--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 a scheme will appear in the muscle - tungsten_. She is still useful to us.

The user under which spins only in the root example, then or immediately rearrange which one 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 =\
--home-directory = / opt / repl \
--datasource-type = oracle \
--datasource-oracle-service = \
--datasource-user = \
--datasource-password = \
--service-name = frommysql \
--rmi-port = 10000 \
--master-thl-port = 2112

So far, everything is simple. After performing these tricky operations - we will only work with the copy in the folder where we installed (/ opt / repl / ...).

We’re not in a hurry to start the replicator, because right away it still won’t start correctly - it will swear in the logs for a lack of configs, because 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, indicate which filters will be used:

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

and the path to the filter itself , and correspondingly 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 tables abyrvalg and transactions.

We make sure that the master listens to the port on which the slave will access it to collect thl logs

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

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

At the end of the story, battle configs will be attached and, of course, a filtering script.

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

If online status means that everything is fine, he began to store MySql binlogs (by default, when they exceed gigabytes - he rubs them himself) and thl files - in fact, that he will send a slave, this will be discussed below. There should be no problems.

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

Make sure that in the value of replicator.master.connect.uri = thl: //: 2112 IP masters are precisely installed, appearances and passwords are correct.

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

We create the table corresponding to replicated in the specified in a slave of an oracle;

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

You can go into the created schemes on Mester and Slave, and pay attention - in the table trep_commit_seqno the values ​​of seqno and epoch_number change.

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

A little patient care.

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

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

Actually - this script on the master and slave is doing 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 troubleshooting.

If we accidentally erased the superfluous, or the network between the servers lay for a long time, or there were a number of reasons, but when connecting, the slave writes that alas and ah - but he wants position N, but the wizard does not 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 exception: Log seek failure: expected seqno = XXXXXXX found seqno = YYYYYYY

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

Stop replication on the slave. We wipe the copied thl logs. Pay attention to the trep_commit_seqno table. Erase the values ​​of seqno and epoch_number, or set the values ​​expected by the master, depending on what he wants. We start. Then, of course, you will have to distill the lost fragment in the old fashioned way from MySql to Oracle (via csv).

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

Wizard config, slave config and js-filter script:

_http: //zalil.ru/32217860

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

Also popular now: