AnalyticDB:Import data from a self-managed MySQL database to AnalyticDB for MySQL

Prerequisites

• An Elastic Compute Service (ECS) instance is created within the same region as the AnalyticDB for MySQL cluster.
• The self-managed MySQL database is hosted on the ECS instance.
• The port number to which the self-managed MySQL database is connected is authorized to access the ECS instance. For more information, see Add a security group rule.
• A source table is created on the self-managed MySQL database. Test data is prepared.
  In this example, the test_adb self-managed MySQL database hosted on the ECS instance is used as the source database. A source table named goods is created in the database. The following statement is used to create the source table:

  CREATE TABLE goods (
  goods_id bigint(20) NOT NULL,
  price double NOT NULL,
  class bigint(20) NOT NULL,
  name varchar(32) NOT NULL,
  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (goods_id)
  );

• If the AnalyticDB for MySQL cluster is in elastic mode, you must turn on ENI in the Network Information section of the Cluster Information page.

Procedure

1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.
2. Create a destination database. For more information, see Create a database.

   In this example, the database named adb_demo is used as the destination database in the AnalyticDB for MySQL cluster.

3. Create an external table.

   Execute the following statement to create an external table named goods_external_table in the adb_demo database:

   CREATE TABLE IF NOT EXISTS goods_external_table (
           goods_id bigint(20) NOT NULL,
           price double NOT NULL,
           class bigint(20) NOT NULL,
           name varchar(32) NOT NULL,
           update_time timestamp,
           PRIMARY KEY (goods_id)
    )
           ENGINE='mysql'  
           TABLE_PROPERTIES='{  
           "url":"jdbc:mysql://mysql-vpc-address:3306/test_adb",  
           "tablename":"goods",  
           "username":"mysql-user-name",  
           "password":"mysql-user-password",
           "charset":"utf8"
     }';

   Parameter	Description
   ENGINE='mysql'	The storage engine that is used for the external table. In this example, MySQL is used.
   TABLE_PROPERTIES	The connection information that is used by the AnalyticDB for MySQL cluster to access the data in the self-managed MySQL database hosted on the ECS instance.
   url	The primary private IP address or the Virtual Private Cloud (VPC) endpoint that is used to connect to the source database in the ECS instance. In this example, the test_adb database is used. Perform the following steps to view the primary private IP address: Log on to the ECS console and find the ECS instance. In the Network Information section on the Instance Details tab, view the Primary Private IP Address value of the ECS instance. Format: "jdbc:mysql://mysql-vpc-address:3306/ecs-database-name". Example: jdbc:mysql://192.168.128.***:3306/test_adb.
   tablename	The name of the source table in the self-managed MySQL database hosted on the ECS instance. In this example, goods is used.
   username	The name of the database account that is used to connect to the self-managed MySQL database hosted on the ECS instance.
   password	The password of the database account.
   charset	The character set that is used by the source database in the ApsaraDB RDS for MySQL instance. Default value: utf8. Valid values: gbk utf8 utf8mb4

4. Create a destination table.

   Execute the following statement to create a destination table named mysql_import_test in the adb_demo database. The destination table is used to store the data that is imported from the self-managed MySQL database hosted on the ECS instance.

   CREATE TABLE IF NOT EXISTS  mysql_import_test (
           goods_id bigint(20) NOT NULL,
           price double NOT NULL,
           class bigint(20) NOT NULL,
           name varchar(32) NOT NULL,
           update_time timestamp,
           PRIMARY KEY (goods_id)
    )
   DISTRIBUTED BY HASH(goods_id);

5. Import data from the self-managed MySQL database hosted on the ECS instance to the AnalyticDB for MySQL cluster.

   REPLACE INTO mysql_import_test
   SELECT * FROM goods_external_table;