Oracle->MySQL的单向复制

操作系统： redhat  as   4.5

oracle version:10.2.0.1

mysql version: mysql  Ver 14.14 Distrib 5.5.28, for Linux (i686) using readline 5.1

源端ip:          172.17.61.131          rhel131

目标端Ip：     172.17.61.133          rhel133

 

 

1）  在源库建立extract进程：

 

GGSCI(rhel131) 1> add extract ext2my,tranlog,begin now

 

GGSCI(rhel131) 2> add rmttrail /u01/app/mysql/ogg11/dirdat/my, extract ext2my

RMTTRAILadded.

 

GGSCI(rhel131) 4> edit params ext2my

 

extractext2my

setenv(oracle_sid=orcl,nls_lang= AMERICAN_AMERICA.WE8ISO8859P1)

userid ogg, password ogg

rmthost 172.17.61.133, mgrport 7809

rmttrail /u01/app/mysql/ogg11/dirdat/my

table scott.test2;

 

GGSCI(rhel131) 5> start extract ext2my

 

2） 在源库建立表的定义文件，并复制到目标库相应的目录

 

GGSCI(rhel131) 9> edit params defgen

DEFSFILE/u01/app/oracle/ogg11/dirdat/test2.def

userid ogg, password ogg

table scott.test2;

 

 [oracle@rhel131 ogg11]$ defgen paramfiledirprm/defgen.prm

 

[oracle@rhel131ogg11]$ scp /u01/app/oracle/ogg11/dirdat/test2.def172.17.61.133:/u01/app/mysql/ogg11/dirdat/test2.def

 

3） 目标库的准备工作：

 

a)        配置mysql的环境变量

 

-bash-3.00$cat .bash_profil

export OGG_HOME=/u01/app/mysql/ogg11
exportPATH=$OGG_HOME:$PATH
exportLD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
exportMYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock

 

b)        初始化ogg:

 

-bash-3.00$./ggsci

 

OracleGoldenGate Command Interpreter for MySQL

Version11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

Linux,x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30

 

Copyright(C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

 

GGSCI(rhel133) 1> create subdirs

 

 

c)         先建立mydb的数据库，以及表test2

 

mysql>create database mydb

    -> ;

QueryOK, 1 row affected (0.00 sec)

 

mysql>show databases;

+--------------------+

|Database           |

+--------------------+

|information_schema |

|mydb               |

|mysql              |

|performance_schema |

|test               |

+--------------------+

5rows in set (0.00 sec)

 

mysql>use mydb;

 

mysql>create table test2(t1 int);

 

mysql>show tables;

+----------------+

|Tables_in_mydb |

+----------------+

|test2          |

+----------------+

1row in set (0.00 sec)

 

d)   在目标库建立checkpoint

GGSCI(rhel133) 8> edit param globals

 

checkpointtablemydb.checkpoint

 

GGSCI(rhel133) 1>  dblogin sourcedbmydb,userid root

Password:

Successfullylogged into database.

 

GGSCI(rhel133) 2> add checkpointtable mydb.checkpoint

 

Successfullycreated checkpoint table mydb.checkpoint.

 

4） 在目标库配置manager进程：

GGSCI(rhel133) 4> edit params mgr

 

PORT7809

PURGEOLDEXTRACTS/u01/app/mysql/ogg11/dirdat, USECHECKPOINTS, MINKEEPDAYS 10

 

GGSCI(rhel133) 5> start mgr

 

 

5） 在目标库配置replicat进程：

GGSCI(rhel133) 3> add replicat rep1, exttrail /u01/app/mysql/ogg11/dirdat/my, checkpointtablemydb.checkpoint

 

GGSCI(rhel133) 5> edit params rep1

replicatrep1

sourcedefs /u01/app/mysql/ogg11/dirdat/test2.def

setenv(nls_lang= AMERICAN_AMERICA.WE8ISO8859P1)

sourcedb mydb, userid root, password 1234

handlecollisions

assumetargetdefs

discardfile /u01/app/mysql/ogg11/dirdat/rep1_discard.txt, append, megabytes 10

maps cott.test2, target mydb.test2;

 

 

GGSCI(rhel133) 6> start replicat rep1

 

6） 最后测试一下：

 

在源库：

SQL>insert into test2 values (1);

 

1row created.

 

SQL>commit;

 

目标库：

mysql>select * from test2;

+------+

|t1   |

+------+

|    1 |

+------+

1row in set (0.00 sec)

 

在源库操作：

SQL>  insert into test2 select rownum from dualconnect by rownum<100;

 

99rows created.

 

SQL>commit;

 

Commitcomplete.

 

目标库查询：

mysql>select count(*) from test2;

+----------+

|count(*) |

+----------+

|      100 |

+----------+

1row in set (0.00 sec)