Publicado el 07 abril 2019

Replicar MySQL (Maestro/Esclavo) en CentOS

La replicación MySQL resulta muy útil en términos de seguridad de los datos, solución Fail-Over, balanceo de carga, backups y para la realización de análisis sin perjudicar el rendimiento. En este post vamos a aprender cómo replicar nuestro servidor MySQL de manera que nuestras bases de datos sean idénticas en dos servidores distintos, y que únicamente necesitemos escribir en uno de ellos para que los datos sean transferidos automáticamente al otro servidor.

¿Qué beneficios ofrece la replicación MySQL?

Cuando escuchamos la expresión «replicar una base de datos», normalmente lo primero que nos viene a la cabeza es la posibilidad de tener varias copias de la misma información, a modo de backup, para poder recuperarla en caso de que se produzca un fallo en el servidor. Pero la replicación de una base de datos ofrece otras ventajas también, por ejemplo, nos permite realizar backups de las bases de datos directamente sobre el esclavo, sin que la escritura en el maestro afecte a nuestra copia de seguridad; nos permite disponer de la información duplicada, de forma que puedan balancearse las consultas entre los dos servidores en caso de ser muy pesadas; también permite realizar los análisis necesarios sobre el esclavo, que normalmente consumen muchos recursos, sin perjudicar el rendimiento en el maestro.

Nuestro entorno

En este ejemplo, vamos a funcionar con las siguientes características:

• Dos servidores Linux con CentOS 7 (funciona igualmente con la versión 6 de CentOS).
• Ambos servidores usan MySQL 5.7.
• El servidor Maestro admite conexiones remotas por el puerto 3306.
• IP del Maestro: 192.168.1.1
• IP del Esclavo: 192.168.1.2

Configurando el servidor Maestro

El servidor maestro será el servidor donde se escriban nuestros datos, y desde aquí se replicarán al servidor esclavo. Para ello debemos realizar unos ajustes en su archivo de configuración, editándolo:

# nano /etc/my.cnf

Añadiremos las siguientes líneas en la sección [mysqld] de nuestro archivo de configuración:

server-id = 1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

A continuación, reiniciamos el servidor MySQL:

# service mysqld restart

Ahora nos conectamos al servidor MySQL como root, pues vamos a crear un usuario para el servidor esclavo y a otorgarle permisos para la replicación de los datos (reemplaza «esclavo» y «contraseña» por el usuario y contraseña que desees):

mysql> GRANT REPLICATION SLAVE ON *.* TO 'esclavo'@'%' IDENTIFIED BY 'contraseña';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1112 |  | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> quit;

Toma nota del nombre del archivo mostrado anteriormente (mysql-bin.000002) y de la posición (1112) ya que la necesitaremos después para la configuración del esclavo. Ahora exportamos todas las bases de datos así como la configuración del maestro:

# mysqldump -u root -p --all-databases --master-data > /root/backup.db

Ahora que ya tenemos lista la exportación, nos conectaremos de nuevo como root al servidor MySQL y desbloquearemos las tablas:

mysql> UNLOCK TABLES;
mysql> quit;

Subimos el backup de MySQL que acabamos de generar al servidor esclavo, con el comando SCP:

# scp /root/backup.db root@192.168.1.2:/root/

Configurando el servidor Esclavo

Ya tenemos listo nuestro servidor maestro, y hemos subido al esclavo la base de datos, vamos entonces a dejar listo el servidor de esclavo, editando su archivo de configuración:

# nano /etc/my.cnf

Añadiremos las siguientes líneas en la sección [mysqld] de nuestro archivo de configuración:

server-id = 2
master-host=192.168.1.1
master-connect-retry=60
master-user=esclavo
master-password=contraseña
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Como ves, la configuración es muy parecida a la que hemos realizado en el servidor Maestro, sólo que en este caso le indicamos a nuestro esclavo cuál es la IP del servidor Maestro, cuántos segundos tardará en volver a intentar la conexión en caso de que falle el primer intento, y el usuario y contraseña que creamos en el servidor Maestro para permitir la replicación.

A continuación, importamos el backup que hicimos en el Maestro y reiniciamos el servidor MySQL:

# mysql -u root -p < /root/backup.db
# service mysqld restart

Ahora accedemos a MySQL como root, detenemos el esclavo, y le indicamos el nombre del archivo del Maestro (mysql-bin.000002) y la posición (1112) que obtuvimos durante la configuración del Maestro, con el comando «SHOW MASTER STATUS;». Recuerda que en el siguiente comando debes reemplazar la IP por la IP de tu Maestro, así como el usuario y contraseña que creaste para conectarte a él:

# mysql -u root -p

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='esclavo', MASTER_PASSWORD='contraseña', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1112;
mysql> slave start;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: esclavo
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 12345100
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 11381900
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: 12345100
Relay_Log_Space: 11382055
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:
1 row in set (0.00 sec)

Si obtienes algo como esto, significa que tienes tu replicación Maestro/Esclavo funcionando y perfectamente sincronizada. Si deseas probarlo, sólo tienes que crear un registro nuevo en cualquier base de datos del servidor Maestro, o incluso una base de datos completa y verás que es inmediatamente replicada en el esclavo. En este reporte que obtenemos del estado del Esclavo, los campos más importantes son «Last_SQL_Error», que estará correcto mientras se encuentre en «0»; en caso contrario habrá que mirar qué ha sucedido y reiniciar el servicio Esclavo con los comandos «stop eslave» y «start eslave»; y el campo «Seconds_Behind_Master», que indica cuántos segundos está retrasado el Esclavo respecto a su Maestro. Este valor nunca debe ser demasiado alto.

Reparando un error de replicación

En ocasiones, puede efectuarse una consulta errónea que resulte en un error de replicación. Aquí vamos a ver cómo poder resolverlo y continuar replicando los datos sin tener que configurar de nuevo todos los pasos anteriores.

Lo primero es detectar el error, de modo que lo buscaremos en primer lugar en el log de MySQL (dependiendo del Sistema Operativo que utilices, tendrás el log de MySQL en una ubicación u otra), en CentOS solemos encontrarlo en el directorio /var/lib/mysql/error.log.

# nano /var/lib/mysql/error.log

ABR 08 09:56:08 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error ‘Table ‘mydb.taggregate_temp_1212047760′ doesn’t exist’ on query. Default database: ‘mydb’. Query: ‘UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
ABR 08 09:56:08 mysqld[1380]: ^ISET thread.views = thread.views + aggregate.views
ABR 08 09:56:08 mysqld[1380]: ^IWHERE thread.threadid = aggregate.threadid’, Error_code: 1146
ABR 08 09:56:08 mysqld[1380]: 080529 9:56:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with «SLAVE START». We stopped at log ‘mysql-bin.001079’ position 203015142

Como vemos, en la última línea nos indica en qué posición se detuvo la replicación.

Esto podremos comprobarlo accediendo a MySQL en el servidor Esclavo y ejecutando el comando mencionado anteriormente «SHOW SLAVE STATUS \G», que nos arrojará también la consulta que nos ha dado error.

Para reparar esto, primero detendremos el esclavo:

mysql> stop slave;

Y ejecutaremos el siguiente comando a continuación:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Con esto le indicamos al esclavo que ignore la última consulta realizada (que es la que nos causó el error). Si quieres ignorar dos consultas, sólo tendrás que cambiar el valor de ese parámetro de 1 a 2, y así con cuantas consultas desees ignorar.

Ahora ya podemos iniciar de nuevo el esclavo y consultar el estado de la replicación:

mysql> start slave;
mysql> SHOW SLAVE STATUS \G

Ya hemos aprendido cómo realizar una replicación de un servidor MySQL Maestro/Esclavo y a solucionar los posibles problemas que puedan surgir con la replicación MySQL. Ahora no tenemos excusa para tener siempre a buen recaudo nuestras bases de datos.