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

作者 : Cristiani Santos da Silva, Ahmed Virani 和 Vanshika Nigam, 2023年12月19日
在 [高级 (300)](https://aws.amazon.com/blogs/database/category/learning- levels/advanced-300/)，， 发布。

关键要点

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

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

PostgreSQL 的 mysql_fdw 扩展是一个针对 MySQL 的 FDW 实现，支持 PostgreSQL 数据库与 MySQL 或 MariaDB 数据库之间的直接连接。mysql_fdw 扩展支持 和 。

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

解决方案概述

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

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

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

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

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

删除)

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

先决条件

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

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

• VPN 连接
• VPC 对等
• AWS 管理的 VPN

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

创建示例表

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

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

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

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

bash ssh -i /path/key-pair-name.pem <instance-user-name>@<instance-public- dns-name>

例如：

ssh -i "ec2-key-mykey.pem" ec2-user@ec2-X-XX-XXX-XX.eu-central-1.compute.amazonaws.com
```

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

bash mysql -u root -p

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

bash CREATE DATABASE mysql_classicmodels;

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

bash USE mysql_classicmodels;

4. 通过运行以下脚本创建三个新表 mysql_offices、mysql_employees 和 mysql_customers：

addressLine1 varchar(50) NOT NULL, addressLine2 varchar(50) DEFAULT NULL,
state varchar(50) DEFAULT NULL, country varchar(50) NOT NULL, postalCodevarchar(15) NOT NULL, territory varchar(10) NOT NULL, PRIMARY KEY (officeCode)
);

/_mysql_offices 表的数据_ / INSERT INTO mysql_offices (officeCode, city, phone,
addressLine1, addressLine2, state, country, postalCode, territory) VALUES
('1', 'Anytown', '212-555-0100', '7 W 34Th Street', 'Suite 200', 'NY',
'10001', 'USA', 'NA'), ('2', 'Anywhere', '571-555-0101', '525 14th St S',
'Suite 5', 'VA', '22202', 'USA', 'NA'), ('3', 'Nowhere', '206-555-0111', '100Main Street', '', 'WA', '98109', 'USA', 'NA'), ('4', 'Any Town',
'936-555-0122', '301 W 125Th St', 'Apt 3', 'NY', '10026', 'USA', 'NA');

/_表结构 - mysql_employees_ / CREATE TABLE mysql_employees ( employeeNumberint(11) NOT NULL, lastName varchar(50) NOT NULL, firstName varchar(50) NOTNULL, extension varchar(10) NOT NULL, email varchar(100) NOT NULL, officeCodevarchar(10) NOT NULL, reportsTo int(11) DEFAULT NULL, jobTitle varchar(50) NOTNULL, PRIMARY KEY (employeeNumber), KEY reportsTo (reportsTo), KEY officeCode
(officeCode), CONSTRAINT employees_ibfk_1 FOREIGN KEY (reportsTo) REFERENCESmysql_employees (employeeNumber), CONSTRAINT employees_ibfk_2 FOREIGN KEY
(officeCode) REFERENCES mysql_offices (officeCode) );

/_mysql_employees 表的数据_ / INSERT INTO mysql_employees (employeeNumber,
lastName, firstName, extension, email, officeCode, reportsTo, jobTitle) VALUES
(1002, 'Rosalez', 'Alejandro', 'x5800', 'alejandro_rosalez@example.com', '1',
NULL, 'President'), (1056, 'Mansa', 'Akua', 'x4611', 'akua_mansa@example.com',
'1', 1002, 'VP Sales'), (1088, 'Silva', 'Ana Carolina', 'x4871',
'anacarolina_silva@example.com', '3', 1056, 'Sales Manager (APAC)'), (1102,
'Desai', 'Arnav', 'x5408', 'arnav_desai@example.com', '4', 1056, 'Sale Manager
(EMEA)'), (1143, 'Salazar', 'Carlos', 'x5428', 'carlos_salazar@example.com',
'1', 1056, 'Sales Manager (NA)'), (1165, 'Ramirez', 'Diego', 'x3291',
'diego_ramirez@example.com', '1', 1143, 'Sales Rep'), (1166, 'Owusu', 'Efua',
'x4065', 'efua_owusu@example.com', '1', 1143, 'Sales Rep'), (1337, 'Doe',
'Jane', 'x6493', 'jane_doe@example.com', '4', 1102, 'Sales Rep'), (1370,
'Doe', 'John', 'x2028', 'john_doe@example.com', '4', 1102, 'Sales Rep'),
(1401, 'Stiles', 'John', 'x2759', 'john_stiles@example.com', '4', 1102, 'SalesRep'), (1504, 'Souza', 'Jorge', 'x102', 'jorge_souza@example.com', '3', 1102,
'Sales Rep'), (1611, 'Mensah', 'Kwaku', 'x101', 'kwaku_mensah@example.com',
'3', 1088, 'Sales Rep');

/_表结构 - mysql_customers_ / CREATE TABLE mysql_customers ( customerNumberint(11) NOT NULL, customerName varchar(50) NOT NULL, contactLastNamevarchar(50) NOT NULL, contactFirstName varchar(50) NOT NULL, phone varchar(50)
NOT NULL, addressLine1 varchar(50) NOT NULL, addressLine2 varchar(50) DEFAULTNULL, city varchar(50) NOT NULL, state varchar(50) DEFAULT NULL, postalCodevarchar(15) DEFAULT NULL, country varchar(50) NOT NULL, salesRepEmployeeNumberint(11) DEFAULT NULL, creditLimit decimal(10,2) DEFAULT NULL, PRIMARY KEY
(customerNumber), KEY salesRepEmployeeNumber (salesRepEmployeeNumber),
CONSTRAINT customers_ibfk_1 FOREIGN KEY (salesRepEmployeeNumber) REFERENCESmysql_employees (employeeNumber) );

/_mysql_customers 表的数据_ / INSERT INTO mysql_customers (customerNumber,
customerName, contactLastName, contactFirstName, phone, addressLine1,
addressLine2, city, state, postalCode, country, salesRepEmployeeNumber,
creditLimit) VALUES (103, 'Atelier graphique', 'Nikki', 'Wolf',
'212-555-0133', '100 Main Street', NULL, 'Anywhere', 'NY', '44000', 'USA',
1370, '21000.00'), (112, 'Signal Gift Stores', 'Pat', 'Candella',
'936-555-0140', '7 W 14th Street', NULL, 'Nowhere', 'NV', '83030', 'USA',
1166, '71800.00'), (114, 'Australian Collectors Co.', 'Paulo', 'Santos',
'832-555-0147', '2308 Janna Way', 'Level 3', 'Any Town', 'WA', '30040', 'USA',
1611, '117300.00'), (121, 'Baane Mini Imports', 'Richard', 'Roe',
'409-555-0154', '1152 Indian Run Dr', NULL, 'No where', 'NJ', '40110', 'USA',
1504, '81700.00'), (124, 'Mini Gifts Distributors Ltd.', 'Saanvi', 'Sarkar',
'404-555-0161', '1 Market Street', NULL, 'Everywhere', 'CA', '97562', 'USA',
1165, '210500.00'), (125, 'Havel Zbyszek Co', 'Shirley', 'Rodriguez',
'306-555-0168', '123 Any Street', NULL, 'Anytown', 'TX', '01012', 'USA', 1370,
'0.00'), (129, 'Mini Wheels Co.', 'Sofía', 'Martínez', '205-555-0175', '5557North Anywhere Street', NULL, 'Nowhere', 'CA', '94217', 'USA', 1165,
'64600.00'), (144, 'Volvo Model Replicas Co', 'Terry', 'Whitlock',
'832-555-0182', '100 Anywhere Ave', NULL, 'Any Where', 'FL', '95822', 'USA',
1504, '53100.00'); ```

## 设置目标实例

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

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

`sql CREATE USER 'mysqlfdwuser' IDENTIFIED BY 'Dbadmin#123';`

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

`sql SELECT user FROM mysql.user;`

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

`sql USE mysql_classicmodels;`

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

`sql GRANT SELECT,INSERT,UPDATE,DELETE ON mysql_offices TO 'mysqlfdwuser'@'%';
GRANT SELECT,INSERT,UPDATE,DELETE ON mysql_employees TO 'mysqlfdwuser'@'%';
GRANT SELECT,INSERT,UPDATE,DELETE ON mysql_customers TO 'mysqlfdwuser'@'%';`

## 设置源 RDS for PostgreSQL 实例

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

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

### 安装 mysql_fdw 扩展

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

  1. 使用具有 `rds_superuser` 角色的账户连接到您的 PostgreSQL DB 实例：

`bash psql --host=<RDS Endpoint> --port=<DB Port> --username=<RDS User>`

  2. 安装 `mysql_fdw` 扩展

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

    
    
    ```sql
    CREATE EXTENSION mysql_fdw;
    ```
    

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

`sql \dx`

### 创建并配置连接点

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

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

`sql CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host
'<Host IP Address>', port '<MySQL DB Port>');`

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

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

`sql \des+`

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

```sql CREATE USER pguser1