學習重點
- Prepare your environment to configure the GoldenGate processes
- Configure and execute the initial data load
- Configure and start the change capture of database operations
- Configure and start the change delivery of database operations
系統需求
- Ubunt 13.03 x 2 - 預設安裝 MySQL與SSH Server
- Oracle GoldenGate 121200
環境準備
- 分別安裝 Oracle GoldenGate 12g for MySQL 於來源資料庫與目標資料庫
- 安裝 MySQL 資料庫
注意事項一：因ogg需要有讀取mysql socket的權限，安裝Oracle GoldenGate必需用mysql帳號
注意事項二：ggsci的指令大小寫沒有差異
設定來源資料庫
設定並啟動Manager Processes
1. 下載ggs安裝包並解壓縮
$ mkdir ggs $ cd ggs $ unzip 121200_ggs_Linux_x64_MySQL_64bit.zip $ tar xvf ggs_Linux_x64_MySQL_64bit.tar
2. 設定Manager Process
$ ./ggsci GGSCI> edit param mgr加入以下內容
YNAMICPORTLIST 7000-9000 DYNAMICPORTREASSIGNDELAY 5 PORT 7809
3. 啟動Manager
GGSCI> start mgr
確認Manager是否有正常啟動
GGSCI> info mgr
設定MySQL資料庫
$ sudo vi /etc/mysql/my.cnf
加入以下設定
[mysqld] log-bin=/var/lib/mysql/mysql-bin max_binlog_size=4096 binlog_format=row socket=/var/run/mysqld/mysqld.sock
[client] socket=/var/run/mysqld/mysqld.sock
重新啟動MySQL服務
$ sudo service mysql restart
建立測試資料
執行以下步驟建立ogg提供的範例資料表，TCUSTMER與TCUSTORD
$ mysql test –uroot –p < demo_mysql_create.sql $ mysql test –uroot –p < demo_mysql_insert.sql mysql> use test; mysql> describe TCUSTMER; mysql> exit
設定Change Capture
建立Extract群組
利用add指令在來源資料庫新增名稱為EMSQ1的extract 群組
GGSCI> add extract EMSQ1, tranlog, begin now
確認新增結果
GGSCI> info extract EMSQ1
建立Extract參數
在來源資料庫執行以下指令
GGSCI> edit param EMSQ1
EXTRACT EMSQ1 DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306 SOURCEDB test, USERID root, PASSWORD Oracle123 RMTHOST 192.168.47.129, MGRPORT 7809 RMTTRAIL ./dirdat/ca TRANLOGOPTIONS ALTLOGDEST /var/lib/mysql/mysql-bin.index TABLE test.TCUSTMER; TABLE test.TCUSTORD;
TABLE test.TCUSTORD;
建立GoldenGate Trail
在來源資料庫執行以下指令
GGSCI> add rmttrail ./dirdat/ca, extract EMSQ1, megabytes 5
確認結果
GGSCI> info rmttrail *
啟動Extract Process
GGSCI> start extract EMSQ1
確認啟動結果
GGSCI> info extract EMSQ1, detail
GGSCI> view report EMSQ1
設定目標資料庫
設定並啟動Manager Processes
下載ggs安裝包並解壓縮
mkdir ggs cd ggs unzip 121200_ggs_Linux_x64_MySQL_64bit.zip tar xvf ggs_Linux_x64_MySQL_64bit.tar
設定Manager Process
$ ./ggsci
GGSCI> edit param mgr
YNAMICPORTLIST 7000-9000 DYNAMICPORTREASSIGNDELAY 5 PORT 7809
啟動Manager
GGSCI> start mgr
確認Manager是否有正常啟動
GGSCI> info mgr
設定MySQL資料庫
$ sudo vi /etc/mysql/my.cnf
加入以下設定
[mysqld] log-bin=/var/lib/mysql/mysql-bin max_binlog_size=4096 binlog_format=row socket=/var/run/mysqld/mysqld.sock [client] socket=/var/run/mysqld/mysqld.sock
重新啟動MySQL服務
$ sudo service mysql restart
建立測試資料
$ mysql test –uroot –p < demo_mysql_create.sql $ mysql test –uroot –p < demo_mysql_insert.sql mysql> use test; mysql> describe TCUSTMER; mysql> exit
設定Change Delivery
The goals of this exercise are to:
Set up the checkpoint table on the target system.
Create a named group that includes the Replicat process and the checkpoint tables.
Configure the Replicat group by adding parameters.
Start the Replicat group.
建立Checkpoint table
在目標資料庫建立GLOBALS參數檔
$ cd ggs $ ./ggsci GGSCI> edit param ./GLOBALS
CHECKPOINTTABLE test.CHKPTAB
啟用GLOBALS參數
GGSCI> exit
建立Replica checkpoint table
在目標資料庫建立以下ggsci指令
$ cd ggs $ ./ggsci GGSCI> dblogin sourcedb test, userid root, password oracle123 GGSCI> add checkpointtable
4. 建立Replicat Group
GGSCI> add replicat RMSQ1, exttrail ./dirdat/ca
5. 建立Replicat參數檔
GGSCI> edit param RMSQ1
REPLICAT RMSQ1 DBOPTIONS HOST 192.168.47.129 CONNECTIONPORT 3306 TARGETDB test, USERID root, PASSWORD Oracle123 HANDLECOLLISIONS ASSUMETARGETDEFS DISCARDFILE ./dirrpt/RMSQ1.DSC, PURGE MAP test.TCUSTMER, TARGET test.TCUSTMER; MAP test.TCUSTORD, TARGET test.TCUSTORD;
6. 啟動Replicat Process
GGSCI> start replicat RMSQ1
確定啟動結果
GGSCI> info replicat RMSQ1
發表文章
