Scenario 2: Migrating Data from an On-Premises RDS Instance for MySQL to DDM

Scenarios

You are using an on-premises RDS for MySQL instance and want to use DDM to store data in a distributed manner.

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 on-premises MySQL instance to DDM

Constraints

• The destination DDM instance can communicate with the ECS where the target RDS for MySQL instance is deployed.
• 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. Methods for creating various types of logical tables are described in Table 2.
• The target RDS for MySQL instance is of the same MySQL version as the source on-premises DB instance.

Prepare for the Migration

• Prepare an ECS that can access the source DB instance.
  1. Ensure that the source on-premises DB instance can communicate with the destination DDM instance and target DB instance.
  2. 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
  3. Ensure that there is enough disk space and memory on the ECS to store and compare dump files.

• Prepare a DDM instance and create the required DDM accounts, schemas, and logical tables.
  1. Apply for a DDM instance and create a DDM account and schema on the DDM console.
  2. Export table structures of the source DB 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--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--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 target database.	This parameter is mandatory.
     DB_PORT	Indicates the listening port of the target 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.	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 generally used for data export. Not all mysqldump parameters are listed here. Log in to the official MySQL website or contact the DDM administrator to optimize certain parameters.

  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 data table structures in the source DB database.

     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, and execute it on the corresponding schema.
     Unsharded	Unsharded

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

Export Data

Connect to the source DB instance using an IP address and export data from the instance using mysqldump.

• Prerequisites
  • The destination DDM instance is in the same subnet and VPC as the required ECS.
  • Security group rules allow DDM access.

Export table data from the source DB instance to a SQL text file, and upload the file to the prepared ECS.

1. Stop the service system of the source DB instance to ensure that exported data is up to date.
2. Open your MySQL client and run the following command to connect to the on-premises RDS instance and export data as 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 source DB 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 target database.	This parameter is mandatory.
   DB_PORT	Indicates the listening port of the target database.	This parameter is mandatory.
   DB_USER	Indicates the database account.	This parameter is mandatory.
   --complete-insert	Uses a complete INSERT statement (including column names).	-
   --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.	-
   --quick	Directly exports data to standard output files without buffering queries.	This avoids sharply increasing memory usage if there is massive volumes of data.
   --hex-blob	Exports binary string fields in hexadecimal format. This parameter is mandatory if there is binary data to be exported.	-
   --no-create-info	Exports data without adding any CREATE TABLE statements.	This parameter is used for data export.
   --skip-comments	Disables additional comments.	-
   --add-locks=false	Indicates that no locks are added to tables.	-
   --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.
   --skip-add-locks	Controls lock operations during data export to avoid performance issues.	-
   --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 generally used for data export. Not all mysqldump parameters are listed here. Log in to the official MySQL website or contact the DDM administrator to optimize certain parameters.
   • Ensure that your MySQL client has the same MySQL version as the target DB 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 failed. Contact DDM technical support.

4. Upload the SQL file to the prepared ECS.

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. Use a MySQL client to connect to the target DDM instance and run the following commands to import structure and data files of unsharded or common tables:

   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 DDM instance.
   • DDM_PORT indicates the port number of the DDM instance.
   • DDM_USER indicates the username for logging in to the DDM instance.
   • DB_NAME indicates the name of the schema. If data of unsharded tables is to be imported, DB_NAME indicates 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. For sharded tables or broadcast tables, use a MySQL client to connect to DDM and import data files.

   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 DDM instance into which data is imported.
   • DDM_PORT indicates the listening port.
   • DDM_USER indicates the DDM account.
   • DB_NAME indicates the name of the target 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 target DB 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, to prevent primary key conflicts. 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, 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-comments --skip-tz-utc [--where=""] {DB_NAME}{TABLE_NAME} > {mysq_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-comments --column-statistics=0 --skip-tz-utc [--where=""] {DB_NAME}{TABLE_NAME} > {mysq_table_data_new.sql}

2. Check data consistency.
   1. Execute the following SQL statement on the source DB instance and destination DDM instance to check whether the number of records in each table is the same. {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 technical support.

3. Verify in an E2E manner whether your application's read-only access is normal.
4. Disable read-only access.

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 source DB instance and contact DDM technical support.