Mysql подключение к postgresql

Интеграция PostgreSQL с другими СУБД: делаем запросы в MySQL

Нередко бывает так, что в большом проекте в силу тех или иных причин — зачастую исторических, хотя бывает по-всякому — его части могут использовать различные СУБД для хранения и поиска критически важных данных. В числе прочего, этому разнообразию способствует конкуренция и развитие технологий, но, так или иначе, взаимодействие между СУБД описывает стандарт SQL/MED 2003 (Management of External Data), который вводит определение Foreign Data Wrappers (FDW) и Datalink.

Первая часть стандарта предлагает средства для чтения данных как набора реляционных таблиц под управлением одного или нескольких внешних источников; FDW также может представлять возможность использовать SQL-интерфейс для доступа к не SQL данным, таким, как файлы или, например, список писем в почтовом ящике. Вторая часть, Datalink, позволяет управлять удаленным SQL-сервером.

Эти две части были реализованы еще в PostgreSQL 9.1 и называются FDW и dblink соответственно. FDW в PostgreSQL сделан максимально гибко, что позволяет разрабатывать wrapper’ы для большого количества внешних источников. В настоящее время мне известны такие FDW, как PostgreSQL, Oracle, SQL Server, MySQL, Cassandra, Redis, RethinkDB, Ldap, а также FDW к файлам типа CSV, JSON, XML и т.п.

В нашей статье мы поговорим о том, как настроить подключение PostgreSQL к MySQL и эффективно выполнять получающиеся запросы.

Для начала собираем и устанавливаем mysql_fdw:

Устаналиваем extension на базу, чтобы загрузились необходимые библиотеки:

И mapping текущего пользователя в PostgreSQL в пользователя MySQL:

После этого мы имеем возможность подключить таблицу MySQL в PostgreSQL:

Допустим, мы храним справочник customers в PostgreSQL:

Попробуем выбрать 5 самых активных покупателей в январе 2014 года:

Как мы видим, запрос неэффективный, так как со стороны MySQL было получено содержимой всей таблицы: SELECT id, customer_id, order_date FROM data.orders . Сервер, в силу естественных ограничений драйвера MySQL, не в состоянии трансформировать запрос таким образом, чтобы для получения корректного результата этот запрос было бы возможно выполнить на стороне MySQL, и поэтому сначала получает таблицу целиком, а потом уже осуществляет фильтрацию. Однако при изменении запроса можно добиться того, чтобы фильтрация по дате осуществлялась на стороне MySQL:

Сравнение order_date с (‘2014-02-01’::timestamp — ‘1 sec’::interval) неправильно, так как timestamptz хранится с большей точность, чем секунда, но это значение выбрано не случайно, посмотрите:

Тут нас поджидает проблема, из-за которой стоит использовать mysql_fdw с большой осторожностью:

Как мы видим, between, представляя из себя синтаксический сахар, был развернут в два условия, одно из которых не вычислено на стороне PostgreSQL: (‘2014-02-01’::timestamp — ‘1 sec’::interval) и преобразовано в разницу двух строк (а не даты и интервала):

В итоге запрос возвращает неправильный результат.

С подобной проблемой столкнулся один из наших клиентов. Проблема была исправлена в форке PostgresPro, https://github.com/postgrespro/mysql_fdw и создан pull-реквест в основной репозиторий EnterpriseDB. Устанавливаем исправленную версию:

Теперь план запроса выглядит так:

Запрос стал быстрее по сравнению с первым, так как с MySQL мы возвращаем значение более точечного запроса:

Операция фильтрации выполняется теперь на стороне MySQL. При определенных условиях будет использоваться индекс по order_date, если он создан.

Таким образом мы ускорили выполнение запроса. На простом примере мы почуствовали силу Open Source и мощь PostgreSQL в расширяемости.

Источник

Подключение к управляемой базе данных на сервере Ubuntu 18.04

Управляемые базы данных имеют ряд преимуществ по сравнению с самоуправляемыми БД: это и автоматические обновления, и упрощенное масштабирование, и высокая доступность. Но если вы только начинаете пользоваться управляемыми базами данных, даже простейшие задачи (типа подключения к базе данных) могут показаться запутанными и сложными.

Рекомендуем:  Как подключить жесткий диск к ноутбуку через hdmi

В этом мануале вы узнаете, как устанавливать клиентские программы различных систем управления базами данных (СУБД) – PostgreSQL, MySQL и Redis – на сервер Ubuntu 18.04. Также вы научитесь использовать эти программы для подключения к экземпляру управляемой базы данных.

Примечание: Данные инструкции подходят для управляемых баз данных любого облачного провайдера. Однако если у вас все же возникнут проблемы с подключением, вы можете обратиться за помощью к документации провайдера.

Требования

  • Сервер Ubuntu 18.04. На этом сервере должен быть пользователь с правами администратора (не root) и настроенный брандмауэр ufw. Следуйте нашему мануалу по начальной настройке сервера Ubuntu 18.04.
  • Экземпляр управляемой базы данных. В этом мануале приведены инструкции по подключению к PostgreSQL, MySQL и Redis. Чтобы создать такую БД, следуйте документации вашего облачного провайдера.

Выполнив начальные требования, вы можете перейти к тому разделу, который относится к вашей БД – работать с мануалом последовательно не нужно.

Подключение к управляемой базе данных PostgreSQL

Чтобы подключиться к управляемой базе данных PostgreSQL, вы можете использовать стандартный клиент командной строки psql. Открытый исходный код psql поддерживается PostgreSQL Development Group и обычно включен в загрузку сервера PostgreSQL. Но вы можете установить psql самостоятельно через пакет postgresql-client с помощью APT.

Если вы еще этого не сделали, обновите индекс пакетов сервера:

sudo apt update

Затем выполните следующую команду для установки psql:

sudo apt install postgresql-client

Менеджер APT попросит вас подтвердить установку пакета. Сделайте это, нажав клавишу Enter.

После этого вы можете подключиться к управляемой базе данных Postgres без дальнейшей настройки. Например, вы можете вызвать psql со следующими флагами:

  • -U указывает пользователя PostgreSQL, чью сессию вы хотите начать.
  • -h указывает имя хоста или IP-адрес управляемой БД.
  • -p задает порт TCP, который прослушивает управляемая БД.
  • -d позволяет указать БД, с которой вы хотите работать.
  • -v (сокращение для variable) позволяет задать переменные соединения. Этот флаг идет перед переменными. После переменной следует знак равенства (=) и значение. Например, если при подключении вы хотите проверить сертификат CA базы данных, вы должны включить в свою команду флаг: -v sslmode=require.
  • -W запрашивает пароль пользователя PostgreSQL. Вообще-то, перед командой psql можно указать PGPASSWORD=password (где password – ваш пароль), но включать пароли в командную строку не принято, так как это опасно.

Синтаксис команды с флагами выглядит так:

psql -U user -h host -p port -d database -v variable=value -W

Если ваша управляемая БД поддерживает унифицированные идентификаторы ресурса (URI), вы можете использовать такой синтаксис:

Теперь вы можете использовать управляемый экземпляр PostgreSQL. О том, как взаимодействовать с PostgreSQL, можно почитать в мануалах:

Подключение к управляемой базе данных MySQL

Чтобы подключиться к управляемой базе данных MySQL, вы можете использовать официальный клиент БД MySQL. В Ubuntu этот клиент обычно устанавливается путем загрузки пакета mysql-client через APT.

Но если вы используете стандартные репозитории Ubuntu, вы получите версию 5.7. Как правило, для доступа к управляемой базе данных MySQL необходима более новая версия, потому нужно установить версию 8.0 или выше. Для этого вы должны сначала добавить программный репозиторий MySQL, а затем установить пакет оттуда.

Примечание: Если вам не нужно устанавливать последнюю версию mysql-client, вы можете просто обновить индекс пакетов сервера и установить mysql-client, не добавляя репозиторий программного обеспечения MySQL:

sudo apt update
sudo apt install mysql-client

Рекомендуем:  Как приготовить скумбрию для правильного питания

Если вы не уверены, какая версия mysql-client вам нужна, вам следует обратиться к документации по управляемым базам данных вашего облачного провайдера.

Для начала перейдите на страницу репозитория MySQL в своем веб-браузере. Найдите кнопку Download в правом нижнем углу и перейдите на следующую страницу. На этой странице вам будет предложено войти в систему или зарегистрировать учетную запись Oracle. Вы можете пропустить этот шаг с помощью ссылки No thanks, just start my download. Щелкните правой кнопкой мыши на эту ссылку и выберите Copy Link Address (эта опция может отличаться в зависимости от браузера).

Теперь вы готовы скачать файл. На сервере перейдите в изменяемый каталог:

Загрузите файл, используя curl. Не забудьте вставить в команду только что скопированный адрес вместо нашей ссылки. Вам также нужно передать curl два флага командной строки. Флаг -O направляет вывод в файл вместо стандартного вывода. Флаг L включает поддержку перенаправлений HTTP, что необходимо в этом случае, потому что скопированный адрес перед загрузкой файла фактически перенаправляет вас в другое место:

curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

Теперь файл загрузится в текущий каталог. Просмотрите текущие файлы, чтобы убедиться в этом:

Вы увидите имя файла, указанное в выводе:

Теперь вы можете добавить репозиторий MySQL APT в список репозиториев вашей системы. Команда dpkg используется для установки, удаления и проверки пакетов программного обеспечения .deb. Следующая команда содержит флаг -i, который указывает репозиторий, из которого вы хотите иметь возможность устанавливать пакеты:

sudo dpkg -i mysql-apt-config*

Во время установки вам будет представлено окно, в котором вы можете выбрать нужную версию MySQL, а также установить репозитории для других инструментов, связанных с MySQL. По умолчанию добавляется только ​​ репозиторий последней стабильной версии MySQL. Это нам подходит. Используйте стрелку вниз, чтобы перейти к пункту меню Ok, и нажмите Enter.

После этого репозиторий будет добавлен. Обновите кэш пакетов apt, чтобы сделать новые пакеты программного обеспечения доступными:

sudo apt update

Затем вы можете почистить систему и удалить загруженный файл, так как в будущем он вам больше не понадобится:

Примечание: Если вам когда-либо понадобится обновить конфигурацию этих репозиториев, выполните следующую команду:

sudo dpkg-reconfigure mysql-apt-config

Выбрав новые опции, выполните следующую команду, чтобы обновить кэш пакетов:

sudo apt update

Теперь, когда вы добавили необходимый репозиторий MySQL, вы можете установить реальное клиентское программное обеспечение MySQL. Сделайте это с помощью следующей команды apt:

sudo apt install mysql-client

Когда команда будет выполнена, проверьте номер версии клиента и убедитесь, что у вас установлена последняя версия:

mysql —version
mysql Ver 8.0.17-cluster for Linux on x86_64 (MySQL Community Server — GPL)

После установки mysql-client вы можете получить доступ к управляемой базе данных, выполнив команду mysql со следующими флагами:

  • -u указывает пользователя MySQL, чью сессию вы хотите начать.
  • -p запросит пароль пользователя. Вы можете включить пароль прямо в команду (в формате –p password), но включать пароли в командную строку не принято, так как это опасно.
  • -h задает имя хоста или IP-адрес БД MySQL.
  • -P задает порт TCP, который прослушивает управляемая БД.
  • -D позволяет указать БД, с которой вы хотите работать.

Синтаксис команды mysql с флагами выглядит так:

mysql -u user -p -h host -P port -D database

Если ваша управляемая БД поддерживает унифицированные идентификаторы ресурса (URI), вы можете использовать такой синтаксис:

Теперь вы можете использовать управляемый экземпляр MySQL. О том, как взаимодействовать с MySQL, можно почитать в мануалах:

Парольная аутентификация в MySQL 8

В MySQL 8.0 и новее по умолчанию используется плагин аутентификации caching_sha2_password. Но пока что (на момент написания статьи) PHP не поддерживает этот плагин. Если вы планируете использовать управляемую базу данных MySQL в приложении на PHP (например, в WordPress или phpMyAdmin), это может привести к проблемам при подключении к базе данных.

Рекомендуем:  Как найти на пк все файлы ворд

Если у вас есть доступ к конфигурационному файлу базы данных, вы можете добавить параметр, чтобы заставить БД использовать модуль аутентификации с поддержкой PHP по умолчанию – например, mysql_native_password:

Однако некоторые провайдеры управляемых баз данных не дают конечным пользователям доступа к конфигурациям БД. В этом случае вы можете подключиться к базе данных и выполнить команду ALTER USER для всех существующих пользователей MySQL, которым необходимо подключиться к БД, но сделать это с помощью плагина caching_sha2_password не получается:

ALTER USER user IDENTIFIED WITH mysql_native_password BY ‘password’;

Конечно, вы можете настроить новых пользователей для аутентификации с помощью плагина mysql_native_password, указать его можно в команде CREATE USER:

CREATE USER user IDENTIFIED WITH mysql_native_password BY ‘password’;

Подключение к управляемой базе данных Redis

При локальной установке Redis поставляется с redis-cli, это интерфейс командной строки. С помощью redis-cli можно подключиться к удаленному управляемому экземпляру Redis, но у интерфейса нет встроенной поддержки TLS/SSL. Вы можете настроить безопасные подключения к управляемому экземпляру Redis через redis-cli, но вы также можете использовать альтернативный клиент Redis со встроенной поддержкой TLS.

Мы рекомендуем вам установить Redli, интерактивный терминал Redis с открытым исходным кодом. Для этого перейдите на страницу Releases проекта Redli на GitHub и найдите таблицу Assets для последней версии. На момент написания мануала это версия 0.4.4.

Здесь найдите ссылку на файл, заканчивающийся на linux_amd64.tar.gz. Эта ссылка указывает на архив (тарбол), который при извлечении создаст в системе несколько файлов. Кликните правой кнопкой мыши по этой ссылке и выберите Copy link address (формулировка может отличаться в зависимости от веб-браузера).

На сервере перейдите в изменяемый каталог:

Затем вставьте скопированную ссылку в следующую команду wget (замените URL-адрес). Эта команда загрузит файл на сервер:

Когда файл загрузится на ваш сервер, распакуйте архив:

tar xvf redli_0.4.4_linux_amd64.tar.gz

Это создаст на сервере следующие файлы:

LICENSE.txt
README.md
redli

Файл redli – это бинарный файл клиента. Переместите его в каталог /usr/local/bin, где Ubuntu ищет исполняемые файлы:

sudo mv redli /usr/local/bin/

На этом этапе вы можете почистить систему и удалить ненужный тарбол:

rm redli 0.4.4_linux_amd64.tar.gz

Теперь вы можете использовать Redli для подключения к управляемому экземпляру Redis. Вы можете сделать это, выполнив команду redli со следующими флагами:

  • -h указывает хост или IP-адрес, к которому нужно подключиться.
  • -a задает пароль для аутентификации Redis.
  • -p – порт, к которому нужно подключиться.

С учетом этих флагов синтаксис redli будет выглядеть следующим образом. Обратите внимание, что этот пример также включает параметр –tls, который позволяет подключаться к управляемой базе данных Redis через TLS/SSL без туннелирования:

redli —tls -h host -a password -p port

Одно из преимуществ Redli перед redis-cli состоит в том, что он понимает протокол rediss, который используется для обозначения URI, указывающего на базу данных Redis. Благодаря этому вы можете использовать строку подключения для доступа к базе данных:

redli —tls -u rediss://user:password@host:port

Обратите внимание, эта команда включает флаг -u, который задает URI соединения в качестве аргумента.

Теперь вы можете начать работу с вашей управляемой БД Redis.

Заключение

Управляемые базы данных – это относительно новый облачный сервис, а потому многие практики, понятные в самоуправляемых БД, кажутся сложными. Пока еще не у всех облачных провайдеров есть хорошая всесторонняя документация для управляемых БД. Цель этого мануала – помочь вам начать работу со своей управляемой БД.

Источник

Adblock
detector