MogDB
Ecological Tools

Migrating Data from MySQL to openGauss/MogDB Using MTK

Supported Versions

  • 5.5
  • 5.6
  • 5.7
  • 8.0

Column Comparison Between MySQL and openGauss/MogDB

MySQL COLUMN_TYPE openGauss data_type data_length data_precision data_scale openGauss ex_data_type
bigint(20) bigint 64 0 bigint
char(1) character 1 character(1)
char(10) character 10 character(10)
char(20) character 20 character(20)
datetime timestamp without time zone timestamp(0) without time zone
decimal(10,0) numeric 10 0 numeric(10,0)
decimal(38,0) numeric 38 0 numeric(38,0)
decimal(65,30) numeric 65 30 numeric(65,30)
double numeric numeric
double(10,5) numeric 10 5 numeric(10,5)
longblob bytea bytea
longtext text text
timestamp(6) timestamp with time zone timestamp with time zone
varbinary(20) bytea bytea
varchar(20) character varying 20 character varying(20)
varchar(30) character varying 30 character varying(30)
varchar(40) character varying 40 character varying(40)
varchar(4000) character varying 4000 character varying(4000)

Precautions

  • Empty string problem

    • OpenGauss A mode is different from MySQL, and data needs to be processed. When the attribute of the column is NOT NULL, it is converted to" "`, and when it is allowed to be NULL, it is converted to NULL
    • OpenGauss PG mode is the same as MySQL, NULL != '' No need to deal with

    Database compatible types. Value range: A, B, C, and PG

    A indicates that MogDB is compatible with Oracle. B indicates that MogDB is compatible with MySQL. C indicates that MogDB is compatible with Teradata. PG indicates that MogDB is compatible with PostgreSQL. However, C is not supported currently. Therefore, the values A, B, and PG are commonly used.

    Note:

    • For the compatibility type of A, the database considers an empty string as null and replace the data type DATE with TIMESTAMP(0) WITHOUT TIME ZONE.
    • For the compatibility type of B, when a string is converted to an integer type, if an illegal value is entered, it will be converted to 0. However, for other compatibility types, an error will be reported.
    • For the compatibility type of PG, CHAR and VARCHAR use character as the string unit. For other compatibility types, byte is used as the string unit. For example, as for UTF-8 character set, CHAR(3) can hold 3 Chinese characters in the compatibility type of PG but only one Chinese character in the other compatibility types.
  • Table

    • rewrite the auto-increment column as the default sequence
    • CHARSET property remove
    • ENGINE property remove
  • Partition Table

    • openGauss does not support subpartition tables.
    • openGauss does not support key partitions.
    • range partition

      • to_days

        MySQL

        CREATE TABLE mtk.range_part_t1 (
            prof_history_id BIGINT(20),
            person_id BIGINT(20) NOT NULL,
            organization_id BIGINT(20) NOT NULL,
            record_date DATETIME NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (to_days(record_date))
        (
            PARTITION yr0 VALUES LESS THAN(736695),
            PARTITION yr7 VALUES LESS THAN(737060),
            PARTITION yr8 VALUES LESS THAN(737425),
            PARTITION yr9 VALUES LESS THAN(MAXVALUE)
        )

        OpenGauss

        CREATE TABLE mtk.range_part_t2 (
            prof_history_id BIGINT,
            person_id BIGINT NOT NULL,
            organization_id BIGINT NOT NULL,
            record_date TIMESTAMP NOT NULL
        ) PARTITION BY RANGE (record_date)
        (
            PARTITION yr0 VALUES LESS THAN('2017-01-01'),
            PARTITION yr7 VALUES LESS THAN('2018-01-01'),
            PARTITION yr8 VALUES LESS THAN('2019-01-01'),
            PARTITION yr9 VALUES LESS THAN(MAXVALUE)
        )
      • year

        MySQL

        CREATE TABLE mtk.range_part_t2 (
            prof_history_id BIGINT(20),
            person_id BIGINT(20) NOT NULL,
            organization_id BIGINT(20) NOT NULL,
            record_date DATETIME NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (year(record_date))
        (
            PARTITION yr0 VALUES LESS THAN(2018),
            PARTITION yr7 VALUES LESS THAN(2019),
            PARTITION yr8 VALUES LESS THAN(2020),
            PARTITION yr9 VALUES LESS THAN(MAXVALUE)
        )

        OpenGauss

        CREATE TABLE mtk.range_part_t1 (
            prof_history_id BIGINT,
            person_id BIGINT NOT NULL,
            organization_id BIGINT NOT NULL,
            record_date TIMESTAMP NOT NULL
        ) PARTITION BY RANGE (record_date)
        (
            PARTITION yr0 VALUES LESS THAN('2018-01-01'),
            PARTITION yr7 VALUES LESS THAN('2019-01-01'),
            PARTITION yr8 VALUES LESS THAN('2020-01-01'),
            PARTITION yr9 VALUES LESS THAN(MAXVALUE)
        )
  • Column

    • For MySql bigint unsigned auto increment columns migrated to openGauss, pay attention to the size of the sequence.
    • The field length will be expanded according to the compatibility mode migrated to openGauss
    • Not supported for the column type

      • Geometry
      • GeometryCollection
      • LineString
      • MultiPoint
      • MultiPolygon
      • Point
      • Polygon
      • MultilineString
    • Timestamp column Default value 0000-00-00 00:00:00 rewrite 1970-01-01
  • Table Data

    • The time column data is 0000-00-00 13:14:13 This kind of data is not supported in openGauss and can be ignored by the parameter mySQLSkipErrorDateTimeData
  • Constraint

    • The name is automatically changed in openGauss.
  • Index

    • The name is automatically changed in openGauss.
    • Index partitions are not supported currently.
  • Syntax

    • year(col3)
    • to_days(t)

Test Environment Preparation

MySQL Database

docker run -d \
    --name mtk_mariadb_102 \
    -e MYSQL_ROOT_PASSWORD=w1pSZb2Cau \
    -v /data/mariadb102/data:/var/lib/mysql \
    -p 6201:3306 \
    mariadb:10.2

openGauss/MogDB Database

Step 1: Install MogDB in a docker container for local testing.

docker run --name mogdb --privileged=true -d -e GS_PASSWORD=secretpassword@123 \
    -v /enmotech/mogdb:/var/lib/mogdb \
    -p 5432:5432 \
    enmotech/mogdb:latest

Step 2: Verify that MogDB in the docker container can be accessed from the host.

$ gsql -d postgres -U mogdb -W'mypass@123' -h 127.0.0.1

gsql ((MogDB 2.0.0 build 5be05d82) compiled at 2020-08-11 10:39:49 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=>

Step 3: Log in to MogDB in the container and create a target database.

$ docker exec -it mogdb /bin/bash
su - omm
$ gsql
omm=# create database mydb;

Step 4: Grant the user the permission to create schemas.

MTK supports concurrent migration of multiple schemas which can be automatically created in MogDB. Therefore, the user needs to be granted the permission for schema creation.

omm=# grant create on database mydb to mogdb;
GRANT

Migration

  1. Edit the mtk_config.json file.
  2. Run MTK.
$ ./mtk -c mtk_config.json --reportFile mtk_report.html --logfile mtk_report.log --debug