AnalyticDB:Export to ApsaraDB RDS for MySQL

Prerequisites

• The ApsaraDB RDS for MySQL instance and the AnalyticDB for MySQL cluster must be in the same VPC and have the same VPC ID.

  Note

  • To find the VPC ID of the ApsaraDB RDS for MySQL instance, log on to the ApsaraDB RDS console, click the instance ID, and on the Database Connection page, view the ID in the Network Type section.

  • Log on to the AnalyticDB for MySQL console. On the Data Warehouse Edition tab, click the cluster ID. On the Cluster Information page, view the VPC ID of the AnalyticDB for MySQL cluster in the Network Information section.

• Create a database in the ApsaraDB RDS for MySQL instance, establish a connection to the instance, and prepare the required test data. For more information, see Create accounts and databases and Connect to an ApsaraDB RDS for MySQL instance.

  In this example, the destination database in the ApsaraDB RDS for MySQL instance is named test_adb. A destination table named courses is required in this database to store the data exported from the AnalyticDB for MySQL cluster. Use the following statement to create the table:

  CREATE TABLE courses (
      id bigint NOT NULL,
      name varchar(32) NOT NULL,
      grade varchar(32) NOT NULL,
      submission_date  timestamp NOT NULL,
      PRIMARY KEY (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 source database. For more information, see Create a database.

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

3. Create the source table and insert data.

   Run the following statement to create a source table named courses in the adb_demo source database. The data from this table will be exported to the courses table in the test_adb destination database.

   CREATE TABLE courses (
   id bigint AUTO_INCREMENT,
   name varchar NOT NULL,
   grade varchar DEFAULT '1st Grade',
   submission_date timestamp
   ) DISTRIBUTED BY HASH(id);

   Run the following statement to insert a row of data into the source table courses:

   INSERT INTO courses (name,submission_date) VALUES("Jams",NOW());

4. Create an external table.

   Run the following statement to create an external table named courses_external_table in the adb_demo source database.

    CREATE TABLE IF NOT EXISTS courses_external_table(
    id bigint NOT NULL,
    name varchar(32) NOT NULL,
    grade varchar(32) NOT NULL,
    submission_date  timestamp NOT NULL,
    PRIMARY KEY (id)
    )
   ENGINE='mysql'  
   TABLE_PROPERTIES='{  
   "url":"jdbc:mysql://mysql-vpc-address:3306/test_adb",  
   "tablename":"courses",  
   "username":"mysql-user-name",  
   "password":"mysql-user-password",
   "charset":"utf8"
   }';

   Parameter	Description
   ENGINE='mysql'	The storage engine for the external table. This example uses MySQL.
   TABLE_PROPERTIES	AnalyticDB for MySQL access methods for RDS MySQL data.
   url	The VPC endpoint of the ApsaraDB RDS for MySQL instance and the name of the destination database. In this example, the destination database is test_adb. For information about how to view the endpoint of the RDS instance, see View and manage instance endpoints and ports. Format: "jdbc:mysql://mysql-vpc-address:3306/rds-database-name". Example: jdbc:mysql://rm-bp1hem632****.mysql.rds.aliyuncs.com:3306/test_adb.
   tablename	The name of the destination table in the ApsaraDB RDS for MySQL instance. This example uses courses.
   username	The username for the destination ApsaraDB RDS for MySQL database.
   password	The password for the specified username.
   charset	The MySQL character set. Valid values: gbk UTF8 (Default) utf8mb4

5. Export data from the source AnalyticDB for MySQL cluster to the destination ApsaraDB RDS for MySQL instance.

   Run the following statement:

   REPLACE INTO courses_external_table
   SELECT * FROM courses;