Scenario 1: Migrating Data from an On-Premises MySQL Instance to DDM

Scenarios

You are using an on-premises 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 is associated with a prepared RDS for MySQL instance and can communicate with the ECS where the 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 different types of logical tables are described in Table 2.
• The prepared RDS for MySQL instance must be of the same MySQL version as the source MySQL instance.

Prepare for the Migration

• Prepare an ECS that can access the data center where the source MySQL instance is deployed.
  1. Ensure that the data center can communicate with the destination DDM instance, prepared RDS for MySQL instance, and prepared ECS.
  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 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 source 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 --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 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. If some parameters are tuned, log in to the official MySQL website to view them.

  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 on-premises 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, log in to the corresponding schema, and execute it. 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 an RDS for MySQL instance.

Export Data

Connect to the on-premises MySQL instance using the IP address of the ECS and export data from the instance using mysqldump.

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. If some parameters are tuned, log in to the official MySQL website to view them.
   • Ensure that your MySQL client has the same MySQL version as the RDS instance associated with your destination DDM 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 DDM customer service.

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. 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, 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 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 only to 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 the destination DDM instance.

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 MySQL instance and contact the DDM instance administrator.