(CN)
MySQL database migration to postgresql kernel database,such as postgresql(pgsql),vastbase,Huawei postgresql,GaussDB,telepg,Kingbase V8R6
-
No need for cumbersome deployment, ready to use out of the box, compact and lightweight
-
Online migration of MySQL to target database tables, views, indexes, foreign keys, self increasing columns, and other objects
-
Multiple goroutines migrate data concurrently, fully utilizing CPU multi-core performance
-
Migrate Partial Tables and row data
-
Record migration logs, dump SQL statements for DDL object creation failures such as tables and views
-
One click migration of MySQL to postgreSQL, convenient, fast, and easy to use
The running client PC needs to be able to connect to both the source MySQL database and the target database simultaneously
run on Windows,Linux,macOS
tar and run
e.g.
[root@localhost opt]# tar -zxvf gomysql2pg-linux64-0.1.7.tar.gz
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 a Windows system, or in a directory with read and write permissions on MacOS or Linux
Edit the example.cfg file and input the source(src) and target(dest) database information separately
src:
host: 192.168.1.3
port: 3306
database: test
username: root
password: 11111
dest:
dbType: Gauss # If you are using the openGauss type (openGauss 5.0.2 passed the test), please be sure to add this line, and for non openGauss types, please annotate this line
host: 192.168.1.200
port: 5432
database: test
username: test
password: 11111
pageSize: 100000
maxParallel: 30
charInLength: false
useNvarchar2: false
Distributed: false
tables:
test1:
- select * from test1
test2:
- select * from test2
exclude:
- 'log1'
- 'log2'
- '*_log'
pageSize: Number of records per page for pagination query
e.g.
pageSize:100000
SELECT t.* FROM (SELECT id FROM test ORDER BY id LIMIT 0, 100000) temp LEFT JOIN test t ON temp.id = t.id;
-
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,Currently, there is new support for wildcard asterisk (*), such as test*
-
charInLength: If true, varchar type stores character length instead of bytes, so it is only compatible with some databases
-
useNvarchar2: if true，dest database use nvarchar2(like GaussDB)
-
Distributed: If it is true, the database is a distributed database such as GaussDB 8.1.3 And before adding the primary key, first change the table distribution column as the primary key, and then add the primary key.
Migrate entire database table structure, row data, views, index constraints, and self increasing columns to target database
gomysql2pg.exe --config file.yml
e.g.
gomysql2pg.exe --config example.yml
on Linux and MacOS you can run
./gomysql2pg --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 |
+------------+----------------------------+----------------------------+-------------+---------------+
|TableData | 2023-07-11 12:23:55.584092 | 2023-07-11 12:28:44.105372 |6 |4m48.5212802s |
|Sequence | 2023-07-11 12:30:04.697570 | 2023-07-11 12:30:12.549534 |1 |7.8519647s |
|Index | 2023-07-11 12:30:12.549534 | 2023-07-11 12:33:45.312366 |5 |3m32.7628317s |
|ForeignKey | 2023-07-11 12:33:45.312366 | 2023-07-11 12:34:00.413767 |0 |15.1014013s |
|View | 2023-07-11 12:34:00.413767 | 2023-07-11 12:34:01.240472 |14 |826.705ms |
|Trigger | 2023-07-11 12:34:01.240472 | 2023-07-11 12:34:01.339078 |1 |98.6061ms |
+------------+----------------------------+----------------------------+-------------+---------------+
Table Create finish elapsed time 5.0256021s
time="2023-07-11T12:34:01+08:00" level=info msg="All complete totalTime 10m30.1667987s\nThe Report Dir C:\\go\\src\\gomysql2pg\\2023_07_11_12_23_31" func=gomysql2pg/cmd.mysql2pg file="C:/go/src/gomysql2pg/cmd/root.go:207"
After migration finish you can compare source table and target database table rows,displayed failed table only
gomysql2pg.exe --config your_file.yml compareDb
e.g.
gomysql2pg.exe --config example.yml compareDb
on Linux and MacOS you can run
./gomysql2pg --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 In addition to migrating the entire database, the tool also supports the migration of some database objects, such as partial table structures, views, self increasing columns, indexes, and so on
Migrate entire database table structure, row data, views, index constraints, and self increasing columns to target database
gomysql2pg.exe --config file.yml
e.g.
gomysql2pg.exe --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
gomysql2pg.exe --config file.yml -s
e.g.
gomysql2pg.exe --config example.yml -s
Create all table structure(only table metadata not row data) to target database
gomysql2pg.exe --config file.yml createTable -t
e.g.
gomysql2pg.exe --config example.yml createTable -t
Read custom tables from yml file and create target table
gomysql2pg.exe --config file.yml createTable -s -t
e.g.
gomysql2pg.exe --config example.yml createTable -s -t
Only migrate the entire database table row data to the target database, only row data, not contain table structure
gomysql2pg.exe --config file.yml onlyData
e.g.
gomysql2pg.exe --config example.yml onlyData
Only migrate custom query SQL from yml file, only row data, not contain table structure
gomysql2pg.exe --config file.yml onlyData -s
e.g.
gomysql2pg.exe --config example.yml onlyData -s
Only migrate MySQL's autoincrement columns to target database sequences
gomysql2pg.exe --config file.yml seqOnly
e.g.
gomysql2pg.exe --config example.yml seqOnly
Only migrate MySQL primary keys, indexes, and other objects to the target database
gomysql2pg.exe --config file.yml idxOnly
e.g.
gomysql2pg.exe --config example.yml idxOnly
Only migrate MySQL views to the target database
gomysql2pg.exe --config file.yml viewOnly
e.g.
gomysql2pg.exe --config example.yml viewOnly
2024-09-23
Add wildcard exclusion table and dump failed table names to a separate log failedTable.log
2024-08-05
new support openGauss(openGauss 5.0.2 test passed)
modify source double and float transform to target double precision
2023-12-20
Add new parameter useNvarchar2,use nvarchar2 support storage character length of unit，like GaussDB R3 version
2023-10-18
Fix MySQL Data Dictionary ORDINAL_POSITION sorting problem
2023-09-28
Add bit data type convert to pg,fix some invalid unicode value like 0,fix issue bug,output invalid data to logfile.
2023-09-14
Add new parameter Distributed,support Distributed database like GaussDB 8.1.3
2023-08-09
(1).Add the parameter charInLength, and only when it is true, varchar or the length of char will be used as the character length. For example, varchar (10 char) can store 10 characters instead of 10 bytes. (2).For the processing of adding geometry data types, currently the geometry type in MySQL is converted to a hexadecimal string using Golang's hex.EncodeToString function
2023-07-21
modify compare full table result all data and readme modify
2023-07-14
add compare database,create table method add double quote
2023-07-11
Using multiple goroutines to create tables concurrently and optimize migration summary information
2023-07-10
Add Makefile and output config info
2023-07-07
Increase the count of the Global variable channel's failure to process the migration row data, which will be shown in the migration summary
2023-06-30
Fixed the issue of only migrating Linux pg libraries and failing to migrate under Windows. The method of creating tables has now been changed to single threaded
2023-06-28
Add commands to separately migrate table row data, optimize migration summary, and dump error information to log files for optimization
2023-06-27
Add migration summary and improve constraints for creating foreign keys
2023-06-26
Add migration summary and improve constraints for creating foreign keys
2023-06-16
Add constraints such as creating indexes, primary keys, etc
2023-06-14
Add Create Sequence
2023-06-13
Add a creation sequence that uses multiple goroutines to concurrently generate migration tasks for each table, create tables, and optimize the rest
2023-06-12
Fix the table created without missing row data in prepareSqlStr. Before migrating data, check if the target table exists, and optimize the rest
2023-06-09
Add create table metadata to target database
2023-06-06
Add a title character map, display version information, and output color text display
2023-06-05
After encountering Ctrl+c input, proactively close the running SQL of the database, simplify the output format, and dump the table data that failed the migration to the log directory
2023-06-02
The config file adds port settings, and a layer of select * from (custom SQL) is added outside the custom SQL, where 1=0 is used to obtain column fields to avoid querying the entire table data. Before using the exec buffer of the copy method, the database connection is actively closed again using row.close
2023-05-24
Add exclusion table parameters and check for abnormal configuration in the config yml file
2023-05-23
The log method prints the calling file and the number of method source lines, increasing the redirection of logs to a flat file