Replicação do Percona Server para MySQL para PostgreSQL usando a ferramenta pg_chameleon

A replicação é um dos recursos conhecidos que permitem criar uma cópia idêntica de um banco de dados. É suportado em quase qualquer sistema de gerenciamento de banco de dados relacional (RDBMS). A replicação oferece benefícios significativos, especialmente alta disponibilidade e balanceamento de carga. Mas e se a replicação for necessária entre dois bancos de dados com uma estrutura diferente, como MySQL e PostgreSQL? É possível replicar continuamente as alterações de um banco de dados MySQL para um banco de dados PostgreSQL? A resposta a esta pergunta é a ferramenta de replicação pg_chameleon .

Para replicação contínua de alterações, pg_chameleon usa a biblioteca de replicação MySQL, que permite obter cópias lógicas de linhas do banco de dados MySQL, que são convertidas em um objeto jsonb. A função pl / pgsql no Postgres decodifica o objeto jsonb e reproduz as alterações no banco de dados do Postgres. Para configurar esse tipo de replicação, a variável binlog_format para o banco de dados MySQL deve ser definida como ROW (string).

Alguns pontos a saber antes de configurar esta ferramenta:

1. As tabelas que precisam ser replicadas devem ter uma chave primária.
2. A ferramenta funciona nas versões do PostgreSQL acima da 9.5 e no sistema MySQL acima da 5.5
3. Para configurar essa replicação, a variável binlog_format deve ser definida como ROW.
4. A versão Python deve ser superior a 3,3

Ao iniciar a replicação, o pg_chameleon recupera dados do MySQL no formato CSV com uma divisão em grupos de um determinado comprimento para evitar sobrecarga de memória. Esses dados são liberados para o Postgres com o comando COPY. Se a cópia falhar, o comando INSERT é executado, o que pode retardar o processo. Se o comando INSERT falhar, a linha será perdida.

Para replicar as alterações do MySQL, pg_chameleon emula o comportamento da réplica do MySQL (escravo). Isso cria um esquema no Postgres, executa o carregamento inicial de dados, conecta-se ao protocolo de replicação do MySQL e copia as linhas na tabela do Postgres. Ao mesmo tempo, as funções correspondentes do Postgres fornecem decodificação e alterações de strings. Isso é semelhante a armazenar logs de transferência nas tabelas do Postgres e aplicá-los a um esquema do Postgres. Não é necessário criar um esquema de banco de dados do Postgres usando qualquer linguagem de descrição de dados. Para as tabelas especificadas ao configurar a replicação, a ferramenta pg_chameleon faz isso automaticamente. Se você precisar converter qualquer tipo de uma maneira específica, poderá especificar isso no arquivo de configuração.

A seguir, é um exercício que você pode experimentar. Use as opções fornecidas se ele atender totalmente aos seus requisitos. Realizamos esses testes no CentOS Linux versão 7.4.

Preparação do ambiente

Configurar o Percona Server para MySQL

Instale o MySQL versão 5.7 e adicione as opções apropriadas para replicação.

Neste exercício, instalei o Percona Server for MySQL versão 5.7 usando o repositório 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 

Para pg_chameleon, os seguintes parâmetros devem ser configurados no arquivo my.cnf (arquivo com parâmetros do servidor MySQL). Você pode adicionar as seguintes opções ao arquivo /etc/my.cnf

 binlog_format= ROW binlog_row_image=FULL log-bin = mysql-bin server-id = 1 

Agora, depois de incluir os parâmetros acima no arquivo my.cnf, inicie o servidor MySQL.

 $ service mysql start 

Obtenha a senha temporária para a conta root no arquivo mysqld.log e redefina a senha root usando o comando mysqladmin.

 $ grep "temporary" /var/log/mysqld.log $ mysqladmin -u root -p password 'Secret123!' 

Agora conecte-se à sua própria instância do banco de dados MySQL e crie um esquema / tabelas de amostra. Também criei uma tabela emp para verificação.

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

Crie um usuário para configurar a replicação usando a ferramenta pg_chameleon e conceda a ele os direitos apropriados seguindo estas etapas.

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

Ao criar um usuário em um servidor MySQL ('usr_replica' @ '%'), pode ser necessário substituir o caractere "%" pelo endereço IP correspondente ou pelo nome do host do servidor executando pg_chameleon.

Configurar o PostgreSQL

Instale o PostgreSQL e execute uma cópia do banco de dados.

Siga estas etapas para instalar o PostgreSQL versão 10.x.

 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 

Como você pode ver nos seguintes logs, você precisa criar um usuário no PostgreSQL com o qual pg_chameleon poderá gravar dados modificados no PostgreSQL. Crie também um banco de dados de destino.

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

Etapas para instalar e configurar a replicação usando pg_chameleon

Etapa 1. Neste exercício, instalei o interpretador Python versão 3.6 e pg_chameleon versão 2.0.8 seguindo estas etapas. Se você já possui a versão necessária do interpretador Python instalada, pode pular as etapas para instalá-lo. Podemos criar um ambiente virtual se o sistema operacional não incluir o Python versão 3.x por padrão.

 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 

Etapa 2. Esta ferramenta requer um arquivo de configuração em que as informações sobre os servidores de origem e de destino serão armazenadas e uma pasta para armazenar logs. Para que o pg_chameleon crie um modelo de arquivo de configuração e as pastas correspondentes, use o seguinte comando.

 $ chameleon set_configuration_files 

Quando este comando é executado, os seguintes resultados são exibidos. Eles mostram que esse comando criou várias pastas e arquivos no local em que você o executou.

 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 

Copie o arquivo de configuração de amostra para outro arquivo, por exemplo, default.yml

 $ cd .pg_chameleon/configuration/ $ cp config-example.yml default.yml 

É assim que fica meu arquivo default.yml depois de incluir todos os parâmetros necessários. Nesse arquivo, se necessário, você pode especificar a conversão de tipos de dados, tabelas que devem ser ignoradas durante a replicação e eventos da linguagem de manipulação de dados que devem ser ignorados para a lista selecionada de tabelas.

 --- #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: 

Etapa 3. Crie uma réplica (banco de dados de destino) usando o comando:

 $ chameleon create_replica_schema --debug 

O comando acima cria um esquema e nove tabelas no banco de dados PostgreSQL especificado no arquivo .pg_chameleon / configuration / default.yml. Essas tabelas são necessárias para controlar a replicação do banco de dados de origem para o destino. O mesmo pode ser visto na próxima revista.

 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) 

Etapa 4. Adicione os dados do banco de dados de origem ao pg_chameleon usando o seguinte comando. Especifique o nome do banco de dados de origem, conforme especificado no arquivo de configuração. Neste exemplo, o nome do banco de dados de origem é mysql e o destino é o banco de dados do Postgres, definido como pg_conn.

 $ chameleon add_source --config default --source mysql --debug 

Após executar o comando especificado, você verá que os dados do banco de dados original são adicionados à tabela 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/AN/A 

Etapa 5. Inicialize a réplica (banco de dados de destino) usando o seguinte comando. Especifique o banco de dados de origem a partir do qual as alterações são replicadas no banco de dados PostgreSQL.

 $ chameleon init_replica --config default --source mysql --debug 

A inicialização inclui as seguintes tarefas no servidor MySQL (fonte).

1. Limpe o cache da tabela e defina o bloqueio somente leitura
2. Obter as coordenadas do banco de dados de origem
3. Copiar dados
4. Desbloquear

O comando acima cria automaticamente o esquema do banco de dados de destino do Postgres.
No arquivo default.yml, mencionamos os seguintes mapeamentos de esquema (schema_mappings).

 schema_mappings: sakila: sch_sakila 

Um novo esquema scott agora foi criado no banco de dados de destino db_replica.

 db_replica=# \dn List of schemas Name | Owner ---------------+------------- public | postgres sch_chameleon | usr_replica sch_sakila | usr_replica (3 rows) 

Etapa 6. Agora inicie a replicação usando o seguinte comando.

 $ chameleon start_replica --config default --source mysql 

Etapa 7. Verifique o status e os erros da replicação usando os seguintes comandos.

 $ chameleon show_status --config default $ chameleon show_errors 

É assim que o estado de replicação se parece:

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

Agora você pode ver que as alterações são constantemente replicadas do banco de dados MySQL para o banco de dados PostgreSQL.

Etapa 8. Para verificação, você pode inserir um registro na tabela de banco de dados MySQL que criamos para verificar a replicação no banco de dados 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) 

Pode ser visto no log acima que o registro inserido na tabela MySQL foi replicado para a tabela no banco de dados Postgres.

Você também pode adicionar vários bancos de dados de replicação de origem ao banco de dados de destino do Postgres.

Link

Nesta documentação, você pode aprender sobre os muitos recursos adicionais disponíveis em pg_chameleon.