Database Migration Made Easy: Migrating from MySQL to GBase 8c

Published on 2025-02-26

In today's wave of digital transformation, due to system architecture, cost, and business considerations, an increasing number of enterprise users need to complete adaptation and migration between heterogeneous databases. GBase 8c, as a third-generation intelligent database product, features multi-model multi-tenancy, elastic scaling, and strongly consistent distributed transactions, enabling it to carry massive enterprise data. It distributes heavy workloads across multiple servers for parallel processing, improving system throughput and response speed, and delivers higher performance, fault tolerance, and reliability.

At the same time,GBase 8c offers high compatibility and provides a complete set of migration solutions and tools to achieve a smooth transition from different databases to GBase 8c. This article uses the migration from a MySQL database to a GBase 8c database as an example to introduce the key steps in the migration process.

Key Preparation Before Migration

Environment Assessment and Compatibility Analysis

(1) Select the GBase 8c target database compatibility mode based on the source database characteristics. In this article, since MySQL is the source database, the target database should choose B compatibility mode, which is specified when creating the database.

(2) Verify object compatibility, focusing on advanced features such as stored procedures, triggers, and user-defined functions in the source database. For example, MySQL's "ENUM" type and "TINYINT" are natively compatible.

(3) Character set adaptation.

Note that the default character set of GBase 8c is "SQL_ASCII". When creating the database, you need to explicitly set "UTF8MB4" to match the MySQL standard.

Considering the above factors, create the target database. For example, to create a database named mytest, specify compatibility mode B and set the character set, execute the command:

CREATE DATABASE mytest WITH DBCOMPATIBILITY='B' ENCODING='UTF8MB4';

To reconfirm the compatibility mode of the target database, execute the command:

SELECT datname,datcompatibility FROM pg_database;

2. Migration Tool Selection Strategy

The built-in command-line toolchain of GBase 8c is suitable for automated migration pipeline scenarios, including the gs_dump logical export tool and the gsql data loading tool.

The companion visual tool DMT adopts a B/S architecture, supports one-click Docker deployment, and provides three core functional modules: data migration, data synchronization, and data verification.

 

Detailed Core Steps of Migration Implementation

1. Database Object Migration

(1) Table Structure Conversion

After exporting DDL scripts using mysqldump --no-data, GBase 8c can directly execute over 90% of MySQL table creation statements.

Special handling scenarios include:

  • Time precision handling: Convert DATETIME(6) to TIMESTAMP(6) WITH TIME ZONE or TIMESTAMP(6) WITHOUT TIME ZONE depending on whether time zone is required.

  • Index optimization: In a distributed environment, it is recommended to use hash distribution keys instead of B-tree indexes.

(2) Stored Procedure Migration

This type of object may require syntax conversion. For example:

The original MySQL statement:

DELIMITER //

CREATE PROCEDURE get_users()

BEGIN

SELECT * FROM users;

END //

DELIMITER ;

After adaptation, the GBase 8c statement is:

CREATE OR REPLACE PROCEDURE get_users()

LANGUAGE plpgsql

AS $$

BEGIN RETURN QUERY SELECT * FROM users;

END;

$$;

2. Data Migration Implementation

Use MySQL's built-in tools to export data. For example, execute:

mysqldump -u root -p --single-transaction --databases db1 > full_backup.sql

The data is exported to the full_backup.sql file.

GBase 8c loads the data, for example:
gsql -d target_db -p 15400 -f full_backup.sql

The data in the full_backup.sql file is imported.

Recommendation: For terabyte-level data, adopt a parallel sharding loading strategy to enhance throughput.

 

Typical Issues and Solutions

1. Character Set Conflict

For instance, during migration, you may encounter an error: ERROR: could not determine collation for view column.

Based on the error message, it is because the character set has not been specified.

Solution:

Explicitly specify COLLATE utf8mb4_general_ci, execute:

CREATE VIEW v1 AS SELECT col1 COLLATE "utf8mb4_general_ci" FROM tbl1;

2. Distributed Transaction Optimization

If there is a performance degradation issue with cross-node UPDATE statements during migration.

Solution:

  • Enable SET dolphin.b_compatibility_mode = on to turn on the dolphin compatibility plugin, thereby improving syntax compatibility.

  • Use HASH distribution to optimize data locality.

3. Function Compatibility Handling

Suppose there is a mismatch in the return value type of STR_TO_DATE during migration.

Solution:

Add an explicit type cast, for example:

SELECT (STR_TO_DATE('2023-01-01', '%Y-%m-%d')::timestamp) + INTERVAL '1 day';

 

Post-Migration Verification

After data migration is completed, verification is required to ensure data integrity and accuracy. This can be done through the following methods:

  • Compare table row counts: Check whether the number of rows in each table is consistent between MySQL and GBase 8c.

  • Random sampling: Randomly select a subset of data to verify data consistency.

  • Check foreign key constraints and indexes: Ensure that all foreign key constraints and indexes have been migrated correctly.