gomysql2pg

Features

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

Pre-requirement

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

Installation

tar and run

e.g.

[root@localhost opt]# tar -zxvf gomysql2pg-linux64-0.1.7.tar.gz

How to use

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

1 Edit yml configuration file

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.

2 Full database migration

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

3 View Migration Summary

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"

4 Compare Source and Target database

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

Other migration modes

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

1 Full database migration

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

2 Custom SQL Query Migration

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

3 Migrate all table structures in the entire database

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

4 Migrate the table structure of custom tables

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

5 Migrate full database table data

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

6 Migrate custom table data

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

7 Migrate self increasing columns to the target sequence

Only migrate MySQL's autoincrement columns to target database sequences

gomysql2pg.exe --config file.yml seqOnly

e.g.
gomysql2pg.exe  --config example.yml seqOnly

8 Migrate index and primary key

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

9 Migration View

Only migrate MySQL views to the target database

gomysql2pg.exe --config file.yml viewOnly

e.g.
gomysql2pg.exe  --config example.yml viewOnly

change history

v0.2.7

2024-09-23

Add wildcard exclusion table and dump failed table names to a separate log failedTable.log

v0.2.6

2024-08-05

new support openGauss(openGauss 5.0.2 test passed)

v0.2.5

modify source double and float transform to target double precision

v0.2.4

2023-12-20

Add new parameter useNvarchar2,use nvarchar2 support storage character length of unit，like GaussDB R3 version

v0.2.3

2023-10-18

Fix MySQL Data Dictionary ORDINAL_POSITION sorting problem

v0.2.2

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.

v0.2.1

2023-09-14

Add new parameter Distributed,support Distributed database like GaussDB 8.1.3

v0.2.0

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 t

Gomysql2pg

Install / Use

README