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
  4. 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.

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.

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: **********

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

mysql -f -h {DDM_ADDRESS} -P {DDM_PORT} -u {DDM_USER} -p {DB_NAME} < {mysql_data.sql}
Enter password: **********