OGG 12c MySQL to MySQL
安裝包下載
下載最新的OGG FOR MySQL,這裡使用的是
ggs_121210_Linux_x64_MySQL_64bit.zip
官方下載地址:
source和target端的OGG安裝
建立OGG主目錄
mkdir /ogg
解壓安裝包,會得到一個tar檔案
unzip ggs_121210_Linux_x64_MySQL_64bit.zip
將tar檔案移至OHGG主目錄,並解壓即可
mv ggs_Linux_x64_MySQL_64bit.tar /ogg/
tar xvf ggs_Linux_x64_MySQL_64bit.tar
修改環境變數
vi ~/.bash_profile
加入如下三個變數
export GGHOME=/ogg
export PATH=$PATH:$GGHOME
export LD_LIBRARY_PATH=/ogg:$LD_LIBRARY_PATH
登入OGG建立OGG的相關目錄
[root@20_138-Mysql_Test1 ogg]# ./ggsci
Oracle GoldenGate Command Interpreter FOR MySQL
VERSION 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Linux, x64, 64BIT (optimized), MySQL Enterprise ON Sep 20 2014 03:43:22
Operating system CHARACTER SET IDENTIFIED AS UTF-8.
Copyright (C) 1995, 2014, Oracle AND/OR its affiliates. ALL rights reserved.
GGSCI (20_138-Mysql_Test1) 1> CREATE subdirs
Creating subdirectories under current DIRECTORY /ogg
Parameter files /ogg/dirprm: already EXISTS
Report files /ogg/dirrpt: created
Checkpoint files /ogg/dirchk: created
PROCESS STATUS files /ogg/dirpcs: created
SQL script files /ogg/dirsql: created
DATABASE definitions files /ogg/dirdef: created
EXTRACT DATA files /ogg/dirdat: created
TEMPORARY files /ogg/dirtmp: created
Credential store files /ogg/dircrd: created
Masterkey wallet files /ogg/dirwlt: created
Dump files /ogg/dirdmp: created
配置MGR
GGSCI (20_138-Mysql_Test1) 2> dit params mgr
PORT 7809
配置SOURCE端
GGSCI (20_138-Mysql_Test1) 4> dblogin sourcedb oggtest@localhost:3306,userid root,PASSWORD test123
Successfully logged INTO database.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 4> ADD EXTRACT e1,tranlog,BEGIN NOW
EXTRACT added.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 6> ADD exttrail /ogg/dirdat/e1,EXTRACT e1
EXTTRAIL added.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 7> edit params e1
EXTRACT e1
setenv (MYSQL_HOME="/var/lib/mysql")
tranlogoptions altlogdest /var/lib/mysql/mysql-bin.index
sourcedb oggtest@localhost:3306,userid root,PASSWORD test123
exttrail /ogg/dirdat/e1
dynamicresolution
gettruncates
TABLE oggtest.*;
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 20> ADD EXTRACT p1,exttrailsource /ogg/dirdat/e1
EXTRACT added.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 21> ADD rmttrail /ogg/dirdat/r1,EXTRACT p1
RMTTRAIL added.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 22> edit param p1
EXTRACT p1
rmthost 192.168.20.139,mgrport 7809
rmttrail /ogg/dirdat/r1
passthru
gettruncates
TABLE oggtest.*;
配置TARGET端
GGSCI (20_139-Mysql_Test2) 3> dblogin sourcedb oggtest@localhost:3306,userid root,PASSWORD work123
Successfully logged INTO database.
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 4> ADD checkpointtable oggtest.checkpoint
Successfully created checkpoint TABLE oggtest.checkpoint.
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 5> ADD replicat r1,exttrail /ogg/dirdat/r1,checkpointtable oggtest.checkpoint
REPLICAT added.
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 5> edit params r1
replicat r1
dboptions HOST localhost,connectionport 3306
targetdb oggtest,userid root,PASSWORD test123
assumetargetdefs
DISCARDFILE /ogg/dirrpt/r1.dsc,append,megabytes 50
MAP oggtest.*,target oggtest.*;
初始化資料
主庫master-data方式匯出oggtest庫,該方式會記錄匯出時的log_file 資訊
mysqldump -uroot -p --master-DATA oggtest > oggtest.sql
開啟oggtest.sql 找到下面內容,記錄下log_file和log_pos
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=5054;
修改extract e1 從匯出的時的binlog開始抽取
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 54> ALTER EXTRACT e1,VAM,lognum 3,logpos 5054
EXTRACT altered.
開啟同步
開啟抽取程式
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 59> START e1
Sending START request TO MANAGER ...
EXTRACT E1 STARTING
開啟投遞程式
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 59> START P1
Sending START request TO MANAGER ...
EXTRACT P1 STARTING
target端開啟應用程式
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 5> START r1
Sending START request TO MANAGER ...
EXTRACT R1 STARTING
觀察程式狀態
SOURCE端
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 63> info ALL
Program STATUS GROUP Lag AT Chkpt TIME Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E1 00:00:00 00:00:03
EXTRACT RUNNING P1 00:00:00 00:00:07
TARGET端
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 40> info ALL
Program STATUS GROUP Lag AT Chkpt TIME Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R1 00:00:00 00:00:09
主庫插入資料,觀察ogg日誌資訊,備庫檢視是否同步,查錯拍錯
下載最新的OGG FOR MySQL,這裡使用的是
ggs_121210_Linux_x64_MySQL_64bit.zip
官方下載地址:
source和target端的OGG安裝
建立OGG主目錄
mkdir /ogg
解壓安裝包,會得到一個tar檔案
unzip ggs_121210_Linux_x64_MySQL_64bit.zip
將tar檔案移至OHGG主目錄,並解壓即可
mv ggs_Linux_x64_MySQL_64bit.tar /ogg/
tar xvf ggs_Linux_x64_MySQL_64bit.tar
修改環境變數
vi ~/.bash_profile
加入如下三個變數
export GGHOME=/ogg
export PATH=$PATH:$GGHOME
export LD_LIBRARY_PATH=/ogg:$LD_LIBRARY_PATH
登入OGG建立OGG的相關目錄
[root@20_138-Mysql_Test1 ogg]# ./ggsci
Oracle GoldenGate Command Interpreter FOR MySQL
VERSION 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Linux, x64, 64BIT (optimized), MySQL Enterprise ON Sep 20 2014 03:43:22
Operating system CHARACTER SET IDENTIFIED AS UTF-8.
Copyright (C) 1995, 2014, Oracle AND/OR its affiliates. ALL rights reserved.
GGSCI (20_138-Mysql_Test1) 1> CREATE subdirs
Creating subdirectories under current DIRECTORY /ogg
Parameter files /ogg/dirprm: already EXISTS
Report files /ogg/dirrpt: created
Checkpoint files /ogg/dirchk: created
PROCESS STATUS files /ogg/dirpcs: created
SQL script files /ogg/dirsql: created
DATABASE definitions files /ogg/dirdef: created
EXTRACT DATA files /ogg/dirdat: created
TEMPORARY files /ogg/dirtmp: created
Credential store files /ogg/dircrd: created
Masterkey wallet files /ogg/dirwlt: created
Dump files /ogg/dirdmp: created
配置MGR
GGSCI (20_138-Mysql_Test1) 2> dit params mgr
PORT 7809
配置SOURCE端
GGSCI (20_138-Mysql_Test1) 4> dblogin sourcedb oggtest@localhost:3306,userid root,PASSWORD test123
Successfully logged INTO database.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 4> ADD EXTRACT e1,tranlog,BEGIN NOW
EXTRACT added.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 6> ADD exttrail /ogg/dirdat/e1,EXTRACT e1
EXTTRAIL added.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 7> edit params e1
EXTRACT e1
setenv (MYSQL_HOME="/var/lib/mysql")
tranlogoptions altlogdest /var/lib/mysql/mysql-bin.index
sourcedb oggtest@localhost:3306,userid root,PASSWORD test123
exttrail /ogg/dirdat/e1
dynamicresolution
gettruncates
TABLE oggtest.*;
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 20> ADD EXTRACT p1,exttrailsource /ogg/dirdat/e1
EXTRACT added.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 21> ADD rmttrail /ogg/dirdat/r1,EXTRACT p1
RMTTRAIL added.
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 22> edit param p1
EXTRACT p1
rmthost 192.168.20.139,mgrport 7809
rmttrail /ogg/dirdat/r1
passthru
gettruncates
TABLE oggtest.*;
配置TARGET端
GGSCI (20_139-Mysql_Test2) 3> dblogin sourcedb oggtest@localhost:3306,userid root,PASSWORD work123
Successfully logged INTO database.
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 4> ADD checkpointtable oggtest.checkpoint
Successfully created checkpoint TABLE oggtest.checkpoint.
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 5> ADD replicat r1,exttrail /ogg/dirdat/r1,checkpointtable oggtest.checkpoint
REPLICAT added.
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 5> edit params r1
replicat r1
dboptions HOST localhost,connectionport 3306
targetdb oggtest,userid root,PASSWORD test123
assumetargetdefs
DISCARDFILE /ogg/dirrpt/r1.dsc,append,megabytes 50
MAP oggtest.*,target oggtest.*;
初始化資料
主庫master-data方式匯出oggtest庫,該方式會記錄匯出時的log_file 資訊
mysqldump -uroot -p --master-DATA oggtest > oggtest.sql
開啟oggtest.sql 找到下面內容,記錄下log_file和log_pos
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=5054;
修改extract e1 從匯出的時的binlog開始抽取
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 54> ALTER EXTRACT e1,VAM,lognum 3,logpos 5054
EXTRACT altered.
開啟同步
開啟抽取程式
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 59> START e1
Sending START request TO MANAGER ...
EXTRACT E1 STARTING
開啟投遞程式
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 59> START P1
Sending START request TO MANAGER ...
EXTRACT P1 STARTING
target端開啟應用程式
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 5> START r1
Sending START request TO MANAGER ...
EXTRACT R1 STARTING
觀察程式狀態
SOURCE端
GGSCI (20_138-Mysql_Test1 DBLOGIN AS root) 63> info ALL
Program STATUS GROUP Lag AT Chkpt TIME Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E1 00:00:00 00:00:03
EXTRACT RUNNING P1 00:00:00 00:00:07
TARGET端
GGSCI (20_139-Mysql_Test2 DBLOGIN AS root) 40> info ALL
Program STATUS GROUP Lag AT Chkpt TIME Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R1 00:00:00 00:00:09
主庫插入資料,觀察ogg日誌資訊,備庫檢視是否同步,查錯拍錯
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26870952/viewspace-2125182/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- OGG 12c mysql複製到oracle部署方案MySqlOracle
- OGG_mysql 12c複製到OGG_oracle 11g部署方案MySqlOracle
- LINUX 環境 mysql to mysql OGG安裝配置(二)LinuxMySql
- 配置ogg異構oracle到mysqlOracleMySql
- ogg12 mysql to oracle 單向同步MySqlOracle
- 配置OGG到MYSQL複製時登陸MYSQL報錯解決MySql
- ORACLE EM 12C 監控MySQLOracleMySql
- MySQL + Oracle GoldenGate + OGG Application AdpaterMySqlOracleGoAPP
- OGG 18.1 for mysql遠端捕獲測試MySql
- 使用ogg 從oracle 同步mysql遇到問題記錄OracleMySql
- 藉助ogg完成oracle到mysql的資料遷移OracleMySql
- OGG Oracle 分割槽壓縮表 到 MySQL分表的實現OracleMySql
- Oracle Goldengate(ogg) 12c認證考試流程OracleGo
- ORACLE(Linux版本)實時同步資料到MYSQL(Linux版本)解決方案:OGGOracleLinuxMySql
- 【Mysql】MySQL管理工具MySQL UtilitiesMySql
- MySQL - 初識MySQLMySql
- 【MySQL】MySQL基礎MySql
- 【MySQL】MySQL 5.7 初探MySql
- mysql 索引( mysql index )MySql索引Index
- 【MySQL】mysql optimize tableMySql
- 「MySQL」 MySQL執行流程MySql
- MySQL入門--mysql命令MySql
- MySQL入門--MySQL安全MySql
- 【Mysql】修改mysql時區MySql
- 【MySQL】MySQL中的鎖MySql
- [mysql]ubuntu安裝mysqlMySqlUbuntu
- MySQL(六):MySQL之MVCCMySqlMVC
- Warning:The /usr/local/mysql/data directory is not owned by the 'mysql' or '_mysql'MySql
- MySQL入門系列:MySQL概述MySql
- mysql + nodejs mysql篇(2)MySqlNodeJS
- 【MySQL(2)| MySQL索引機制】MySql索引
- 重返MySQL之MySQL基礎MySql
- 安裝mysql和mysql workbenchMySql
- 【MySQL】九、MySQL與IO.MySql
- Mysql入門【Mysql約束】MySql
- MYSQL學習(二) --MYSQL框架MySql框架
- 【MYSQL】Mysql常用檢查sqlMySql
- MySQL(二) MySql常用優化MySql優化