Репликация является одной из хорошо известных функций, позволяющих создавать идентичную копию базы данных. Она поддерживается практически в любой реляционной системе управления базой данных (РСУБД). Возможность репликации обеспечивает значительные преимущества, в особенности высокую доступность и распределение нагрузки. Но как быть, если требуется репликация между двумя базами данных (БД) с разной структурой, такими как 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.