OGG 18.1 for mysql遠端捕獲測試

margiex發表於2018-11-06

Ogg18.1 remote capture要求mysql5.7版本,只能從linux遠端捕獲mysql on windows or linux,且不支援DDL捕獲。支援遠端mysqlcommunity/commerical版本。

 

源端資料庫配置

OGG登入DB的使用者及密碼: ogg/Welcome!23

 

確保源庫有開啟日誌

修改my.cnf,開啟日誌存放路徑,並設定格式

[mysqld]

log-bin=/var/log/mysql/mysql-bin.log

確保執行mysql的使用者有權訪問這個目錄.

重啟mysqld之後,可以看到生成的日誌

image

 

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.

 

檢視佇列檔案,已經有捕獲到新增資料

image

可以看到,通過遠端捕獲配置,goldengate仍然可以從mysql db中獲取增量資料。

 

通過以上的配置,可以在一臺linux 節點上,安裝多個goldengate的介質,比如 OGG for oracle, ogg for mysql, ogg for bigdata等,從而實現類似HUB的機制,將資料的抽取和投遞集中在一個節點上進行管理,降低資料集中和資料複製的運維複雜性。

 

 

相關文章