Mysql postgresql на одном сервере

Перекрестная репликация между PostgreSQL и MySQL


Я в общих чертах расскажу о перекрестной репликации между PostgreSQL и MySQL, а еще о методах настройки перекрестной репликации между этими двумя серверами базы данных. Обычно базы данных в перекрестной репликации называются однородными, и это удобный метод перехода с одного сервера реляционной СУБД на другой.

Базы данных PostgreSQL и MySQL принято считать реляционными, но с дополнительными расширениями они предлагают возможности NoSQL. Здесь мы обсудим репликацию между PostgreSQL и MySQL, с точки зрения реляционных СУБД.

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

Обычно репликация между двумя идентичными серверами баз данных выполняется либо в двоичном режиме, либо с помощью запросов между ведущим узлом (он же издатель, главный или активный) и ведомым (подписчик, ожидающий или пассивный). Цель репликации — предоставить в реальном времени копию главной базы данных на стороне ведомого. При этом данные передаются от ведущего к ведомому, то есть от активного к пассивному, потому что репликация выполняется только в одну сторону. Но можно настроить репликацию между двумя базами данных в обе стороны, чтобы данные передавались от ведомого к ведущему в конфигурации «активный-активный». Все это, в том числе каскадная репликация, возможно между двумя и более идентичными серверами баз данных, Конфигурация «активный-активный» или «активный-пассивный» зависит от потребности, доступности таких возможностей в исходной конфигурации или использования внешних решений для настройки и существующих компромиссов.

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

Перекрестная репликация между MySQL и PostgreSQL нужна для однократной миграции с одного сервера баз данных на другой. Эти базы данных используют разные протоколы, поэтому связать их напрямую не получится. Чтобы наладить обмен данными, можно использовать внешний опенсорс-инструмент, например pg_chameleon.

Что такое pg_chameleon

pg_chameleon — это система репликации из MySQL в PostgreSQL на Python 3. В ней используется опенсорс-библиотека mysql-replication, тоже на Python. Образы строк извлекаются из таблиц MySQL и сохраняются как объекты JSONB в базе данных PostgreSQL, а потом расшифровываются функцией pl/pgsql и воспроизводятся в базе данных PostgreSQL.

Возможности pg_chameleon

Несколько схем MySQL из одного кластера можно реплицировать в одну целевую базу данных PostgreSQL с конфигурацией «один ко многим»
Имена исходной и целевой схем не могут совпадать.
Данные репликации можно извлечь из каскадной реплики MySQL.
Таблицы, которые не могут реплицироваться или создают ошибки, исключаются.
Каждой функцией репликации управляют демоны.
Контроль с помощью параметров и файлов конфигурации на базе YAML.

Пример

Хост vm1 vm2
Версия ОС CentOS Linux 7.6 x86_64 CentOS Linux 7.5 x86_64
Версия сервера БД MySQL 5.7.26 PostgreSQL 10.5
Порт БД 3306 5433
IP-адрес 192.168.56.102 192.168.56.106

Для начала подготовьте все необходимые компоненты для установки pg_chameleon. В этом примере установлен Python 3.6.8, который создает виртуальную среду и активирует ее.

После успешной установки Python3.6 нужно выполнить остальные требования, например создать и активировать виртуальную среду. Кроме того, pip-модуль обновляется до последней версии и используется для установки pg_chameleon. В командах ниже намеренно устанавливается pg_chameleon 2.0.9, хотя последняя версия — 2.0.10. Это нужно, чтобы избежать новых багов в обновленной версии.

Затем мы вызываем pg_chameleon (chameleon — это команда) с аргументом set_configuration_files, чтобы включить pg_chameleon и создать каталоги и файлы конфигурации по умолчанию.

Теперь мы создаем копию config-example.yml как default.yml, чтобы он стал файлом конфигурации по умолчанию. Образец файла конфигурации для этого примера приводится ниже.

Файл конфигурации в этом примере — это образец файла с pg_chameleon с незначительными изменениями в соответствии с исходной и целевой средами, и ниже приводится обзор различных разделов файла конфигурации.

В файле конфигурации default.yml есть раздел глобальных параметров (global settings), где можно управлять такими настройками, как расположение файла блокировки, расположение логов, период хранения логов и т. д. Дальше идет раздел переопределения типов (type override), где указан набор правил для переопределения типов во время репликации. В примере по умолчанию используется правило переопределения типа, которое преобразует tinyint(1) в логическое значение. В следующем разделе указываем детали подключения к целевой базе данных. В нашем случае это база данных PostgreSQL, обозначенная как pg_conn. В последнем разделе указываем данные источника, то есть параметры подключения исходной базы данных, схему сопоставления исходной и целевой баз данных, таблицы, которые нужно пропустить, время ожидания, память, размер пакета. Заметьте, что «sources» указано во множественном числе, то есть мы можем добавить несколько исходных баз данных для одной целевой, чтобы настроить конфигурацию «многие к одному».

База данных world_x в примере содержит 4 таблицы со строками, которые сообщество MySQL предлагает для примера. Ее можно загрузить здесь. Пример базы данных поставляется в виде tar и сжатого архива с инструкциями по созданию и импорту строк.

В базах данных MySQL и PostgreSQL создается специальный пользователь с одинаковым именем usr_replica. В MySQL ему предоставляются дополнительные права на чтение всех реплицируемых таблиц.

На стороне PostgreSQL создается база данных db_replica, которая будет принимать изменения из базы данных MySQL. Пользователь usr_replica в PostgreSQL автоматически настраивается как владелец двух схем pgworld_x и sch_chameleon, которые содержат фактические реплицированные таблицы и таблицы с каталогами репликации соответственно. За автоматическую конфигурацию отвечает аргумент create_replica_schema, как вы увидите ниже.

База данных MySQL настраивается с изменениями некоторых параметров, чтобы подготовить ее к репликации, как показано ниже. Нужно будет перезапустить сервер баз данных, чтобы изменения вступили в силу.

Сейчас важно проверить подключение к обоим серверам баз данных, чтобы при выполнении команд pg_chameleon не возникло проблем.

На узле PostgreSQL:

Следующие три команды pg_chameleon (chameleon) подготавливают среду, добавляют источник и инициализируют реплику. Аргумент create_replica_schema в pg_chameleon создает схему по умолчанию (sch_chameleon) и схему репликации (pgworld_x) в базе данных PostgreSQL, как мы уже говорили. Аргумент add_source добавляет исходную базу данных в конфигурацию, считывая файл конфигурации (default.yml), и в нашем случае это mysql, а init_replica иницализирует конфигурацию на основе параметров в файле конфигурации.

Выходные данные этих трех команд очевидно указывают на их успешное выполнение. Все сбои или синтаксические ошибки указываются в простых и понятных сообщениях с подсказками по исправлению проблем.

Наконец, запустим репликацию с помощью start_replica и получим сообщение об успешном выполнении.

Статус репликации можно запросить с помощью аргумента show_status, а просмотреть ошибки — с помощью аргумента show_errors.

Как мы уже говорили, каждой функцией репликации управляют демоны. Чтобы просмотреть их, запросим таблицу процессов командой Linux ps, как показано ниже.

Репликация не считается настроенной, пока мы не протестируем ее в реальном времени, как показано ниже. Мы создаем таблицу, вставляем пару записей в базу данных MySQL и вызываем аргумент sync_tables в pg_chameleon, чтобы обновить демоны и реплицировать таблицу с записями в базу данных PostgreSQL.

Чтобы подтвердить результаты теста, запрашиваем таблицу из базы данных PostgreSQL и выводим строки.

Если мы выполняем миграцию, следующие команды pg_chameleon будут ее окончанием. Команды нужно выполнять после того, как мы убедимся, что строки всех целевых таблиц были реплицированы, а результатом будет аккуратно перенесенная база данных PostgreSQL без ссылок на исходную базу данных или схему репликации (sch_chameleon).

По желанию следующими командами можно удалить исходную конфигурацию и схему репликации.

Преимущества pg_chameleon

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

Недостатки pg_chameleon

Поддерживается только с MySQL 5.5 и выше в качестве источника и PostgreSQL 9.5 и выше в качестве целевой базы данных.
У каждой таблицы должен быть первичный или уникальный ключ, иначе таблицы инициализируются в процессе init_replica, но не реплицируются.
Односторонняя репликация — только из MySQL в PostgreSQL. Поэтому подходит только для схемы «активный-пассивный».
Исходной может быть только база данных MySQL, а поддержка базы данных PostgreSQL как источника только экспериментальная и с ограничениями (узнайте больше здесь)

Итоги по pg_chameleon

Метод репликации в pg_chameleon отлично подходит для миграции базы данных из MySQL в PostgreSQL. Существенный минус в том, что репликация только односторонняя, поэтому специалисты по базам данных вряд ли захотят использовать его для чего-то, кроме миграции. Но проблему односторонней репликации можно решить еще одним опенсорс-инструментом — SymmetricDS.

Подробнее читайте в официальной документации здесь. Справку по командной строке можно найти здесь.

Обзор SymmetricDS

SymmetricDS — это опенсорс-инструмент, который реплицирует любую базу данных в любую другую распространенную базу данных: Oracle, MongoDB, PostgreSQL, MySQL, SQL Server, MariaDB, DB2, Sybase, Greenplum, Informix, H2, Firebird и другие облачные экземпляры БД, например Redshift, и Azure и т. д. Доступные функции: синхронизация баз данных и файлов, репликация нескольких ведущих баз данных, фильтрованная синхронизация, преобразование и другие. Это инструмент на Java, и требуется стандартный выпуск JRE или JDK (версии 8.0 или выше). Здесь можно записывать изменения данных по триггерам в исходной базе данных и направлять их в соответствующую целевую базу данных в виде пакетов.

Возможности SymmetricDS

Инструмент не зависит от платформы, то есть две или несколько разных БД могут обмениваться данными.
Реляционные БД синхронизируются с помощью записи изменения данных, а БД на основе файловых систем используют синхронизацию файлов.
Двусторонняя репликация с использованием методов Push и Pull на основе набора правил.
Передача данных возможна по защищенным сетям и сетям с низкой пропускной способностью.
Автоматическое восстановление при возобновлении работы узлов после сбоя и автоматическое разрешение конфликтов.
Совместимость с облаком и эффективные API расширений.

Пример

SymmetricDS можно настроить в одном из двух вариантов:
Ведущий (родительский) узел, который централизованно координирует репликацию данных между двумя ведомыми (дочерними) узлами, и обмен данными между дочерним узлами осуществляется только через родительский.
Активный узел (узел 1) может обмениваться данными для репликации с другим активным узлом (узел 2) без посредника.

В обоих вариантах обмен данными происходит с помощью Push и Pull. В этом примере мы рассмотрим конфигурацию «активный-активный». Описывать всю архитектуру слишком долго, так что изучите руководство, чтобы узнать больше об устройстве SymmetricDS.

Установить SymmetricDS очень просто: загрузите опенсорс-версию zip-файла отсюда и извлеките ее, куда захотите. В таблице ниже приводятся сведения о месте установки и версии SymmetricDS в этом примере, а также версии баз данных, версии Linux, IP-адреса и порты для обоих узлов.

Хост vm1 vm2
Версия ОС CentOS Linux 7.6 x86_64 CentOS Linux 7.6 x86_64
Версия сервера БД MySQL 5.7.26 PostgreSQL 10.5
Порт БД 3306 5832
IP-адрес 192.168.1.107 192.168.1.112
Версия SymmetricDS SymmetricDS 3.9 SymmetricDS 3.9
Путь установки SymmetricDS /usr/local/symmetric-server-3.9.20 /usr/local/symmetric-server-3.9.20
Имя узла SymmetricDS corp-000 store-001

Здесь мы устанавливаем SymmetricDS в /usr/local/symmetric-server-3.9.20, и тут же будут храниться разные вложенные каталоги и файлы. Нас интересуют вложенные каталоги samples и engines. В каталоге samples хранятся примеры файлов конфигурации со свойствами узла, а также примеры скриптов SQL для быстрого начала демонстрации.

В каталоге samples видим три файла конфигурации со свойствами узла — имя показывает характер узла в определенной схеме.

В SymmetricDS есть все необходимые файлы конфигурации для базовой схемы из 3 узлов (вариант 1), и те же файлы можно использовать для схемы из 2 узлов (вариант 2). Копируем нужный файл конфигурации из каталога samples в engines на хосте vm1. Получается так:

Этот узел в конфигурации SymmetricDS называется corp-000, а подключение к базе данных обрабатывается драйвером mysql jdbc, который использует строку подключения, указанную выше, и учетные данные для входа. Мы подключаемся к базе данных replica_db, а во время создания схемы будут созданы таблицы. sync.url показывает место связи с узлом для синхронизации.

Узел 2 на хосте vm2 настраивается как store-001, а остальное указано в файле node.properties, который приводится ниже. Узел store-001 выполняет базу данных PostgreSQL, а pgdb_replica — это база данных для репликации. registration.url позволяет хосту vm2 связаться с хостом vm1 и получить от него детали конфигурации.

Готовый пример SymmetricDS содержит параметры для настройки двусторонней репликации между двумя серверами базы данных (двумя узлами). Приведенные ниже шаги выполняются на хосте vm1 (corp-000), который создаст пример схемы с 4 таблицами. Затем выполнение create-sym-tables командой symadmin создает таблицы каталогов, где будут храниться правила и направление репликации между узлами. Наконец, в таблицы загружается пример данных.

В примере таблицы item и item_selling_price настроены автоматически для репликации из corp-000 в store-001, а таблицы sale (sale_transaction и sale_return_line_item) автоматически настроены для репликации из store-001 в corp-000. Теперь создаем схему в базе данных PostgreSQL на хосте vm2 (store-001), чтобы подготовить ее к приему данных от corp-000.

Обязательно проверяем, что в базе данных MySQL на vm1 есть примеры таблиц и таблицы каталогов SymmetricDS. Заметьте, что системные таблицы SymmetricDS (с префиксом sym_) сейчас доступны только на узле corp-000, потому что там мы выполнили команду create-sym-tables и будем управлять репликацией. А еще в базе данных на узле store-001 будет всего 4 таблицы примера без данных.

Все. Среда готова для запуска серверных процессов sym на обоих узлах, как показано ниже.

Записи логов отправляются в файл фонового лога (symmetric.log) в папке логов в каталоге, где установлен SymmetricDS, а также в стандартные выходные данные. Сервер sym теперь можно инициировать на узле store-001.

Если запустить серверный процесс sym на хосте vm2, он создаст таблицы каталога SymmetricDS еще и в базе данных PostgreSQL. Если запустить серверный процесс sym на обоих узлах, они скоординируются друг с другом, чтобы реплицировать данные с corp-000 на store-001. Если через несколько секунд мы запросим все 4 таблицы по обе стороны, то увидим, что репликация выполнена успешно. Или можно отправить начальную загрузку на узел store-001 из corp-000 следующей командой.

На этом этапе в таблицу item в базе данных MySQL на узле corp-000 (хост: vm1) вставляется новая запись, и можно проверить ее репликацию в базу данных PostgreSQL на узле store-001 (хост: vm2). Мы видим операцию Pull для перемещения данных из corp-000 в store-001.

Чтобы выполнить операцию Push для перемещения данных из store-001 в corp-000, вставляем запись в таблицу sale_transaction и проверяем, что репликация выполнена.

Мы видим успешную настройку двусторонней репликации таблиц примера между базами данных MySQL и PostgreSQL. Чтобы настроить репликацию для новых пользовательских таблиц, выполняем следующие действия. Создаем таблицу t1 для примера и настраиваем правила ее репликации следующим образом. Так мы настраиваем только репликацию из corp-000 в store-001.

Затем конфигурация получает уведомление об изменении схемы, то есть добавлении новой таблицы, с помощью команды symadmin с аргументом sync-triggers, который воссоздает триггеры для сопоставления определений таблиц. Выполняется send-schema для отправки изменений схемы на узел store-001, и репликация таблицы t1 настроена.

Преимущества SymmetricDS

Простая установка и конфигурация, включая готовый набор файлов с параметрами для создания схемы с тремя или двумя узлами.
Кросплатформенность баз данных и независимость от платформы, включая серверы, ноутбуки и мобильные устройства.
Репликация любой базы данных в любую другую базу данных локально, в WAN или в облаке.
Возможность оптимальной работы с парой баз данных или нескольким тысячами для удобной репликации.
Платная версия с графическим интерфейсом и отличной поддержкой.

Недостатки SymmetricDS

Нужно вручную определять в командной строке правила и направление репликации через операторы SQL для загрузки таблиц каталогов, что бывает неудобно.
Настраивать много таблиц для репликации бывает утомительно, если не использовать скрипты для создания операторов SQL, которые определяют правила и направление репликации.
В логи заносится слишком много информации, и иногда нужно наводить порядок в файле лога, чтобы он не занимал слишком много места.

Итоги по SymmetricDS

SymmetricDS позволяет настраивать двустороннюю репликацию между двумя, тремя и даже несколькими тысячами узлов, чтобы выполнять репликацию и синхронизировать файлы. Это уникальный инструмент, который самостоятельно выполняет многие задачи, например автоматическое восстановление данных после длительного простоя на узле, защищенный и эффективный обмен данными между узлами по HTTPS, автоматическое управление конфликтами на основе набора правил и т. д. SymmetricDS выполняет репликацию между любыми базами данных, поэтому его можно использовать для самых разных сценариев, включая миграцию, переход на новую версию, распространение, фильтрацию и преобразование данных на разных платформах.

Пример создан на основе официального краткого руководства по SymmetricDS. В руководстве пользователя подробно описываются различные понятия, связанные с настройкой репликации с помощью SymmetricDS.

Источник

Читайте также:  Как поменять губку на принтере
Поделиться с друзьями
КомпСовет
Adblock
detector