This topic describes how to migrate the data of an ApsaraDB RDS for MySQL instance to an ApsaraDB RDS for PostgreSQL instance and use the ApsaraDB RDS for PostgreSQL instance as the real-time data analytics instance of the ApsaraDB RDS for MySQL instance.

Background information

If you want to analyze the data of an ApsaraDB RDS for MySQL instance by using the features of ApsaraDB RDS for PostgreSQL or you want to process spatio-temporal data or analyze user profile data by using GIS, you can use Data Transmission Service (DTS) to migrate the data of the ApsaraDB RDS for MySQL instance to an ApsaraDB RDS for PostgreSQL instance. Then, you can use the ApsaraDB RDS for PostgreSQL instance as the real-time data analytics instance of the ApsaraDB RDS for MySQL instance.

Prerequisites

  • An ApsaraDB RDS for MySQL instance is created. This instance is known as the source instance. For more information, see Create an ApsaraDB RDS for MySQL instance.
  • An ApsaraDB RDS for PostgreSQL instance is created. This instance is known as the destination instance. For more information, see Create an ApsaraDB RDS for PostgreSQL instance.
  • The available storage space of the destination instance must be greater than the storage space that is occupied on the source instance.

Procedure

  1. Prepare test data on the source instance.
  2. Create a migration task.
  3. Migrate the data of the source instance to the destination instance..

Prepare test data on the source instance

  1. Connect to the source instance.
    mysql -h <Endpoint> -u <Username> -P <Port number> -p
  2. Create a test database named db1.
    CREATE DATABASE db1;
  3. Log on to the db1 database.
    USE db1;
  4. Create a test table named test_mm and a test table named test_innodb.
    CREATE TABLE `test_mm` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_id` VARCHAR (20) NOT NULL,
    `group_id` INT (11) NOT NULL,
    `create_time` datetime NOT NULL,
    PRIMARY KEY (`id`),KEY `index_user_id` (`user_id`) USING HASH
    ) ENGINE = innodb AUTO_INCREMENT = 1 
    DEFAULT CHARSET = utf8;
    CREATE TABLE `test_innodb` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_id` VARCHAR (20) NOT NULL,
    `group_id` INT (11) NOT NULL,
    `create_time` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
    ) ENGINE = innodb AUTO_INCREMENT = 1 
    DEFAULT CHARSET = utf8;
  5. Create a rand_ string function that is used to generate random strings.
    delimiter $$
    CREATE FUNCTION rand_string(n int) RETURNS varchar(255)
    begin
    declare chars_str varchar(100)
    default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
    declare return_str varchar(255) default "";
    declare i int default 0;
    while i < n do
    set return_str=concat(return_str,substring(chars_str,floor(1+rand()*62),1));
    set i= i+1;
    end while;
    return return_str;
    end $$
    delimiter ;
  6. Create a stored procedure that is used to insert test data.
    delimiter $$
    CREATE PROCEDURE `insert_data`(IN n int)
    BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
    INSERT into test_mm (user_id,group_id,create_time ) VALUEs
    (rand_string(20),FLOOR(RAND() * 100) ,now() );
    set i=i+1;
    END WHILE;
    END $$
    delimiter ;
  7. Call the stored procedure that you created.
    CALL insert_data(1000000);
    INSERT INTO test_innodb SELECT * FROM test_mm;

Create a migration task

  1. Before you create a migration task, you must log on to the ApsaraDB RDS console and create a database in the destination instance. The database that you create is the destination database to which you can migrate the data of the source instance. For more information, see Create a database on an ApsaraDB RDS for PostgreSQL instance.
    Note In the example provided in this section, the database that you create is named db2.
  2. Log on to the DTS console
  3. In the left-side navigation pane, click Data Migration.
  4. In the upper Migration Tasks section of the page, select the region where the destination instance resides.
    Create a migration task
  5. In the upper-right corner of the page, click Create Migration Task.
  6. Configure the source and destination databases.
    Configure source and destination databases
    Section Parameter Description
    N/A Task Name

    DTS automatically generates a task name. We recommend that you specify an informative name for easy identification. You do not need to use a unique task name.

    Source Database Instance Type Select RDS Instance.
    Instance Region Select the region where the source instance resides.
    RDS Instance ID Select the ID of the source instance.
    Database Account Enter the username of the account has permissions on the source database in the source instance. The account must have the following permissions:
    • The SELECT permission that is required for the schema migration.
    • The SELECT permission that is required for full data migration.
    • The REPLICATION CLIENT permission, REPLICATION SLAVE permission, SHOW VIEW permission, and SELECT permission that are required for incremental data migration.
    Database Password Enter the password of the database account.
    Connection Method Select Non-encrypted or SSL-encrypted. If you select SSL-encrypted, you must enable SSL encryption for the RDS instance before you configure the data migration task. For more information, see Configure SSL encryption for an ApsaraDB RDS for MySQL instance.
    Destination Database Instance Type Select RDS Instance.
    Instance Region Select the region where the destination instance resides.
    RDS Instance ID Select the ID of the destination instance.
    Database Account Enter the username of the account that has permissions on the destination database in the destination instance. The account must have the following permissions:
    • LOGIN permission.
    • CONNECT permission and CREATE permission on the destination database.
    • CREATE permission on the schema of the destination database.
    Database Password Enter the password of the database account.
  7. Click Test Connectivity next to the Database Password in the Source Database section and the Source Database section. You can proceed with the next step only after the source instance and the destination instance passes the connectivity test.
  8. In the lower-right corner of the page, click Set Whitelist and Next.
  9. Specify the migration types and the tables that you want to migrate.
    Select migration types and the tables that you want to migrate.
    Parameter Description
    Migration Types Select Schema Migration, Full Data Migration, and Incremental Data Migration.
    Available Select the tables that you want to migrate. For this example, select the test_innodb table and the test_mm table.
    Selected View the selected tables.
    Rename Databases and Tables The default value is Do Not Change Database and Table Names. If you want to modify the names of multiple databases and tables at a time, select Change Database and Table Names. In the lower-right corner of the page, click Advanced Settings to modify the names of multiple databases and tables at a time.
    Retry Time for Failed Connection The default value is 720 minutes. You do not need to change the default value.
  10. In the lower part of the page, click Precheck.
    Note
    • A precheck is required before the migration task starts. The migration task starts only after it passes the precheck.
    • If the migration task fails the precheck, you can click the Note icon next to each failed check item to view the details about the failure.
      • You can troubleshoot the failed check items as instructed. Then, you must run the precheck again.
      • If you do not want to troubleshoot the failed check items, you can ignore them and perform a precheck again.
  11. When Precheck Passed 100% is displayed on the Precheck page, click Next.
    Precheck successful
  12. In the Confirm Settings dialog box, specify the Channel Specification and select Data Transmission Service.
    Note DTS provides various migration specifications. The migration speed varies based on the migration specifications that you select based on your business requirements. For more information, see Specifications of data migration instances.
  13. Select Data Transmission Service (Pay-As-You-Go) Service Terms.
  14. Click Buy and Start to start the migration task. You can view the progress of the migration task in the migration task list.

Migrate the data of the source instance to the destination instance.

  1. View the progress of the full data migration operation.
    1. Connect to the destination instance.
      psql -h <Endpoint> -U <Username> -p <Port number> -d db2
      Note The db2 database is created in the "Create a migration task" section to run as the destination database. Therefore, when you connect to the destination instance, you can specify the database name as db2.
    2. Run the \dn command to check whether the db1 database is mapped as a schema named db1 in the db2 database.
      db2=> \dn
         List of schemas
        Name  |   Owner
      --------+------------
       db1    | test1
       public | pg*******
      (2 rows)
      Note After the data of the db1 database in the source instance is migrated to the db2 database in the destination instance, a schema named db1 is generated in the db2 database to accommodate the data of the db1 database.
    3. Run the \dt+ db1.* command to view the status of the tables in the db1 database.
      db2=> \dt+ db1.*
                                  List of relations
       Schema |    Name     | Type  | Owner | Persistence | Size  | Description
      --------+-------------+-------+-------+-------------+-------+-------------
       db1    | test_innodb | table | test1 | permanent   | 65 MB |
       db1    | test_mm     | table | test1 | permanent   | 65 MB |
      (2 rows)
                                      
    4. Run the following commands to query the number of data records in the test_innodb table and the test_mm table:
      # Query the number of data records in the test_innodb table. 
      SELECT COUNT(*) FROM db1.test_innodb;
      
      # Query the number of data records in the test_mm table. 
      SELECT COUNT(*) FROM db1.test_mm;
      Result:
      db2=> SELECT COUNT(*) FROM db1.test_innodb;
        count
      ---------
       1000000
      (1 row)
                
      db2=> SELECT COUNT(*) FROM db1.test_mm;
        count
      ---------
       1000000
      (1 row)                        
      Note

      After the data of the db1 database in the source instance is migrated to the db2 database in the destination instance, a schema named db1 is generated in the db2 database to accommodate the data of the db1 database. Therefore, when you run a query in the destination instance to query the data of the db1 database, you must specify the schema name as db1.

      If you do not want to specify the schema every time when you query the data of the db1 database, you can set the search_path parameter.
      db2=> show search_path;
         search_path
      -----------------
       "$user", public
      (1 row)
      
      db2=> set search_path = db1, "$user", public;
      SET
      db2=> show search_path;
           search_path
      ----------------------
       db1, "$user", public
      (1 row)
                                              
  2. Test whether the data that is inserted into the source database is continuously migrated to the destination database.
    1. Insert data into the source database.
      INSERT INTO test_innodb (user_id, group_id, `create_time`) VALUES ('testuser', 1, '2021-07-29 12:00:00');
      Result
      mysql> INSERT INTO test_innodb (user_id, group_id, `create_time`) VALUES ('testuser', 1, '2021-07-29 12:00:00');
      Query OK, 1 row affected (0.04 sec)
      
      mysql> SELECT * FROM test_Innodb WHERE user_id = 'testuser';
      +---------+----------+----------+---------------------+
      | id      | user_id  | group_id | create_time         |
      +---------+----------+----------+---------------------+
      | 1000001 | testuser |        1 | 2021-07-29 12:00:00 |
      +---------+----------+----------+---------------------+
      1 row in set (0.03 sec)
    2. Check whether the data that is inserted into the source database is migrated to the destination database.
      db2=> SELECT * FROM test_Innodb WHERE user_id = 'testuser';
         id    | user_id  | group_id |     create_time
      ---------+----------+----------+---------------------
       1000001 | testuser |        1 | 2021-07-29 12:00:00
      (1 row)
  3. Test whether the data that is updated in the source database is continuously migrated to the destination database.
    1. Update data in the source database.
      UPDATE test_innodb set group_id = 2 WHERE user_id = 'testuser';
      Result:
      mysql> UPDATE test_innodb set group_id = 2 WHERE user_id = 'testuser';
      Query OK, 1 row affected (0.03 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
      
      mysql> SELECT * FROM test_innodb WHERE user_id = 'testuser';
      +---------+----------+----------+---------------------+
      | id      | user_id  | group_id | create_time         |
      +---------+----------+----------+---------------------+
      | 1000001 | testuser |        2 | 2021-07-29 12:00:00 |
      +---------+----------+----------+---------------------+
      1 row in set (0.03 sec)
    2. Check whether the data that is updated in the source database is migrated to the destination database.
      db2=> SELECT * FROM test_innodb WHERE user_id = 'testuser';
         id    | user_id  | group_id |     create_time
      ---------+----------+----------+---------------------
       1000001 | testuser |        2 | 2021-07-29 12:00:00
      (1 row)
  4. Test whether the data that is deleted from the source instance is continuously migrated to the destination database.
    1. Delete data from the source database.
      DELETE FROM test_innodb WHERE user_id = 'testuser';
      Result:
      mysql> DELETE FROM test_innodb WHERE user_id = 'testuser';
      Query OK, 1 row affected (0.03 sec)
      
      mysql> SELECT * FROM test_innodb WHERE user_id = 'testuser';
      Empty set (0.03 sec)
      
      mysql> SELECT MAX(id) FROM test_innodb;
      +---------+
      | MAX(id) |
      +---------+
      | 1000000 |
      +---------+
      1 row in set (0.03 sec)
      Note When no data is inserted, the maximum value of the id field in the return result is 1000000. After data is inserted, the value of the id field increases to 1000001. After data is deleted, the value of the id field decreases to 1000000.
    2. Check whether the data that is deleted from the source database is also deleted from the destination database.
      db2=> SELECT * FROM test_innodb WHERE user_id = 'testuser';
       id | user_id | group_id | create_time
      ----+---------+----------+-------------
      (0 rows)
      
      db2=> SELECT MAX(id) FROM test_innodb;
         max
      ---------
       1000000
      (1 row)