Export data from AnalyticDB for MySQL to MySQL

This topic describes how to use AnalyticDB for MySQL external tables and the INSERT INTO statement to export data from AnalyticDB for MySQL to MySQL.

Prerequisites

• An ApsaraDB RDS for MySQL instance is created, a whitelist is configured, and an account, a databases, and tables are created. For more information, see Quick start of ApsaraDB RDS MySQL.
  Note The ApsaraDB RDS for MySQL instance and AnalyticDB for MySQL cluster must be in the same VPC. You must add the CIDR block of this VPC to the whitelist of the ApsaraDB RDS for MySQL instance. In this case, the AnalyticDB for MySQL database can use the VPC endpoint of the ApsaraDB RDS for MySQL instance to access the ApsaraDB RDS for MySQL database.

  In this example, a table named courses is created in the test_adb database in ApsaraDB RDS for MySQL to store the data exported from AanlyticDB for MySQL.

  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)
  )                     

• An AnalyticDB for MySQL cluster is created, a whitelist is configured, and an account and a database are created. For more information, see Quick start of AnalyticDB for MySQL.

  In this example, the data in the courses table of the adb_demo database in AnalyticDB for MySQL is exported to the courses table of the test_adb database in ApsaraDB RDS for MySQL.

  create Table courses (
  id bigint AUTO_INCREMENT,
  name varchar NOT NULL,
  grade varchar DEFAULT 'Grade 3',
  submission_date timestamp
  ) DISTRIBUTE BY HASH(id)                   

  insert into courses (name,submission_date) values("Jams",NOW());                   

Note If AnalyticDB MySQL cluster is in elastic mode, log on to the AnalyticDB MySQL console and view the cluster information. Enable Elastic Network Interface (ENI) in the Network Information section.

1. Connect to an AnalyticDB for MySQL cluster and the target database. For more information, see Connect to a cluster.
2. Use the CREATE TABLE statement to create an external mapping table named courses_external_table in the AnalyticDB for MySQL database named adb_demo.

    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"
   }'                 

   Parameter	Description
   ENGINE='mysql'	Specifies the table as an external table whose data engine is MySQL.
   TABLE_PROPERTIES	Specifies the connection information used by the AnalyticDB for MySQL database to access the data in the ApsaraDB RDS for MySQL database.
   url	The VPC endpoint of the ApsaraDB RDS for MySQL instance. The endpoint is in the "jdbc:mysql://mysql-vpc-address:3306/rds-database-name" format. Note AnalyticDB for MySQL can access ApsaraDB RDS for MySQL only through the VPC endpoint. You can log on to the ApsaraDB RDS for MySQL console and view the internal endpoint of the ApsaraDB RDS for MySQL instance on the Basic Information page.
   tablename	The source table in the ApsaraDB RDS for MySQL database, which is the courses table in this example.
   username	The account used to connect to the test_adb database in ApsaraDB RDS for MySQL. For more information, see Create databases and accounts for an ApsaraDB RDS for MySQL instance.
   password	The password of the account used to connect to the ApsaraDB RDS for MySQL database.

3. Execute the INSERT statement to export the data in AnalyticDB for MySQL to the ApsaraDB RDS for MySQL.

   insert into courses_external_table
   select * from courses;