Help Center/ Distributed Database Middleware/ Best Practices/ Migrating an Entire RDS Database to DDM
Updated on 2022-09-15 GMT+08:00

Migrating an Entire RDS Database to DDM

Scenario

This section describes how to migrate an entire RDS instance (the old RDS instance) to an unsharded schema of a DDM instance to split read and write requests in 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.
  • If a large amount of data is involved, contact DDM technical engineers by submitting a service ticket or through after-sales services. Fully rehearse the migration before migrating data.

Preparations before Migration

  • Prepare an ECS that can access the old RDS instance, the target DDM instance, and the RDS instance associated with the target DDM instance.
    1. Ensure that the old RDS instance, the target DDM instance, and the RDS instance associated with the target DDM instance are in the same VPC for network connectivity.
    2. Configure the same security group for the above-mentioned instances. If they belong to different security groups, enable the required ports.
    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 instance, and configure the required DDM account and DDM schema for it.
  • If the target schema is sharded, create a logical table with the same structure as data tables in the old RDS instance on the DDM console.

Constraints

  • To ensure data integrity, stop services in the old RDS instance before data migration.
  • In this scenario, associating the old RDS instance with DDM does not mean data association. You need to export data from the old RDS instance and then import it into the DDM instance.
  • The version of the RDS instance associated with the target DDM instance must be consistent with the MySQL version of the old RDS instance.

Exporting Data from the Old RDS Instance

  1. Log in to the target ECS.
  2. Run the following command to export structure data. Set parameters in italics to actual values. For details about the parameters, see Table 1.

    mysqldump -h {DB_ADDRESS} -P {DB_PORT} -u {DB_USER} -p --skip-lock-tables --add-locks=false --set-gtid-purged=OFF --no-data {DB_NAME} > {mysql_schema.sql}
    Table 1 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 name 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

  3. Run the following command to export data of the entire database. Set the parameters in italics. For details, see Table 1.

    mysqldump -h {DB_ADDRESS} -P {DB_PORT} -u {DB_USER} -p --hex-blob --complete-insert --skip-lock-tables --skip-tz-utc --skip-add-locks --set-gtid-purged=OFF --no-create-info {DB_NAME} > {mysql_data.sql}

  4. Run the following command on the ECS to view the SQL files exported in 2 and 3.

    ls -l

Importing Data into DDM

  1. Run the following command on the ECS to import the structure file into DDM:

    mysql -f -h {DDM_ADDRESS} -P {DDM_PORT} -u {DDM_USER} -p {DB_NAME} < {mysql_schema.sql}
    Enter password: **********
    Table 2 Parameter description

    Parameter

    Description

    Remarks

    DDM_ADDRESS

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

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

    DDM_PORT

    Listening port of the DDM instance into which data is to 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 into which data is to be imported

    N/A

    mysql_schema.sql

    Name of the structure file to be imported

    Name of the file exported in 2

    mysql_data.sql

    Name of the entire DB data file to be imported

    Name of the file exported in 3

  2. Run the following command on the ECS to import the data file into DDM:

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