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的機制,將資料的抽取和投遞集中在一個節點上進行管理,降低資料集中和資料複製的運維複雜性。