In some read focus system, we may make copies of the write database to take the heavy read activites, however how to make a read database if we plan to write on Oracle and read from MySQL database? Since the write is not heavy, we could incrementally replicate data from Oracle to MySQL with data capture by trigger or by Oracle materialized view log.
I have wrote a tool for Oracle to Oracle replication, now I will release another tool mysqlsync to do this job.
DataSync: Oracle Data Replication Utility, Release 3.0.1
(c) Copyright Lou Fangxin (AnySQL.net) 2010, all rights reserved.
Usage: datasync keyword=value [,keyword=value,...]
Valid Keywords:
user1 = username/password@host:port:sid for source database.
user2 = username/password@host:port:database for target database.
array = array fetch size
long = maximum size for long, long raw, CLOB, BLOB columns.
crypt = encrypt the connection info only, no data copy (YES/NO).
parfile = read command option from parameter file
config = configuration file.
* wait = wait time in tenth of second after each loop.
* rowid = use rowid based materialized vew log table(FULL/INSERT).
* dbid = target database flag column of log table.
* dblist = all target database flag columns of log table.
* charset = character set name of the target database.
* ncharset= national character set name of the target database.
* safe = double column buffer for character set conversion.
log = log file name for screen messages.
Notes:
datasync user1=scott/tiger user2=scott/tiger config=scott.cfg
Config:
Source # Key Col # Log Table # Target # Part # Conf # Filler
EMP # EMPNO # MLOG$_EMP # EMP
Then we will start to do the replication of table (SCOTT.EMP), it's very simple.
1, Create Materilaized View Log on SCOTT.EMP
CREATE MATERILIZED VIEW LOG ON EMP WITH SEQUENCE, PRIMARY KEY;
CREATE INDEX MLOG$_EMP_IX1 ON MLOG$_EMP (SEQUENCE$$);
2, Prepare a configuration file for replication, one table per line.
D:\>type scott.cfg
EMP #EMPNO #MLOG$_EMP #EMP
3, Data initialize between tow databases with DataCopy for MySQL (ora2mysql)
ora2mysql user1=scott/tiger@db1 user2=test/test@localhost:3306:test table=emp
4, start data replication daemon with init mode
mysqlsync user1=scott/tiger@db1 user2=test/test@localhost:3306:test config=scott.cfg
5, Make DML changes on source database, changes will be replicated to MySQL database.
update emp set comm = nvl(comm,0) + 200;
Now you have a very light replication tools, you can use it to make read database for you application.