Data Lake Analytics - Deprecated:Synchronize an ApsaraDB RDS schema with one click

Usage notes

Before you create a schema of an ApsaraDB RDS for MySQL, ApsaraDB RDS for SQL Server, or ApsaraDB RDS for PostgreSQL database in DLA, you must add the CIDR block 100.104.0.0/16 to a Configure an IP address whitelist for an ApsaraDB RDS for MySQL instance of the ApsaraDB RDS for MySQL, ApsaraDB RDS for SQL Server, or ApsaraDB RDS for PostgreSQL instance.

The ApsaraDB RDS for MySQL, ApsaraDB RDS for SQL Server, or ApsaraDB RDS for PostgreSQL instance is deployed in a virtual private cloud (VPC). By default, DLA cannot access instance resources in the VPC. To enable DLA to access instance resources in the VPC, you must add the CIDR block 100.104.0.0/16 to a whitelist of the instance. This operation is called reverse access of the VPC.

Permission declaration: After you add the CIDR block 100.104.0.0/16 to the whitelist of the instance, you have agreed to the use of reverse access. This allows you to read data from and write data to the ApsaraDB RDS for MySQL, ApsaraDB RDS for SQL Server, or ApsaraDB RDS for PostgreSQL database.

Preparations

Before you use DLA to read data from and write data to an ApsaraDB RDS for MySQL database, perform the following operations to prepare test data in the database:

1. Create an ApsaraDB RDS for MySQL instance
2. Configure an IP address whitelist for an ApsaraDB RDS for MySQL instance.
3. Create accounts and databases for an ApsaraDB RDS for MySQL instance
4. Connect to an ApsaraDB RDS for MySQL instance
5. Create a table in DLA.

Procedure

You can edit SQL statements in the DMS console to create an ApsaraDB RDS for MySQL schema for DLA. You can also use a MySQL client or MySQL command-line tool to connect to DLA and edit SQL statements in the DMS console to create an ApsaraDB RDS for MySQL schema for DLA.

Step 1: Create an ApsaraDB RDS for MySQL schema

1. Log on to the DLA console.

2. In the left-side navigation tree, choose Serverless SQL > SQL access point. On the SQL access point page, find your VPC in the VPC Network section and click Log on in DMS in the Actions column. Then, execute the following statement to create an ApsaraDB RDS for MySQL schema:

   ​CREATE SCHEMA hello_mysql_vpc_rds WITH DBPROPERTIES (
      CATALOG = 'mysql', 
      LOCATION = 'jdbc:mysql://rm-2zer0vg58mf*****.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'
    );​

   • VPC_ID: the ID of the VPC to which the ApsaraDB RDS for MySQL instance belongs.

   • INSTANCE_ID: the ID of the ApsaraDB RDS for MySQL instance. You can view the instance ID on the instance details page.

Step 2: Run the MSCK REPAIR TABLE command to synchronize table data

Run the SHOW TABLES command to check whether tables exist in the schema.

​mysql> use hello_mysql_vpc_rds;
Database changed
mysql> show tables;
Empty set (0.01 sec)

Run the following commands to synchronize all the tables from the ApsaraDB RDS for MySQL database to the schema in DLA with one click:

​mysql> msck repair database hello_mysql_vpc_rds;
+-------------------------------+-----------+
| TableName                     | Operation |
+-------------------------------+-----------+
| admin_acc_log                 | CREATED   |
| fractest                      | CREATED   |
| person                        | CREATED   |
| prep_stmt_test                | CREATED   |
| staples                       | CREATED   |
| test_datetime                 | CREATED   |
| top10_user                    | CREATED   |
| type_test                     | CREATED   |
| world1                        | CREATED   |
| world2                        | CREATED   |
+-------------------------------+-----------+
17 rows in set (1.60 sec)
mysql> show tables;
+-------------------------------+
| Table_Name                    |
+-------------------------------+
| admin_acc_log                 |
| fractest                      |
| person                        |
| prep_stmt_test                |
| staples                       |
| test_datetime                 |
| top10_user                    |
| type_test                     |
| world1                        |
| world2                        |
+-------------------------------+
17 rows in set (0.02 sec)

After table data is synchronized, you can use DLA to manage data of the ApsaraDB RDS for MySQL database.