Репликация является одной из хорошо известных функций, позволяющих создавать идентичную копию базы данных. Она поддерживается практически в любой реляционной системе управления базой данных (РСУБД). Возможность репликации обеспечивает значительные преимущества, в особенности высокую доступность и распределение нагрузки. Но как быть, если требуется репликация между двумя базами данных (БД) с разной структурой, такими как MySQL и PostgreSQL? Можно ли непрерывно осуществлять репликацию изменений из БД MySQL в БД PostgreSQL? Ответом на этот вопрос является инструмент репликации pg_chameleon.
Для непрерывной репликации изменений pg_chameleon использует библиотеку репликации MySQL, позволяющую получить логические копии строк из БД MySQL, которые преобразовываются в объект jsonb. Функция pl/pgsql в Postgres декодирует объект jsonb и воспроизводит изменения в БД Postgres. Для настройки такого типа репликации переменная binlog_format для базы MySQL должна иметь значение ROW (строка).
Несколько моментов, которые следует знать до настройки этого инструмента:
- Таблицы, кото��ые необходимо реплицировать, должны иметь первичный ключ.
- Инструмент работает в версиях PostgreSQL выше 9.5 и системы MySQL выше 5.5
- Для настройки такой репликации переменная binlog_format должна иметь значение ROW.
- Версия языка Python должна быть выше 3.3
При запуске репликации pg_chameleon получает данные из MySQL в формате CSV с разбиением на группы определенной длины, чтобы избежать перегрузки памяти. Эти данные сбрасываются в Postgres командой COPY (копировать). Если скопировать не удается, выполняется команда INSERT (вставка), что может замедлять процесс. Если выполнить команду INSERT не удается, строка теряется.
Для репликации изменений из MySQL pg_chameleon имитирует поведение реплики (slave) MySQL. При этом создается схема в Postgres, выполняется начальная загрузка данных, производится подключение к протоколу репликации MySQL, в таблице Postgres сохраняются копии строк. При этом соответствующие функции Postgres обеспечивают декодирование строк и внесение изменений. Это аналогично хранению журналов передачи в таблицах Postgres и применению их к схеме Postgres. Создавать схему БД Postgres с использованием любых языков описания данных не требуется. Для таблиц, указанных при настройке репликации, инструмент pg_chameleon делает это автоматически. Если необходимо конвертировать какие-либо типы определенным образом, можно указать это в файле конфигурации.
Далее представлено упражнение, с выполнением которого можно поэкспериментировать. Используйте предложенные варианты, если оно полностью удовлетворяет вашим требованиям. Мы проводили такие тесты на ОС CentOS Linux версии 7.4.
Подготовка среды
Настройте систему Percona Server for MySQL
Установите MySQL версии 5.7 и добавьте соответствующие параметры для репликации.
В этом упражнении я установил систему Percona Server for MySQL версии 5.7 с помощью репозитория YUM.
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm yum install Percona-Server-server-57 echo "mysql ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers usermod -s /bin/bash mysql sudo su - mysql
Для pg_chameleon требуется настройка следующих параметров в файле my.cnf (файл с параметрами сервера MySQL). Можно добавить следующие параметры в файл /etc/my.cnf
binlog_format= ROW binlog_row_image=FULL log-bin = mysql-bin server-id = 1
Теперь, после включения вышеуказанных параметров в файл my.cnf, запустите сервер MySQL.
$ service mysql start
Получите временный пароль для учетной записи root из файла mysqld.log и сбросьте пароль root с помощью команды mysqladmin.
$ grep "temporary" /var/log/mysqld.log $ mysqladmin -u root -p password 'Secret123!'
Теперь подключитесь к собственному экземпляру базы данных MySQL и создайте образец схемы/таблиц. Я также создал таблицу emp для проверки.
$ wget http://downloads.mysql.com/docs/sakila-db.tar.gz $ tar -xzf sakila-db.tar.gz $ mysql -uroot -pSecret123! < sakila-db/sakila-schema.sql $ mysql -uroot -pSecret123! < sakila-db/sakila-data.sql $ mysql -uroot -pSecret123! sakila -e "create table emp (id int PRIMARY KEY, first_name varchar(20), last_name varchar(20))"
Создайте пользователя для настройки репликации с использованием инструмента pg_chameleon и предоставьте ему соответствующие права, выполнив следующие действия.
$ mysql -uroot -p create user 'usr_replica'@'%' identified by 'Secret123!'; GRANT ALL ON sakila.* TO 'usr_replica'@'%'; GRANT RELOAD, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'usr_replica'@'%'; FLUSH PRIVILEGES;
При создании пользователя на сервере MySQL (‘usr_replica’@’%’) может понадобиться заменить символ «%» соответствующим IP-адресом или именем хоста сервера, на котором работает pg_chameleon.
Настройте PostgreSQL
Установите PostgreSQL и запустите копию базы данных.
Для установки PostgreSQL версии 10.х выполните следующие действия.
yum install https://yum.postgresql.org/10/redhat/rhel-7.4-x86_64/pgdg-centos10-10-2.noarch.rpm yum install postgresql10* su - postgres $/usr/pgsql-10/bin/initdb $ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data start
Как видно из следующих журналов, вам необходимо создать пользователя в PostgreSQL, с помощью которого pg_chameleon сможет записать измененные данные в PostgreSQL. Также создайте целевую базу данных.
postgres=# CREATE USER usr_replica WITH ENCRYPTED PASSWORD 'secret'; CREATE ROLE postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica; CREATE DATABASE
Этапы установки и настройки репликации с использованием pg_chameleon
Этап 1. В этом упражнении я установил интерпретатор языка Python версии 3.6 и pg_chameleon версии 2.0.8, выполнив следующие действия. Если у вас уже установлена необходимая версия интерпретатора Python, действия по его установке можно пропустить. Мы можем создать виртуальную среду, если ОС не включает язык Python версии 3.х по умолчанию.
yum install gcc openssl-devel bzip2-devel wget cd /usr/src wget https://www.python.org/ftp/python/3.6.6/Python-3.6.6.tgz tar xzf Python-3.6.6.tgz cd Python-3.6.6 ./configure --enable-optimizations make altinstall python3.6 -m venv venv source venv/bin/activate pip install pip --upgrade pip install pg_chameleon
Этап 2. Для этого инструмента требуется файл конфигурации, где будут храниться сведения об исходном и целевом серверах, и папка для хранения журналов. Чтобы инструмент pg_chameleon создал шаблон файла конфигурации и соответствующие папки, используйте следующую команду.
$ chameleon set_configuration_files
При выполнении этой команды выводятся следующие результаты. Они показывают, что эта команда создала несколько папок и файлов в том месте, откуда вы ее запускали.
creating directory /var/lib/pgsql/.pg_chameleon creating directory /var/lib/pgsql/.pg_chameleon/configuration/ creating directory /var/lib/pgsql/.pg_chameleon/logs/ creating directory /var/lib/pgsql/.pg_chameleon/pid/ copying configuration example in /var/lib/pgsql/.pg_chameleon/configuration//config-example.yml
Скопируйте образец файла конфигурации в другой файл, допустим, default.yml
$ cd .pg_chameleon/configuration/ $ cp config-example.yml default.yml
Вот как выглядит мой файл 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: "localhost" port: "5432" user: "usr_replica" password: "secret" database: "db_replica" charset: "utf8" sources: mysql: db_conn: host: "localhost" port: "3306" user: "usr_replica" password: "Secret123!" charset: 'utf8' connect_timeout: 10 schema_mappings: sakila: sch_sakila 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:
Этап 3. Создайте реплику (целевую БД), используя команду:
$ chameleon create_replica_schema --debug
Приведенная выше команда обеспечивает создание схемы и девяти таблиц в базе данных PostgreSQL, указанных в файле .pg_chameleon/configuration/default.yml file. Эти таблицы требуются для управления репликацией из исходной БД в целевую. То же самое можно увидеть в следующем журнале.
db_replica=# \dn List of schemas Name | Owner ---------------+------------- public | postgres sch_chameleon | target_user (2 rows) db_replica=# \dt sch_chameleon.t_* List of relations Schema | Name | Type | Owner ---------------+------------------+-------+------------- sch_chameleon | t_batch_events | table | target_user sch_chameleon | t_discarded_rows | table | target_user sch_chameleon | t_error_log | table | target_user sch_chameleon | t_last_received | table | target_user sch_chameleon | t_last_replayed | table | target_user sch_chameleon | t_log_replica | table | target_user sch_chameleon | t_replica_batch | table | target_user sch_chameleon | t_replica_tables | table | target_user sch_chameleon | t_sources | table | target_user (9 rows)
Этап 4. Добавьте данные исходной БД в pg_chameleon с помощью следующей команды. Укажите имя исходной БД, как указано в файле конфигурации. В этом примере имя исходной БД — mysql, а целевой является БД Postgres, определенная как pg_conn.
$ chameleon add_source --config default --source mysql --debug
После выполнения указанной команды вы увидите, что данные исходной БД добавлены в таблицу t_sources.
db_replica=# select * from sch_chameleon.t_sources; -[ RECORD 1 ]-------+---------------------------------------------- i_id_source | 1 t_source | mysql jsb_schema_mappings | {"sakila": "sch_sakila"} enm_status | ready t_binlog_name | i_binlog_position | b_consistent | t b_paused | f b_maintenance | f ts_last_maintenance | enm_source_type | mysql v_log_table | {t_log_replica_mysql_1,t_log_replica_mysql_2} $ chameleon show_status --config default Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay ----------- ------------- ------ -------- ------------ ---------- ----------- ------------ ------------- 1 mysql mysql ready Yes N/A N/A
Этап 5. Инициализируйте реплику (целевую БД) при помощи следующей команды. Укажите исходную БД, из которой производится репликация изменений в БД PostgreSQL.
$ chameleon init_replica --config default --source mysql --debug
Инициализация включает следующие задачи на сервере MySQL (исходный).
- Очистите кэш таблиц и установите блокировку «только для чтения»
- Получите координаты исходной БД
- Скопируйте данные
- Снимите блокировку
Указанная выше команда автоматически создает схему целевой БД Postgres.
В файле default.yml мы упомянули следующие сопоставления схемы (schema_mappings).
schema_mappings: sakila: sch_sakila
Теперь создана новая схема scott в целевой базе данных БД db_replica.
db_replica=# \dn List of schemas Name | Owner ---------------+------------- public | postgres sch_chameleon | usr_replica sch_sakila | usr_replica (3 rows)
Этап 6. Теперь начните репликацию, используя следующую команду.
$ chameleon start_replica --config default --source mysql
Этап 7. Проверьте состояние репликации и наличие ошибок, используя следующие команды.
$ chameleon show_status --config default $ chameleon show_errors
Вот так выглядит состояние репликации:
$ chameleon show_status --source mysql Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay ----------- ------------- ------ -------- ------------ ---------- ----------- ------------ ------------- 1 mysql mysql running No N/A N/A == Schema mappings == Origin schema Destination schema --------------- -------------------- sakila sch_sakila == Replica status == --------------------- --- Tables not replicated 0 Tables replicated 17 All tables 17 Last maintenance N/A Next maintenance N/A Replayed rows Replayed DDL Skipped rows
Теперь можно увидеть, что изменения постоянно реплицируются из БД MySQL в БД PostgreSQL.
Этап 8. Для проверки можно вставить запись в таблицу БД MySQL, которую мы создали для проверки репликации в БД Postgres.
$ mysql -u root -pSecret123! -e "INSERT INTO sakila.emp VALUES (1,'avinash','vallarapu')" mysql: [Warning] Using a password on the command line interface can be insecure. $ psql -d db_replica -c "select * from sch_sakila.emp" id | first_name | last_name ----+------------+----------- 1 | avinash | vallarapu (1 row)
Из приведенного выше журнала видно, что запись, вставленная в таблицу MySQL, была реплицирована в таблицу в БД Postgres.
Также можно добавлять несколько исходных БД для репликации в целевую БД Postgres.
Из этой документации вы можете получить сведения о множестве дополнительных возможностей, имеющихся в pg_chameleon.
