CentOS7部署Oracle GoldenGate for MySQL 19.1(远程抽取)

1、配置MySQL参数文件并重启MySQL

####修改MySQL配置文件
[root@schj ~]# vi /etc/my.cnf
server-id=1
log-bin=/usr/local/mysql/data/log-bin
log-bin-index=/usr/local/mysql/data/log-bin.index
binlog_format=ROW

####重启MySQL
[root@schj ~]# systemctl restart mysqld

2、添加Oracle GoldenGate 的数据库用户并给予权限

mysql> CREATE USER 'ggadm'@'192.168.253.131' IDENTIFIED BY 'ggadm'; 
mysql> GRANT INSERT,UPDATE,DELETE,CREATE,EXECUTE,SELECT ON employees.* TO 'ggadm'@'192.168.253.20’ IDENTIFIED BY 'ggadm';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'ggadm'@'192.168.253.20' IDENTIFIED BY 'ggadm';
mysql> FLUSH PRIVILEGES;

3、安装Oracle GoldenGate

3.1、解压安装包

unzip 191003_ggs_Linux_x64_MySQL_64bit.zip
mkdir ogg_mysql 
tar -xvf ggs_Linux_x64_MySQL_64bit.tar -C  ../ogg_mysql
cd ogg_mysql
ldd ggsci
    linux-vdso.so.1 =>  (0x00007ffdb7b8c000)
    librt.so.1 => /lib64/librt.so.1 (0x00007fc413405000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007fc413201000)
    libgglog.so => /home/oracle/soft/ogg_mysql/./libgglog.so (0x00007fc412e6c000)
    libggutil.so => /home/oracle/soft/ogg_mysql/./libggutil.so (0x00007fc412c4c000)
    libggrepo.so => /home/oracle/soft/ogg_mysql/./libggrepo.so (0x00007fc412968000)
    libdb-6.1.so => /home/oracle/soft/ogg_mysql/./libdb-6.1.so (0x00007fc412552000)
    liblmdb.so => /home/oracle/soft/ogg_mysql/./liblmdb.so (0x00007fc41233d000)
    libggperf.so => /home/oracle/soft/ogg_mysql/./libggperf.so (0x00007fc412106000)
    libggparam.so => /home/oracle/soft/ogg_mysql/./libggparam.so (0x00007fc4108f5000)
    libicui18n.so.62 => /home/oracle/soft/ogg_mysql/./libicui18n.so.62 (0x00007fc410410000)
    libicuuc.so.62 => /home/oracle/soft/ogg_mysql/./libicuuc.so.62 (0x00007fc41000f000)
    libicudata.so.62 => /home/oracle/soft/ogg_mysql/./libicudata.so.62 (0x00007fc40dff7000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fc40dddb000)
    libxerces-c-3.2.so => /home/oracle/soft/ogg_mysql/./libxerces-c-3.2.so (0x00007fc40d767000)
    libantlr3c.so => /home/oracle/soft/ogg_mysql/./libantlr3c.so (0x00007fc40d54c000)
    libmysqlclient.so.21 => /home/oracle/soft/ogg_mysql/./libmysqlclient.so.21 (0x00007fc40ccbd000)
    libggnnzitp.so => /home/oracle/soft/ogg_mysql/./libggnnzitp.so (0x00007fc40c1ea000)
    libstdc++.so.6 => /home/oracle/soft/ogg_mysql/./libstdc++.so.6 (0x00007fc40be53000)
    libm.so.6 => /lib64/libm.so.6 (0x00007fc40bb51000)
    libgcc_s.so.1 => /home/oracle/soft/ogg_mysql/./libgcc_s.so.1 (0x00007fc40b93b000)
    libc.so.6 => /lib64/libc.so.6 (0x00007fc40b56e000)
    /lib64/ld-linux-x86-64.so.2 (0x00007fc413d41000)
    libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fc40b354000)
    libssl.so.10 => /lib64/libssl.so.10 (0x00007fc40b0e2000)
    libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007fc40ac81000)
    libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007fc40aa34000)
    libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007fc40a74b000)
    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007fc40a547000)
    libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007fc40a32c000)
    libz.so.1 => /lib64/libz.so.1 (0x00007fc40a116000)
    libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007fc409f07000)
    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007fc409d03000)
    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fc409aea000)
    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007fc4098c3000)
    libpcre.so.1 => /lib64/libpcre.so.1 (0x00007fc409661000)

3.2、创建相关目录

./ggsci
GGSCI (schj) 1> create subdirs

Creating subdirectories under current directory /home/oracle/soft/ogg_mysql

Parameter file                 /home/oracle/soft/ogg_mysql/dirprm: created.
Report file                    /home/oracle/soft/ogg_mysql/dirrpt: created.
Checkpoint file                /home/oracle/soft/ogg_mysql/dirchk: created.
Process status files           /home/oracle/soft/ogg_mysql/dirpcs: created.
SQL script files               /home/oracle/soft/ogg_mysql/dirsql: created.
Database definitions files     /home/oracle/soft/ogg_mysql/dirdef: created.
Extract data files             /home/oracle/soft/ogg_mysql/dirdat: created.
Temporary files                /home/oracle/soft/ogg_mysql/dirtmp: created.
Credential store files         /home/oracle/soft/ogg_mysql/dircrd: created.
Masterkey wallet files         /home/oracle/soft/ogg_mysql/dirwlt: created.
Dump files                     /home/oracle/soft/ogg_mysql/dirdmp: created.

4、添加进程并配置进程参数

4.1、测试能否登录MySQL

GGSCI (schj) 2> dblogin sourcedb employees@192.168.253.60,userid ggadm,password ggadm
Successfully logged into database.

4.2、添加管理进程并配置参数

GGSCI (schj DBLOGIN as ggadm) 3> edit params mgr
port 17809
DYNAMICPORTLIST  17810-17820
--AUTOSTART ER  *
--AUTORESTART ER *,RETRIES 5,WAITMINUTES 7, RESETMINUTES 60
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7

LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
####启动管理进程
GGSCI (schj DBLOGIN as ggadm) 5> start mgr

4.3、添加抽取进程并配置参数

GGSCI (schj DBLOGIN as ggadm) 7> add extract e1,tranlog,begin now
EXTRACT added.

GGSCI (schj DBLOGIN as ggadm) 8> add exttrail ./dirdat/ee,extract e1
EXTTRAIL added.
GGSCI (schj DBLOGIN as ggadm) 9> edit params e1

GGSCI (schj DBLOGIN as ggadm) 10> view params e1

extract e1
sourcedb employees@192.168.253.20,userid ggadm, password ggadm
exttrail ./dirdat/e1
--远程抽取需要配置
TRANLOGOPTIONS ALTLOGDEST REMOTE
dynamicresolution
gettruncates
UPDATERECORDFORMAT FULL
REPORTCOUNT EVERY 2 SECONDS, RATE
--端口不是3306需要设置
--DBOPTIONS CONNECTIONPORT 3307
--表无主键或者唯一键
--DBOPTIONS LIMITROWS
table employees.*;

####启动抽取进程
GGSCI (schj) 3> start e1
Sending START request to MANAGER ...
EXTRACT E1 starting

4.4、添加投递进程并配置参数

GGSCI (schj) 7> add extract pump,exttrailsource ./dirdat/e1              
EXTRACT added.
GGSCI (schj) 8> add rmttrail /data/ogg/dirdat/e1,extract pump,megabytes 200              
RMTTRAIL added.
GGSCI (schj) 10> edit params pump
GGSCI (schj) 11> view params pump

extract pump
passthru
-- enable compress
rmthost 192.168.253.20,mgrport 7839,compress
rmttrail /data/ogg/dirdat/e1
table employees.*;
#####启动投递进程
start pump

注:

1、抽取进程用户在MySQL的配置文件所在的目录具有可读和可执行权限;
2、抽取进程用户对MySQL配置文件拥有可读权限;
3、抽取进程用户对bin-log日志所在的目录具有可读和可执行权限;
4、抽取进程对tmp目录具有可读和可执行权限。
上一篇:使用原生js自定义内置标签


下一篇:oracle goldengate for downstreams 配置--for 数据库参数配置和ogg进程参数配置