Goldengate mysql-oracle复制配置文档

Goldengate mysql-oracle复制配置文档

1.mysql端数据库设置

$vi /etc/my.cnf

[mysqld]添加

log-bin=/var/lib/mysql/binlog/zmd.bin                                                  --开启mysql binlog
max_binlog_size=4096
binlog_format=row                                                                      --Goldengate需要row模式的binlog模式

[client]添加
default-character-set = gbk                                                            --指定客户端字符集

MYSQL>grant all privileges on *.* to myrep@localhost identified by 'myrep';            --mysql中建立复制专有用户并授权

重启mysql




2.oracle端数据库设置

SQL>create user myrep identified by myrep;

SQL>grant dba to myrep;




3.Goldengate设置初始数据同步

3.1 mysql端配置

GGSCI>edit params mgr

内容
port10000

GGSCI>start mgr;

GGSCI>edit params init1

内容
extract init1
sourcedb mydb@127.0.0.1:3306 userid myrep,password myrep
sqlexec "set names gbk;"                                                                 --设定mysql字符集
rmthost 10.110.13.34,mgrport 10000
rmttask replicat,group repinit
table myrep.sysparm;

3.2 oracle端配置

GGSCI>edit params mgr

内容
port 10000

GGSCI>start mgr

GGSCI>edit params repinit

内容
replicat repinit
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")                                             --指定replicat的oracle字符集环境变量
getenv (NLS_LANG)
userid myrep,password myrep
sourcedefs ./sysparm.def
MAP "myrep.sysparm", TARGET myrep.sysparm;                                                --注意大小写

3.3 发起初始同步

mysql端运行

GGSCI> start init1                                                                        --发起初始同步

GGSCI>info init1,detail                                                                   --查看初始数据是否同步




4.配置Goldengate复制

4.1 mysql端配置extract

GGSCI>edit params ex1

内容
extract ex1
setenv (MYSQL_HOME="/var/lib/mysql")                                                       --指定my.cnf的位置
getenv (MYSQL_HOME)
tranlogoptions altlogdest /var/lib/mysql/binlog/zmd.index                                  --指定binlog index的位置
sourcedb myrep@localhost:3306,userid myrep,password myrep
exttrail ./dirdat/ex
--dynamicresolution
--gettruncates
table "myrep.sysparm";

GGSCI>add extract ex1,vam,begin now

GGSCI>add exttrail ./dirdat/ex,extract ex1

4.2 mysql端配置data pump

GGSCI>edit params pmp1

内容
extract pmp1
rmthost 10.110.13.34,mgrport 10000
rmttrail ./dirdat/ex
PASSTHRU
table "myrep.sysparm";

GGSCI>add extract pmp1 exttrailsource ./dirdat/ex

GGSCI>add rmttrail ./dirdat/ex,extract pmp1

4.3 oracle端配置replicat

GGSCI>edit params rep1

内容
replicat rep1
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
getenv (NLS_LANG)
userid myrep,password myrep
sourcedefs ./sysparm.def
discardfile /tmp/mysql.dsc,append,megabytes 100
gettruncates
map "myrep.sysparm", target myrep.sysparm;

SQL>@chkpt_ora_create.sql                                                                  --建立checkpoint表

GGSCI>edit params ./GLOBALS

内容
checkpointtable myrep.ggs_checkpoint

GGSCI>add replicat rep1,checkpointtable myrep.ggs_checkpoint,exttrail ./dirdat/ex




5 Goldengate维护

GGSCI>info ex1,showch                                                                      --查看最近checkpoint的时间

GGSCI>view report ex1,detail

GGSCI>view report pmp1,detail

GGSCI>view report rep1,detail