AnalyticDB:Import data from self-managed MySQL

Prerequisites

• You have an ECS instance that is in the same region and VPC as your AnalyticDB for MySQL cluster.

• You have installed a self-managed MySQL database on the ECS instance.

• You have added an inbound rule to the security group of the ECS instance to allow access to the self-managed MySQL database on port 3306. For more information, see Add a security group rule.

• You have created a database and prepared test data in the self-managed MySQL database.

  In this example, a source database named test_adb is created, and 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.

  Important

  When you enable or disable ENI, database connections may be interrupted for approximately 2 minutes. During this period, you cannot perform read or write operations. Proceed with caution when you enable or disable ENI.

Procedure

1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.

2. Create a database. For more information, see Create a database.

   In this example, a database named adb_demo is created in the AnalyticDB for MySQL cluster.

3. Create an external table.

   Run the following command to create an external table named goods_external_table in the target database adb_demo.

   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'	Specifies the storage engine for the external table. This example uses MySQL.
   TABLE_PROPERTIES	The connection information that AnalyticDB for MySQL uses to access the self-managed MySQL database.
   url	The Primary Private IP (VPC address) of the ECS instance and the name of the source database. In this example, the source database is test_adb. To view the IP address: Log on to the ECS console and find the target instance. On the Instance Details page, in the Network Information section, view the Primary Private IP. 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. In this example, the table is named goods.
   username	The username for accessing the source database in the self-managed MySQL instance.
   password	The password for the account.
   charset	The character set of the MySQL database. Valid values: gbk UTF8 (Default) utf8mb4

4. Create a target table.

   Run the following command to create a target table named mysql_import_test in the adb_demo database. This table is used to store the data imported from the self-managed MySQL database.

   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 on the ECS instance to the target AnalyticDB for MySQL cluster.

   REPLACE INTO mysql_import_test
   SELECT * FROM goods_external_table;