使用goldengate從mysql同步資料到oracle
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
源端 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用canal.adapter同步資料到MySQLAPTMySql
- 使用SeaTunnel從InfluxDB同步資料到DorisUX
- flinkcdc同步mysql資料到selectdbMySql
- KunlunDB 快速入門 4.0(從Oracle實時同步資料到kunlunDB)Oracle
- Oracle使用RMAN從Windows遷移資料到LinuxOracleWindowsLinux
- mysql 如何毫秒級同步資料到 elasticsearchMySqlElasticsearch
- Logstash7.6.2同步Mysql資料到ElasticSearchMySqlElasticsearch
- ogg 同步pg資料到oracle--步驟Oracle
- ORACLE(Linux版本)實時同步資料到MYSQL(Linux版本)解決方案:OGGOracleLinuxMySql
- 使用ogg 從oracle 同步mysql遇到問題記錄OracleMySql
- mysqldump從mysql遷移資料到OceanBaseMySql
- docker搭建Elasticsearch、Kibana、Logstash 同步mysql資料到ESDockerElasticsearchMySql
- 從物件儲存服務同步資料到Elasticsearch物件Elasticsearch
- 使用DataLakeAnalytics從OSS清洗資料到AnalyticDB
- Oracle GoldenGate同步服務歸檔空間維護OracleGo
- MySQL 資料主從同步MySql主從同步
- 使用laradock配置mysql主從同步MySql主從同步
- oracle 資料透過goldengate 實時同步到kafka訊息佇列中OracleGoKafka佇列
- 使用GoldenGate 遷移Oracle到PostgreSQL/LightDBGoOracleSQL
- GoldenGate實時投遞資料到大資料平臺(7)– Apache HbaseGo大資料Apache
- 使用Data Lake Analytics從OSS清洗資料到AnalyticDB
- [資料庫]MYSQL主從同步資料庫MySql主從同步
- About the Oracle GoldenGate TrailOracleGoAI
- ORACLE GoldenGate Initial LoadOracleGo
- ORACLE GoldenGate 使用技巧-容錯處理等OracleGo
- MySQL 到Oracle 實時資料同步HYXSMySqlOracle
- Kettle:Oracle多表格批量同步資料=》mysqlOracleMySql
- 使用DataX同步MaxCompute資料到TableStore(原OTS)最佳化指南
- 使用oracle的logminer同步資料Oracle
- Debezium vs OGG vs Tapdata:如何實時同步 Oracle 資料到 Kafka 訊息佇列?OracleKafka佇列
- mysql主從同步MySql主從同步
- [資料整合/資料同步] 基於資料庫增量日誌的資料同步方案 : Flink CDC/Debezium/DataX/Canal/Oracle Goldengate/Kettle/Sqoop資料庫OracleGoOOP
- Oracle GoldenGate安裝(一)OracleGo
- Oracle GoldenGate安裝(二)OracleGo
- Oracle GoldenGate安裝(三)OracleGo
- Oracle GoldenGate 18.1釋出OracleGo
- Flink同步Kafka資料到ClickHouse分散式表Kafka分散式
- Oracle GoldenGate Veridata 12.2.1.4安裝配置使用全手冊OracleGo
- 資料同步:教你如何實時把資料從 MySQL 同步到 OceanBaseMySql