Replicación master slave con MySQL

La replicación master slave es una configuración muy utilizada en MySQL y en otras bases de datos es utilizar una base de datos para la escritura y otra o varias bases de datos para la lectura, es lo que conocemos como replicación master slave, en esta publicación vamos a analizar y ver como lo podemos hacer con MySQL.

La replicación master slave con MySQL nos permite tener varias copias sincronizas de nuestra base de datos principal, cualquier cambio en el maestro (master) se replicará en los esclavos (slaves), esta configuración nos proporciona escalabilidad y backup, entre otras cosas, como por ejemplo hacer consultas sobre la réplica sin afectar a la base de datos principal.

Estos son los pasos que seguiremos

  1. CONFIGURACIÓN DEL MASTER (MAESTRO)
  2. CONFIGURACIÓN DEL SLAVE (ESCLAVO)
  3. ACTIVAMOS LA REPLICACIÓN

Software utilizado

  • Ubuntu: sistema operativo Linux que usamos en este tutorial.
  • MySQL: base de datos relacional MySQL que usamos como maestro y como esclavo.
  • Virtual Box: sistema de virtualización creado por Oracle, aquí tengo instalado dos servidores MySQL uno con MySQL de Oracle y otro con Mariadb usando ambos como esclavos en mis pruebas.
  • MariaDB: base de datos relacional fork MySQL, y que mantiene la compatibilidad en su mayor parte, aprovechando este tutorial para hacer algunas pruebas lo configuro también como esclavo.

La explicación de este tutorial se realiza para entornos linux, en concreto usaremos Ubuntu, se necesitará tener los privilegios de sudo y la base de datos MySQL ya instalada, aquí puedes ver una instalación sencilla con MySQL (Mariadb):

Tutorial MariaDB- Preparando el entorno

Tutorial MariaDB- Preparando el entorno


 

1. CONFIGURACIÓN DEL MASTER (MAESTRO) EN REPLICACIÓN MASTER SLAVE

Configuración del fichero de MySQL

Vamos a configurar en primer lugar nuestra base de datos MySQL para habilitar su funcionamiento como master y poder así realizar la replica master slave, para ello abrimos el fichero de configuración de base de datos con nuestro editor favorito:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

En este tipo de configuraciones la idea es tener la réplica en otro servidor con lo que configuraríamos el maestro en una ip y el esclavo en la ip correspondiente del servidor redundante, empecemos.

En primer lugar, actualizamos la dirección ip en local tal y como lo tendremos por defecto (si no lo hemos tocado):

bind-address            = 192.168.0.18

En el fichero de configuración encontramos un apartado de Logging and Replication, donde haremos el primer cambio, buscamos el campo server-id, aquí necesitamos tener un número que sea único, y que no este definido en otro grupo de replicación, como estamos con la primera configuración simplemente lo dejamos con el valor que trae por defecto 1:

server-id               = 1

La siguiente línea que vamos a quitar los comentarios es log_bin, aquí es donde se guardan todos los cambios que se realizan en el maestro, y es el que utiliza el esclavo para replicarse.

log_bin                 = /var/log/mysql/mysql-bin.log

Por último indicamos la base de datos que vamos a replicar en mi caso escojo customerdb, que estoy utilizando en varios ejemplos, para ello introducimos el nombre de nuestra base de datos en el campo binlog_do_db:

 
binlog_do_db            = customerdb

Listo, ahora salimos y reiniciamos nuestra base de datos, vamos a continuar la configuración directamente en MySQL.

$ sudo service mysql restart

Configuración en la base de datos MySQL

Abrimos el terminal MySQL para ir dando permisos al usuario en la configuración esclava y otras configuraciones:

$ mysql -u root -p

En mi caso voy a configurar los permisos para el usuario xulescode con la password xulescode, dando así los privilegios necesarios para el usuario xulescode en la base de datos esclavo, realizando a continuación un flush sobre los privilegios:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'xulescode'@'%' IDENTIFIED BY 'xulescode';
mysql> FLUSH PRIVILEGES;


 

Copiamos nuestra base de datos MySQL

Esta parte tenemos varias formas de hacerlo, las más utilizadas son:

  • Snapshot: consiste en hacer un snapshot de nuestra base de datos, y después, copiar los datos del directorio de datos (datadir) del maestro (master) al directorio (datadir) del esclavo (slave)
  • Copia: consiste en bloquear nuestra base de datos maestro (master) para hacer una copia, obtener el punto desde el cual se tiene que sincronizar el esclavo (slave) y listo, esta es la que vamos a ver a continuación.

Seleccionamos la base de datos para la que estamos haciendo la réplica:

mysql> USE customerdb;

Ahora bloqueamos las tablas de la base de datos para hacer la copia y prevenir cambios:

mysql> FLUSH TABLES WITH READ LOCK;

Comprobamos el estado de la base de datos maestro y obtenemos el punto de sincronización desde el cual le vamos a decir al esclavo que tiene que empezar:

mysql> SHOW MASTER STATUS;

Este es el resultado que aparecerá en nuestro terminal:

MySQL SHOW MASTER STATUS

SHOW MASTER STATUS

Como podemos ver en la imagen se indica este punto en la columna Position, en Binlog_Do_DB se indica la base de datos, los valores que vemos en la imagen los utilizaremos más tarde.

Con la base de datos bloqueada hacemos la copia de la base de datos en otro terminal:

$ mysqldump -u root -p --opt customerdb > customerdb.sql

Una vez finalizada la copia desbloqueamos las tablas de la base de datos y damos por terminada la configuración del maestro.

mysql> UNLOCK TABLES;
mysql> QUIT;

Dependiendo de como tengas configurado tu servidor MySQL y tus permiso, puede ser necesario cambiar los privilegios debido al cambio de localhost a una ip, si es así, simplemente ejecuta el siguiendo comando para todas las bases de datos:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'xulescode'@'%' IDENTIFIED BY 'xulescode' WITH GRANT OPTION;

Si solo quieres modificar los privilegios para la base de de datos que estas utilizando modifica *.* por customerdb.*, así solo cambiamos customerdb


 

2. CONFIGURACIÓN DEL SLAVE (ESCLAVO) EN REPLICACIÓN MASTER SLAVE

Configuramos ahora el esclavo, seguiremos los pasos para crear la nueva base de datos replicada, configuramos MySQL en el servidor esclavo, y finalmente iniciaremos la sincronización, veamos como.


 

Creamos y copiamos la base de datos

Creamos la nueva base de datos en nuestro servidor réplica:

mysql> CREATE DATABASE customerdb;

Ahora vamos a importar la base de datos que copiamos anteriormente en el servidor replicado, simplemente ejecutamos el siguiente comando:

$mysql -u root -p customerdb < customerdb.sql;


 

Configuramos el esclavo en el fichero

Una vez finalizado editamos el fichero de configuración de MySQL para definir el funcionamiento:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Empezamos definiendo la Ip de nuestro servidor, igual que antes en bind_address

bind-address            = 192.168.0.14

Como ya comentamos antes, necesitamos tener identificadores únicos en el campo server-id, así que lo buscamos y lo modificamos poniendo el valor 2:

server-id               = 2

Activamos la línea log_bin:

log_bin                 = /var/log/mysql/mysql-bin.log

Indicamos la base de datos que va a trabajar como esclavo (slave) customerdb en el campo binlog_do_db:

 
binlog_do_db            = customerdb

Ahora necesitamos añadir una línea que no aparece por defecto en el fichero de configuración para el relay-log, que al igual que el binary log consiste en una serie de ficheros numerados que describen los eventos de cambios en la base de datos, para más información lo puedes consultar en la documentación de Mysql: Slave Relay Log:

relay-log               = /var/log/mysql/mysql-relay-bin.log

Una vez finalizados estos pasos reiniciamos el servidor MySQL.


 

3. ACTIVAMOS LA REPLICACIÓN

Seguimos en el servidor de replica, accedemos y vamos a ejecutar un comando para indicar cual es el servidor maestro (master), así como el usuario y password de acceso al mismo, así tendríamos la siguiente línea de código:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.18',MASTER_USER='xulescode', MASTER_PASSWORD='xulescode', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=402;

Estos son los parámetros que necesitamos configurar en detalle:

  • MASTER_HOST=’192.168.0.18′: ip del servidor maestro (master).
  • MASTER_USER=’xulescode’: nombre del usuario utilizado para la sincronización.
  • MASTER_PASSWORD=’xulescode’: clave para el usuario definido.
  • MASTER_LOG_FILE=’mysql-bin.000001′: fichero log que hemos obtenido al hacer la copia y consultar el servidor maestro (master).
  • MASTER_LOG_POS= 402: posición de inicio de la sincronización, que hemos obtenido del maestro al igual que el fichero.

Con está instrucción damos por configurado el servidor esclavo (slave) ya solo nos queda activarlo:

mysql> START SLAVE;

Y comprobar su estado con :

mysql> SHOW SLAVE STATUS\G;

El resultado que saldrá en pantalla si todo fue bien es el siguiente:

mysqk> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.18
                  Master_User: xulescode
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1007
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 1298
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_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: 1007
              Relay_Log_Space: 1758
              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: 
1 row in set (0.00 sec)

¿Qué pasa si tenemos algún error de configuración?

Si tenemos algún fallo no podremos establecer la conexión con el servidor maestro, en este fichero nos saldrán también los errores. Para mostrarte el resultado con un ejemplo sencillo, he configurado temporalmente el maestro con la ip local (localhost), en este caso en el resultado que saldrá en pantalla lo podemos ver una línea indicándonos que no se ha podido establecer la conexión, como vemos en la salida por terminal (resumida), tal que así:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.0.18
                  Master_User: xulescode
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 402
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
                ...
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'xulescode@192.168.0.18:3306' - retry-time: 60  retries: 86400  message: Can't connect to MySQL server on '192.168.0.18' (111 "Connection refused")
               Last_SQL_Errno: 0
               ...
1 row in set (0.00 sec)

Como se puede ver, se indica que la conexión ha sido rechazada, si este fuese tu caso simplemente actualiza la ip en el servidor maestro y ejecuta de nuevo:

mysql> START SLAVE; 
mysql> SHOW SLAVE STATUS\G;

Verás ahora que en las indicaciones aparece el servidor conectado.

Como anécdota, durante la preparación de esta publicación he configurado para el servidor maestro (master) un servidor MySQL con versión 5.6.31 y como esclavos (slaves) un servidor con MySQL con versión 5.6.31 y una servidor con Mariadb (MySQL) con el mismo número de versión, sin tener problemas de compatibilidad y funcionamiento.

Documentación

A continuación, os dejo algunos enlaces interesantes sobre la configuración de la replicación master slave con Mysql:

Espero que te haya resultado útil esta publicación, si es así compártelo.Código Xules

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *