Help Center> Distributed Database Middleware> Best Practices> Migrating an Entire MyCat Database to DDM
Updated on 2022-12-07 GMT+08:00

Migrating an Entire MyCat Database to DDM

Scenario

This section describes how to migrate an entire MyCat database to DDM.

  • Services may be interrupted during migration. The duration of the interruption depends on the amount of data to be migrated and on network conditions.
  • Data migration is complicated and is recommended during off-peak hours. This guide is for reference only. The actual migration scheme you should use depends on your specific service scenarios, the amount of data to be migrated, and how much downtime can be tolerated.
  • You can only access DDM through an ECS. Therefore, you must export databases as files to the ECS, and then import data in the files into DDM from the ECS.

Preparations for Migration

  • Prepare an ECS that can access the MyCat database, the target DDM instance, and the RDS DB instance associated with the target DDM instance.
    1. Ensure that the MyCat database, the target DDM instance, and the associated RDS DB instance are in the same VPC for stable network connectivity.
    2. Configure the same security group for the ECS where the MyCat database is deployed, the target DDM instance, and the associated RDS DB instance. If they belong to different security groups, configure their security group rules to allow them to access each other.
    3. Install an official MySQL client on the ECS. Version 5.6 or 5.7 is recommended.
      • Red Hat Linux: yum install mysql mysql-devel
      • Debian Linux: apt install mysql-client-5.7 mysql-client-core-5.7
    1. Ensure that there is enough disk space and memory on the ECS to store and compare dump files.
  • Prepare a DDM instance that has been associated with an RDS DB instance, and configure DDM accounts and schemas for it as required.
  • Use MyCat 1.6 as an example in this section.

Migration Scheme

Table types in MyCat and DDM are different from each other and migration schemes vary by table type, as described in Table 1.

Table 1 Migration schemes

MyCat Table Type

DDM Table Type

Migration Scheme

Unsharded

Unsharded

  1. Export structure data and table data from MyCat.
  2. Connect to the RDS DB instance associated with the target DDM instance and import data from unsharded tables to the target DDM instance.

Sharded: hash sharding (including by date)

Sharded: hash sharding (including by date function)

  1. Export all table structure data from the MyCat database.
  2. Create a table with the same structure as the exported table on the DDM console.
  3. Export data from the entire MyCat database to DDM.
  4. Connect to DDM to import all database data.

Sharded: range sharding (including by date)

Sharded: range sharding (including by date function)

Broadcast

Broadcast

Constraints

  • MyCat services need to be stopped before data migration to ensure data integrity.
  • Associating the MyCat database with a DDM instance for data association is not supported. You must export data from the MyCat database and then import the data into the DDM instance.
  • The version of the associated RDS DB instance associated must be consistent with that of the MyCat database.

Exporting Table Structure Data from the MyCat Database

  1. Log in to the target ECS.
  2. Run the following command to export table structure data in the MyCat database. Configure the parameters in italics. For details about the parameters, see Table 2.

    mysqldump -h {DB_ADDRESS} -P {DB_PORT} -u {DB_USER} -p   --skip-lock-tables --add-locks=false --set-gtid-purged=OFF --no-data --order-by-primary {DB_NAME} > {mysql_schema.sql}
    Enter password: **********
    Table 2 Parameter description

    Parameter

    Description

    Remarks

    DB_ADDRESS

    Connection address of the database whose data is to be exported

    Mandatory

    DB_PORT

    Listening port of the database

    Mandatory

    DB_USER

    Database user

    Mandatory

    DB_NAME

    Database name

    Mandatory

    mysql_schema.sql

    Name of the table structure file

    The file varies depending on the table whose structure is exported.

    You are advised to name the file in the format of schema name_schema to prevent data from being overwritten, for example, mysql_schema.sql.

    mysql_data.sql

    Name of the database data file

    N/A

Exporting Data from the Entire MyCat Database

  1. Log in to the ECS.
  2. Run the following command to export data of the MyCat database. Configure the parameters in italics. For details about the parameters, see Table 2.

    mysqldump -h {DB_ADDRESS} -P {DB_PORT} -u {DB_USER} -p   --hex-blob --complete-insert --skip-lock-tables --add-locks=false --set-gtid-purged=OFF --quick --no-create-info --order-by-primary {DB_NAME} > {mysql_data.sql}
    Enter password: **********

  3. Run the following command on the ECS to view the exported SQL file.

    ls -l

Importing Data from Unsharded Tables into the Target DDM Instance

On the ECS, use a MySQL client to connect to the associated RDS DB instance. Run the following commands to import the table structure and database files.

For unsharded or common tables:
mysql -f -h {RDS_ADDRESS} -P {RDS_PORT} -u {RDS_USER} -p {DB_NAME} < {mysql_table_schema.sql}
Enter password: **********
mysql -f -h {RDS_ADDRESS} -P {RDS_PORT} -u {RDS_USER} -p {DB_NAME} < {mysq_table_data.sql}
Enter password: **********
  • RDS_ADDRESS indicates the address of an RDS DB instance into which data is to be imported.
  • RDS_PORT indicates the port number of an RDS DB instance.
  • RDS_USER indicates the username of an RDS DB instance.
  • DB_NAME indicates the name of an RDS database. If data of unsharded tables is to be imported, DB_NAME indicates the name of the first shard of RDS.
  • mysql_table_schema.sql indicates the name of a table structure file to be imported.
  • mysq_table_data.sql indicates the name of a table data file to be imported.

Importing Data from Sharded or Broadcast Tables into the Target DDM Instance

On the ECS, use a MySQL client to connect to DDM and run the following command to import all database data into DDM.
mysql -f -h {DDM_ADDRESS} -P {DDM_PORT} -u {DDM_USER} -p {DB_NAME} < {mysql_data.sql}
Enter password: **********
Table 3 Parameter description

Parameter

Description

Remarks

DDM_ADDRESS

Connection address of the DDM instance into which data is to be imported

View the connection address and port number on the Basic Information tab on the DDM console.

DDM_PORT

Listening port of the DDM instance into which the data will be imported

DDM_USER

User accessing the DDM instance

Account for creating a schema. The account must have both read and write permissions.

DB_NAME

Name of the schema that the data will be imported into

N/A

mysql_data.sql

Name of the entire database file to be imported

Name of the file exported in 2