To facilitate the demonstration, complete the following steps to prepare the test data in ApsaraDB for RDS (RDS).

  1. Create an ApsaraDB RDS for MySQL instance
  2. Set a whitelist
  3. Create databases and accounts for an ApsaraDB RDS for MySQL instance
  4. Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance
  5. Create a table and insert the test data to the table.
    1. Create a table named person.
      create table person (
id int,
name varchar(1023),
age int
);
    2. Insert the test data to the person table.
      insert into person 
values 
(1, 'james', 10), 
(2, 'bond', 20), 
(3, 'jack', 30), 
(4, 'lucy', 40);
  6. Create a MySQL schema.

    All tables in the example use the following schema:

    CREATE SCHEMA hello_mysql_vpc_rds WITH DBPROPERTIES (
    CATALOG = 'mysql', 
    LOCATION = 'jdbc:mysql://rm-2zer0vg58mfofake.mysql.rds.aliyuncs.com:3306/rds_mysql_dbname',
    USER = 'rds_mysqldb_username',
    PASSWORD = 'rds_mysqldb_password',
    INSTANCE_ID = 'rds_mysql_instance_id',
    VPC_ID = 'rds_mysqldb_vpcid'
  );

Considerations

Before you create a MySQL, SQL Server, or PostgreSQL schema in DLA, you must add the CIDR block 100.104.0.0/16 to the whitelist of your RDS instance.

The RDS instance is in a VPC, and by default DLA cannot access resources in the VPC. To enable DLA to access the RDS instance, you need to use the reverse VPC technology, that is, add the CIDR block 100.104.0.0/16 to the whitelist of your RDS instance.

Permission statement: When you use the method described in this topic to create a MySQL schema, you agree that we can use the reverse VPC technology to read and write data from and to RDS.

Example 1: Create a table by using CREATE TABLE LIKE MAPPING

Create a table:

create external table person like mapping('person');
+-------------+-----------+-----------------+
desc person;
+-------------+-----------+-----------------+
| Field       | Type      | Collation       |
+-------------+-----------+-----------------+
| id          | int       | utf8_general_ci |
| age         | int       | utf8_general_ci |
| name        | varchar   | utf8_general_ci |

Query table data:

SELECT * FROM hello_mysql_vpc_rds.person
 +------+------+----+
 | id  | name | age |
 +-----+------+-----+
 |    1| james|   10|
 |    2| bond |   20|
 |    3| jack |   30|
 |    4| lucy |   40|

Example 2: Copy a table by using CREATE TABLE LIKE

Traditional databases allow you to create new tables or collections by copying existing ones. DLA provides CREATE TABLE LIKE, which can achieve the same purpose.

Syntax:

create external table_name2 like table_name_1;

Examples:

In the preceding example, you can run CREATE TABLE LIKE to copy the person table as a new table named person_2, which has the same structure and data as the person table. 

create external table person_2 like person;
+------+-------------------+---+------+
SELECT * FROM hello_mysql_vpc_rds.person_2
 +------+------+----+
 | id  | name | age |
 +-----+------+-----+
 |    1| james|   10|
 |    2| bond |   20|
 |    3| jack |   30|
 |    4| lucy |   40|