AnalyticDB:Export data to ApsaraDB RDS for MySQL - AnalyticDB

Use an external table to export data from an AnalyticDB for MySQL cluster to an ApsaraDB RDS for MySQL instance. The external table maps to a destination table in the RDS instance, and a single REPLACE INTO ... SELECT statement moves the data.

Prerequisites

Before you begin, make sure you have:

An AnalyticDB for MySQL cluster and an ApsaraDB RDS for MySQL instance in the same virtual private cloud (VPC). To verify, compare their VPC IDs:
- RDS instance: In the ApsaraDB RDS console, click the instance ID, then view the VPC ID in the Network Type field on the Database Connection page.
- AnalyticDB cluster: In the AnalyticDB for MySQL console, go to the Data Warehouse Edition (V3.0) tab, click the cluster ID, then view the VPC ID in the Network Information section of the Cluster Information page.
A database and test data on the RDS instance. See Create accounts and databases and Connect to an ApsaraDB RDS for MySQL instance
(Elastic mode only) ENI turned on in the Network Information section of the Cluster Information page

Export data to RDS for MySQL

This example exports data from a source table named courses in the AnalyticDB for MySQL cluster to a destination table of the same name in the RDS instance. The source database is adb_demo and the destination database is test_adb.

Step 1: Create the destination table in RDS

Connect to the test_adb database in your RDS instance, then create the 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)
);

Step 2: Connect to the AnalyticDB for MySQL cluster

Connect to the cluster, then create a source database. This example uses a database named adb_demo.

Step 3: Create the source table and insert test data

Create a source table named courses in the adb_demo database:

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

Insert a row of test data:

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

Step 4: Create an external table

Create an external table named courses_external_table in the adb_demo database. This table maps to the courses table in the RDS instance and acts as the write target:

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

Replace the placeholder values in TABLE_PROPERTIES with your actual connection details:

Parameter	Description
`ENGINE='mysql'`	Identifies MySQL as the storage engine for the external table.
`url`	The VPC endpoint of the RDS instance and the destination database name. Format: `jdbc:mysql://<vpc-endpoint>:3306/<database-name>`. Example: `jdbc:mysql://192.168.128.***:3306/test_adb`. To find your VPC endpoint, see View and manage instance endpoints and ports.
`tablename`	The name of the destination table in the RDS database.
`username`	The account name used to connect to the RDS database.
`password`	The password for the account.
`charset`	The character set of the source database. Default: `utf8`. Valid values: `gbk`, `utf8`, `utf8mb4`.

Step 5: Export the data

Run the following statement to copy data from the AnalyticDB source table to the RDS destination table:

REPLACE INTO courses_external_table
SELECT * FROM courses;

Verify the export

SELECT * FROM courses LIMIT 100;

Confirm that the exported rows match the source data.

What's next

After the data is exported, you can log on to the test_adb destination database in the ApsaraDB RDS for MySQL instance to verify that the data has been imported from the source table to the courses destination table.