从 Amazon Aurora 和 Amazon RDS for PostgreSQL 使用 mys

2026-01-27 12:30:39

从Amazon Aurora和Amazon RDS for PostgreSQL连接到MySQL和MariaDB

作者 Cristiani Santos da Silva Ahmed Virani 和 Vanshika Nigam 2023年12月19日在 高级 (300)，Amazon EC2，RDS for PostgreSQL 发布。

关键要点

PostgreSQL 的 mysqlfdw 扩展可在 Amazon RDS for PostgreSQL 上实现与 MySQL 和 MariaDB 的直接连接。本文将详细介绍如何设置 mysqlfdw 扩展，以及如何通过 RDS for PostgreSQL 访问 MySQL 数据库的数据。该解决方案允许用户在不同的数据库系统之间读取和操作数据。

在处理多个数据库引擎或共享数据的方案时，如何实现跨引擎数据库连接成为了一项挑战。为了解决这个问题，PostgreSQL 提供了外部数据封装器FDW，这些库可以通过抽象数据源连接和数据检索的复杂性，方便与外部数据源的通信。

PostgreSQL 的 mysqlfdw 扩展是一个针对 MySQL 的 FDW 实现，支持 PostgreSQL 数据库与 MySQL 或 MariaDB 数据库之间的直接连接。mysqlfdw 扩展支持 Amazon Aurora PostgreSQL 兼容版 154、149、1312、1216 及更高版本 和 Amazon RDS for PostgreSQL 142、136 及更高版本。

本文演示了设置和使用 mysqlfdw PostgreSQL 扩展在 Amazon RDS for PostgreSQL 上的步骤，使您能够访问 MySQL 数据库中的数据。此博客中的示例同样适用于 Amazon Aurora PostgreSQL 兼容版。

解决方案概述

使用 Amazon RDS for PostgreSQL 上的 mysqlfdw 扩展，您可以从 Amazon RDS for MySQL、Amazon Aurora MySQL 兼容版、MariaDB 及其他 MySQL 兼容数据库中检索数据。

假设您在 Amazon Elastic Compute Cloud (Amazon EC2) 实例上安装了 MySQL 数据库，其中包含多个表。您的 RDS for PostgreSQL 用户需要直接使用 SELECT、INSERT、UPDATE 和 DELETE 命令查询和修改来自该 MySQL 数据库的数据。

为了实现对 MySQL 表的直接访问，我们将在 RDS for PostgreSQL 数据库中配置 mysqlfdw 扩展。

所有必需的命令将从 Linux Bastion Host 中发出，无需直接连接到数据库主机。如果您使用的是自己的 MySQL 或 PostgreSQL 数据库主机，请相应更新连接凭证。

以下图示展示了我们的解决方案架构：

在本文中，我们使用 MySQL 作为目标数据库。博客中的示例同样适用于 Amazon Aurora MySQL 兼容版和 MariaDB。

先决条件

在安装 mysqlfdw 扩展之前，确保您满足以下先决条件：

项目描述MySQL 实例本文使用在 Amazon EC2 上运行的 MySQL。Amazon Aurora PostgreSQL 兼容版或 RDS for PostgreSQL本文使用 RDS for PostgreSQL 数据库。如果您尚未拥有 RDS for PostgreSQL 实例，您可以创建一个。有关说明，请参见 创建和连接到 PostgreSQL DB 实例。安全组配置配置一个 安全组，以允许源数据库和目标数据库之间的流量。

若您打算将本地网络连接到 Amazon RDS，建议您至少具备以下一种网络连接选项，来建立本地网络与 Amazon RDS 的连接。连接选项的选择将根据您的具体需求和用例而有所不同，但拥有这些连接选项将确保您能够与 RDS 实例建立安全可靠的连接：

VPN 连接AWS Direct ConnectVPC 对等AWS Transit GatewayAWS 管理的 VPN

确保具备这些先决条件将帮助您顺利开展任务。

创建示例表

在本文中，我们将使用 Amazon EC2 上的 MySQL 数据库作为目标数据库，并创建一个示例数据库。我们使用堡垒主机访问 MySQL 数据库，并利用 MySQL 命令行工具执行命令。

如果您想使用自己的数据库，您可以跳过设置目标实例的步骤。只需确保相应更新数据库名称、表名和用户账户名称即可。

本文中，我们通过 SSH 连接。有关如何连接到 EC2 实例的更多信息，请参见 连接到您的实例。

使用以下命令连接到堡垒主机：

bashssh i /path/keypairnamepem ltinstanceusernamegt@ltinstancepublicdnsnamegt

例如：

bashssh i ec2keymykeypem ec2user@ec2XXXXXXXXeucentral1computeamazonawscom

使用具有创建数据库权限的用户连接到数据库：

bashmysql u root p

通过运行以下命令创建一个名为 mysqlclassicmodels 的新数据库：

bashCREATE DATABASE mysqlclassicmodels

使用以下命令选择新创建的数据库：

bashUSE mysqlclassicmodels

通过运行以下脚本创建三个新表 mysqloffices、mysqlemployees 和 mysqlcustomers：

sql/ 表结构 mysqloffices /CREATE TABLE mysqloffices ( officeCode varchar(10) NOT NULL city varchar(50) NOT NULL phone varchar(50) NOT NULL addressLine1 varchar(50) NOT NULL addressLine2 varchar(50) DEFAULT NULL state varchar(50) DEFAULT NULL country varchar(50) NOT NULL postalCode varchar(15) NOT NULL territory varchar(10) NOT NULL PRIMARY KEY (officeCode))

/ mysqloffices 表的数据 /INSERT INTO mysqloffices (officeCode city phone addressLine1 addressLine2 state country postalCode territory) VALUES(1 Anytown 2125550100 7 W 34Th Street Suite 200 NY 10001 USA NA)(2 Anywhere 5715550101 525 14th St S Suite 5 VA 22202 USA NA)(3 Nowhere 2065550111 100 Main Street WA 98109 USA NA)(4 Any Town 9365550122 301 W 125Th St Apt 3 NY 10026 USA NA)

/ 表结构 mysqlemployees /CREATE TABLE mysqlemployees ( employeeNumber int(11) NOT NULL lastName varchar(50) NOT NULL firstName varchar(50) NOT NULL extension varchar(10) NOT NULL email varchar(100) NOT NULL officeCode varchar(10) NOT NULL reportsTo int(11) DEFAULT NULL jobTitle varchar(50) NOT NULL PRIMARY KEY (employeeNumber) KEY reportsTo (reportsTo) KEY officeCode (officeCode) CONSTRAINT employeesibfk1 FOREIGN KEY (reportsTo) REFERENCES mysqlemployees (employeeNumber) CONSTRAINT employeesibfk2 FOREIGN KEY (officeCode) REFERENCES mysqloffices (officeCode))

/ mysqlemployees 表的数据 /INSERT INTO mysqlemployees (employeeNumber lastName firstName extension email officeCode reportsTo jobTitle) VALUES(1002 Rosalez Alejandro x5800 alejandrorosalez@examplecom 1 NULL President)(1056 Mansa Akua x4611 akuamansa@examplecom 1 1002 VP Sales)(1088 Silva Ana Carolina x4871 anacarolinasilva@examplecom 3 1056 Sales Manager (APAC))(1102 Desai Arnav x5408 arnavdesai@examplecom 4 1056 Sale Manager (EMEA))(1143 Salazar Carlos x5428 carlossalazar@examplecom 1 1056 Sales Manager (NA))(1165 Ramirez Diego x3291 diegoramirez@examplecom 1 1143 Sales Rep)(1166 Owusu Efua x4065 efuaowusu@examplecom 1 1143 Sales Rep)(1337 Doe Jane x6493 janedoe@examplecom 4 1102 Sales Rep)(1370 Doe John x2028 johndoe@examplecom 4 1102 Sales Rep)(1401 Stiles John x2759 johnstiles@examplecom 4 1102 Sales Rep)(1504 Souza Jorge x102 jorgesouza@examplecom 3 1102 Sales Rep)(1611 Mensah Kwaku x101 kwakumensah@examplecom 3 1088 Sales Rep)

/ 表结构 mysqlcustomers /CREATE TABLE mysqlcustomers ( customerNumber int(11) NOT NULL customerName varchar(50) NOT NULL contactLastName varchar(50) NOT NULL contactFirstName varchar(50) NOT NULL phone varchar(50) NOT NULL addressLine1 varchar(50) NOT NULL addressLine2 varchar(50) DEFAULT NULL city varchar(50) NOT NULL state varchar(50) DEFAULT NULL postalCode varchar(15) DEFAULT NULL country varchar(50) NOT NULL salesRepEmployeeNumber int(11) DEFAULT NULL creditLimit decimal(102) DEFAULT NULL PRIMARY KEY (customerNumber) KEY salesRepEmployeeNumber (salesRepEmployeeNumber) CONSTRAINT customersibfk1 FOREIGN KEY (salesRepEmployeeNumber) REFERENCES mysqlemployees (employeeNumber))

/ mysqlcustomers 表的数据 /INSERT INTO mysqlcustomers (customerNumber customerName contactLastName contactFirstName phone addressLine1 addressLine2 city state postalCode country salesRepEmployeeNumber creditLimit) VALUES(103 Atelier graphique Nikki Wolf 2125550133 100 Main Street NULL Anywhere NY 44000 USA 1370 2100000)(112 Signal Gift Stores Pat Candella 9365550140 7 W 14th Street NULL Nowhere NV 83030 USA 1166 7180000)(114 Australian Collectors Co Paulo Santos 8325550147 2308 Janna Way Level 3 Any Town WA 30040 USA 1611 11730000)(121 Baane Mini Imports Richard Roe 4095550154 1152 Indian Run Dr NULL No where NJ 40110 USA 1504 8170000)(124 Mini Gifts Distributors Ltd Saanvi Sarkar 4045550161 1 Market Street NULL Everywhere CA 97562 USA 1165 21050000)(125 Havel Zbyszek Co Shirley Rodriguez 3065550168 123 Any Street NULL Anytown TX 01012 USA 1370 000)(129 Mini Wheels Co Sofa Martnez 2055550175 5557 North Anywhere Street NULL Nowhere CA 94217 USA 1165 6460000)(144 Volvo Model Replicas Co Terry Whitlock 8325550182 100 Anywhere Ave NULL Any Where FL 95822 USA 1504 5310000)

设置目标实例

为了使 PostgreSQL 服务器能够访问 MySQL，您必须在 MySQL 实例上配置一个用户。为此，请完成以下步骤：

连接到您的 MySQL DB 实例并创建一个新用户和密码：

sqlCREATE USER mysqlfdwuser IDENTIFIED BY Dbadmin#123

您可以通过运行以下查询验证用户是否已在 MySQL 中创建：

sqlSELECT user FROM mysqluser

使用以下命令选择在示例表创建过程中创建的数据库：

sqlUSE mysqlclassicmodels

授予将在 PostgreSQL 中用作外部表的表所需的权限。

sqlGRANT SELECTINSERTUPDATEDELETE ON mysqloffices TO mysqlfdwuser@GRANT SELECTINSERTUPDATEDELETE ON mysqlemployees TO mysqlfdwuser@GRANT SELECTINSERTUPDATEDELETE ON mysqlcustomers TO mysqlfdwuser@

设置源 RDS for PostgreSQL 实例

要配置您的 RDS for PostgreSQL 实例，您必须在您的 RDS for PostgreSQL 实例上完成三个主要步骤：

安装 mysqlfdw 扩展创建连接点服务器，将 RDS for PostgreSQL 与 MySQL DB 实例连接起来创建外部表，以便从 MySQL 查询数据到 PostgreSQL 数据库

安装 mysqlfdw 扩展

在本文中，我们使用 Linux Bastion Host 访问 RDS for PostgreSQL 数据库，并利用 psql 命令行工具运行命令。

使用具有 rdssuperuser 角色的账户连接到您的 PostgreSQL DB 实例：

加速器下载永久免费版

bashpsql host=ltRDS Endpointgt port=ltDB Portgt username=ltRDS Usergt

安装 mysqlfdw 扩展

请注意，只有具有 rdssuperuser 权限的用户才能执行以下命令：

sqlCREATE EXTENSION mysqlfdw

您可以使用以下命令检查已安装的扩展列表：

sqldx

创建并配置连接点

要设置连接点，请完成以下步骤：

创建外部服务器并配置连接点，从 Amazon RDS for PostgreSQL 到 MySQL 数据库，通过在 RDS for PostgreSQL DB 实例上运行以下命令：

sqlCREATE SERVER mysqlserver FOREIGN DATA WRAPPER mysqlfdwOPTIONS (host ltHost IP Addressgt port ltMySQL DB Portgt)

PostgreSQL DB 实例上的外部服务器作为 MySQL 数据库的网关。您可以使用私有 IP 或公共 DNS 主机名作为主机端点。本文中，我们使用了 MySQL 数据库 EC2 实例的私有 IP。

使用以下命令验证外部服务器是否已创建成功：

sqldes

为本文创建一个名为 pguser1 的新用户，运行以下命令：

sqlCREATE USER pguser1