Хабрахабр

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

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

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

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

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

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

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

Что такое pg_chameleon

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

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

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

Пример

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

$> 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

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

$> 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), где можно управлять такими настройками, как расположение файла блокировки, расположение логов, период хранения логов и т. В примере по умолчанию используется правило переопределения типа, которое преобразует tinyint(1) в логическое значение. Дальше идет раздел переопределения типов (type override), где указан набор правил для переопределения типов во время репликации. В нашем случае это база данных PostgreSQL, обозначенная как pg_conn. В следующем разделе указываем детали подключения к целевой базе данных. Заметьте, что «sources» указано во множественном числе, то есть мы можем добавить несколько исходных баз данных для одной целевой, чтобы настроить конфигурацию «многие к одному». В последнем разделе указываем данные источника, то есть параметры подключения исходной базы данных, схему сопоставления исходной и целевой баз данных, таблицы, которые нужно пропустить, время ожидания, память, размер пакета.

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

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

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;

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

Аргумент create_replica_schema в pg_chameleon создает схему по умолчанию (sch_chameleon) и схему репликации (pgworld_x) в базе данных PostgreSQL, как мы уже говорили. Следующие три команды pg_chameleon (chameleon) подготавливают среду, добавляют источник и инициализируют реплику. Аргумент 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

Команды нужно выполнять после того, как мы убедимся, что строки всех целевых таблиц были реплицированы, а результатом будет аккуратно перенесенная база данных PostgreSQL без ссылок на исходную базу данных или схему репликации (sch_chameleon). Если мы выполняем миграцию, следующие команды pg_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

5 и выше в качестве источника и PostgreSQL 9. Поддерживается только с MySQL 5. Поэтому подходит только для схемы «активный-пассивный».
Исходной может быть только база данных MySQL, а поддержка базы данных PostgreSQL как источника только экспериментальная и с ограничениями (узнайте больше здесь) 5 и выше в качестве целевой базы данных.
У каждой таблицы должен быть первичный или уникальный ключ, иначе таблицы инициализируются в процессе init_replica, но не реплицируются.
Односторонняя репликация — только из 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 в этом примере, а также версии баз данных, версии Linux, IP-адреса и порты для обоих узлов. Установить SymmetricDS очень просто: загрузите опенсорс-версию zip-файла отсюда и извлеките ее, куда захотите.

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

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

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

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

$> 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

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

Узел store-001 выполняет базу данных PostgreSQL, а pgdb_replica — это база данных для репликации. Узел 2 на хосте vm2 настраивается как store-001, а остальное указано в файле node.properties, который приводится ниже. 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

Приведенные ниже шаги выполняются на хосте vm1 (corp-000), который создаст пример схемы с 4 таблицами. Готовый пример SymmetricDS содержит параметры для настройки двусторонней репликации между двумя серверами базы данных (двумя узлами). Наконец, в таблицы загружается пример данных. Затем выполнение 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

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

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

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

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

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

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

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

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

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

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

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. Так мы настраиваем только репликацию из corp-000 в store-001. Создаем таблицу t1 для примера и настраиваем правила ее репликации следующим образом.

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)

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

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

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

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

Показать больше

Похожие публикации

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»