Export data to a self-managed MySQL database

Prerequisites

• You have an ECS instance that is in the same region and VPC as your AnalyticDB for MySQL cluster.

• You have installed a self-managed MySQL database on the ECS instance.

• You have added an inbound rule to the security group of the ECS instance to allow access to the self-managed MySQL database on port 3306. For more information, see Add a security group rule.

• You have created a database and prepared test data in your self-managed MySQL database.

  In this example, the destination database is a self-managed MySQL database named test_adb. A destination table named courses is created in this database to store the data exported from the AnalyticDB for MySQL cluster. The following statement is used to create the 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.

  Important

  When you enable or disable ENI, database connections may be interrupted for approximately 2 minutes. During this period, you cannot perform read or write operations. Proceed with caution when you enable or disable ENI.

Procedure

1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.

2. Create a source database. For more information, see Create a database.

   In this example, the source database in the AnalyticDB for MySQL cluster is named adb_demo.

3. Create a source table and insert data.

   Run the following command to create a source table named courses in the adb_demo source database. The data from this table will be exported to the courses table in the test_adb destination database.

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

   Run the following statement to insert a row of data into the courses source table:

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

4. Create an external table.

   Run the following statement to create an external mapping table named courses_external_table in the source database 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",
   "charset":"utf8"
   }';

   Parameter	Description
   ENGINE=’mysql’	The storage engine for the external table. This example uses MySQL.
   TABLE_PROPERTIES	The connection properties that AnalyticDB for MySQL uses to access the self-managed MySQL database.
   url	The Primary Private IP (VPC IP address) of the ECS instance and the name of the destination database. In this example, the database name is test_adb. To find the IP address, perform the following steps: Log on to the ECS console and find the target instance. On the Instance Details page, in the Network Information section, view the Primary Private IP. 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 self-managed MySQL database. In this example, the table name is courses.
   username	The username of the database account used to access the destination database.
   password	The password for the specified database account.
   charset	The character set of the MySQL database. Valid values: gbk UTF8 (default) utf8mb4

5. Export data from the AnalyticDB for MySQL cluster to the destination self-managed MySQL database.

   Syntax:

   REPLACE INTO courses_external_table
   SELECT * FROM courses;