使用goldengate同步mysql

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:使用goldengate同步mysql

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、source端

add extract extl,vam,begin now
ADD EXTTRAIL F:\ogg\mysql\dirdat\rl, EXTRACT EXTL
edit params extl
extract extl
sourcedb test@localhost:3306,userid root,password xifenfei
exttrail F:\ogg\mysql\dirdat\rl
dynamicresolution
gettruncates
table test.t_1;
add EXTRACT pump1, EXTTRAILSOURCE F:\ogg\mysql\dirdat\rl, BEGIN now
add rmttrail /ogg/mysql/dirdat/rl extract pump1
edit params pump1
extract pump1
rmthost 192.168.1.111,mgrport 7808
rmttrail /ogg/mysql/dirdat/rl
PASSTHRU
gettruncates
table test.t_1;

二、target端

edit params ./GLOBALS
dblogin sourcedb test@localhost:3306,userid root,password xifenfei
CHECKPOINTTABLE ogg.chkpoint
ADD CHECKPOINTTABLE ogg.chkpoint
add replicat repl exttrail /ogg/mysql/dirdat/rl,begin now,checkpointtable ogg.chkpoint
edit params repl
replicat repl
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB test,userid root,password xifenfei
assumetargetdefs
reperror default,discard
discardfile /tmp/mysql.dsc,append,megabytes 100
gettruncates
map TEST.T_1, target "test.t_1";

三、说明
本实例是win 中的mysql同步到linux中的mysql
有个注意点:
1、add extract extl,vam,begin now 不像oracle中的tranlog
2、注意各个参数中schema.tablename大小写问题
1)win中不区分,全部使用小写会自动转换为大写
2)在replicate参数中,因为map是从win中的data pump中得到,所以map后面的schema.tablename需要大写
3)在replicate参数中,因为table是linux中的对应linux中的数据库名和表名(mysql是以文件形式存储,一般均为小写),所以map后面的schema.tablename需要小写+双引号,防止转为大写
3、注意mysql数据库编码
四、与oracle不同之处
1、登录
dblogin sourcedb dbname@localhost:3306,userid root,password xifenfei
2、Extract中访问mysql
sourcedb dbname@localhost:3306,userid root,password xifenfei
3、Replicat中访问mysql
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB dbname,userid root,password xifenfei

goldedgate 初始化数据

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:goldedgate 初始化数据

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、mysql数据库初始化
1、source端配置

ADD EXTRACT EINI1, SOURCEISTABLE
EDIT PARAMS EINI1
EXTRACT EINI1
sourcedb test@localhost:3306,USERID root, PASSWORD xifenfei
RMTHOST 192.168.1.111, MGRPORT 7808
RMTTASK REPLICAT, GROUP RINI1
TABLE "test.web_statistics";

3、target端配置

ADD REPLICAT RINI1, SPECIALRUN
EDIT PARAMS RINI1
REPLICAT RINI1
ASSUMETARGETDEFS
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB test,userid root,password xifenfei
DISCARDFILE ./dirrpt/RINI2.dsc, PURGE
MAP "test.web_statistics", TARGET "test.web_statistics";

3、开启复制
start extract eini1
二、oracle数据库
1、source端配置

ADD EXTRACT EINI1, SOURCEISTABLE
EDIT PARAMS EINI1
EXTRACT EINI1
SETENV (NLS_LANG =AMERICAN_AMERICA.UTF8)
USERID ogg, PASSWORD "xifenfei"
RMTHOST 192.168.1.111, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINI1
TABLE chf.init_obj;

2、target端配置

ADD REPLICAT RINI1, SPECIALRUN
EDIT PARAMS RINI1
REPLICAT RINI1
ASSUMETARGETDEFS
SETENV (NLS_LANG =AMERICAN_AMERICA.UTF8)
USERID ogg, PASSWORD xifenfei
DISCARDFILE ./dirrpt/RINI1.dsc, PURGE
MAP chf.init_obj, TARGET chf.init_obj;

3、开启复制
start extract eini1

goldengate通用配置

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:goldengate通用配置

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、理论
source

ADD EXTRACT <ext>, TRANLOG, BEGIN <time>, [, THREADS]
ADD EXTTRAIL <local_trail>, EXTRACT <ext>
EDIT PARAMS <ext>
-- Identify the Extract group:
EXTRACT <ext>
-- Specify database login information as needed for the database:
[SOURCEDB <dsn_1>,][USERID <user>[, PASSWORD <pw>]]
-- Specify the local trail that this Extract writes to:
EXTTRAIL <local_trail>
-- Specify tables to be captured:
TABLE <owner>.<table>;
ADD EXTRACT <pump_1>, EXTTRAILSOURCE <local_trail>, BEGIN <time>
ADD RMTTRAIL <remote_trail_1>, EXTRACT <pump_1>
EDIT PARAMS <pump_1>
-- Identify the data pump group:
EXTRACT <pump_1>
-- Specify database login information as needed for the database:
[SOURCEDB <dsn_1>,][USERID <user>[, PASSWORD <pw>]]
-- Specify the name or IP address of the first target system:
RMTHOST <target_1>, MGRPORT <portnumber>
-- Specify the remote trail on the first target system:
RMTTRAIL <remote_trail_1>
-- Allow mapping, filtering, conversion or pass data through as-is:
[PASSTHRU | NOPASSTHRU]
-- Specify tables to be captured:
TABLE <owner>.<table>;

target

edit params ./GLOBALS
CHECKPOINTTABLE <owner>.<tablename>
ADD REPLICAT <rep_1>, EXTTRAIL <remote_trail_1>, BEGIN <time> checkpointtable <owner>.<tablename>
edit params <rep_1>
-- Identify the Replicat group:
REPLICAT <rep_1>
-- State whether or not source and target definitions are identical:
SOURCEDEFS <full_pathname> | ASSUMETARGETDEFS
-- Specify database login information as needed for the database:
[TARGETDB <dsn_3>,] [USERID <user id>[, PASSWORD <pw>]]
-- Specify error handling rules:
REPERROR (<error>, <response>)
-- Specify tables for delivery:
MAP <owner>.<table>, TARGET <owner>.<table>[, DEF <template name>];

二、配置实例
source

add extract extl,tranlog,begin now
ADD EXTTRAIL /u01/ogg/dirdat/rl, EXTRACT EXTL
ADD TRANDATA chf.objce_t
edit params extl
extract extl
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
userid ogg,password xifenfei
exttrail /u01/ogg/dirdat/rl
dynamicresolution
gettruncates
table chf.objce_t;
ADD EXTRACT pump1, EXTTRAILSOURCE /u01/ogg/dirdat/rl, BEGIN now
add rmttrail /u01/ogg/dirdat/rl extract pump1
edit params pump1
extract pump1
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
userid ogg, password xifenfei
rmthost 192.168.1.111, mgrport 7809
rmttrail /u01/ogg/dirdat/rl
PASSTHRU
gettruncates
table chf.objce_t;

target

edit params ./GLOBALS
CHECKPOINTTABLE ogg.chkpoint
ADD CHECKPOINTTABLE ogg.chkpoint
add replicat repl exttrail /u01/ogg/dirdat/rl,begin now,checkpointtable ogg.chkpoint
edit repl
replicat repl
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
userid ogg,password xifenfei
assumetargetdefs
reperror default,discard
discardfile /tmp/repsz.dsc,append,megabytes 100
gettruncates
map chf.objce_t, target chf.objce_t; 

ORACLE GOLDENGATE安装配置

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORACLE GOLDENGATE安装配置

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

Oracle GoldenGate软件基于数据库日志结构变化,通过解析源端在线日志或归档日志获得数据增量,再将这些变化应用到目标数据库,从而实现源库和目标库的数据同步。下面通过一个简单的示例,详细介绍利用GoldenGate实现Oracle数据库之间的同步。
1. 安装
1.1 下载介质
GoldenGate的安装介质可以从Oracle的官网上下载。
http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html
1.2 配置GoldenGate用户
下载完成后将其拷贝到源和目标的相应位置解压完成后,即可以开始进行配置。
# su – oracle
$ mkdir /u01/ggate
$ cd /u01/ggate
$ tar xvf …….tar
注意,如果使用Oracle 11g的数据库,需要创建一个link文件。
$ ln -s /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so –
/u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so
$ vi ~/.bash_profile
添加如下的内容:
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ggate
export GGATE=/u01/app/oracle/ggate
1.3 创建目录
使用ggsci工具,创建必要的目录。
$ cd /u01/app/oracle/ggate
$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x86, 32bit (optimized), Oracle 10 on Jul 28 2010 13:24:18
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (gridcontrol) 1> create subdirs
至此,GoldenGate基本的安装完成。
Note. 此部分需要在源端和目标端完成。
2. 源数据库配置
GoldenGate主要通过抓取源端数据库重做日志进行分析,将获取的数据应用到目标端,实现数据同步。因此,为了让GoldenGate能够正常工作,源数据库需要进行一定配置。
2.1 归档模式、附加日志、强制日志

--查看
select db.LOG_MODE, db.SUPPLEMENTAL_LOG_DATA_MIN, db.FORCE_LOGGING
  from v$database db
--修改
--1)archivelog
shutdown immediate
startup mount
alter database archivelog;
alter database open;
--2)force logging
alter database force logging;
--3)supplemental log data
alter database add supplemental log data;

2.2 关闭数据库的recyblebin
alter system set recyclebin=off scope=spfile;
如果数据库是10g,需要关闭recyclebin并重启;或者手工purge recyclebin。
2.3 配置复制的DDL支持

create user ggate identified by ggate default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to ggate;
grant execute on utl_file to ggate;
@$GGATE/marker_setup.sql;
@$GGATE/ddl_setup.sql;
@$GGATE/role_setup.sql;
grant GGS_GGSUSER_ROLE to ggate;
@$GGATE/ddl_enable.sql;

2.4 创建源端和目标端的测试用户

--source
create user sender identified by oracle default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to sender;
--destination
create user receiver identified by oracle default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to receiver;

3. 配置manager
在源端和目标端分别执行下面的步骤。
3.1 创建manager
[ggate@gridcontrol gg]$ ./ggsci
GGSCI (gridcontrol) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (gridcontrol) 2> edit params mgr
PORT 7809
ggate (gridcontrol) 3> start manager
Manager started.
4. 配置源端复制队列
GGSCI (gridcontrol) 1> add extract ext1, tranlog, begin now
EXTRACT added.
GGSCI (gridcontrol) 2> add exttrail /u01/ggate/dirdat/lt, extract ext1
EXTTRAIL added.
GGSCI (gridcontrol) 3> edit params ext1
extract ext1
userid ggate@source, password oracle
rmthost centos4, mgrport 7809
rmttrail /u01/ggate/dirdat/lt
ddl include mapped objname sender.*;
table sender.*;
GGSCI (gridcontrol) 6> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 00:10:55
5. 配置目标端同步队列
5.1 在目标端添加checkpoint表
[oracle@centos4 ggate]$ ./ggsci
GGSCI (centos4) 1> edit params ./GLOBAL –添加下列内容
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
GGSCI (centos4) 2> dblogin userid ggate password ggate
Successfully logged into database.
GGSCI (centos4) 3> add checkpointtable ggate.checkpoint
Successfully created checkpoint table GGATE.CHECKPOINT.
5.2 创建同步队列
GGSCI (centos4) 4> add replicat rep1, exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint
REPLICAT added.
GGSCI (centos4) 5> edit params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggate,password ggate
discardfile /u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10
DDL
map sender.*, target receiver.*;
6. 开启同步
GGSCI (gridcontrol) 14> start extract ext1
GGSCI (gridcontrol) 15> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:05
GGSCI (centos4) 7> start replicat rep1
GGSCI (centos4) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:00