El mundo de MySQL & MariaDB

Guía de replicación MySQL: instalación y gestión

Aplicaciones / Otros
目次

1. ¿Qué es la replicación MySQL? Su resumen y usos

La replicación MySQL es una función que sincroniza en tiempo real una copia de la base de datos con otros servidores. Esto permite mejorar la redundancia y el rendimiento de la base de datos. A continuación, se explica en detalle en qué escenarios se utiliza la replicación MySQL y cómo funciona.

Resumen de la replicación MySQL

La replicación MySQL comparte el contenido de la base de datos entre varios servidores mediante una configuración de servidor maestro y servidor esclavo. Específicamente, el servidor maestro registra las actualizaciones de datos en el registro binario y el servidor esclavo las lee y las aplica, sincronizando los datos. De este modo, si el servidor maestro falla, se puede cambiar al servidor esclavo para mantener el servicio.

Usos de la replicación MySQL

La replicación MySQL se utiliza ampliamente en los siguientes casos.
  • Garantizar alta disponibilidad: al utilizar el servidor esclavo en caso de una falla, se puede minimizar el tiempo de inactividad.
  • Balanceo de carga: al distribuir las consultas de solo lectura al servidor esclavo, se distribuye la carga del servidor maestro.
  • Preservación de datos y copias de seguridad: la replicación replica los datos en tiempo real, por lo que también puede usarse como respaldo.

Tipos de replicación

En la replicación MySQL existen los siguientes tipos según el método de sincronización de datos.
  • Replicación asíncrona: el maestro avanza el procesamiento sin esperar el momento en que el esclavo reciba la información de actualización, lo que permite respuestas rápidas. Sin embargo, en caso de falla, los datos
  • Replicación semisíncrona: se avanza el procesamiento después de confirmar que los datos se han reflejado en el esclavo, por lo que es más fiable que la asíncrona, aunque la velocidad de respuesta es ligeramente más lenta.
En la siguiente sección se explicarán los conceptos básicos de la replicación MySQL, como el registro binario y GTID.

2. Conceptos básicos de la replicación MySQL

Para comprender la replicación MySQL, es importante entender el papel del registro binario y del GTID (Global Transaction ID). Estos elementos son la base para que los datos se reproduzcan con precisión.

Funciones del maestro y del esclavo

En la replicación MySQL, el servidor maestro y el servidor esclavo desempeñan roles diferentes. El servidor maestro registra los cambios de datos en el registro binario y los distribuye al esclavo. El servidor esclavo aplica los registros recibidos del maestro y actualiza los datos. De este modo, el esclavo puede mantener el mismo contenido que los datos más recientes del maestro.

Registro binario y registro de relé

En la base de la replicación MySQL se utilizan los siguientes dos registros.
  1. registro binario（Binary Log）
  • El registro binario es lo que registra las actualizaciones de datos (INSERT, UPDATE, DELETE, etc.) en el servidor maestro. De este modo, el servidor esclavo puede mantener el mismo estado de datos que el maestro.
  1. registro de relé（Relay Log）
  • El registro de relé es lo que el servidor esclavo guarda en su propio sistema del registro binario recibido del maestro. El hilo SQL del esclavo ejecuta este registro de relé secuencialmente, reflejando los cambios de datos.

Qué es GTID (Global Transaction ID)

GTID es un mecanismo que asigna un ID único a cada transacción, lo que ayuda a mantener la consistencia de la sincronización entre varios esclavos. Al usar GTID, ya no es necesario especificar la posición del registro binario, y solo se aplican automáticamente al esclavo las transacciones que no se han obtenido del maestro, lo que simplifica considerablemente la gestión.

Ventajas de GTID

  • Identificación única: Cada transacción recibe un GTID único, lo que permite saber claramente qué transacciones ya se han aplicado.
  • Recuperación fácil: Al usar GTID, incluso si el maestro o el esclavo se reinician, solo se reaplican las transacciones que no se habían aplicado.
  • Eficiencia en la gestión operativa: Incluso en entornos a gran escala con varios servidores esclavos, es posible gestionar fácilmente manteniendo la consistencia de las transacciones.
Para usar GTID, es obligatorio configurar gtid_mode=ON y enforce_gtid_consistency=ON. Al aplicar estas configuraciones en el maestro y el esclavo, se puede habilitar la replicación basada en GTID. En la siguiente sección se explicarán los pasos concretos para configurar la replicación MySQL.

3. Procedimiento de configuración de replicación MySQL

En esta sección se explican detalladamente los pasos para configurar la replicación MySQL. Siguiendo los pasos a continuación, podrá crear la configuración básica de maestro y esclavo y lograr la sincronización de datos en tiempo real.

Configuración del servidor maestro

Primero, edite el archivo de configuración del servidor maestro (normalmente my.cnf o my.ini) para habilitar el registro binario y establecer el ID del servidor.
  1. Edición del archivo de configuración
  • Agregue la siguiente configuración a la sección [mysqld] y establezca el ID del servidor con un valor único (por ejemplo, 1).
   [mysqld]
   server-id=1
   log-bin=mysql-bin
  • server-id debe especificarse con un número único diferente para cada servidor, y log-bin indica la habilitación del registro binario.
  1. Creación del usuario de replicación
  • Cree un usuario exclusivo para replicación en el servidor maestro y concédale los permisos necesarios.
   CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
   FLUSH PRIVILEGES;
  • Este usuario es necesario para que el servidor esclavo acceda a los datos del maestro.
  1. Verificación del estado del maestro
  • Verifique el archivo de registro binario actual y la posición (ubicación del registro). Esta información será necesaria para la configuración del servidor esclavo.
   SHOW MASTER STATUS;
  • Los valores File (nombre del archivo de registro) y Position (posición) mostrados por este comando se utilizan en la configuración del lado esclavo.

Configuración del servidor esclavo

A continuación, edite el archivo de configuración del servidor esclavo y establezca el ID del servidor y la información del maestro.
  1. Edición del archivo de configuración
  • El servidor esclavo también debe establecer un server-id único (por ejemplo, 2). El ID del servidor debe ser diferente al del servidor maestro.
   [mysqld]
   server-id=2
  • Para evitar escrituras de datos en el servidor esclavo, también es común establecer read_only=ON.
  1. Configuración de la información del maestro en el esclavo
  • Ejecute los siguientes comandos en el servidor esclavo para especificar el nombre de host del maestro, el usuario, el nombre del archivo de registro binario y la posición.
   CHANGE MASTER TO
       MASTER_HOST='master_host',
       MASTER_USER='repl',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=123;
  • En MASTER_LOG_FILE y MASTER_LOG_POS ingrese los valores que verificó previamente en el maestro.
  1. Inicio de la replicación
  • Ejecute el siguiente comando en el servidor esclavo para iniciar la replicación.
   START SLAVE;

Verificación del estado de la replicación

Verifique que la replicación esté configurada correctamente entre el maestro y el esclavo.
  • Verificación del estado del maestro
  SHOW MASTER STATUS;
  • Verificación del estado del esclavo
  SHOW SLAVE STATUSG;
  • Si Slave_IO_Running y Slave_SQL_Running aparecen como Yes, la replicación está funcionando correctamente.
En la siguiente sección se explicarán métodos avanzados de configuración de replicación MySQL. Se abordarán las diferencias entre replicación asíncrona y semisíncrona, y los pasos de configuración que utilizan GTID.

4. Tipos y aplicaciones de replicación

MySQL replication tiene dos tipos según el método de sincronización de datos: replicación asíncrona y replicación semisíncrona. Comprender sus características y los criterios de selección según los escenarios de uso permite mejorar el rendimiento y la fiabilidad del sistema. Además, aquí se explican también las ventajas de la configuración de replicación que utiliza GTID (Global Transaction Identifier).

Diferencias entre replicación asíncrona y replicación semisíncrona

1. Replicación asíncrona

La replicación asíncrona devuelve la respuesta al cliente en el momento en que el servidor maestro completa la transacción. Es decir, mientras la sincronización de datos con el servidor esclavo está retrasada, el maestro puede seguir procesando nuevas solicitudes. Por ello, ofrece un alto rendimiento de respuesta y es adecuada para sistemas con objetivo de balanceo de carga. Sin embargo, en caso de falla, es necesario tener en cuenta que los datos que no se hayan replicado al esclavo pueden perderse.

2. Replicación semisíncrona

La replicación semisíncrona devuelve la respuesta al cliente después de que el servidor maestro confirma que la transferencia de datos al servidor esclavo se ha completado. Esto mejora la integridad de los datos, pero al esperar la confirmación del esclavo, el tiempo de respuesta de la transacción puede alargarse. La replicación semisíncrona es adecuada para sistemas que requieren alta integridad de datos o entornos donde la fiabilidad de los datos es la máxima prioridad.

Replicación con GTID

GTID (Global Transaction Identifier) asigna un ID único a cada transacción, manteniendo la integridad de las transacciones tanto en el maestro como en el esclavo. Al habilitar GTID, la gestión de la replicación se simplifica en comparación con la replicación basada en la posición del registro binario tradicional.

Ventajas de GTID

  • Mejora de la integridad de datos: Con GTID, el esclavo puede reconocer automáticamente las transacciones que no se han aplicado, lo que facilita mantener la integridad de los datos.
  • Simplificación de la gestión de replicación: Al usar GTID, el cambio y la recuperación del maestro o del esclavo se pueden realizar de manera eficiente. Al eliminar la necesidad de especificar la posición del registro binario, la administración se vuelve más sencilla.

Configuración de replicación GTID

Para aprovechar GTID, es necesario añadir las siguientes opciones a los archivos de configuración del maestro y del esclavo y habilitarlas. Configuración del servidor maestro
[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON
Configuración del servidor esclavo
[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
read_only=ON
En un entorno con GTID habilitado, basta con usar el comando CHANGE MASTER TO en el esclavo para establecer la información del maestro, y la replicación mediante GTID se ejecutará automáticamente. En la siguiente sección se explicarán los métodos de mantenimiento de la replicación MySQL y los puntos clave de monitoreo en la gestión operativa.

5. Mantenimiento y monitoreo de la replicación

Para operar la replicación de MySQL de manera adecuada, es indispensable realizar mantenimiento y monitoreo periódicos. En esta sección se explican los comandos para verificar que la replicación funciona correctamente y los métodos para abordar errores comunes.

Métodos para verificar el estado de la replicación

Para comprender el estado de la replicación, use los siguientes comandos para comprobar la sincronización entre el maestro y el esclavo.

Verificación del estado del maestro

El estado de la replicación en el servidor maestro se puede verificar con el comando SHOW MASTER STATUS. Este comando muestra el nombre actual del archivo de registro binario y la posición, lo que permite confirmar las últimas actualizaciones que deben enviarse al esclavo.
SHOW MASTER STATUS;
La salida de este comando incluye los siguientes campos:
  • File: nombre del archivo de registro binario actual que genera el maestro
  • Position: posición actual dentro del registro binario
  • Binlog_Do_DB y Binlog_Ignore_DB: bases de datos incluidas o excluidas de la replicación

Verificación del estado del esclavo

El estado de la replicación en el servidor esclavo se puede verificar con el comando SHOW SLAVE STATUS. Los resultados de este comando contienen información para determinar si el servidor esclavo está funcionando correctamente.
SHOW SLAVE STATUSG;
Los campos importantes incluyen:
  • Slave_IO_Running y Slave_SQL_Running: si ambos son Yes, indican que el esclavo está funcionando correctamente.
  • Seconds_Behind_Master: muestra cuántos segundos lleva el esclavo retrasado respecto al maestro. Normalmente, lo ideal es que este valor sea 0.

Solución de problemas de la replicación

Los problemas que suelen surgir durante la operación de la replicación incluyen errores de conexión y desincronizaciones de datos. A continuación se presentan mensajes de error comunes y sus soluciones.

1. Error de conexión

Si Slave_IO_Running muestra No, significa que el esclavo no puede conectarse al maestro. Pruebe las siguientes soluciones.
  • Verificar el nombre de host o la dirección IP del servidor maestro: asegúrese de que la dirección del maestro sea correcta.
  • Revisar la configuración del firewall: confirme que el puerto necesario (normalmente 3306) esté abierto.

. Desincronización de datosh4> Si el campo Last_Error contiene información del error, es posible que exista una desincronización de entre el maestro y el esclavo. Cuando ocurre, es necesario detener el esclavo y corregir el problema.

3. Resolución de la latenciah4>
Las causas de la latencia del esclavo pueden ser el rendimiento del hardware del esclavo o problemas de. En caso necesario, reforzar la configuración del esclavo puede mejorar la situación.
 En la siguiente sección se profundizará en los detalles de los problemas deación y sus soluciones.
  
6. Problemas comunes y sus soluciones
 En la replicación de MySQL pueden ocurrir varios problemas durante la operación. Aquí se explican en detalle los problemas comunes y sus métodos de solución. Detectar los problemas temprano y abordarlos adecuadamente permite mantener el funcionamiento estable del sistema.
1. Cuando Slave_IO_Running está detenido
 Síntoma：SHOW SLAVE STATUS en la salida del comando, Slave_IO_Running está en No, indica que el esclavo no puede conectarse al maestro. Causas y soluciones：
  • Problemas de red：Si hay problemas con la conexión de red, el esclavo no podrá acceder al maestro. Verifique la configuración del firewall y compruebe que el maestro sea accesible.
  • Error en el nombre de host o dirección IP del maestro：CHANGE MASTER TO verifique que el nombre de host o la dirección IP especificados no estén incorrectos.
  • Problemas de permisos de usuario：Si el usuario de replicación configurado en el maestro no tiene los permisos suficientes, la conexión también fallará. GRANT REPLICATION SLAVE verifique que se hayan otorgado los permisos correctos.
2. Inconsistencias de datos en el esclavo
 Síntoma：Si los datos del esclavo y del maestro no coinciden, los datos del esclavo pueden quedar en un estado inconsistente. Causas y soluciones：
  • Corrección manual de datos：Si ocurre una inconsistencia, detenga el esclavo y corrija manualmente la transacción problemática. Después de la corrección, reinicie el esclavo para restaurar la replicación. STOP SLAVE; # Corrija los datos según sea necesario START SLAVE;
  • Resincronización de datos：Si hay inconsistencias a gran escala, obtenga una copia de seguridad completa de los datos del maestro y vuelva a sincronizar el esclavo.
3. Retraso en la replicación
 Síntoma：en la salida de SHOW SLAVE STATUS Seconds_Behind_Master si no es 0, indica que el esclavo está retrasado respecto al maestro. Normalmente, cuanto menor sea este valor, mejor. Causas y soluciones：
  • Rendimiento del hardware del esclavo：Si las especificaciones del servidor esclavo son bajas, el procesamiento no puede seguir el ritmo y se produce retraso. Actualizar el hardware es eficaz.
  • Optimización de consultas：Si las consultas enviadas por el maestro tardan mucho en ejecutarse en el esclavo, se produce retraso. Añadir índices y optimizar las consultas ayuda a reducir el tiempo de procesamiento.
4. Error de permisos del usuario de replicación
 Síntoma：si se muestra un mensaje de error relacionado con permisos en Last_Error, es posible que el esclavo no tenga los permisos necesarios para conectarse al maestro. Causas y soluciones：
  • Concesión de permisos mediante reconfiguración：Verifique que exista un usuario con los permisos adecuados en el maestro y, si es necesario, vuelva a configurarlo. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'IP del esclavo'; FLUSH PRIVILEGES;
5. Crecimiento excesivo del binlog
 Síntoma：El registro binario del maestro puede crecer excesivamente, lo que ocupa espacio en el disco del servidor. Causas y soluciones：
  • Rotación del binlog：Eliminar o archivar periódicamente los binlogs ayuda a prevenir su crecimiento. expire_logs_days al configurarlo, los logs que superen el período especificado se eliminarán automáticamente. SET GLOBAL expire_logs_days = 7; # Eliminar logs mayores a 7 días
 De esta manera, al comprender los problemas comunes y sus soluciones en la replicación de MySQL, es posible una gestión operativa fluida. En la siguiente sección, como resumen del artículo, se revisarán los puntos clave de la operación de replicación.
  
7. Resumen
 MySQL la replicación es una función importante para mejorar la integridad de los datos y la fiabilidad del sistema. En este artículo, se explicó en detalle desde los conceptos básicos de la replicación de MySQL, los pasos de configuración, hasta la monitorización y solución de problemas en la gestión operativa. Finalmente, se resumen a continuación los puntos clave para la gestión operativa de la replicación.
Revisión de los puntos clave
  1. Tipos de replicación y selección
  • La replicación asíncrona sobresale en velocidad de respuesta y es ideal para balanceo de carga, pero si se requiere mayor fiabilidad, la replicación semisíncrona es la adecuada. Seleccione el método apropiado según los requisitos del sistema.
  1. Uso eficaz de GTID
  • Al aprovechar GTID, no es necesario especificar la posición del binlog, lo que permite una gestión de transacciones fluida. Es especialmente útil en entornos con múltiples esclavos o en sistemas donde la recuperación ante fallos es crucial.
  1. Verificación periódica del estado
  • Es importante monitorizar periódicamente el estado de los servidores maestro y esclavo usando los comandos SHOW MASTER STATUS y SHOW SLAVE STATUS. Si se detecta alguna anomalía, una respuesta rápida permite minimizar el riesgo de inconsistencias de datos y retrasos.
  1. Adquisición de habilidades de solución de problemas comunes
  • Los errores de conexión del esclavo, inconsistencias de datos y retrasos son problemas típicos de la replicación de MySQL. Comprender los métodos básicos de solución para cada uno de estos problemas facilita una respuesta fluida durante la operación.
  1. Gestión del binlog
  • Cuando el binlog crece, ocupa espacio en disco del servidor; por ello se recomienda usar la configuración <codeexpire_logs_days para programar su eliminación automática y realizar mantenimiento periódico.
 La replicación de MySQL no termina una vez configurada; la monitorización diaria y el mantenimiento adecuado son imprescindibles. Al verificar el estado periódicamente y ajustar la configuración según sea necesario, se puede construir y mantener un sistema de bases de datos altamente fiable.  Esperamos que este artículo sea útil para comprender e implementar la replicación de MySQL. Le deseamos una operación de replicación fluida en el futuro.