Oracle數(shù)據(jù)遷移MySQL的三種簡單方法
更新時(shí)間：2023年06月05日 10:58:16 作者：牛牛的筆記
對(duì)于許多企業(yè)而言,遷移數(shù)據(jù)庫時(shí)最大的挑戰(zhàn)之一是如何從一個(gè)數(shù)據(jù)庫平臺(tái)順利遷移到另一個(gè)平臺(tái),下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)遷移MySQL的三種簡單方法,需要的朋友可以參考下
前言:
現(xiàn)今，Oracle數(shù)據(jù)遷移MySQL的需求已經(jīng)越來越普遍，主要的遷移場景大致可以分為三類，第一類是涉及小表以及少量表的一次性遷移，無需進(jìn)行增量同步，第二類是涉及大表以及多表的一次性遷移，第三類是涉及增量實(shí)時(shí)同步，而對(duì)于數(shù)據(jù)的遷移方法，常見的方式有使用第三方的同步工具CDC進(jìn)行Oracle到MySQL的數(shù)據(jù)遷移、使用開源的同步工具以及應(yīng)用層面進(jìn)行遷移同步。
基于作者的遷移實(shí)施經(jīng)驗(yàn)，本文接下來將講述三種操作相對(duì)簡單，可落地的Oracle數(shù)據(jù)遷移MySQL的方法。
測試遷移方法:
| 遷移方法 | 適合場景 | 測試場景 |
| navicat | 操作簡單，同步效率一般，適合小表的一次性遷移 | Oracle一次性同步表test.test1到MySQL下的db1.test1 |
| navicat+sqluldr+load data infile | 操作較復(fù)雜，同步效率高，適合少量大表的一次性遷移 | Oracle一次性同步表test.test1到MySQL下的db1.test1 |
| navicat+Oracle GoldenGate(OGG) | 操作復(fù)雜，同步效率較高，適合需要大批量的大表進(jìn)行遷移以及需要實(shí)時(shí)增量同步 | Oracle全量+實(shí)時(shí)增量同步表test.test1,test2到MySQL下的db1.test1,test2 |
遷移方式一(navicat)
這種遷移方式主要適合小表的一次性遷移，navicat的同步效率速度一般。
遷移開始之前，我們需要先安裝navicat，在一臺(tái)能訪問Oracle源端和MySQL目標(biāo)端的機(jī)器上安裝即可。
安裝完navicat之后，配置源端Oracle連接
配置目標(biāo)端MySQL連接
開始進(jìn)行遷移，選擇工具--->數(shù)據(jù)傳輸
選擇源端Oracle以及目標(biāo)端MySQL
下一步選擇要同步的表TEST，也可以選擇同步全部表
傳輸模式選擇自動(dòng)，點(diǎn)下一步開始進(jìn)行同步
傳輸同步完成，整個(gè)同步的效率還是較慢的，7.2W的數(shù)據(jù)，用了1分鐘。
遷移方式二(navicat+sqluldr+load data infile)
這種遷移方式主要適合少量大表的一次性遷移，通過navicat工具進(jìn)行Oracle-->MySQL表結(jié)構(gòu)轉(zhuǎn)化,再通過sqluldr將Oracle數(shù)據(jù)導(dǎo)出到本地文件，最后再通過load data infile將數(shù)據(jù)導(dǎo)入MySQL。
注:navicat工具雖然有同步數(shù)據(jù)的功能，但在實(shí)際的操作過程中，同步數(shù)據(jù)的效率以及成功率都很低，所以這里只作為數(shù)據(jù)字典轉(zhuǎn)化的工具。
使用navicat工具進(jìn)行表結(jié)構(gòu)同步，步驟可以參考遷移方式一里面的操作，主要在數(shù)據(jù)傳輸同步時(shí)，選項(xiàng)里面只同步表結(jié)構(gòu)，不創(chuàng)建記錄。
點(diǎn)擊開始，完成表結(jié)構(gòu)同步
接下來進(jìn)行數(shù)據(jù)的導(dǎo)出導(dǎo)入，先安裝導(dǎo)出工具sqluldr
---解壓安裝包 unzip sqluldr2linux64.zip ./sqluldr2linux64.bin --help ---拷貝sqluldr2linux64.bin到$ORACLE_HOME的bin目錄 cp -rp sqluldr2linux64.bin $ORACLE_HOME/bin ---重命名為sqluldr2.bin mv sqluldr2linux64.bin sqluldr2.bin
測試安裝成功
[oracle@rac19a ~]$ sqluldr2.bin --help ? SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1 (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved. ? License: Free for non-commercial useage, else 100 USD per server. ? Usage: SQLULDR2 keyword=value [,keyword=value,...] ? Valid Keywords: user = username/password@tnsname sql = SQL file name query = select statement field = separator string between fields record = separator string between records rows = print progress for every given rows (default, 1000000) file = output file name(default: uldrdata.txt) log = log file name, prefix with + to append mode fast = auto tuning the session level parameters(YES) text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH). charset = character set name of the target database. ncharset= national character set name of the target database. parfile = read command option from parameter file ? for field and record, you can use '0x' to specify hex character code, \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27
使用sqluldr導(dǎo)出為文本類型為MYSQL
sqluldr2.bin user=test/oracle@pdb1 query="select * from test" text=MYSQL field=',' charset=AL32UTF8 head='NO' file=/home/oracle/test_001.csv log=test.log
導(dǎo)出過程很快，58w的數(shù)據(jù)，只需要7秒
0 rows exported at 2022-10-12 22:18:14, size 0 MB.
583680 rows exported at 2022-10-12 22:18:21, size 108 MB.
output file /home/oracle/test_001.csv closed at 583680 rows, size 108 MB.再將從Oracle導(dǎo)出的MYSQL文件導(dǎo)入MySQL數(shù)據(jù)庫
LOAD DATA INFILE '/tmp/test_001.csv' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"';
導(dǎo)入過程很快，58w的數(shù)據(jù)，只需要13秒
test@mysql.sock 22:38: [db1]>LOAD DATA INFILE '/tmp/test_001.csv' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"'; Query OK, 583680 rows affected (13.43 sec) Records: 583680 Deleted: 0 Skipped: 0 Warnings: 0
整個(gè)數(shù)據(jù)同步過程還是較快的，但操作步驟較為繁瑣，不太適合多表操作。
遷移方式三(navicat+Oracle GoldenGate(OGG))
這種遷移方式適合大批量的大表或者需要增量同步的表進(jìn)行遷移，支持全量初始化+Oracle GoldenGate(OGG)增量同步，通過navicat工具進(jìn)行Oracle-->MySQL表結(jié)構(gòu)轉(zhuǎn)化，再通過數(shù)據(jù)同步工具OGG進(jìn)行全量表初始化以及后續(xù)的增量同步。
注意:使用增量方式同步的表都需要有主鍵，確保每行數(shù)據(jù)的唯一。
先使用navicat進(jìn)行表結(jié)構(gòu)的轉(zhuǎn)化，具體參考遷移方式二里面的步驟。
Oracle源端配置OGG準(zhǔn)備
1 數(shù)據(jù)庫開啟歸檔模式 ---查看是否開啟歸檔模式 archive log list ---開啟歸檔模式 startup mount alter database archvielog ; alter database open; ? 2 數(shù)據(jù)庫開啟force_logging ---查看是否開啟force logging select force_logging from v$database; ----開啟force logging alter database force logging; alter system switch logfile; ? 3 數(shù)據(jù)庫開啟補(bǔ)充日志supplemental logging ---查看補(bǔ)充日志 SELECT supplemental_log_data FROM v$database; ---開啟補(bǔ)充日志 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Alter system switch logfile; 4 開啟ogg參數(shù) alter system set enable_goldengate_replication=true scope=both; 5 配置stream_pool大小 (MAX_SGA_SIZE * # of integrated Extracts) + 25% head room For example, using the default values for the MAX_SGA_SIZE with two integrated Extracts: ( 1GB * 2 ) * 1.25 = 2.50GB STREAMS_POOL_SIZE = 2560M
MySQL目標(biāo)端配置OGG準(zhǔn)備
1 開啟bin_log ---確認(rèn)是否開啟bin_log show variables like 'log_bin'; 2 開啟bin_log(需要重啟生效) 在my,cnf 中 [mysqld] 添加如下 [mysqld] # binlog configuration log-bin = /usr/local/var/mysql/logs/mysql-bin.log expire-logs-days = 14 max-binlog-size = 500M server-id = 1 ? 2 確認(rèn)binlog_format ----確認(rèn)格式為row show variables like 'binlog_format'; ? 3 確認(rèn)sql_mode ----確認(rèn)包含STRICT_TRANS_TABLES show variables like 'sql_mode'; ? 4 確認(rèn)版本 ----確認(rèn)版本，5.7.10之后才支持部分DDL （CREATE TABLE, ALTER TABLE, and DROP TABLE operations are supported.） select version();
創(chuàng)建ogg同步用戶
1 oracle源端同步用戶創(chuàng)建
create tablespace ogg_tbs datafile size 1g; create user ogg identified by "oggoracle"; grant resource,dba,connect to ogg;
2 mysql目標(biāo)端同步用戶創(chuàng)建
CREATE USER ogg IDENTIFIED by "oggmysql"; GRANT ALL PRIVILEGES ON *.* TO 'ogg'@'%';
Oracle源端安裝ogg軟件
1 配置環(huán)境變量
---/home/oracle/.bash_profile export OGG_HOME=/u01/app/ogg export PATH=$OGG_HOME:$PATH
2 解壓安裝ogg軟件
---解壓安裝ogg軟件，安裝包:p31766135_191004_Linux-x86-64.zip cd /tmp/ unzip p31766135_191004_Linux-x86-64.zip cd 31766135/ mv files/* /u01/app/ogg/ ---驗(yàn)證 oracle@rac19b ~]$ ggsci ? Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.4 31637694_FBO Linux, x64, 64bit (optimized), Oracle 19c on Aug 19 2020 20:08:53 Operating system character set identified as UTF-8. ? Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. ? GGSCI (rac19b) 1>
3 創(chuàng)建ogg配置目錄
GGSCI (rac19b) 10> create subdirs ? Creating subdirectories under current directory /home/oracle ? Parameter file /u01/app/ogg/dirprm: created. Report file /u01/app/ogg/dirrpt: created. Checkpoint file /u01/app/ogg/dirchk: created. Process status files /u01/app/ogg/dirpcs: created. SQL script files /u01/app/ogg/dirsql: created. Database definitions files /u01/app/ogg/dirdef: created. Extract data files /u01/app/ogg/dirdat: created. Temporary files /u01/app/ogg/dirtmp: created. Credential store files /u01/app/ogg/dircrd: created. Masterkey wallet files /u01/app/ogg/dirwlt: created. Dump files /u01/app/ogg/dirdmp: created. ? GGSCI (rac19b) 11>
4 啟動(dòng)MGR進(jìn)程
---編輯mgr配置 cd /u01/app/ogg/ ./ggsci GGSCI (rac19b) 1> edit params mgr ---配置以下參數(shù) PORT 7809 autorestart extract * ,waitminutes 2,resetminutes 5 PURGEOLDEXTRACTS /u01/app/ogg/dirdat/*,USECHECKPOINTS,MINKEEPDAYS 5 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 ---啟動(dòng)mgr進(jìn)程 GGSCI (rac19b) 1> start mgr Manager started. ? GGSCI (rac19b) 2> info all ? Program Status Group Lag at Chkpt Time Since Chkpt ? MANAGER RUNNING ?
MySQL目標(biāo)端安裝ogg軟件
1 配置環(huán)境變量
---/etc/profile export OGG_HOME=/opt/ogg export PATH=$OGG_HOME:$PATH
2 解壓安裝ogg軟件
---解壓安裝ogg軟件，安裝包:ggs_Linux_x64_MySQL_64bit.tar cd ogg/ tar xvf /tmp/ggs_Linux_x64_MySQL_64bit.tar ---驗(yàn)證 [mysql@rac19a ~]$ ggsci ? Oracle GoldenGate Command Interpreter for MySQL Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144 Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:41:32 Operating system character set identified as UTF-8. ? Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
3 創(chuàng)建ogg配置目錄
GGSCI (rac19a) 1> create subdirs ? Creating subdirectories under current directory /home/mysql ? Parameter file /opt/ogg/dirprm: created. Report file /opt/ogg/dirrpt: created. Checkpoint file /opt/ogg/dirchk: created. Process status files /opt/ogg/dirpcs: created. SQL script files /opt/ogg/dirsql: created. Database definitions files /opt/ogg/dirdef: created. Extract data files /opt/ogg/dirdat: created. Temporary files /opt/ogg/dirtmp: created. Credential store files /opt/ogg/dircrd: created. Masterkey wallet files /opt/ogg/dirwlt: created. Dump files /opt/ogg/dirdmp: created.
4 啟動(dòng)MGR進(jìn)程
---編輯mgr配置 cd /opt/ogg/ ./ggsci GGSCI (rac19b) 1> edit params mgr ---配置以下參數(shù) PORT 7809 AUTOSTART REPLICAT * AUTORESTART REPLICAT *,RETRIES 5,WAITMINUTES 2,RESETMINUTES 10 PURGEOLDEXTRACTS /opt/ogg/dirdat/*,USECHECKPOINTS,MINKEEPDAYS 5 ACCESSRULE, PROG *, IPADDR 192.168.2.*, ALLOW LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 ---啟動(dòng)mgr進(jìn)程 GGSCI (rac19a) 2> start mgr Manager started. ? ? GGSCI (rac19a) 3> info all ? Program Status Group Lag at Chkpt Time Since Chkpt ? MANAGER RUNNING
5 配置全局文件以及檢查表
---ogg連接MySQL GGSCI (rac19a DBLOGIN as ogg) 8> dblogin sourcedb db1@192.168.2.201:3306,userid ogg,password oggmysql Successfully logged into database. ---創(chuàng)建檢查表 GGSCI (rac19a DBLOGIN as ogg) 9> ADD CHECKPOINTTABLE db1.checkpoint ? Successfully created checkpoint table db1.checkpoint. ? GGSCI (rac19a DBLOGIN as ogg) 10> ---配置文件設(shè)置全局檢查表 GGSCI (rac19a DBLOGIN as ogg) 10> edit params ./GLOBALS ---添加以下配置 CHECKPOINTTABLE db1.checkpoint
Oracle源端配置抽取以及投遞進(jìn)程(增量進(jìn)程)
1 對(duì)同步表添加補(bǔ)充日志
---ogg連接Oracle GGSCI (rac19b) 3> dblogin userid ogg password oggoracle Successfully logged into database. ---為表test.test1添加同步日志 GGSCI (rac19b as ogg@testdb) 4> add trandata test.test1 ? 2022-10-13 13:08:58 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST.TEST1. ? 2022-10-13 13:08:58 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST.TEST1. ? 2022-10-13 13:08:58 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST.TEST1. ? 2022-10-13 13:08:59 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TEST1 ***** Oracle Goldengate support native capture on table TEST.TEST1. Oracle Goldengate marked following column as key columns on table TEST.TEST1: ID. ---為表test.test2添加同步日志 GGSCI (rac19b as ogg@testdb) 5> add trandata test.test2 ? 2022-10-13 13:09:04 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST.TEST2. ? 2022-10-13 13:09:04 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST.TEST2. ? 2022-10-13 13:09:04 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST.TEST2. ? 2022-10-13 13:09:04 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TEST2 ***** Oracle Goldengate support native capture on table TEST.TEST2. Oracle Goldengate marked following column as key columns on table TEST.TEST2: ID. ? GGSCI (rac19b as ogg@testdb) 6>
2 創(chuàng)建EXTRACT抽取進(jìn)程
GGSCI (rac19b) 2> edit params e_test ? extract E_TEST SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.0.0/dbhome_1") SETENV (ORACLE_SID = "testdb") SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") userid ogg, password oggoracle exttrail ./dirdat/es ? gettruncates TRANLOGOPTIONS INCLUDEREGIONID,EXCLUDEUSER ogg TRANLOGOPTIONS BUFSIZE 2048000 TRANLOGOPTIONS DBLOGREADER,DBLOGREADERBUFSIZE 2048000 ? DISCARDFILE ./dirrpt/E_TEST.dsc,APPEND,MEGABYTES 1000 DISCARDROLLOVER AT 6:00 REPORTROLLOVER AT 6:00 REPORTCOUNT EVERY 1 HOURS,RATE ? ? FETCHOPTIONS MISSINGROW ABEND STATOPTIONS REPORTFETCH ? WARNLONGTRANS 1H,CHECKINTERVAL 10m ? DYNAMICRESOLUTION TABLE TEST.TEST1; TABLE TEST.TEST2;
3 設(shè)置EXTRACT抽取進(jìn)程參數(shù)
GGSCI (rac19b) 2> edit params e_test ? extract E_TEST SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.0.0/dbhome_1") SETENV (ORACLE_SID = "testdb") SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") userid ogg, password oggoracle exttrail ./dirdat/es ? gettruncates TRANLOGOPTIONS INCLUDEREGIONID,EXCLUDEUSER ogg TRANLOGOPTIONS BUFSIZE 2048000 TRANLOGOPTIONS DBLOGREADER,DBLOGREADERBUFSIZE 2048000 ? DISCARDFILE ./dirrpt/E_TEST.dsc,APPEND,MEGABYTES 1000 DISCARDROLLOVER AT 6:00 REPORTROLLOVER AT 6:00 REPORTCOUNT EVERY 1 HOURS,RATE? ? FETCHOPTIONS MISSINGROW ABEND STATOPTIONS REPORTFETCH ? WARNLONGTRANS 1H,CHECKINTERVAL 10m ? DYNAMICRESOLUTION TABLE TEST.TEST1; TABLE TEST.TEST2;
4 創(chuàng)建EXTRACT投遞進(jìn)程
GGSCI (rac19b) 3> add extract P_TEST,exttrailsource ./dirdat/es EXTRACT added. ? GGSCI (rac19b) 4> add RMTTRAIL ./dirdat/rs,ext P_TEST,megabytes 1000 RMTTRAIL added. ? GGSCI (rac19b) 5> ?
5 設(shè)置EXTRACT投遞進(jìn)程參數(shù)
extract P_TEST userid ogg, password oggoracle rmthost 192.168.2.201, mgrport 7809 rmttrail /opt/ogg/dirdat/rs passthru ? DISCARDFILE ./dirrpt/P_TEST.dsc,APPEND,MEGABYTES 1000 DISCARDROLLOVER AT 6:00 ? REPORTROLLOVER AT 6:00 REPORTCOUNT EVERY 1 HOURS,RATE ? TABLE TEST.TEST1; TABLE TEST.TEST2;
6 啟動(dòng)源端抽取以及投遞進(jìn)程
---啟動(dòng)抽取以及投遞進(jìn)程 GGSCI (rac19b) 8> start *test ? Sending START request to MANAGER ... EXTRACT E_TEST starting ? Sending START request to MANAGER ... EXTRACT P_TEST starting ? ---確認(rèn)狀態(tài)正常running GGSCI (rac19b) 14> info all ? Program Status Group Lag at Chkpt Time Since Chkpt ? MANAGER RUNNING EXTRACT RUNNING E_TEST 00:00:02 00:00:07 EXTRACT RUNNING P_TEST 00:00:00 00:00:03 ? GGSCI (rac19b) 15> ---確認(rèn)目標(biāo)端能接收到隊(duì)列文件 ? [mysql@rac19a dirdat]$ ls -rlth total 20K -rw-r----- 1 mysql mysql 19K Oct 13 13:24 rs000000000 [mysql@rac19a dirdat]$
MySQL目標(biāo)端配置復(fù)制進(jìn)程(增量進(jìn)程)
1 添加復(fù)制進(jìn)程
GGSCI (rac19a DBLOGIN as ogg) 11> add replicat r_test,exttrail /opt/ogg/dirdat/rs,checkpointtable db1.checkpoint REPLICAT added. ? GGSCI (rac19a DBLOGIN as ogg) 12> info all ? Program Status Group Lag at Chkpt Time Since Chkpt ? MANAGER RUNNING REPLICAT STOPPED R_TEST 00:00:00 00:00:04
2 配置復(fù)制進(jìn)程參數(shù)
GGSCI (rac19a DBLOGIN as ogg) 13> edit params r_test
?
replicat r_test
setenv (MYSQL_HOME="/usr/local/mysql")
setenv (MYSQL_UNIX_PORT="/opt/mysql/data/mysql.sock")
dboptions host 192.168.2.201,connectionport 3306
targetdb db1,userid ogg, password oggmysql
?
discardfile /opt/ogg/dirrpt/r_test.dsc,append,megabytes 1000
DISCARDROLLOVER AT 6:00
?
REPERROR (DEFAULT, ABEND)
?
MAXTRANSOPS 5000
HANDLECOLLISIONS
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS, RATE
MAP test.test1, TARGET db1.test1;
MAP test.test2, TARGET db1.test2;3 HANDLECOLLISIONS參數(shù)說明
參數(shù)是實(shí)現(xiàn) OGG 全量數(shù)據(jù)與增量數(shù)據(jù)銜接的關(guān)鍵，其實(shí)現(xiàn)原理是在全量數(shù)據(jù)初始完成之后，開啟增量抽取進(jìn)程，應(yīng)用全量數(shù)據(jù)初始化期間產(chǎn)生的 redo log。
當(dāng)全量應(yīng)用完成后，開啟增量回放進(jìn)程，應(yīng)用全量期間的增量數(shù)據(jù)?？赡軙?huì)出現(xiàn)數(shù)據(jù)沖突的情況，這就是為什么表一定要有主鍵或者唯一鍵，使用該參數(shù)后增量回放 DML 語句時(shí)主要有以下沖突場景及處理邏輯：
1 目標(biāo)端不存在 delete 語句的記錄，忽略該問題并不記錄到 discardfile。
2 目標(biāo)端丟失 update 記錄，更新的是主鍵值，update 轉(zhuǎn)換成 insert，更新的鍵值是非主鍵，忽略該問題并不記錄到 discardfile。
3 目標(biāo)端重復(fù) insert 已存在的主鍵值，這將被 replicat 進(jìn)程轉(zhuǎn)換為 UPDATE 現(xiàn)有主鍵值的。
4 在初始化數(shù)據(jù)，并追完增量數(shù)據(jù)之后，建議把HANDLECOLLISIONS參數(shù)去掉，Oracle官方建議不要一直使用該參數(shù),這可能導(dǎo)致數(shù)據(jù)不準(zhǔn)。
Oracle源端配置數(shù)據(jù)初始化進(jìn)程(數(shù)據(jù)全量初始化進(jìn)程)
1 添加初始化進(jìn)程
GGSCI (rac19b) 17> add extract e_init,sourceistable EXTRACT added.
2 配置初始化進(jìn)程
GGSCI (rac19b) 20> edit params e_init ? extract e_init SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.0.0/dbhome_1") SETENV (ORACLE_SID = "testdb") SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") userid ogg, password oggoracle RMTHOST 192.168.2.201,MGRPORT 7809 RMTTASK REPLICAT,GROUP r_init ? table test.test1; table test.test2;
MySQL目標(biāo)端配置數(shù)據(jù)初始化進(jìn)程(數(shù)據(jù)全量初始化進(jìn)程)
1 添加初始化進(jìn)程
GGSCI (rac19b) 17> add extract e_init,sourceistable EXTRACT added.
2 配置初始化進(jìn)程
GGSCI (rac19b) 20> edit params e_init ? extract e_init SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.0.0/dbhome_1") SETENV (ORACLE_SID = "testdb") SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") userid ogg, password oggoracle RMTHOST 192.168.2.201,MGRPORT 7809 RMTTASK REPLICAT,GROUP r_init ? table test.test1; table test.test2;
全量數(shù)據(jù)初始化
數(shù)據(jù)初始化會(huì)將全表的數(shù)據(jù)通過創(chuàng)建的ogg進(jìn)程e_init,r_init從Oracle源端同步到MySQL目標(biāo)端。
1 啟動(dòng)Oracle源端的e_init初始化進(jìn)程
GGSCI (rac19a DBLOGIN as ogg) 17> add replicat r_init,specialrun REPLICAT added.
2 目標(biāo)端查看同步的進(jìn)度
GGSCI (rac19a DBLOGIN as ogg) 18> edit params r_init ? replicat r_init setenv (MYSQL_HOME="/usr/local/mysql") setenv (MYSQL_UNIX_PORT="/opt/mysql/data/mysql.sock") dboptions host 192.168.2.201,connectionport 3306 targetdb db1,userid ogg, password oggmysql discardfile /opt/ogg/dirrpt/r_init.dsc,append,megabytes 1000 MAXTRANSOPS 5000 MAP test.test1, TARGET db1.test1; MAP test.test2, TARGET db1.test2;
3 同步完成，會(huì)輸出總的數(shù)量
Report at 2022-10-13 14:35:19 (activity since 2022-10-13 14:30:57)
?
Output to r_init:
?
From Table TEST.TEST1:
# inserts: 999901
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From Table TEST.TEST2:
# inserts: 1000000
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0?
?
REDO Log Statistics
Bytes parsed 0
Bytes output 285986537增量數(shù)據(jù)同步
1 啟動(dòng)目標(biāo)端復(fù)制進(jìn)程r_test
GGSCI (rac19a DBLOGIN as ogg) 65> start r_test ? Sending START request to MANAGER ... REPLICAT R_TEST starting? ? GGSCI (rac19a DBLOGIN as ogg) 66> info all ? Program Status Group Lag at Chkpt Time Since Chkpt ? MANAGER RUNNING REPLICAT RUNNING R_TEST 00:00:00 00:00:01
2 查看增量同步信息
GGSCI (rac19a DBLOGIN as ogg) 67> stats r_test ? Sending STATS request to REPLICAT R_TEST ... ? Start of Statistics at 2022-10-13 14:45:24. ? Replicating from TEST.TEST1 to db1.test1: ? ---collisions解決沖突數(shù)據(jù)的行數(shù) *** Total statistics since 2022-10-13 14:45:17 *** Total inserts 0.00 Total updates 0.00 Total deletes 99.00 Total upserts 0.00 Total discards 0.00 Total operations 99.00 Total delete collisions 99.00 ? ? Replicating from TEST.TEST2 to db1.test2: ---增量update了10行 *** Total statistics since 2022-10-13 14:45:17 *** Total inserts 0.00 Total updates 10.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 10.00? ? End of Statistics. ?
3 注釋去除HANDLECOLLISIONS參數(shù)
注:要在增量同步進(jìn)程應(yīng)用完初始化期間產(chǎn)生的日志以及實(shí)時(shí)同步之后，再去除參數(shù)。
#編輯配置文件，注釋---HANDLECOLLISIONS GGSCI (rac19a DBLOGIN as ogg) 71> edit params r_test ? replicat r_test setenv (MYSQL_HOME="/usr/local/mysql") setenv (MYSQL_UNIX_PORT="/opt/mysql/data/mysql.sock") dboptions host 192.168.2.201,connectionport 3306 targetdb db1,userid ogg, password oggmysql ? discardfile /opt/ogg/dirrpt/r_test.dsc,append,megabytes 1000 DISCARDROLLOVER AT 6:00 ? REPERROR (DEFAULT, ABEND) ? MAXTRANSOPS 5000? ? ---HANDLECOLLISIONS REPORTROLLOVER AT 6:00 REPORTCOUNT EVERY 1 HOURS, RATE MAP test.test1, TARGET db1.test1; MAP test.test2, TARGET db1.test2; ? #重啟進(jìn)程生效 GGSCI (rac19a DBLOGIN as ogg) 73> stop r_test ? Sending STOP request to REPLICAT R_TEST ... Request processed. ? ? GGSCI (rac19a DBLOGIN as ogg) 74> start r_test ? Sending START request to MANAGER ... REPLICAT R_TEST starting? ? GGSCI (rac19a DBLOGIN as ogg) 75>
4 測試數(shù)據(jù)同步情況
Oracle源端刪除999行數(shù)據(jù)，當(dāng)前數(shù)據(jù)99001
SQL> select count(*) from test.test2;
?
COUNT(*)
----------
1000000
?
SQL> delete from test.test2 where rownum<1000;
?
999 rows deleted.
?
SQL> commit;
?
Commit complete.
?
SQL> select count(*) from test.test2;
?
COUNT(*)
----------
999001
?
SQL>MySQL目標(biāo)端同步刪除的操作，數(shù)據(jù) 一致都為999001
root@mysql.sock 14:33: [db1]>select count(*) from db1.test2; +----------+ | count(*) | +----------+ | 999001 | +----------+ 1 row in set (0.17 sec) ? root@mysql.sock 14:54: [db1]> ?
使用navicat+Oracle GoldenGate(OGG)的方式，操作步驟比較復(fù)雜，但如果需要遷移的表多，并且需要實(shí)時(shí)的增量同步，那么還是比較適合的。
總結(jié)
到此這篇關(guān)于Oracle數(shù)據(jù)遷移MySQL的三種簡單方法的文章就介紹到這了,更多相關(guān)Oracle數(shù)據(jù)遷移MySQL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- 關(guān)于MySQL數(shù)據(jù)遷移--data目錄直接替換注意事項(xiàng)的詳解
- 如何把sqlserver數(shù)據(jù)遷移到mysql數(shù)據(jù)庫及需要注意事項(xiàng)
- MySQL數(shù)據(jù)遷移使用MySQLdump命令
- mysql中用于數(shù)據(jù)遷移存儲(chǔ)過程分享
- Python中MySQL數(shù)據(jù)遷移到MongoDB腳本的方法
- MySQL 億級(jí)數(shù)據(jù)導(dǎo)入導(dǎo)出及遷移筆記
- mysql數(shù)據(jù)庫如何實(shí)現(xiàn)億級(jí)數(shù)據(jù)快速清理
- MySQL億級(jí)數(shù)據(jù)平滑遷移雙寫方案實(shí)戰(zhàn)
相關(guān)文章
window10 安裝Oracle19C 和SQL Developer 的圖文教程
這篇文章主要介紹了window10 安裝Oracle19C 和SQL Developer 教程（圖文詳解）,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
Oracle查詢表空間大小及每個(gè)表所占空間的大小語句示例
Oracle表空間大小的查看方法應(yīng)該是我們都需要掌握的知識(shí),下面這篇文章主要給大家介紹了關(guān)于Oracle查詢表空間大小及每個(gè)表所占空間的大小語句的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-12-12
Zabbix是一款開源的網(wǎng)絡(luò)監(jiān)控和管理系統(tǒng),可以用于監(jiān)控各種網(wǎng)絡(luò)設(shè)備、服務(wù)器和應(yīng)用程序等,本文給大家介紹了zabbix監(jiān)控oracle表空間的操作方法,并通過代碼示例和圖文講解的非常詳細(xì),需要的朋友可以參考下2024-04-04
oracle 合并查詢 事務(wù) sql函數(shù)小知識(shí)學(xué)習(xí)
oracle 合并查詢 事務(wù) sql函數(shù)小知識(shí)學(xué)習(xí)，需要的朋友可以參考下2012-12-12
在Oracle數(shù)據(jù)庫管理中,了解特定表或索引所占用的空間對(duì)于性能調(diào)優(yōu)、存儲(chǔ)規(guī)劃以及資源分配至關(guān)重要,本文檔介紹了三種常用的方法來查詢Oracle數(shù)據(jù)庫中表占用的空間,感興趣的小伙伴跟著小編一起來看看吧2024-12-12
MySQL實(shí)現(xiàn)按分秒統(tǒng)計(jì)數(shù)據(jù)量方式
在MySQL中,通過使用GROUP BY結(jié)合時(shí)間處理函數(shù),可以有效統(tǒng)計(jì)每秒、每分鐘、每5分鐘、每10分鐘和每30分鐘的交易量,通過對(duì)transaction_time字段進(jìn)行格式化和分段,可以靈活調(diào)整統(tǒng)計(jì)的時(shí)間間隔,此方法適用于需要精確時(shí)間段統(tǒng)計(jì)的場景,如性能監(jiān)控、數(shù)據(jù)分析等2024-10-10
oracle11g用戶登錄時(shí)被鎖定問題的解決方法 （ora-28000 the account is locked）
最近在操作oracle11g的使用出現(xiàn)錯(cuò)誤的現(xiàn)象：ora-28000 the account is locked,既用戶無法登錄問題，如何解決此問題呢？下面小編給大家?guī)砹薿racle11g用戶登錄時(shí)被鎖定問題的解決方法，感興趣的朋友一起看看吧2017-07-07
這篇文章主要介紹了Oracle外鍵不加索引引起死鎖的情況及解決,需要的朋友可以參考下2014-05-05
這篇文章主要為大家詳細(xì)介紹了Weblogic控制臺(tái)修改用戶密碼的方法,文中通過圖片進(jìn)行了詳細(xì)的講解,感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2023-08-08
Oracle中在pl/sql developer修改表的2種方法
Oracle中在pl/sql developer修改表的2種方法，需要的朋友可以參考一下2013-03-03