Scenario 3: Migrating Data from an ECS-hosted MySQL Instance on Huawei Cloud to DDM

Scenarios

You have built an ECS-hosted MySQL instance on Huawei Cloud and want to migrate your data from the instance to DDM for distributed data storage.

Services may be interrupted during migration. The duration of the interruption depends on the amount of data to be migrated and on network conditions.

Migration Process

Figure 1 Migrating data from an ECS-hosted MySQL instance to DDM on Huawei Cloud

The ECS where the ECS-hosted MySQL instance is located, destination DDM instance, and prepared RDS for MySQL instance must be in the same VPC and have the same security group rules.

Constraints

• The Huawei ECS where the MySQL instance is hosted, destination DDM instance, and prepared RDS for MySQL instance must be in the same VPC and security group.
• Before data migration, you have to stop your workloads to ensure data integrity.
• Before migrating data, you have to prepare a new DDM instance and create schemas, sharded tables, or broadcast tables with the same names and structures as the source. Table 2 describes the methods for creating various types of logical tables.
• The new RDS for MySQL instance must be of the same MySQL version as the source RDS for MySQL instance.

Prepare for the Migration

• Prepare an ECS that can access the source MySQL instance.
  1. Ensure that the ECS where the source MySQL instance is located, destination DDM instance, and prepared new RDS for MySQL instance are in the same VPC.
  2. Configure the same security group for the ECS, destination DDM instance, and prepared new RDS for MySQL instance. If they belong to different security groups, configure security group rules to allow them to access each other.
  3. Install an official MySQL (5.6 or 5.7) client on the ECS and the following OSs by running the required commands:
     • 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 and configure information about DDM accounts, schemas, and logical tables.
  1. Create a DDM instance and then create a DDM account and schema on the DDM console.
  2. Export table structures of the ECS-hosted MySQL instance to a SQL text file.
     • If the MySQL client version is 5.6 or 5.7, run the following command:

       mysqldump -h {DB_ADDRESS} -P {DB_PORT} -u {DB_USER} -p --single-transaction --set-gtid-purged=OFF --no-data --skip-add-locks --add-locks=false --skip-tz-utc {DB_NAME} {TABLE_NAME} > {mysql_table_schema.sql}

     • If the MySQL client version is 8.0, run the following command:

       mysqldump -h {DB_ADDRESS} -P {DB_PORT} -u {DB_USER} -p --single-transaction --set-gtid-purged=OFF --no-data --skip-add-locks --add-locks=false --column-statistics=0 --skip-tz-utc {DB_NAME} {TABLE_NAME} > {mysql_table_schema.sql}

     Table 1 describes the parameters in the command.

     Table 1 Parameter description

     Parameter	Description	Remarks
     DB_ADDRESS	Indicates the connection address of the database whose data is to be exported.	This parameter is mandatory.
     DB_PORT	Indicates the listening port of the database.	This parameter is mandatory.
     DB_USER	Indicates the database account.	This parameter is mandatory.
     --skip-lock-tables	Indicates that data is exported when tables are not locked.	Indicates that the declaration for table locking is enabled for some parameters by default. Add this parameter to the end of each data export statement.
     --add-locks=false	Indicates that no locks are added to tables.	-
     --no-data	Indicates that only database table structures are exported.	This parameter is used to export table structures.
     --column-statistics=0	Indicates column statistics is disabled when the MySQL client version is 8.0.	If the MySQL client version is 8.0, this parameter is mandatory.
     DB_NAME	Indicates the database name.	This parameter is mandatory.
     TABLE_NAME	Indicates the table name.	Separate every table names of the same type using a space. Exporting only service-related table structures is recommended.
     mysql_table_schema.sql	Indicates the name of the generated table structure file.	The file varies depending on the table whose structure is exported. You can name the file in the format of schema name_logical table name_schema to prevent data from being overwritten, for example, mysql_table_schema.sql.

     

     The parameters listed above are commonly used for data export. Not all mysqldump parameters are listed here. To optimize certain parameters, log in to the official MySQL website.

  3. Create a logical table.
     Ensure that structures of the logical table are consistent with those exported in 2. Map source tables to the logical table of the destination DDM instance, and specify migration policies for different table structures and data.

     

     Before creating a logical table, execute SHOW CREATE TABLE TABLE_NAME to query structures of data tables in the source MySQL instance.

     Table 2 Table migration policies

     Schema Type	Logical Table Type	Table Structure Migration Policy	Table Data Migration Policy
     Sharded	Sharded	Specify a sharding key in each SQL statement for creating original tables, connect to DDM, and execute the new SQL statement on the corresponding schema. For details about how to add a sharding key, see documents about CREATE TABLE statement.	Import source table data using DDM.
     Sharded	Broadcast	Specify a broadcast table in each SQL statement for creating original tables, connect to DDM, and execute the new SQL statement on the corresponding schema. For details about how to add a broadcast table, see documents about CREATE TABLE statement.
     Sharded	Unsharded	Obtain the SQL statement for creating original tables, connect to DDM, log in to the corresponding schema, and execute the statement.
     Unsharded	Unsharded

  4. Clear test data in the destination DDM instance to prevent conflicts with the data to be migrated.
• Prepare a new RDS for MySQL instance.

Export Data

Export table data from the ECS-hosted MySQL instance to a separate SQL text file.

1. Stop the service of the ECS-hosted MySQL instance to ensure that the exported data is up to date.
2. Export table data from the ECS-hosted MySQL instance to a SQL text file.
   • If the MySQL client version is 5.6 or 5.7, run the following command:

     mysqldump -h {DB_ADDRESS} -P {DB_PORT} -u {DB_USER} -p --single-transaction --hex-blob --complete-insert --set-gtid-purged=OFF --quick --no-create-info --skip-comments  --skip-add-locks --add-locks=false --skip-tz-utc [--where=""] {DB_NAME}{TABLE_NAME} > {mysql_table_data.sql}

   • If the MySQL client version is 8.0, run the following command:

     mysqldump -h {DB_ADDRESS} -P {DB_PORT} -u {DB_USER} -p --single-transaction --hex-blob --complete-insert --set-gtid-purged=OFF --quick --no-create-info --skip-comments  --skip-add-locks --add-locks=false --column-statistics=0 --skip-tz-utc [--where=""] {DB_NAME}{TABLE_NAME} > {mysql_table_data.sql}

   

   If the ECS-hosted MySQL instance contains multiple schemas, export data from each schema separately.

   Table 3 describes the parameters in the above command.

   Table 3 Parameter description

   Parameter	Description	Remarks
   DB_ADDRESS	Indicates the connection address of the database whose data is to be exported.	This parameter is mandatory.
   DB_PORT	Indicates the listening port of the database.	This parameter is mandatory.
   DB_USER	Indicates the database account.	This parameter is mandatory.
   --single-transaction	After this parameter is configured, a BEGIN SQL statement is submitted before data is exported. The BEGIN SQL statement does not block any application and ensures data consistency when the data is exported. It applies only to the multi-version storage engine, InnoDB.	-
   --hex-blob	Exports binary string fields in hexadecimal format. This parameter is mandatory if there is binary data to be exported.	-
   --complete-insert	Uses a complete INSERT statement (including column names).	-
   --set-gtid-purged=OFF	If the MySQL version is 8.0 or 5.7, configure this parameter.	If the MySQL version is 5.6 or earlier, do not configure this parameter.
   --quick	Directly exports data to standard output files without buffering queries.	-
   --no-create-info	Exports data without adding any CREATE TABLE statements.	-
   --skip-comments	Disables additional comments.	-
   --skip-add-locks	Controls lock operations during data export to avoid performance issues.	-
   --add-locks=false	Indicates that no locks are added to tables.	-
   --where	Dumps only the records selected based on a specified WHERE condition.	If the condition contains command comment symbols such as special spaces or characters, put the condition in quotes.
   DB_NAME	Indicates the database name.	This parameter is mandatory.
   TABLE_NAME	Indicates the table name.	Separate every table names of the same type using a space. Exporting only service-related table structures is recommended.
   mysql_table_data.sql	Indicates the name of the generated table data file.	The file name varies depending on the table whose data is exported. You can name the file in the format of schema name_logical table name_data to prevent data from being overwritten, for example, mysql_table_data.sql.

   

   • The parameters listed above are commonly used for data export. Not all mysqldump parameters are listed here. To optimize certain parameters, log in to the official MySQL website.
   • Ensure that your MySQL client has the same MySQL version as the target RDS for MySQL instance if you want to migrate data using mysqldump. If the versions are inconsistent, data export may be affected.

3. Check the size of the SQL text file and check whether data is successfully exported.
   • If the file size is not 0 bytes, data export is successful.
   • If the file size is 0 bytes, data export fails. Contact the DDM administrator.

Import Data

1. Enable read-only access to DDM databases on your application.
2. Clear test data in the destination DDM instance to prevent conflicts with the data to be migrated.
3. If you want to import unsharded or common tables, use a MySQL client to connect to the destination DDM instance and run the following commands:

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

   • DDM_ADDRESS indicates the address of the destination DDM instance that data will be imported into.
   • DDM_PORT indicates the port number of the destination DDM instance.
   • DDM_USER indicates the username for logging in to the destination DDM instance.
   • DB_NAME indicates the name of the DDM schema. If you want to import unsharded tables, set DB_NAME to the name of the first shard in the DDM instance.
   • mysql_table_schema.sql indicates the name of the table structure file to be imported.
   • mysql_table_data.sql indicates the name of the table data file to be imported.
     

     Before importing data of unsharded or common tables, delete the last line (for example, Dump completed on 2018-06-28 19:53:03) in the table structure file. Otherwise, data import may fail.

4. If you want to import sharded tables or broadcast tables, use a MySQL client to connect to the destination DDM instance and run the following command:

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

   • DDM_ADDRESS indicates the address of the destination DDM instance that data is imported into.
   • DDM_PORT indicates the listening port of the destination DDM instance.
   • DDM_USER indicates the username for logging in to the destination DDM instance.
   • DB_NAME indicates the name of the DDM schema.
   • mysql_table_data.sql indicates the name of the table data file to be imported.
   

   • Import data during off-peak hours. Performance of the destination DDM instance and its associated RDS for MySQL instance may be affected during data import.
   • If an interruption or exception occurs during data import, execute TRUNCATE TABLE {TABLE_NAME} to clear and import data again. Executing this statement will clear all table data. Exercise caution when executing it.
   • Ensure that the number of data records to be imported into a broadcast table is less than 5 million.

Verify Data

1. Back up logical information of the destination DDM instance on the ECS.
   • Export table structures:
     • If the MySQL client version is 5.6, run the following command:

       mysqldump -h {DDM_ADDRESS} -P {DDM_PORT} -u {DDM_USER} -p --skip-lock-tables --default-auth=mysql_native_password  --set-gtid-purged=OFF --skip-tz-utc --no-data {DB_NAME} {TABLE_NAME} > {mysql_table_schema_new.sql}

     • If the MySQL client version is 8.0, run the following command:

       mysqldump -h {DDM_ADDRESS} -P {DDM_PORT} -u {DDM_USER} -p --skip-lock-tables --default-auth=mysql_native_password --column-statistics=0 --set-gtid-purged=OFF --skip-tz-utc --no-data {DB_NAME} {TABLE_NAME} > {mysql_table_schema_new.sql}

   • Export table data:
     • If the MySQL client version is 5.6 or 5.7, run the following command:

       mysqldump -h {DDM_ADDRESS} -P {DDM_PORT} -u {DDM_USER} -p --single-transaction --hex-blob --complete-insert --set-gtid-purged=OFF --quick --no-create-info --skip-add-locks --add-locks=false --skip-comments --skip-tz-utc [--where=""] {DB_NAME}{TABLE_NAME} > {mysql_table_data_new.sql}

     • If the MySQL client version is 8.0, run the following command:

       mysqldump -h {DDM_ADDRESS} -P {DDM_PORT} -u {DDM_USER} -p --single-transaction --hex-blob --complete-insert --set-gtid-purged=OFF --quick --no-create-info --skip-add-locks --add-locks=false --skip-comments --column-statistics=0 --skip-tz-utc [--where=""] {DB_NAME}{TABLE_NAME} > {mysql_table_data_new.sql}

2. Check data consistency.
   1. Execute the following SQL statement on the ECS-hosted MySQL instance and destination DDM instance to check whether there are the same number of records in each table. {TABLE_NAME} indicates the table name.

      select count(*) from {TABLE_NAME};

   2. On the ECS, check whether table structures and data records are consistent before and after the export:

      diff -B -w -q -i {mysql_table_schema.sql} {mysql_table_schema_new.sql};echo $?
      diff -B -w -q -i {mysql_table_data.sql} {mysql_table_data_new.sql};echo $?

      

      • The command for exporting table structures is available to only unsharded and common tables.
      • Table structures and data records cannot be compared if they are exported in a sequence different from before.
      • If yes, the data is successfully migrated.
      • If no, contact DDM customer service.

3. Verify in an E2E manner whether your application can read related tables of the destination DDM instance normally.
4. Disable read-only access to DDM databases on your application.

Verify Services

1. Switch the service data source to DDM.
2. Check whether you can read and write data from and to DDM normally.
   • If yes, data migration is completed.
   • If no, switch the service data source to the ECS-hosted MySQL instance and contact DDM customer service.