Export data to a self-managed MySQL database - AnalyticDB

AnalyticDB for MySQL allows you to use external tables to import and export data. This topic describes how to use external tables of AnalyticDB for MySQL Data Warehouse Edition (V3.0) to export data to a self-managed MySQL database.

Prerequisites

An Elastic Compute Service (ECS) instance is created in the same virtual private cloud (VPC) 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.
Test data is prepared.
In this example, an ECS-hosted self-managed MySQL database named test_adb is used as the destination database. A destination table named courses is created in the database. The table is used to store data that is exported from the AnalyticDB for MySQL cluster. The following statement can be used to create a destination 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.

Procedure

Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.
Create a source database. For more information, see Create a database.
In this example, a database named adb_demo is used as the source database in the AnalyticDB for MySQL cluster.
Create a source table and insert data into the source table.
Execute the following statement to create a source table named courses in the adb_demo source database. Then, export data in the source table to the courses table in the test_adb self-managed MySQL database.
```
CREATE TABLE courses (
id bigint AUTO_INCREMENT,
name varchar NOT NULL,
grade varchar DEFAULT '1st Grade',
submission_date timestamp
) DISTRIBUTED BY HASH(id);
```
Execute the following statement to insert a row of data into the courses source table:
```
INSERT INTO courses (name,submission_date) VALUES("Jams",NOW());
```

Create an external table.

Execute the following statement to create an external table named courses_external_table in the adb_demo 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 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 ECS-hosted self-managed MySQL database.
`url`	The primary private IP address or the VPC endpoint of the ECS instance, and the name of the destination database. In this example, the name of the destination database is `test_adb`. Perform the following steps to view the VPC endpoint of the ECS instance: 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 destination table in the ECS-hosted self-managed MySQL database. In this example, `courses` is used.
`username`	The username of the database account that is used to connect to the ECS-hosted self-managed MySQL database.
`password`	The password of the database account.
`charset`	The character set that is used by the source database. Valid values: gbk utf8 (default) utf8mb4

Export data from the AnalyticDB for MySQL cluster to the ECS-hosted self-managed MySQL database.
Execute the following statement:
```
REPLACE INTO courses_external_table
SELECT * FROM courses;
```

What to do next

After the data is exported, you can connect to the test_adb self-managed MySQL database. Then, execute the following statement to check whether the data is imported from the source table to the courses destination table:

SELECT * FROM courses LIMIT 100;