(CN)
Online migration of Oracle databases to target MySQL kernel databases, such as MySQL, PolarDB, Percona Server MySQL, MariaDB, OceanBase, TiDB, GaussDB for MySQL
- Migrate the entire database table structure and table row data to the target database
- The target database table structure is a superset of the source database that can migrate row data
- Multi thread batch migration of table row data
- Data comparison between source and target databases
The running client PC needs to be able to connect to both the source database and the target database simultaneously
run on Windows,Linux,macOS
unzip and run
e.g.
[root@localhost opt]# unzip OracleSync2MySQL-linux64-0.0.1.zip
The following is an example of a Windows platform, with the same command-line parameters as other operating systems
Note: Please run this tool in CMD on Windows system, or in a directory with read and write permissions on MacOS or Linux
If you run on linux please first set LD_LIBRARY_PATH=./instantclient
example below
[root@uatenv OracleSync2MySQL]# pwd
/opt/OracleSync2MySQL
[root@uatenv OracleSync2MySQL]# ls
example.yml instantclient OracleSync2MySQL
[root@uatenv OracleSync2MySQL]# export LD_LIBRARY_PATH=./instantclient
Edit the example.cfg file and input the source(src) and target(dest) database information separately
src:
host: 192.168.1.200
port: 1521
database: orcl
username: admin
password: oracle
dest:
host: 192.168.1.37
port: 3306
database: test_polar
username: root
password: 11111
pageSize: 100000
maxParallel: 100
batchRowSize: 1000
tables:
test:
- select * from test
exclude:
operationlogdatabase: src is oracle service_name,dest is database name
pageSize: Number of records per page for pagination query
maxParallel: The maximum number of concurrency that can run goroutine simultaneously
tables: Customized migrated tables and customized query source tables, indented in yml format
exclude: Tables that do not migrate to target database, indented in yml format
batchRowSize: Number of rows in batch insert target table
Migrate entire database table structure, row data, views, index constraints, and self increasing columns to target database
OracleSync2MySQL.exe --config file.yml
e.g.
OracleSync2MySQL.exe --config example.yml
on Linux and MacOS you can run
Note: If running on Linux, please first set the environment variable in the directory where the tool is located to specify the instantclient used by the current tool directory
[root@uatenv OracleSync2MySQL]# pwd
/opt/OracleSync2MySQL
[root@uatenv OracleSync2MySQL]# ls
example.yml instantclient OracleSync2MySQL
[root@uatenv OracleSync2MySQL]# export LD_LIBRARY_PATH=./instantclient
[root@uatenv OracleSync2MySQL]#./OracleSync2MySQL --config example.yml
After the entire database migration is completed, a migration summary will be generated to observe if there are any failed objects. By querying the migration log, the failed objects can be analyzed
+-------------------------+---------------------+-------------+----------+
| SourceDb | DestDb | MaxParallel | PageSize |
+-------------------------+---------------------+-------------+----------+
| 192.168.149.37-sourcedb | 192.168.149.33-test | 30 | 100000 |
+-------------------------+---------------------+-------------+----------+
+-----------+----------------------------+----------------------------+-------------+--------------+
|Object | BeginTime | EndTime |FailedTotal |ElapsedTime |
+-----------+----------------------------+----------------------------+-------------+--------------+
|Table | 2023-07-21 17:12:51.680525 | 2023-07-21 17:12:52.477100 |0 |796.579837ms |
|TableData | 2023-07-21 17:12:52.477166 | 2023-07-21 17:12:59.704021 |0 |7.226889553s |
+-----------+----------------------------+----------------------------+-------------+--------------+
Table Create finish elapsed time 5.0256021s
After migration finish you can compare source table and target database table rows,displayed failed table only
OracleSync2MySQL.exe --config your_file.yml compareDb
e.g.
OracleSync2MySQL.exe --config example.yml compareDb
on Linux and MacOS you can run
./OracleSync2MySQL --config example.yml compareDb
Table Compare Result (Only Not Ok Displayed)
+-----------------------+------------+----------+-------------+------+
|Table |SourceRows |DestRows |DestIsExist |isOk |
+-----------------------+------------+----------+-------------+------+
|abc_testinfo |7458 |0 |YES |NO |
|log1_qweharddiskweqaz |0 |0 |NO |NO |
|abcdef_jkiu_button |4 |0 |YES |NO |
|abcdrf_yuio |5 |0 |YES |NO |
|zzz_ss_idcard |56639 |0 |YES |NO |
|asdxz_uiop |290497 |190497 |YES |NO |
|abcd_info |1052258 |700000 |YES |NO |
+-----------------------+------------+----------+-------------+------+
INFO[0040] Table Compare finish elapsed time 11.307881434s Migrate entire database table structure, row data, views, index constraints, and self increasing columns to target database
OracleSync2MySQL.exe --config file.yml
e.g.
OracleSync2MySQL.exe --config example.yml
Note: If running on Linux, please first set the environment variable in the directory where the tool is located to specify the instantclient used by the current tool directory
[root@uatenv OracleSync2MySQL]# pwd
/opt/OracleSync2MySQL
[root@uatenv OracleSync2MySQL]# ls
example.yml instantclient OracleSync2MySQL
[root@uatenv OracleSync2MySQL]# export LD_LIBRARY_PATH=./instantclient
[root@uatenv OracleSync2MySQL]#./OracleSync2MySQL --config example.yml
only migrate some tables not entire database, and migrate the table structure and table data to the target database according to the custom query statement in file.yml
OracleSync2MySQL.exe --config file.yml -s
e.g.
OracleSync2MySQL.exe --config example.yml -s
Create all table structure(only table metadata not row data) to target database
OracleSync2MySQL.exe --config file.yml createTable -t
e.g.
OracleSync2MySQL.exe --config example.yml createTable -t
Read custom tables from yml file and create target table
OracleSync2MySQL.exe --config file.yml createTable -s -t
e.g.
OracleSync2MySQL.exe --config example.yml createTable -s -t
Only migrate all row data from the source database to the target database, excluding table structures
OracleSync2MySQL.exe --config file.yml onlyData
e.g.
OracleSync2MySQL.exe --config example.yml onlyData
only migrate file.yml custom sql query table row data exclude table struct
OracleSync2MySQL.exe --config file.yml onlyData -s
e.g.
OracleSync2MySQL.exe --config example.yml onlyData -s
Do not migrate any data, only dump DDL statements. Output DDL statements for table creation, indexes, auto increment columns, views, and other objects to createSql.log in the log file
OracleSync2MySQL.exe --config file.yml -m
e.g.
OracleSync2MySQL.exe --config example.yml -m
2024-06-04 fix tablemeta view problem,add -m mode only output ddl script to plat file
2024-05-30 fix missing default value
2024-05-14 add column comment
2024-03-29 fix some error
2023-11-27 fix -s mode with upper table name
2023-10-24 modify fetchTableMap
2023-08-31 When modifying the insert method for migrating data, the insert statement has been changed from the previous insert into tableName values to insert into tableName (col1, col2) values. Fix the issue of incorrect timestamp type conversion text
2023-08-23 New add triggers & sequence Oracle autoincrement migration to target database autoincrement columns, migrate foreign keys, indexes of normal index type, comment comments, views, dump source database functions, stored procedures, and other objects to flat files
2023-08-14 Add Oracle instantclient
2023-08-04 Fix the issue of tables without data not being created in the target database, add new indexes, and migrate constraints
2023-08-01 Modify the number of connection pools for the source and target databases to unlimited, and use Godror to connect to Oracle
2023-07-28 Paging query to obtain bug fixes and increase timestamp type adaptation
2023-07-27 Oracle full database migration of tables and table data to the target MySQL database