OGG 18.1 for mysql远程捕获测试
Ogg18.1 remote capture要求mysql为5.7版本，只能从linux远程捕获mysql on windows or linux，且不支持DDL捕获。支持远程mysql为community/commerical版本。
源端数据库配置
OGG登录DB的用户及密码： ogg/Welcome!23
确保源库有打开日志
修改my.cnf，打开日志存放路径，并设置格式
[mysqld]
log-bin=/var/log/mysql/mysql-bin.log
确保运行mysql的用户有权访问这个目录.
重启mysqld之后，可以看到生成的日志
mysql中查看
mysql>show variables like 'log_%';
mysql> show master logs;
+------------------------+-----------+
| Log_name | File_size |
+------------------------+-----------+
| mysqlcs-mysql-1.000001 | 177 |
| mysqlcs-mysql-1.000002 | 7713925 |
+------------------------+-----------+
2 rows in set (0.00 sec)
查看日志内容
mysqlbinlog mysqlcs-mysql-1.000001
源DB准备
创建testdb并授权
create database testdb;
CREATE USER 'ogg'@'192.168.1.53' IDENTIFIED BY 'Welcome!23';
GRANT ALL PRIVILEGES ON *.* TO 'ogg'@'192.168.1.53' WITH GRANT OPTION;
FLUSH PRIVILEGES;
创建测试表
create table t1 (id int primary key, name varchar(50));
create table t2_tgt (id int primary key, name varchar(50));
create table t3 (id int, name varchar(50));
OGG抽取配置
远程捕获不支持DDL同步。
确认OGG能远程连接到mysql
GGSCI > dblogin sourcedb testdb@192.168.1.52, userid ogg, password Welcome!23
Successfully logged into database.
GGSCI (DBLOGIN as root) 10>
Mgr.prm
Port 7809
ex1.prm
extract ex1
sourcedb testdb@192.168.1.52, userid ogg, password Welcome!23
TRANLOGOPTIONS ALTLOGDEST REMOTE
exttrail ./dirdat/ea
--ddl include mapped
table testdb.*;
GGSCI (DBLOGIN as root) 10> add ext ex1, tranlog, begin now
EXTRACT added.
GGSCI (DBLOGIN as root) 11> add exttrail ./dirdat/ea, ext ex1
EXTTRAIL added.
确认进程正常启动
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX1 10:43:02 00:00:07
GGSCI (dbcs12c) 2> info ex1
EXTRACT EX1 Last Started 2018-11-05 21:25 Status RUNNING
Checkpoint Lag 10:43:02 (updated 00:00:09 ago)
Process ID 19857
VAM Read Checkpoint 2018-11-05 23:17:39.000000
Log Number: 2
Record Offset: 7713925
已经可以读取日志
测试
Mysql插入数据
mysql> insert into testdb.t1 values(1,'bcs');
Query OK, 1 row affected (0.01 sec)
源DB上插入、更新、删除记录，抽取进程可正常捕获：
GGSCI (dbcs12c) 3> stats ex1, total
Sending STATS request to EXTRACT EX1 ...
Start of Statistics at 2018-11-06 10:01:27.
Output to ./dirdat/ea:
Extracting from testdb.t1 to testdb.t1:
*** Total statistics since 2018-11-05 21:35:34 ***
Total inserts 2.00
Total updates 4.00
Total deletes 1.00
Total discards 0.00
Total operations 7.00
Extracting from testdb.t3 to testdb.t3:
*** Total statistics since 2018-11-05 21:35:34 ***
Total inserts 1.00
Total updates 2.00
Total deletes 0.00
Total discards 0.00
Total operations 3.00
End of Statistics.
查看队列文件，已经有捕获到新增数据
可以看到，通过远程捕获配置，goldengate仍然可以从mysql db中获取增量数据。
通过以上的配置，可以在一台linux 节点上，安装多个goldengate的介质，比如 OGG for oracle, ogg for mysql, ogg for bigdata等，从而实现类似HUB的机制，将数据的抽取和投递集中在一个节点上进行管理，降低数据集中和数据复制的运维复杂性。
