Перекрестная репликация между 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.

Пример

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

$> wget https://www.python.org/ftp/python/3.6.8/Python-3.6.8.tar.xz
$> tar -xJf Python-3.6.8.tar.xz
$> cd Python-3.6.8
$> ./configure --enable-optimizations
$> make altinstall

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

$> python3.6 -m venv venv
$> source venv/bin/activate
(venv) $> pip install pip --upgrade
(venv) $> pip install pg_chameleon==2.0.9

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

(venv) $> chameleon set_configuration_files
creating directory /root/.pg_chameleon
creating directory /root/.pg_chameleon/configuration/
creating directory /root/.pg_chameleon/logs/
creating directory /root/.pg_chameleon/pid/
copying configuration  example in /root/.pg_chameleon/configuration//config-example.yml

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

$> cat default.yml
---
#global settings
pid_dir: '~/.pg_chameleon/pid/'
log_dir: '~/.pg_chameleon/logs/'
log_dest: file
log_level: info
log_days_keep: 10
rollbar_key: ''
rollbar_env: ''

# type_override allows the user to override the default type conversion into a different one.
type_override:
  "tinyint(1)":
    override_to: boolean
    override_tables:
      - "*"

#postgres  destination connection
pg_conn:
  host: "192.168.56.106"
  port: "5433"
  user: "usr_replica"
  password: "pass123"
  database: "db_replica"
  charset: "utf8"

sources:
  mysql:
    db_conn:
      host: "192.168.56.102"
      port: "3306"
      user: "usr_replica"
      password: "pass123"
      charset: 'utf8'
      connect_timeout: 10
    schema_mappings:
      world_x: pgworld_x
    limit_tables:
#      - delphis_mediterranea.foo
    skip_tables:
#      - delphis_mediterranea.bar
    grant_select_to:
      - usr_readonly
    lock_timeout: "120s"
    my_server_id: 100
    replica_batch_size: 10000
    replay_max_rows: 10000
    batch_retention: '1 day'
    copy_max_memory: "300M"
    copy_mode: 'file'
    out_dir: /tmp
    sleep_loop: 1
    on_error_replay: continue
    on_error_read: continue
    auto_maintenance: "disabled"
    gtid_enable: No
    type: mysql
    skip_events:
      insert:
        - delphis_mediterranea.foo #skips inserts on the table delphis_mediterranea.foo
      delete:
        - delphis_mediterranea #skips deletes on schema delphis_mediterranea
      update:

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

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

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

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

mysql> CREATE USER usr_replica ;
mysql> SET PASSWORD FOR usr_replica='pass123';
mysql> GRANT ALL ON world_x.* TO 'usr_replica';
mysql> GRANT RELOAD ON *.* to 'usr_replica';
mysql> GRANT REPLICATION CLIENT ON *.* to 'usr_replica';
mysql> GRANT REPLICATION SLAVE ON *.* to 'usr_replica';
mysql> FLUSH PRIVILEGES;

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

postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';
CREATE ROLE
postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;
CREATE DATABASE

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

$> vi /etc/my.cnf
binlog_format= ROW
binlog_row_image=FULL
log-bin = mysql-bin
server-id = 1

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

На узле PostgreSQL:

$> mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_x

На узле MySQL :

$> psql -p 5433 -U usr_replica -h 192.168.56.106 db_replica

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

$> chameleon create_replica_schema --debug
$> chameleon add_source --config default --source mysql --debug
$> chameleon init_replica --config default --source mysql --debug

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

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

$> chameleon start_replica --config default --source mysql 
output: Starting the replica process for source mysql

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

Результат.

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

Результат.

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

mysql> create table t1 (n1 int primary key, n2 varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1,'one');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (2,'two');
Query OK, 1 row affected (0.00 sec)

$> chameleon sync_tables --tables world_x.t1 --config default --source mysql
Sync tables process for source mysql started.

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

$> psql -p 5433 -U usr_replica -d db_replica -c "select * from pgworld_x.t1";
 n1 |  n2
----+-------
  1 | one
  2 | two

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

$> chameleon stop_replica --config default --source mysql 
$> chameleon detach_replica --config default --source mysql --debug

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

$> chameleon drop_source --config default --source mysql --debug
$> chameleon drop_replica_schema --config default --source mysql --debug

Преимущества 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-адреса и порты для обоих узлов.

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

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

corp-000.properties
store-001.properties
store-002.properties

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

$> cat engines/corp-000.properties
engine.name=corp-000
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://192.168.1.107:3306/replica_db?autoReconnect=true&useSSL=false
db.user=root
db.password=admin123
registration.url=
sync.url=http://192.168.1.107:31415/sync/corp-000
group.id=corp
external.id=000

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

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

$> cat engines/store-001.properties
engine.name=store-001
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.1.112:5832/pgdb_replica
db.user=postgres
db.password=admin123
registration.url=http://192.168.1.107:31415/sync/corp-000
group.id=store
external.id=001

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

vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> ./dbimport --engine corp-000 --format XML create_sample.xml
vm1$> ./symadmin --engine corp-000 create-sym-tables
vm1$> ./dbimport --engine corp-000 insert_sample.sql

В примере таблицы 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.

vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> ./dbimport --engine store-001 --format XML create_sample.xml

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

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

vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> sym 2>&1 &

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

vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> sym 2>&1 &

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

vm1$> ./symadmin --engine corp-000 reload-node 001

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

mysql> insert into item values ('22000002','Jelly Bean');
Query OK, 1 row affected (0.00 sec)

vm2$> psql -p 5832 -U postgres pgdb_replica -c "select * from item"
 item_id  |   name
----------+-----------
 11000001 | Yummy Gum
 22000002 | Jelly Bean
(2 rows)

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

Результат.

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

mysql> create table  t1 (no integer);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sym_channel (channel_id,create_time,last_update_time) 
values ('t1',current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sym_trigger (trigger_id, source_table_name,channel_id,
last_update_time, create_time) values ('t1', 't1', 't1', current_timestamp,
current_timestamp);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sym_trigger_router (trigger_id, router_id,
Initial_load_order, create_time,last_update_time) values ('t1',
'corp-2-store-1', 1, current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)

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

vm1$> ./symadmin -e corp-000 --node=001 sync-triggers    
vm1$> ./symadmin send-schema -e corp-000 --node=001 t1

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

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

Недостатки SymmetricDS

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

Итоги по SymmetricDS

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

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