La replicación es una de las características conocidas que le permiten crear una copia idéntica de una base de datos. Es compatible con casi cualquier sistema de gestión de bases de datos relacionales (RDBMS). La replicación proporciona beneficios significativos, especialmente alta disponibilidad y equilibrio de carga. Pero, ¿qué sucede si se requiere la replicación entre dos bases de datos con una estructura diferente, como MySQL y PostgreSQL? ¿Es posible replicar continuamente los cambios de una base de datos MySQL a una base de datos PostgreSQL? La respuesta a esta pregunta es la herramienta de replicación pg_chameleon .
Para la replicación de cambio continuo, pg_chameleon usa la biblioteca de replicación MySQL, que le permite obtener copias lógicas de filas de la base de datos MySQL, que se convierten en un objeto jsonb. La función pl / pgsql en Postgres decodifica el objeto jsonb y reproduce los cambios en la base de datos de Postgres. Para configurar este tipo de replicación, la variable binlog_format para la base de datos MySQL debe establecerse en ROW (cadena).
Algunos puntos para saber antes de configurar esta herramienta:
- Las tablas que deben replicarse deben tener una clave principal.
- La herramienta funciona en versiones de PostgreSQL por encima de 9.5 y el sistema MySQL por encima de 5.5
- Para configurar esta replicación, la variable binlog_format debe establecerse en ROW.
- La versión de Python debe ser superior a 3.3
Al iniciar la replicación, pg_chameleon recupera datos de MySQL en formato CSV con un desglose en grupos de cierta longitud para evitar la sobrecarga de memoria. Estos datos se vuelcan a Postgres con el comando COPY. Si la copia falla, se ejecuta el comando INSERT, que puede ralentizar el proceso. Si el comando INSERT falla, la fila se pierde.
Para replicar los cambios de MySQL, pg_chameleon emula el comportamiento de la réplica de MySQL (esclavo). Esto crea un esquema en Postgres, realiza la carga de datos inicial, se conecta al protocolo de replicación MySQL y copia las filas en la tabla de Postgres. Al mismo tiempo, las funciones correspondientes de Postgres proporcionan decodificación de cadenas y cambios. Esto es similar a almacenar registros de transferencia en tablas de Postgres y aplicarlos a un esquema de Postgres. No es necesario crear un esquema de base de datos Postgres utilizando ningún lenguaje de descripción de datos. Para las tablas especificadas al configurar la replicación, la herramienta pg_chameleon hace esto automáticamente. Si necesita convertir algún tipo de una manera específica, puede especificar esto en el archivo de configuración.
El siguiente es un ejercicio con el que puedes experimentar. Utilice las opciones proporcionadas si cumple con todos sus requisitos. Realizamos estas pruebas en CentOS Linux versión 7.4.
Preparación del medio ambiente
Configurar el servidor Percona para MySQL
Instale MySQL versión 5.7 y agregue las opciones apropiadas para la replicación.
En este ejercicio, instalé el Servidor Percona para MySQL versión 5.7 usando el repositorio 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, los siguientes parámetros deben configurarse en el archivo my.cnf (archivo con parámetros del servidor MySQL). Puede agregar las siguientes opciones al archivo /etc/my.cnf
binlog_format= ROW binlog_row_image=FULL log-bin = mysql-bin server-id = 1
Ahora, después de incluir los parámetros anteriores en el archivo my.cnf, inicie el servidor MySQL.
$ service mysql start
Obtenga la contraseña temporal para la cuenta raíz del archivo mysqld.log y restablezca la contraseña raíz usando el comando mysqladmin.
$ grep "temporary" /var/log/mysqld.log $ mysqladmin -u root -p password 'Secret123!'
Ahora conéctese a su propia instancia de la base de datos MySQL y cree un esquema / tablas de muestra. También creé una tabla emp para la verificación.
$ 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))"
Cree un usuario para configurar la replicación utilizando la herramienta pg_chameleon y otórguele los derechos apropiados siguiendo estos pasos.
$ 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;
Al crear un usuario en el servidor MySQL ('usr_replica' @ '%'), puede ser necesario reemplazar el carácter "%" con la dirección IP correspondiente o el nombre de host del servidor que ejecuta pg_chameleon.
Configurar PostgreSQL
Instale PostgreSQL y ejecute una copia de la base de datos.
Siga estos pasos para instalar PostgreSQL versión 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 puede ver en los siguientes registros, debe crear un usuario en PostgreSQL con el que pg_chameleon podrá escribir datos modificados en PostgreSQL. También cree una base de datos de destino.
postgres=# CREATE USER usr_replica WITH ENCRYPTED PASSWORD 'secret'; CREATE ROLE postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica; CREATE DATABASE
Pasos para instalar y configurar la replicación usando pg_chameleon
Etapa 1. En este ejercicio, instalé el intérprete de Python versión 3.6 y pg_chameleon versión 2.0.8 siguiendo estos pasos. Si ya tiene instalada la versión requerida del intérprete de Python, puede omitir los pasos para instalarlo. Podemos crear un entorno virtual si el sistema operativo no incluye Python versión 3.x por defecto.
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 herramienta requiere un archivo de configuración donde se almacenará la información sobre los servidores de origen y destino, y una carpeta para almacenar registros. Para que pg_chameleon cree una plantilla de archivo de configuración y las carpetas correspondientes, use el siguiente comando.
$ chameleon set_configuration_files
Cuando se ejecuta este comando, se muestran los siguientes resultados. Muestran que este comando creó varias carpetas y archivos en el lugar donde lo ejecutó.
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 el archivo de configuración de muestra a otro archivo, digamos default.yml
$ cd .pg_chameleon/configuration/ $ cp config-example.yml default.yml
Así es como se ve mi archivo default.yml después de incluir todos los parámetros necesarios en él. En este archivo, puede especificar opcionalmente la conversión de los tipos de datos, las tablas que se deben omitir durante la replicación y los eventos del lenguaje de manipulación de datos que se deben ignorar para la lista de tablas seleccionada.
--- #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:
Paso 3. Cree una réplica (base de datos de destino) con el comando:
$ chameleon create_replica_schema --debug
El comando anterior crea un esquema y nueve tablas en la base de datos PostgreSQL especificada en el archivo .pg_chameleon / configuration / default.yml. Estas tablas son necesarias para controlar la replicación de la base de datos de origen al destino. Lo mismo se puede ver en la próxima revista.
db_replica=# \dn List of schemas Name | Owner
Paso 4. Agregue los datos de la base de datos de origen a pg_chameleon usando el siguiente comando. Especifique el nombre de la base de datos de origen como se especifica en el archivo de configuración. En este ejemplo, el nombre de la base de datos fuente es mysql, y el destino es la base de datos Postgres, definida como pg_conn.
$ chameleon add_source --config default --source mysql --debug
Después de ejecutar el comando especificado, verá que los datos de la base de datos original se agregan a la tabla t_sources.
db_replica=# select * from sch_chameleon.t_sources; -[ RECORD 1 ]-------+---------------------------------------------- i_id_source | 1 t_source | mysql jsb_schema_mappings | {: } 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
Paso 5. Inicialice la réplica (base de datos de destino) con el siguiente comando. Especifique la base de datos de origen desde la que se replican los cambios a la base de datos PostgreSQL.
$ chameleon init_replica --config default --source mysql --debug
La inicialización incluye las siguientes tareas en el servidor MySQL (fuente).
- Borre el caché de la tabla y configure el bloqueo de solo lectura
- Obtenga las coordenadas de la base de datos fuente
- Copiar datos
- Desbloquear
El comando anterior crea automáticamente el esquema de la base de datos de Postgres de destino.
En el archivo default.yml, mencionamos las siguientes asignaciones de esquema (schema_mappings).
schema_mappings: sakila: sch_sakila
Ahora se ha creado un nuevo esquema scott en la base de datos db_replica de destino.
db_replica=# \dn List of schemas Name | Owner
Paso 6. Ahora comienza la replicación usando el siguiente comando.
$ chameleon start_replica --config default --source mysql
Paso 7. Verifique el estado de replicación y los errores utilizando los siguientes comandos.
$ chameleon show_status --config default $ chameleon show_errors
Así es como se ve el estado de replicación:
$ 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
Ahora puede ver que los cambios se replican constantemente desde la base de datos MySQL a la base de datos PostgreSQL.
Paso 8. Para la verificación, puede insertar un registro en la tabla de la base de datos MySQL que creamos para verificar la replicación en la base de datos 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)
Se puede ver en el registro anterior que el registro insertado en la tabla MySQL fue replicado a la tabla en la base de datos Postgres.
También puede agregar múltiples bases de datos de replicación de origen a la base de datos de Postgres de destino.
Enlace
A partir de esta documentación, puede obtener información sobre las muchas funciones adicionales disponibles en pg_chameleon.