使用goldengate從mysql同步資料到oracle

lsq_008發表於2016-11-01
GoMySqlOracle
1. 環境資訊

源端 migu-mysqlmaster-801259907-zflo5  資料庫mysql 5.6.34  ogg版本:12.1.2.1.0
目標端 192.168.119.6   資料庫 oracle 11.2.0.4   ogg版本:12.1.2.1.0

2.mysql安裝配置
(1) 解壓mysql安裝檔案mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz
(2)準備引數檔案
[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
basedir=/home/aspire/apps/mysql
datadir=/home/aspire/apps/mysql/data
port=3306
socket=/home/aspire/apps/mysql/tmp/mysql.sock
max_connections = 1000

table_open_cache = 128k
max_allowed_packet = 52M
binlog_cache_size = 1M
max_heap_table_size = 64M

read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 8M

query_cache_size = 64M
query_cache_limit = 2M

#做從同時給其他從做主時候必須使用，否則其他從無法同步資料 
log-slave-updates

#master slave error
slave_skip_errors = all
max_connections = 65535
binlog_format = row

(3)資料庫初始化
cd /home/aspire/apps/mysql/scripts
./mysql_install_db --user=aspire --basedir=/home/aspire/apps/mysql --datadir=/home/aspire/apps/mysql/data


(4)修改root密碼，建立aspire使用者
update user set Password = password('123456') where User='root';
flush privileges;

grant all privileges on migu_point_exchange2.* to aspire@'%' identified by 'aspire';
GRANT REPLICATION SLAVE ON *.* TO 'aspire'@'%';

3. oracle 安裝配置
(1)建立ogg使用者,並授權
create user ogg identified by ogg default tablespace users;
grant dba to ogg;

(2)建立測試表

4.ogg安裝


--源端為mysql，mysql版本的ogg直接解壓到安裝目錄下即可。
--源端為oracle，如果無法啟動圖形介面，需要進行靜默安裝，首先準備響應檔案:
[oracle@BJ-FT-1F-119-6 response]$ grep -v "#" oggcore.rsp|grep -v "^$"

oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/home/oracle/install/ggs
START_MANAGER=false
MANAGER_PORT=7809
DATABASE_LOCATION=/apps/oracle/product/11.2.0
INVENTORY_LOCATION=/apps/oraInventory
UNIX_GROUP_NAME=

--建立安裝目錄
mkdir home/oracle/install/ggs

--執行靜默安裝
cd fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller -silent -responseFile /home/oracle/install/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

5.源端ogg配置

(1) 建立子目錄
./ggsci
create subdirs

(2) 配置manager程式
./ggsci

dblogin sourcedb test,userid root,password 123456

edit param mgr

port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5

start mgr

info mgr
info all

(3) 配置extract程式
edit param ggext

extract ggext
setenv (MYSQL_HOME=”/home/aspire/apps/mysql”)
tranlogoptions altlogdest /home/aspire/apps/mysql/data/mysql-bin.index
sourcedb test@localhost:3306,userid root,password 123456
exttrail ./dirdat/e2
dynamicresolution
gettruncates
table test.t1;
table migu_point_exchange2.test;

add extract ggext,tranlog,begin now
add exttrail ./dirdat/e2,extract ggext

(4) 配置pump程式
edit params ggpump

extract ggpump
rmthost 192.168.119.6,mgrport 7809
rmttrail ./dirdat/e2
passthru
gettruncates
table test.t1;
table migu_point_exchange2.test;
 
add extract ggpump,exttrailsource ./dirdat/e2
add rmttrail ./dirdat/e2,extract ggpump

(5)異構平臺配置defgen：
edit params defgen

defsfile /home/oracle/install/ogg/dirdef/defgen.prm
sourcedb test@localhost:3306, userid root,password 123456
table test.t1;
table migu_point_exchange2.test;


./defgen  paramfile ./dirprm/defgen.prm

--建立之後將產生的defgen.prm檔案傳到目標端ogg的dirdef目錄下

(6)啟動extract和pump程式

start EXTRACT GGEXT
start EXTRACT GGPUMP


6.目標端ogg配置(oracle)
1) 建立子目錄
./ggsci
create subdirs

(2) 配置manager程式
./ggsci
edit param mgr

port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5

start mgr

info mgr
info all

(3)配置replicat程式組：
./ggsci

dblogin userid ogg password ogg
add checkpointtable ogg.checkpoint

edit param ./GLOBALS
checkpointtable ogg.checkpoint

edit param ggrep
replicat ggrep
sourcedefs ./dirdef/defgen.prm
userid ogg,password ogg
reperror default,discard
discardfile ./dirrpt/ggrep.dsc,append,megabytes 50
dynamicresolution
map test.t1, target scott.t1;
map  migu_point_exchange2.test, target scott.test;
 
add replicat ggrep,exttrail ./dirdat/e2

start replicat ggrep
info all

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-2127442/，如需轉載，請註明出處，否則將追究法律責任。

相關文章