Hace unos días configurábamos una replicación de tipo Maestro – Esclavo entre dos entornos MySQL para un cliente cuando surgió la necesidad de hacer una replicación selectiva de las tablas contenidas en la base de datos maestra.
Las replicaciones de tipo Maestro – Esclavo son de tipo asíncrono, es decir, el host Maestro se encarga de generar en un log las operaciones que ha procesado y manda dicho log para que un motor esclavo se encargue de replicarlas.
A modo de breve configuración la replicación entre el Maestro y Esclavo se define de la siguiente forma:
Configuración del host Maestro
En el host que hará las funciones de maestro tendremos que configurar las siguientes opciones en su my.cnf, lo que suele ser más comodo a la hora de aislar los cambios es utilizar un fichero replicacion.cnf que alojaremos en el directorio de configuración de MySQL, por ejemplo /etc/mysql/conf.d:
# Contenido del fichero replicacion.cnf log-bin server_id = 1 log-basename = dbmaster binlog-format = ROW binlog-do-db = nuestra_db
Estas opciones activan un server_id que deberá ser único en el esquema de replicación, activa los logs binarios y le dice al motor que escriba las operaciones realizadas sobre la base de datos nuestra_db en el fichero de log, cuyo nombre se formará mediante la cadena dbmaster. Para que la replicación se realice de forma correcta el tipo de formato del fichero de binlog debe ser ROW.
Con esto ya estaría listo, no olvidéis reiniciar el motor MySQL para aplicar los cambios.
Configuración del host Esclavo
Una vez configurado el host Maestro pasaremos a modificar el host Esclavo, en este caso haremos la misma operación, crear un fichero replicacion.cnf dentro del directorio /etc/mysql/conf.d.
El contenido es el siguiente:
server_id = 2 replicate_do_db = nuestra_db
Una vez reiniciado el motor MySQL podemos empezar el proceso de sincronización inicial entre el Maestro y el Esclavo.
Replicando por primera vez la base de datos
El proceso de replicación inicial consta de dos fases. Primero bloquearemos en solo lectura la base de datos que queremos replicar (nuestra_db) y haremos un backup de la misma. Después verificaremos en qué posición del fichero del binlog está el Maestro para que, al recuperar el backup en el esclavo y configurar la replicación, la sincronización entre ambos sea válida.
El proceso puede parecer complejo pero es bastante sencillo, comenzamos entrando en el Maestro y realizando un FLUSH TABLES WITH READ LOCK:
# Nos logueamos en el motor MySQL y bloqueamos las tablas en solo lectura MariaDB > FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) # Recuperamos el estado del Master MariaDB > show master status; +---------------------+----------+------------------------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------------+----------+------------------------------------------+------------------+ | dbmaster-bin.000005 | 884 | nuestra_db | | +---------------------+----------+------------------------------------------+------------------+ 1 row in set (0.00 sec) # En otra consola ssh u otra terminal, hacemos un dump de todas las bases de datos o de sólo la que necesitemos root@dbmaster:~# mysqldump --all-databases --user=root --password --master-data > alldatabase.sql Enter password: root@dbmaster:~# ls -larth *.sql -rw-r--r-- 1 root root 470K jul 24 21:40 alldatabase.sql # Copiamos el dump hasta el servidor slave root@dbmaster:~# scp alldatabase.sql gtk@172.26.13.239:/home/gtk/ gtk@172.26.13.239 password: alldatabase.sql # Desde el terminal inicial en el que hicimos el FLUSH TABLES WITH READ LOCK hacemos el desbloqueo MariaDB > UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
Y posteriormente ya en el Esclavo importaremos el dump de la base de datos y configuraremos la replicación:
# Importamos la base de datos desde el dump que hicimos en el Maestro root@dbslave:~# mysql -u root -p < /home/gtk/alldatabase.sql Enter password: # Entramos en la terminal del motor root@dbslave:~# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.1.38-MariaDB-0+deb9u1 Debian 9.8 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # Paramos la replicación en caso de que estuviese corriendo previamente MariaDB > stop slave; Query OK, 0 rows affected (0.01 sec) # Configuramos la replicación, el usuario replication_user y su password tienen que ser válidos y con permisos de REPLICATION SLAVE # Utilizamos los valores obtenidos en el SHOW MASTER STATUS en la configuración de la replicación MariaDB > change master to master_host='dbmaster', MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='dbmaster-bin.000003', MASTER_LOG_POS=884; # Comenzamos la replicación MariaDB > start slave; Query OK, 0 rows affected (0.00 sec) # Comprobamos que está funcionando # Los valores más importantes son: # Slave_IO_Running: Yes # Slave_SQL_Running: Yes # Seconds_Behind_Master: 0 MariaDB > show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.26.13.238 Master_User: replication_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: dbmaster-bin.000005 Read_Master_Log_Pos: 884 Relay_Log_File: mysqld-relay-bin.000018 Relay_Log_Pos: 1094 Relay_Master_Log_File: dbmaster-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: nuestra_db Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 884 Relay_Log_Space: 1684 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative 1 row in set (0.00 sec)
Modificando la replicación para excluir ciertas tablas
Si habéis seguido los pasos ya deberíais tener una replicación Maestro – Esclavo funcionando, pero ¿qué pasaría si no necesitarais replicar ciertas tablas de la base de datos?
En ocasiones hay tablas que crecen muy rápido y que no tienen ningún tipo de valor para nuestro esquema de replicación, por ejemplo, tablas de logs o tablas que no contienen información necesaria o que pueden recrearse muy fácilmente.
En este caso podemos configurar la replicación para que omita ciertas tablas ya sea mediante la exclusión específica de las mismas o utilizando un patrón. La configuración la haremos de la siguiente forma:
# Agregamos la configuración en las variables replicate_ignore_table y replicate_wild_ignore_table server_id = 2 replicate_do_db = nuestra_db replicate_ignore_table = nuestra_db.logs replicate_wild_ignore_table = nuestra_db.error% # Conectamos al motor que previamente habremos reiniciado root@dbslave:~# mysql -u root -p Password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 10.1.38-MariaDB-0+deb9u1 Debian 9.8 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # Revisamos el estado de la replicación MariaDB > show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: nuestra_db Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: nuestra_db.log Replicate_Wild_Do_Table: nuestra_db.error%
Como veis, la replicación está funcionando y las tablas log y toda aquella tabla que comience por error no serán replicadas desde el Maestro al Esclavo.
De esta forma, hemos configurado una replicación selectiva de una base de datos eligiendo sólo las tablas que necesitamos.
