H.4. mysql_fdw — доступ к базам данных MySQL #
- H.4.1. Возможности
- H.4.2. Вынос наружу
- H.4.3. Поддерживаемые платформы
- H.4.4. Установка
- H.4.5. Использование
- H.4.6. Параметры
CREATE SERVER- H.4.7. Параметры
CREATE USER MAPPING- H.4.8. Параметры
CREATE FOREIGN TABLE- H.4.9. Параметры
IMPORT FOREIGN SCHEMA- H.4.10. Поддержка
TRUNCATE- H.4.11. Функции
- H.4.12. Генерируемые столбцы
- H.4.13. Примеры
- H.4.2. Вынос наружу
mysql_fdw — это обёртка сторонних данных (FDW), которая подключает Postgres Pro к MySQL.
Расширение работает с Postgres Pro, а также с EDB Postgres Advanced Server 13, 14, 15, 16, 17 и 18.
H.4.1. Возможности #
H.4.1.1. Основные возможности #
В этом разделе описываются возможности mysql_fdw.
H.4.1.1.1. Записываемый FDW #
Пользователь может выполнять операции добавления, обновления и удаления в сторонних таблицах с помощью mysql_fdw. Расширение использует механизм приведения типов Postgres Pro для обеспечения двунаправленного приведения типов между типами данных MySQL и Postgres Pro.
H.4.1.1.2. Пул соединений #
mysql_fdw имеет пул соединений, который использует одно и то же соединение с базой данных MySQL для всех запросов в рамках одного сеанса. Это повышает производительность.
H.4.1.1.3. Подготовленные операторы #
Запросы SELECT используют подготовленные операторы вместо «простого» протокола запросов.
H.4.2. Вынос наружу #
H.4.2.1. Вынос наружу предложений WHERE #
mysql_fdw выносит наружу предложение WHERE сторонней таблицы на сторонний сервер. Затем предложение WHERE выполняется на стороннем сервере, вследствие чего в Postgres Pro передаётся меньше строк. Это повышает производительность.
H.4.2.2. Вынос наружу столбцов #
mysql_fdw выполняет вынос наружу столбцов и извлекает только столбцы, входящие в целевой список SELECT. Это повышает производительность.
H.4.2.3. Вынос наружу JOIN #
mysql_fdw поддерживает вынос наружу JOIN. Соединения двух сторонних таблиц с одного и того же удалённого сервера MySQL выносятся на удалённый сервер вместо извлечения всех строк из обеих таблиц и их последующего соединения на локальном уровне, что повышает производительность. В настоящее время во избежание возможных сбоев при соединении выносятся только операции в предложениях JOIN, где используются исключительно реляционные и арифметические операторы. Кроме того, поддерживаются только соединения INNER и LEFT/RIGHT OUTER, а соединения FULL OUTER, SEMI и ANTI не поддерживаются. Это повышает производительность.
H.4.2.4. Вынос наружу AGGREGATE #
mysql_fdw поддерживает вынос наружу AGGREGATE. Агрегаты выносятся на удалённый сервер MySQL вместо извлечения всех строк и их последующего агрегирования на локальном уровне. Это обеспечивает значительное повышение производительности в случаях, когда агрегаты можно вынести наружу. В настоящее время вынос наружу ограничен агрегатными функциями min(), max(), sum(),avg() и count(), чтобы избежать выноса наружу функций, отсутствующих на сервере MySQL. Фильтры и сортировка агрегатов также не выносятся наружу.
H.4.2.5. Вынос наружу ORDER BY #
mysql_fdw поддерживает вынос наружу ORDER BY. По возможности предложение ORDER BY выносится на удалённый сервер для получения упорядоченного набора результатов непосредственно со стороннего сервера. Это может обеспечить эффективное соединение слиянием. Упорядочение значений NULL на сервере MySQL работает противоположным образом в Postgres Pro. Поэтому для получения равнозначного результата в начало каждого выражения ORDER BY добавляется предложение expression IS NULL.
H.4.2.6. Вынос наружу LIMIT OFFSET #
mysql_fdw поддерживает вынос наружу ограничения количества строк и смещения. По возможности выполняйте операции LIMIT и OFFSET на удалённом сервере. Это снижает сетевой трафик между локальным сервером Postgres Pro и удалёнными серверами MySQL. Параметры ALL/NULL не поддерживаются на сервере MySQL, поэтому они не выносятся наружу. Использование OFFSET без LIMIT также не поддерживается на сервере MySQL, поэтому запросы с такой конструкцией не выносятся наружу.
H.4.3. Поддерживаемые платформы #
mysql_fdw разрабатывался на Linux и работает в любой POSIX-совместимой системе.
H.4.4. Установка #
mysql_fdw поставляется вместе с Postgres Pro в виде отдельного пакета mysql-fdw-ent-18 (подробные инструкции по установке приведены в Главе 17). Для mysql_fdw требуется клиентская C-библиотека MySQL, которую можно загрузить с официального веб-сайта MySQL.
После установки Postgres Pro создайте расширение mysql_fdw:
CREATE EXTENSION mysql_fdw;
Эта команда определит необходимые функции и создаст оболочку сторонних данных.
H.4.5. Использование #
В следующих разделах описываются параметры, принимаемые mysql_fdw, и приводятся подробности их использования.
H.4.6. Параметры CREATE SERVER #
В этом разделе описываются параметры, которые mysql_fdw принимает через команду CREATE SERVER.
host(string)Адрес или имя хоста сервера MySQL.
По умолчанию:
127.0.0.1.port(integer)Номер порта сервера MySQL.
По умолчанию:
3306.secure_auth(boolean)Включает или отключает безопасную аутентификацию.
По умолчанию:
true.init_command(string)SQL-оператор, который требуется выполнить при подключении к серверу MySQL.
use_remote_estimate(boolean)Управляет тем, выдаёт ли mysql_fdw удалённые команды
EXPLAINдля получения оценок стоимости.По умолчанию:
false.reconnect(boolean)Включает или отключает автоматическое переподключение к серверу MySQL при потере текущего соединения.
По умолчанию:
false.sql_mode(string)Задаёт MySQL
sql_modeдля установленного соединения.По умолчанию:
ANSI_QUOTES.ssl_key(string)Путь к файлу закрытого ключа клиента.
ssl_cert(string)Путь к файлу открытого ключа клиента.
ssl_ca(string)Путь к файлу сертификата центра сертификации (Certificate Authority, CA). При использовании этот параметр должен указывать на тот же сертификат, который используется сервером.
ssl_capath(string)Путь к каталогу, содержащему доверенные файлы SSL-сертификатов CA.
ssl_cipher(string)Список разрешённых шифров для SSL-шифрования.
fetch_size(integer)Количество строк, которое mysql_fdw должен извлекать за одну операцию выборки. Этот параметр можно задать для сторонней таблицы или стороннего сервера. Параметр на уровне таблицы переопределяет параметр на уровне сервера.
По умолчанию:
100.character_set(string)Набор символов, используемый для соединения с MySQL. По умолчанию используется значение
auto, что означает автоматическое определение на основе параметров операционной системы. До появления параметраcharacter_setнабор символов задавался аналогично кодировке базы данных Postgres Pro. Чтобы вернуть это поведение, установите для параметраcharacter_setзначениеPGDatabaseEncoding.По умолчанию:
auto.mysql_default_file(string)Задаёт путь к файлу параметров по умолчанию MySQL, если сведения о подключении (такие как имя пользователя и пароль) должны считываться из этого файла.
truncatable(boolean)Управляет тем, разрешает ли mysql_fdw опустошать сторонние таблицы с помощью команды
TRUNCATE. Этот параметр можно задать для сторонней таблицы или стороннего сервера. Параметр на уровне таблицы переопределяет параметр на уровне сервера.По умолчанию:
true.
H.4.7. Параметры CREATE USER MAPPING #
В этом разделе описываются параметры, которые mysql_fdw принимает через команду CREATE USER MAPPING.
username(string)Имя пользователя для подключения к MySQL.
Обязательный параметр.
password(string)Пароль для аутентификации на сервере MySQL.
Обязательный параметр.
H.4.8. Параметры CREATE FOREIGN TABLE #
В этом разделе описываются параметры уровня таблицы, которые mysql_fdw принимает через команду CREATE FOREIGN TABLE.
dbname(string)Имя базы данных MySQL для выполнения запросов.
Обязательный параметр.
table_name(string)Имя таблицы MySQL
По умолчанию: имя сторонней таблицы.
fetch_size(integer)Аналогичен параметру
fetch_sizeдля стороннего сервера.max_blob_size(integer)Максимальный размер объекта
BLOB, считываемого без усечения.truncatable(boolean)Аналогичен параметру стороннего сервера.
По умолчанию:
true.
H.4.9. Параметры IMPORT FOREIGN SCHEMA #
mysql_fdw поддерживает IMPORT FOREIGN SCHEMA. В этом разделе описываются настраиваемые параметры, которые принимает расширение.
import_default(boolean)Управляет тем, включаются выражения
DEFAULTдля столбцов в определения сторонних таблиц, импортируемых со стороннего сервера.По умолчанию:
false.import_not_null(boolean)Управляет тем, включаются ли ограничения столбцов
NOT NULLв определения сторонних таблиц, импортируемых со стороннего сервера.По умолчанию:
true.import_enum_as_text(boolean)Может использоваться для сопоставления типа MySQL
ENUMс типомTEXTв определениях сторонних таблиц. Если параметр не задан, для типа, который не может быть создан, возвращается предупреждение.По умолчанию:
false.import_generated(boolean)Управляет тем, включаются ли выражения генерируемых столбцов (
GENERATED) в определения сторонних таблиц, импортируемых со стороннего сервера. КомандаIMPORTзавершится ошибкой, если импортируемое генерируемое выражение использует функцию или оператор, которые не существуют в Postgres Pro.По умолчанию:
true.
H.4.10. Поддержка TRUNCATE #
mysql_fdw реализует API команды TRUNCATE для обёрток сторонних данных, появившихся в Postgres Pro 14. В MySQL предусмотрена команда TRUNCATE. За подробностями обратитесь к https://dev.mysql.com/doc/refman/9.7/en/truncate-table.html.
Применяются следующие ограничения:
TRUNCATE ... CASCADEне поддерживается;TRUNCATE ... RESTART IDENTITYне поддерживается и игнорируется;TRUNCATE ... CONTINUE IDENTITYне поддерживается и игнорируется;таблицы MySQL со ссылками на внешние ключи невозможно опустошить.
H.4.11. Функции #
Помимо стандартных функций mysql_fdw_handler() и mysql_fdw_validator(), mysql_fdw предоставляет следующие служебные функции, вызываемые пользователями:
mysql_fdw_version()Возвращает номер версии в виде целого числа.
mysql_fdw_display_pushdown_list()Выводит содержимое файла
mysql_fdw_pushdown.config.
H.4.12. Генерируемые столбцы #
Хотя mysql_fdw выполняет в MySQL добавление или обновление значения генерируемого столбца, это значение может измениться внутри MySQL. В результате нет гарантии, что последующие операции SELECT обнаружат в столбце ожидаемое сгенерированное значение. Это ограничение также применимо и к postgres_fdw.
За подробностями обратитесь к:
H.4.13. Примеры #
H.4.13.1. Создание стороннего сервера с соответствующей конфигурацией #
Один раз для каждого стороннего источника данных от имени суперпользователя Postgres Pro:
-- Создайте объект сервера -- CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306');
H.4.13.2. Предоставление прав на использование стороннего сервера обычному пользователю в Postgres Pro #
Один раз для обычного пользователя, не обладающего правами суперпользователя в Postgres Pro, от имени суперпользователя Postgres Pro:
GRANT USAGE ON FOREIGN SERVER mysql_server TO pguser;
Где pguser — это пример пользователя для работы со сторонним сервером (и сторонними таблицами).
Рекомендуется запускать команды от имени суперпользователя только при строгой необходимости, поэтому обычному пользователю разрешается доступ к стороннему серверу. Это не обязательно для данного примера, но является рекомендацией по безопасности.
H.4.13.3. Сопоставление пользователей #
Создайте подходящее сопоставление пользователей:
-- Создайте сопоставление пользователей -- CREATE USER MAPPING FOR pguser SERVER mysql_server OPTIONS (username 'foo', password 'bar');
Где pguser — это пример пользователя для работы со сторонним сервером (и сторонними таблицами).
H.4.13.4. Создание сторонней таблицы #
Все SQL-команды CREATE FOREIGN TABLE могут выполняться от имени обычного пользователя Postgres Pro, если предоставлены соответствующие права с помощью GRANT USAGE ON FOREIGN SERVER. Из соображений безопасности права суперпользователя Postgres Pro не требуется, хотя от его имени эти команды также будут работать.
Укажите параметр table_name, если имя таблицы MySQL отличается от имени сторонней таблицы:
-- Создайте стороннюю таблицу --
CREATE FOREIGN TABLE warehouse (
warehouse_id int,
warehouse_name text,
warehouse_created timestamp
)
SERVER mysql_server
OPTIONS (dbname 'db', table_name 'warehouse');Дополнительные операции с данными сторонних таблиц:
-- Вставьте новые строки в таблицу --
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);
-- Выберите данные из таблицы --
SELECT * FROM warehouse ORDER BY 1;
warehouse_id | warehouse_name | warehouse_created
-------------+----------------+-------------------
1 | UPS | 10-JUL-20 00:00:00
2 | TV | 10-JUL-20 00:00:00
3 | Table | 10-JUL-20 00:00:00
-- Удалите строку таблицы --
DELETE FROM warehouse where warehouse_id = 3;
-- Обновите строку таблицы --
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;
-- Выведите план выполнения для таблицы с параметром verbose --
EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=10.00..11.00 rows=1 width=36)
Output: warehouse_id, warehouse_name
-> Foreign Scan on public.warehouse (cost=10.00..1010.00 rows=1000 width=36)
Output: warehouse_id, warehouse_name
Local server startup cost: 10
Remote query: SELECT `warehouse_id`, `warehouse_name` FROM `db`.`warehouse` WHERE ((`warehouse_name` LIKE BINARY 'TV'))H.4.13.5. Импорт базы данных MySQL в виде схемы в Postgres Pro #
IMPORT FOREIGN SCHEMA someschema FROM SERVER mysql_server INTO public;