Кросс-Репликация Между Postgresql И Mysql

Я опишу перекрестную репликацию между PostgreSQL и MySQL, а также методы настройки перекрестной репликации между двумя серверами баз данных.

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

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

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

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

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

При этом данные передаются от мастера к слейву, то есть от активного к пассивному, поскольку репликация осуществляется только в одном направлении.

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

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

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

Описанная конфигурация возможна между разными серверами баз данных.

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

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

Кросс-репликация между MySQL и PostgreSQL необходима для единовременной миграции с одного сервера базы данных на другой.

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

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

Что такое pg_chameleon

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

Имена исходной и целевой схемы не могут совпадать.

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

Каждая функция репликации контролируется демонами.

Управление с помощью параметров и файлов конфигурации на основе YAML.

Пример

   

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

   

Кроме того, модуль 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 есть раздел глобальных настроек, где можно управлять такими настройками, как расположение файла блокировки, расположение логов, срок хранения логов и т.д. Далее идёт раздел переопределения типов, где набор правил для переопределения типов во время репликации.

В примере по умолчанию используется правило переопределения типа, которое преобразует tinyint(1) в логическое значение.

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

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

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

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

Аргумент 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

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

Односторонняя репликация — только из MySQL в PostgreSQL. Поэтому он подходит только для схемы «активно-пассивный».

Источником может быть только база данных MySQL, а поддержка базы данных PostgreSQL в качестве источника является только экспериментальной и с ограничениями (подробнее Здесь )

Результаты для pg_chameleon

Но проблему односторонней репликации можно решить с помощью другого инструмента с открытым исходным кодом — SymmetricDS. Подробнее читайте в официальной документации.

Здесь .

Справку по командной строке можно найти Здесь .

Обзор SymmetricDS

Redshift, Azure и т. д. Доступные функции: синхронизация баз данных и файлов, репликация базы данных с несколькими хозяевами, фильтрованная синхронизация, преобразование и другие.

Это инструмент Java, и для него требуется стандартная версия JRE или JDK (версия 8.0 или выше).

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

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

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

Двусторонняя репликация с использованием методов Push и Pull на основе набора правил.

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

Автоматическое восстановление при возобновлении работы узлов после сбоя и автоматическое разрешение конфликтов.

Совместимые с облаком и мощные API-интерфейсы расширений.

Пример

Активный узел (Узел 1) может обмениваться данными для репликации с другим активным узлом (Узел 2) без посредника.

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

Описывать всю архитектуру заняло бы слишком много времени, поэтому проведите исследование.

управление чтобы узнать больше об устройстве SymmetricDS. Установить SymmetricDS очень просто: загрузите версию zip-файла с открытым исходным кодом.

отсюда и выноси куда хочешь.

В таблице ниже представлена информация о месте установки и версии SymmetricDS в этом примере, а также версии базы данных, версии Linux, IP-адреса и порты для обоих узлов.

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

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

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

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

Скопируйте необходимый файл конфигурации из каталога примеров в механизмы на хосте 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, а подключение к базе данных обрабатывается драйвером jdbc mysql, который использует приведенную выше строку подключения и учетные данные для входа.

Подключаемся к базе данных Replica_db и при создании схемы будут созданы таблицы.

sync.url показывает, куда обращаться к узлу для синхронизации.

Узел 2 на хосте vm2 настроен как store-001, а остальное указано в файле node.properties ниже.

Node 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_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 на виртуальной машине 1 есть примеры таблиц и таблицы каталога 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, а также в стандартный вывод. Теперь сим-сервер можно запустить на узле store-001.

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

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

Или вы можете отправить загрузочную загрузку в хранилище узла-001 из corp-000 с помощью следующей команды.

vm1$> .

/symadmin --engine corp-000 reload-node 001

На этом этапе новая запись вставляется в таблицу элементов базы данных 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 выполняется для отправки изменений схемы в хранилище узла-001, и настраивается репликация таблицы t1.

vm1$> .

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

/symadmin send-schema -e corp-000 --node=001 t1

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

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

Реплицируйте любую базу данных в любую другую базу данных локально, в глобальной сети или в облаке.

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

Платная версия с графическим интерфейсом и отличной поддержкой.

Недостатки SymmetricDS

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

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

Результаты для SymmetricDS

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

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

• Ретроспективы В Проектных Командах: Что Это Такое, Зачем Нужны И Как Их Эффективно Проводить

  19 Oct, 24

• Логика Хостера: Не Пойман — Не Вор. Как Доказать Обратное. Обзор Сервиса Hosttracker, Часть 3

  19 Oct, 24

• Два Странных Обновления Из Вконтакте

  19 Oct, 24

• Новые Города В Myclime

  19 Oct, 24

• Венера И Вулканы. Скудные Ответы На Накопившиеся Вопросы

  19 Oct, 24

• Будь Мужиком, Открой Ип

  19 Oct, 24

• Брандмауэр Или Брандмауэр? Какое Имя Встречается Чаще?

  19 Oct, 24

• Умная Нормализация Данных

  19 Oct, 24

• Чистая Быстрая Архитектура Как Альтернатива Viper

  19 Oct, 24

• Автоматизированное Тестирование Parallels Cloud Server

  19 Oct, 24