mysql联合查询步骤数据库_联合查询多个MySQL实例

背景信息

前提条件

通过DLA联合查询两个MySQL实例数据前，您需要通过以下操作在两个MySQL实例中准备好测试数据

注意DLA和两个MySQL所属地域必须相同，否则无法进行本文档操作。

由于DLA将通过MySQL的VPC连接MySQL数据库，建议您创建MySQL实例时，网络类型选择VPC。同时，MySQL支持通过切换为专有网络将经典网络切换为VPC。写入测试数据MySQL实例1中创建orders_db数据库和order_item表，写入测试数据：create table orders_db.order_item (

id bigint not null primary key auto_increment,

prod_id bigint comment '商品ID',

prod_cnt int comment '商品数量'

);

insert into orders_db.order_item values

(1, 1, 2),

(2, 2, 3),

(3, 3, 4),

(4, 2, 5),

(5, 1, 6);MySQL实例2中创建prod_db数据库和prod表，写入测试数据：create table prod_db.prod (

id bigint not null primary key auto_increment,

prod_name varchar(31) comment '商品名称'

);

insert into prod_db.prod values

(1, '键盘'),

(2, '鼠标'),

(3, '显示器');

实施步骤

注意事项

在DLA中创建MySQL数据库连接前，需要将IP地址段100.104.0.0/16分别加入到MySQL

由于您的MySQL实例位于VPC内，默认情况下DLA无法访问该VPC中的资源。为了让DLA访问MySQL，需要利用VPC反向访问技术，即在MySQL白名单中添加100.104.0.0/16IP地址段。

说明

权限声明：当您在MySQL白名单中添加了100.104.0.0/16IP地址段，即视为您同意我们利用VPC反向访问技术读取MySQL数据库数据。

步骤一：在DLA中创建MySQL数据库连接

登录SQL访问点，单击登录到DMS，分别在DLA中创建两个底层映射到MySQL的数据库连接。CREATE SCHEMA dla_orders_db WITH DBPROPERTIES (

CATALOG = 'mysql',

LOCATION = 'jdbc:mysql://rm-******.mysql.rds.aliyuncs.com:3306/orders_db',

USER = 'mysql_db_user_name',

PASSWORD = 'mysql_db_password',

VPC_ID = 'mysql_vpc_id',

INSTANCE_ID = 'mysql_instance_id'

);

CREATE SCHEMA dla_prod_db WITH DBPROPERTIES (

CATALOG = 'mysql',

LOCATION = 'jdbc:mysql://rm-******.mysql.rds.aliyuncs.com:3306/prod_db',

USER = 'mysql_db_user_name',

PASSWORD = 'mysql_db_password',

VPC_ID = 'mysql_vpc_id',

INSTANCE_ID = 'mysql_instance_id'

);

参数说明LOCATION：由jdbc:mysql://MySQL内网连接地址:端口号/MySQL数据库名构成。

USER：连接MySQL数据库所使用的用户名。

PASSWORD：连接MySQL数据库所使用的用户名对应的密码。

VPC_ID：MySQL实例所属VPC ID。

INSTANCE_ID：MySQL实例ID。

步骤二：在DLA中创建MySQL外表

针对MySQL中order_item表，本示例在DMS for Data Lake Analytics中创建同名外表order_item：create external table order_item (

id bigint,

prod_id bigint,

prod_cnt int,

);

针对MySQL中prod表，本示例在DMS for Data Lake Analytics中创建同名外表prod：create external table prod (

id bigint,

prod_name varchar(31)

);

步骤三：通过DLA联合查询多个MySQL实例数据

MySQL数据库连接和外表创建成功后，接下来您可以通过MySQL客户端或者MySQL命令行工具连接DLA，使用标准SQL语句操作MySQL数据库数据。

也可以直接在DMS for Data Lake Analytics中操作MySQL数据库数据。

以下示例通过MySQL命令行工具连接DLA，联合查询order_item表和prod表数据：mysql> select * from dla_orders_db.order_item;

+------+---------+----------+

| id | prod_id | prod_cnt |

+------+---------+----------+

| 1 | 1 | 2 |

| 2 | 2 | 3 |

| 3 | 3 | 4 |

| 4 | 2 | 5 |

| 5 | 1 | 6 |

+------+---------+----------+

5 rows in set (0.22 sec)

mysql> select * from dla_prod_db.prod;

+------+-----------+

| id | prod_name |

+------+-----------+

| 1 | 键盘 |

| 2 | 鼠标 |

| 3 | 显示器 |

+------+-----------+

3 rows in set (0.18 sec)

select p.prod_name, sum(o.prod_cnt) cnt

from dla_prod_db.prod p inner join dla_orders_db.order_item o

on p.id = o.prod_id

group by p.prod_name

order by cnt desc;

+------+-----------+

| prod_name | cnt |

+-----------+------+

| 键盘 | 8 |

| 鼠标 | 8 |

| 显示器 | 4 |

+-----------+------+

3 rows in set (0.55 sec)

后续操作

我们通过DLA联合查询多个MySQL实例数据后，可以通过DLA将查询到的数据存入