AnalyticDB:Export data to a self-managed MySQL database

Prerequisites

Before you begin, ensure that you have:

• An ECS instance in the same virtual private cloud (VPC) and region as the AnalyticDB for MySQL cluster

• A self-managed MySQL database hosted on that ECS instance

• The MySQL port (typically 3306) authorized for inbound traffic in the ECS security group rules. For details, see Add a security group rule

• Test data ready to export

• (Required for elastic mode) ENI turned on in the Network Information section of the Cluster Information page

Example setup used in this guide:

The destination is an ECS-hosted MySQL database named test_adb, with a courses table created as follows:

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

Export data using an external table

This procedure creates a source table in AnalyticDB for MySQL, creates an external table mapped to the destination MySQL database, and then writes data to the external table.

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

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

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 INTO courses_external_table
SELECT * FROM courses;

Verify the export

Connect to the test_adb MySQL database and run:

SELECT * FROM courses LIMIT 100;

Confirm the exported rows appear in the courses destination table.

Troubleshooting

Cannot connect to the MySQL database

The most common cause is a missing or incorrect security group rule on the ECS instance. Check that port 3306 is authorized for inbound traffic from your AnalyticDB for MySQL cluster. See Add a security group rule.

If the port is open but the connection still fails:

• Verify that the url in TABLE_PROPERTIES uses the Primary Private IP Address of the ECS instance, not the public IP.

• Verify that username and password are correct.

• Verify that the JDBC URL format is jdbc:mysql://<ip>:3306/<database-name> with no trailing slash or extra characters.

No data appears in the destination table after export

Run SELECT * FROM courses LIMIT 10; in the AnalyticDB for MySQL cluster to confirm the source table contains data before running REPLACE INTO.

Also verify that the schema of courses_external_table matches the destination table in test_adb. Mismatched column types or names can cause the write to fail or raise an error.