云原生数据仓库AnalyticDB PostgreSQL版支持通过mysql_fdw插件直接访问和对MySQL服务器上的数据进行增删改查,无需将这些数据写入到云原生数据仓库AnalyticDB PostgreSQL版中。
注意事项
对MySQL外表执行DML操作时(UPDATE、DELETE、INSERT),源数据库表结构必须以首列作为单一主键,或作为构成复合主键的一部分。执行数据查询(SELECT操作)不受此主键约束限制。
前提条件
已安装mysql_fdw插件。具体操作,请参见安装、升级与卸载插件。
已创建云原生数据仓库 AnalyticDB PostgreSQL 版数据库账号。具体操作,请参见创建数据库账号。
快速入门
使用mysql_fdw插件来访问远程MySQL上的数据,操作步骤如下:
登录云原生数据仓库AnalyticDB PostgreSQL版控制台。单击目标实例ID,进入目标实例基本信息页面。
单击右上角登录数据库,在弹出框登录实例页面填写数据库账号和数据库密码,单击登录。
进入数据库,使用CREATE SERVER命令创建一个外部服务器,这个外部服务器表示需要访问的MySQL数据库。如下所示是一个简单的使用示例。
-- 创建foreign server CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306'); -- 给刚刚创建的mysql_svr赋予一个user mapping(指定用户名密码) CREATE USER MAPPING FOR public SERVER mysql_svr OPTIONS (username '$MySQL_USER_NAME', password '$MySQL_PASS'); -- 使用mysql_svr这个foreign server创建外表 CREATE FOREIGN TABLE test_foreign_table( c1 INT, c2 INT, c3 TEXT, c4 TEXT) SERVER mysql_svr OPTIONS(dbname 'mysql_fdw_database', table_name 'test_foreign_table');
参数说明:
CREATE SERVER:创建一个外部服务器。这个外部服务器表示需要访问的MySQL数据库。
CREATE USER MAPPING:创建一个用户映射,指定需要访问的MySQL数据库使用的用户名和密码。
CREATE FOREIGN TABLE:创建一个外表。外表的列名必须匹配需要被访问的远程表。也可以使用IMPORT FOREIGN SCHEMA的方式来让mysql_fdw自动在云原生数据仓库 AnalyticDB PostgreSQL 版实例上创建需要访问的MySQL表的外表。
对MySQL数据库进行查询数据、插入数据、新增数据和删除数据的操作(仅支持这些操作)。
语法
CREATE SERVER
创建一个外部服务器。
CREATE SERVER [IF NOT EXISTS] server_name [TYPE 'server_type'] [VERSION 'server_version']
FOREIGN DATA WRAPPER fdw_name
[OPTIONS([mpp_execute 'any | master | all segments' ], option 'value' [, ... ])]
option 'value' 中支持的参数列表
options | 含义 | 默认值 |
host | MySQL数据库的IP地址(或者host)。 | 127.0.0.1 |
port | MySQL数据库的端口。 | 3306 |
init_command | 和MySQL创建连接时执行的SQL命令。 | 空 |
secure_auth | v5.7.5版本以前,MySQL使用password()函数将明文密码转换为加密密码,这里设置为true,标志密码在服务端采用这种加密方式。 | true |
use_remote_estimate | 在生成计划时,使用EXPLAIN方式,向MySQL获取表的统计信息。 | false |
reconnect | 是否允许连接中断时自动重连。 | false |
character_set | 连接使用的字符集,默认值是auto(获取MySQL客户端所在的OS的字符集)。 | auto |
sql_mode | 设置MySQL的sql mode,用于影响SQL与数据的合法性校验等问题。具体的取值可以参考文档: | ANSI_QUOTES |
ssl_key | 客户端私钥文件的路径名。 | 空 |
ssl_cert | 客户端公钥证书文件的路径名。 | 空 |
ssl_ca | 证书颁发机构(CA)颁发的证书文件的路径名。如果使用此选项,必须指定服务器使用相同的证书。 | 空 |
ssl_capath | 包含受信任SSL CA证书文件的目录的路径名。 | 空 |
ssl_cipher | 用于SSL加密的允许密码列表。 | <none> |
fetch_size | 此选项指定mysql_fdw每次获取数据时应该获取的行数。如果在外表上设置了fetch_size,会覆盖服务器上设定的值。 | 100 |
CREATE USER MAPPING
通过CREATE USER MAPPING
命令来定义用户映射。
CREATE USER MAPPING [ IF NOT EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC }
SERVER server_name
[ OPTIONS ( option 'value' [ , ... ] ) ]
option 'value' 中支持的参数列表
options | 含义 | 默认值 |
username | 需要访问的MySQL的用户名。 | 空 |
password | 需要访问的MySQL的用户的密码。 | 空 |
CREATE FOREIGN TABLE
在数据库管理系统中创建一个外表。
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
SERVER server_name
[ OPTIONS ( [ mpp_execute 'any | master | all segments' ], option 'value' [, ... ] ) ]
option 'value' 中支持的参数列表
options | 含义 | 默认值 |
dbname | 创建的外部数据库名字。 | 空 |
table_name | 创建的外表的名字。 | 在自定义名称。 |
fetch_size | 此选项指定mysql_fdw每次获取数据时应该获取的行数。在外表上设定,会覆盖在服务器上设定的值。 | 100 |
IMPORT FOREIGN SCHEMA
将MySQL端的表信息导入到云原生数据仓库 AnalyticDB PostgreSQL 版端(创建成外表)。
IMPORT FOREIGN SCHEMA remote_schema
[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
FROM SERVER server_name
INTO local_schema
[ OPTIONS ( option 'value' [, ... ] ) ]
option 'value' 中支持的参数列表
options | 含义 | 默认值 |
import_default | 同步外表结构时是否导入默认表达式。 | FALSE |
import_not_null | 同步外表结构时是否导入非空表达式。 | TRUE |
import_enum_as_text | 同步外表结构时是否将MySQL ENUM类型映射为TEXT类型,否则会发出警告表示需要创建类型。 | FALSE |
兼容性
云原生数据仓库 AnalyticDB PostgreSQL 版的mysql_fdw插件兼容如下MySQL实例。
公有云RDS MySQL
版本号 | 是否兼容 |
5.5 | 是 |
5.6 | 是 |
5.7 | 是 |
8.0 | 是 |
自建MySQL
社区v5.7以下的版本不再维护,以v5.7及以上为主要版本。
版本号 | 是否兼容 |
5.7 | 是 |
8.0(主要版本) | 是 |
PolarDB MySQL
版本号 | 是否兼容 |
8.0.2 | 是 |
8.0.1 | 是 |
5.7 | 是 |
5.6 | 是 |
功能特性和数据类型
功能特性
连接池
在同一会话中的所有查询都使用相同的MySQL数据库连接,而不是去创建一个新的MySQL连接。
WHERE从句下推
把外表的WHERE从句下推到MySQL上,把与外表相关的WHERE条件加在MySQL上执行,因此需要传输到云原生数据仓库 AnalyticDB PostgreSQL 版实例的行数会更少。
投影下推
不同于将一个表的所有数据都从MySQL中查询到云原生数据仓库 AnalyticDB PostgreSQL 版实例中,mysql_fdw只返回那些属于 SELECT目标表的列,这样可以降低传输到云原生数据仓库 AnalyticDB PostgreSQL 版实例的数据流量,提升性能。
PREPARED STATEMENT
SELECT查询使用PREPARED STATEMENT查询而不是简单的查询协议。
JOIN下推
同一MySQL数据库的两个外表之间的连接操作被下推到远端的MySQL数据库上执行,而不是先获取两个表的所有行并在云原生数据仓库 AnalyticDB PostgreSQL 版实例本地执行连接操作,从而提升了性能。
目前仅将包含关系和算术运算符的JOIN子句下推,以避免任何潜在的连接失败问题。
目前只支持INNER和LEFT或RIGHT OUTER连接,而不支持FULL OUTER、SEMI 和ANTI连接。
AGGREGATE下推
将聚合操作(AGGREGATE)下推到远端的MySQL数据库上执行,而不是获取所有行并在云原生数据仓库 AnalyticDB PostgreSQL 版实例本地进行聚合。这样做可以显著提升性能。
目前下推仅限于聚合函数MIN、MAX、SUM、AVG和COUNT。
ORDER BY下推
将ORDER BY子句下推到远端的MySQL数据库上执行,可以从MySQL获得有序的结果集。在MySQL数据库对于NULL的行为与云原生数据仓库 AnalyticDB PostgreSQL 版相反。为了获得等效的结果,在每个ORDER BY表达式的开头添加“expression IS NULL”子句,MySQL执行正向排序后会把NULL放到最后一个,和云原生数据仓库 AnalyticDB PostgreSQL 版的行为保持一致。
LIMIT OFFSET下推
在远端的MySQL数据库上执行LIMIT和OFFSET操作,不需要把所有数据拿到云原生数据仓库 AnalyticDB PostgreSQL 版数据库,减少了云原生数据仓库 AnalyticDB PostgreSQL 版数据库和MySQL数据库之间的数据流量。
数据类型
编号 | MySQL 数据类型 | 映射AnalyticDB PostgreSQL版数据类型 |
1 | BIT(N) | BIT(N) |
2 | TINYINT | SMALLINT |
3 | TINYINT UNSIGNED | SMALLINT |
4 | SMALLINT | SMALLINT |
5 | SMALLINT UNSIGNED | BIGINT |
6 | MEDIUMINT | INT |
7 | MEDIUMINT UNSIGNED | INT |
8 | INT | INT |
9 | INT UNSIGNED | BIGINT |
10 | BIGINT | BIGINT |
11 | BIGINT UNSIGNED | NUMERIC |
12 | DECIMAL(M,N) | NUMERIC |
13 | FLOAT | REAL |
14 | DOUBLE(M,N) | DOUBLE PRECISION |
15 | DATE | DATE |
16 | DATETIME | DATETIME without TIME zone |
17 | TIMESTAMP | DATETIME without TIME zone |
18 | TIME | TIME without TIME zone |
19 | YEAR[4] | 不支持 |
20 | CHAR[N] | CHAR[N] |
21 | VARCHAR[N] | CHARACTER VARYING(N) |
22 | BINARY[N] | BYTEA |
23 | VARBINARY[N] | BYTEA |
24 | TINYBLOB | 不支持 |
25 | TINYTEXT | TEXT |
26 | BLOB | bytea |
27 | TEXT | TEXT |
28 | MEDIUMBLOB | BYTEA |
29 | MEDIUMTEXT | TEXT |
30 | LONGBLOB | BYTEA |
31 | LONGTEXT | TEXT |
32 | ENUM(",") | 需要手动创建ENUM,会有提示。 |
33 | SET('','') | 不支持 |
34 | GEOMETRY | 不支持 |
35 | POINT | POINT |
36 | LINESTRING | 不支持 |
37 | POLYGON | POLYGON |
38 | MULTIPOINT | 不支持 |
39 | MULTILINESTRING | 不支持 |
40 | MULTIPOLYGON | 不支持 |
41 | GEOMETRCOLLECTION | 不支持 |
42 | JSON | JSON |