ogg mysql_ogg oracle to mysql

oracle 远端配置详见  上一篇  ogg to kafka

mysql端ogg安装和配置(适用于源端和目标端)

6.1源库mysql基础配置

1、开启log_bin

下[mysqld]中添加一下内容：

server_id=001

log_bin

binlog_format=row

log_bin_index=/home/mysql/mysql-bin.index

重启mysql服务

service mysqld restat

6.2在mysql用户下安装oracle goldgate

(1)如果没有mysql用户则创建mysql用户和mysql组，并创建ogg的安装目录，指定其用户和组。

[root@xxxxxx~]# useradd  mysql

[root@xxxxxx ~]# usermod -g mysql mysql

[root@xxxxxx ~]# mkdir /ogg

[root@xxxxxx ~]# chown -R mysql:mysql /ogg

[root@xxxxxx ~]# chmod -R 775 /ogg

[root@xxxxxx ~]# su - mysql

(2)将goldengate文件使用tar指令解压到/ogg目录下。

tar xvf ggs_Linux_x64_MySQL_64bit.tar

(3)然后在ogg目录下创建subdirs目录

[mysql@xxxxxx ~]cd /ogg

[mysql@xxxxxx ogg]./ggsci

[mysql@xxxxxx ogg]create subdirs

[mysql@xxxxxx ogg]exit

(4)在mysql中，将需要复制的原表或者目标表访问权限赋予指定用户

mysql>create user 'oggadmin @'%' identified by '123456';

mysql>grant all privileges on *.* to ' oggadmin'@'%';

mysql>flush privileges;

(5)配置启动管理进程(源端和目标端都适用)

GGSCI (hadooptest05) 2>  edit params mgr

port 7809

dynamicportlist 7840-7939

autorestart er *, retries 5, waitminutes 3

purgeoldextracts /ogg/dirdat/*,usecheckpoints, minkeepdays 2

GGSCI (hadooptest05) 3> start mgr

Manager started.

(6)配置并启动抽取进程(源端)

GGSCI (hadooptest05) 4> edit param extr

extract extr

setenv(MYSQL_HOME="/var/lib/mysql")

sourcedb test@localhost:3306 userid oggadmin password 123456

exttrail /ogg/dirdat/me

discardfile  /ogg/dirrpt/extr.dsc,append

TranLogOptions AltLogDest /var/lib/mysql/node1-bin.index

dynamicresolution

gettruncates

table test.*;

GGSCI (hadooptest05) 5> add extract extr,tranlog,begin now

GGSCI (hadooptest05) 6> add exttrail /ogg/dirdat/me,extract extr

GGSCI (hadooptest05) 7> start extr

(7)配置并启动传递进程(源端)

GGSCI (hadooptest05) 8> edit param pump

extract pump

passthru

sourcedb test@localhost:3306 userid oggadmin password 123456

rmthost  192.168.88.133,mgrport 7809,compress

rmttrail  /ogg/dirdat/mp

dynamicresolution

numfiles 3000

table test.*;

GGSCI (hadooptest05) 9> add extract pump,exttrailsource /ogg/dirdat/me

GGSCI (hadooptest05) 10> add rmttrail /ogg/dirdat/mp,extract pump

GGSCI (hadooptest05) 11> start pump

(8)创建表定义文件

创建参数文件:

GGSCI (hadooptest05) 10>  edit param defgen

defsfile ./dirdef/defgen.def

sourcedb test@192.168.88.132:3306 userid oggadmin password 123456

table test.*;

生成表定义文件

./defgen paramfile ./dirprm/defgen.prm

传至目标库目录

scp /ogg/dirdef/defgen.def mysql@192.168.7.221:/ogg/dirdef/

6.3目标端mysql数据库配置ogg进程

(1)和源端一样创建mysql用户和安装ogg。

(2)配置管理进程

GGSCI (db) 3>  edit params mgr

port 7809

dynamicportlist 7840-7939

autorestart er *, retries 5, waitminutes 3

purgeoldextracts /ogg/dirdat/*,usecheckpoints, minkeepdays 2

GGSCI (db) 4> start mgr

Manager started.

(3)添加检查点

报错：

GGSCI (node2.test.com) 5> dblogin sourcedb test@localhost:3306 userid oggadmin password 123456

2019-02-23 22:41:53  WARNING OGG-00769  MySQL Login failed: . SQL error (2002). Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).

ERROR: Failed to connect to MySQL database engine for HOST localhost, DATABASE test, USER oggadmin, PORT 3306.

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

GGSCI (db) 5>  dblogin sourcedb test@localhost:3306 userid oggadmin password 123456

GGSCI (db DBLOGIN as ogg) 6>  add checkpointtable test.checkpoint

GGSCI (db DBLOGIN as ogg) 7>  edit params ./GLOBALS

checkpointtable desttest.checkpoint

checkpointtable test.checkpoint

(4)配置复制进程

GGSCI (db) 1>  edit params rep

replicat rep

targetdb test@localhost:3306 userid oggadmin password 123456

handlecollisions

sourcedefs /ogg/dirdef/ defgen.def

discardfile /ogg/dirrpt/mcr1.dsc,purge

map test.sourcetb, target test.targettb;

GGSCI (db) 2> add replicat rep,exttrail /ogg/dirdat/mp,checkpointtable test.checkpoint

GGSCI (db) 3> start replicat rep

rep配置

replicat rep

setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)

targetdb test@localhost:3306 userid oggadmin password 123456

sourcedefs /ogg/dirdef/defgen.def

DISCARDFILE /ogg/dirrpt/rep.dsc, APPEND, MEGABYTES 1024

GETUPDATEBEFORES

allownoopupdates

INSERTUPDATES

INSERTDELETES

INSERTALLRECORDS

INSERTMISSINGUPDATES

HANDLECOLLISIONS

map test.sourcetb, target test.targettb,COLMAP(USEDEFAULTS,ext_date_time=@GETENV('GGHEADER','COMMITTIMESTAMP'),ext_ogg_seq=0);