使用mysql_fdw访问MySQL数据

云原生数据仓库AnalyticDB PostgreSQL版支持通过mysql_fdw插件直接访问和对MySQL服务器上的数据进行增删改查,无需将这些数据写入到云原生数据仓库AnalyticDB PostgreSQL版中。

注意事项

对MySQL外表执行DML操作时(UPDATE、DELETE、INSERT),源数据库表结构必须以首列作为单一主键,或作为构成复合主键的一部分。执行数据查询(SELECT操作)不受此主键约束限制。

前提条件

快速入门

使用mysql_fdw插件来访问远程MySQL上的数据,操作步骤如下:

  1. 登录云原生数据仓库AnalyticDB PostgreSQL版控制台。单击目标实例ID,进入目标实例基本信息页面。

  2. 单击右上角登录数据库,在弹出框登录实例页面填写数据库账号数据库密码,单击登录

  3. 进入数据库,使用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表的外表。

  4. 对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与数据的合法性校验等问题。具体的取值可以参考文档:

MySQL官方文档

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