Replicación selectiva de tablas en un esquema Maestro - Esclavo de MySQL

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:

Índice de contenidos

Configuración del host Maestro
Configuración del host Esclavo
Replicando por primera vez la base de datos
Modificando la replicación para excluir ciertas tablas

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
[mysqld]

log-bin
server_id       = 1
log-basename    = dbmaster
binlog-format   = ROW
binlog-do-db    = nuestra_db

# Contenido del fichero replicacion.cnf

[mysqld]

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:

[mysqld]

server_id       = 2
replicate_do_db = nuestra_db

[mysqld]

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 [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

# Recuperamos el estado del Master
MariaDB [(none)]> 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 [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

# Nos logueamos en el motor MySQL y bloqueamos las tablas en solo lectura

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

# Recuperamos el estado del Master

MariaDB [(none)]> show master status;

+---------------------+----------+------------------------------------------+------------------+

+---------------------+----------+------------------------------------------+------------------+

+---------------------+----------+------------------------------------------+------------------+

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 [(none)]> 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 [(none)]> 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 [(none)]> 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 [(none)]> 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 [(none)]> 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)

# 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

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 [(none)]> 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 [(none)]> 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 [(none)]> 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 [(none)]> 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
[mysqld]

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 [(none)]> 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%

# Agregamos la configuración en las variables replicate_ignore_table y replicate_wild_ignore_table

[mysqld]

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# Revisamos el estado de la replicación

MariaDB [(none)]> 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.