Migrate Self-Managed MySQL Data via External Tables - AnalyticDB for MySQL

AnalyticDB for MySQL uses external tables to read data directly from a self-managed MySQL database, then loads it into a Data Warehouse Edition cluster table in a single SQL statement. This topic walks you through the end-to-end process using a sample dataset.

Before you begin

Before you begin, make sure you have:

An Elastic Compute Service (ECS) instance in the same region and Virtual Private Cloud (VPC) as your AnalyticDB for MySQL cluster
A self-managed MySQL database installed on that ECS instance
An inbound security group rule that opens port 3306 on the ECS instance. See Add a security group rule
A source database and test data ready on the self-managed MySQL database

This example uses a source database named test_adb that contains a table called goods:

CREATE TABLE goods (
  goods_id bigint(20) NOT NULL,
  price double NOT NULL,
  class bigint(20) NOT NULL,
  name varchar(32) NOT NULL,
  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (goods_id)
);

Elastic mode only: If your cluster runs in elastic mode, enable ENI on the Cluster Information page under Network Information before proceeding.

Important

Enabling or disabling ENI interrupts database connections for approximately 2 minutes. Read and write operations are unavailable during this window.

Import data from a self-managed MySQL database

Step 1: Connect to the cluster

Connect to your AnalyticDB for MySQL cluster. See Connect to an AnalyticDB for MySQL cluster.

Step 2: Create a destination database

Create a database in the cluster to hold the imported data. See Create a database.

This example uses a database named adb_demo.

Step 3: Create an external table

An external table maps to the source table in the self-managed MySQL database. AnalyticDB for MySQL uses this mapping to read data over JDBC without storing it locally.

Run the following statement to create an external table named goods_external_table in adb_demo:

CREATE TABLE IF NOT EXISTS goods_external_table (
  goods_id bigint(20) NOT NULL,
  price double NOT NULL,
  class bigint(20) NOT NULL,
  name varchar(32) NOT NULL,
  update_time timestamp,
  PRIMARY KEY (goods_id)
)
ENGINE='mysql'
TABLE_PROPERTIES='{
  "url":"jdbc:mysql://mysql-vpc-address:3306/test_adb",
  "tablename":"goods",
  "username":"mysql-user-name",
  "password":"mysql-user-password",
  "charset":"utf8"
}';

Replace the placeholders in TABLE_PROPERTIES with your actual values:

Parameter	Description	Required
`ENGINE='mysql'`	The storage engine for the external table. This example uses MySQL.	Yes
`url`	JDBC connection URL for the source database. Format: `jdbc:mysql://<ECS-primary-private-IP>:3306/<source-database-name>`. Example: `jdbc:mysql://192.168.128.*:3306/test_adb`. To find the ECS primary private IP address, go to the ECS console, open the target instance, and check Primary Private IP Address under the Instance Details tab in the Network Information** section.	Yes
`tablename`	Name of the source table in the self-managed MySQL database.	Yes
`username`	Account used to connect to the self-managed MySQL database.	Yes
`password`	Password for the account.	Yes
`charset`	Character set of the MySQL database. Valid values: `gbk`, `utf8` (default), `utf8mb4`.	No

Step 4: Create a destination table

Create a table in adb_demo to store the imported data:

CREATE TABLE IF NOT EXISTS mysql_import_test (
  goods_id bigint(20) NOT NULL,
  price double NOT NULL,
  class bigint(20) NOT NULL,
  name varchar(32) NOT NULL,
  update_time timestamp,
  PRIMARY KEY (goods_id)
)
DISTRIBUTED BY HASH(goods_id);

Step 5: Import the data

Run a single REPLACE INTO ... SELECT statement to copy all rows from the external table into the destination table:

REPLACE INTO mysql_import_test
SELECT * FROM goods_external_table;

Verify the import

Connect to the adb_demo database and run the following query to confirm the data was imported successfully:

SELECT * FROM mysql_import_test LIMIT 100;

What's next

Add more external tables to consolidate data from other self-managed MySQL databases into the same cluster.