Cómo configurar una replicación MySQL – Clouding.io

Introducción

Cuando trabajas con bases de datos, puede ser útil tener respaldos de los datos. Esto proporciona redundancia en caso de que uno de los servidores de bases de datos falle y mejorar la disponibilidad. La práctica de sincronizar los datos a través de múltiples bases de datos separadas se llama replicación.

MySQL es un sistema de gestión de bases de datos relacionales, y es la base de datos relacional de código abierto más popular del mundo actualmente. Viene incorporada con un número de características de replicación, permitiéndole mantener múltiples copias de la información.

Nota informativa:

Este tipo de replicación de bases de datos se ha denominado históricamente replicación "Maestro-Esclavo". Aunque el equipo de MySQL reconoció el origen negativo de esta terminología, así como también de los términos lista negra y blanca.

Fue anunciado en el blog oficial de MySQL, actualizado el programa de base de datos y su documentación para utilizar un lenguaje más inclusivo. Sin embargo, todavía puede aparecer esta terminología y sigue siendo utilizable por compatibilidad.

Durante este artículo se utilizará la terminología "Origen-Replica" siempre que sea posible.

Este artículo explicará cómo configurar una instancia de MySQL en un servidor como base de datos de origen/fuente y luego configurar una instancia en otro servidor para que funcione como su réplica.

Requisitos

Este artículo se utilizará dos servidores Ubuntu 22.04 LTS para la explicación, pero puede utilizarse todas las versiones superiores a Ubuntu 16.04 LTS o cualquier distribución Debian con el administrador de demonios systemd.

También puede usarse para distribuciones basadas en RHEL, modificándolo la instalación de MySQL con el gestor de paquetes correspondiente y que incorpore systemd. En el siguiente artículo tienes la lista de todas las distribuciones Linux disponibles en Clouding: Imágenes Linux.

Creación de escenario para servidor Origen

Primero se explicará la creación de un escenario de un servidor Origen (Master) para el artículo, si ya tienes instalado MySQL con la base de datos a replicar salta a la sección Configuración de servidor fuente.

Primero ejecuta lo siguiente en servidor Fuente para la instalación de MySQL:

# apt install mysql-server -y

Una vez instalado, deberás crear las base de datos. Ejecutando lo siguiente crearás dos bases de datos, se creará dos para explicar la ignoración de una base de datos en replicación:

# mysql -u root -e "CREATE DATABASE base_de_datos_replicacion; CREATE DATABASE base_de_datos_no_replica"

Posteriormente, crea una de tabla y añade información de ejemplo:

# mysql -u root -e "USE base_de_datos_replicacion; CREATE TABLE tabla (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, usuario VARCHAR(20) UNIQUE); INSERT INTO tabla (id, usuario) VALUES ('01', 'clouding')"

Para mostrar contenido de la tabla creada, puedes ejecutar un SELECT:

# mysql -u root -e "USE base_de_datos_replicacion; SELECT * FROM tabla"

Deberías obtener el siguiente resultado en la consulta:

  +----+----------+
  | id | usuario  |
  +----+----------+
  | 1 | clouding  |
  +----+----------+
  1 row in set (0.00 sec)

Configuración de servidor Origen

Primero crea un usuario en el servidor Origen para la replicación, puedes modificar % por la dirección IP del servidor o rango de servidores replica (recuerda modificar la contraseña de ejemplo):

# mysql -u root -e "CREATE USER 'usuario_replicacion'@'%' IDENTIFIED WITH mysql_native_password BY 'contraseña'"

Posteriormente, otorgar privilegios de replicación al usuario creado en el servidor Origen:

# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO 'usuario_replicacion'@'%'"

Tienes más información sobre la creación de usuarios en el siguiente artículoCómo administrar usuarios y permisos en MySQL.

Después edita el archivo de configuración de MySQL del servidor Origen:

# nano /etc/mysql/mysql.conf.d/mysqld.cnf

Deberás editar y descomentar las siguientes líneas de la configuración:

  bind-address = 10.20.10.0
  mysqlx-bind-address = 10.20.10.0
  server-id = 1
  log_bin = /var/log/mysql/mysql-bin.log
  binlog_do_db = base_de_datos_replicacion # base de datos para replicación
  binlog_ignore_db = base_de_datos_no_replica # base de datos que ignorar

Se ha indicado el rango de la red interna por defecto del DHCP, pero deberás cambiarlo en caso de utilizar otro red para la replicación y también introducir los nombres de las bases de datos para la replicación. Así como también las bases de datos que desees ignorar en la replicación y no proporcionarlas al servidor replica (Precaución: añadir una base de datos por opción).

Una vez modificado en el archivo, reinicia MySQL para aplicar los cambios:

# systemctl restart mysql

Ahora deberás bloquear tablas para realizar un exportación de las bases de datos:

# mysql -u root -e "FLUSH TABLES WITH READ LOCK"

También consulta la posición comprobando el estado de Origen (recuerda anotar posición):

# mysql -u root -e "SHOW MASTER STATUS"

Deberías obtener un resultado similar al siguiente:

  +------------------+----------+----------------------------+---------------------------+-------------------+
  | File             | Position | Binlog_Do_DB               | Binlog_Ignore_DB          | Executed_Gtid_Set |
  +------------------+----------+----------------------------+---------------------------+-------------------+
  | mysql-bin.000001 | 157      | base_de_datos_replicacion  | base_de_datos_no_replica  |                   |
  +------------------+----------+----------------------------+---------------------------+-------------------+
  1 row in set (0.00 sec)

Una vez bloqueadas las tablas y anotada la posición, haz una exportación de las base de datos en el servidor Origen:

# mysqldump -u root base_de_datos_replicacion > dump.sql

Y transfiere el dump al servidor Replica, ejecutando lo siguiente puedes copiarla con el protocolo SSH desde el servidor Origen:

# scp dump.sql root@IP_servidor_replica:/tmp/

Una vez creado el dump, puedes desbloquear tablas en el servidor Origen:

# mysql -u root -e "UNLOCK TABLES"

Configuración de servidor Replica

Ahora deberás acceder al servidor Replica, todos los comandos de esta sección debes ejecutarlos en él. Primero deberás instalar MySQL en el servidor Replica para posteriormente configurarlo:

# apt install mysql-server -y

También crear las base de datos para la replicación, en este caso se utiliza la creada en la sección Creación de escenario en servidor Fuente:

# mysql -u root -e "CREATE DATABASE base_de_datos_replicacion"

Una vez creada las bases de datos, deberás importar las copias del servidor Origen. Ejecutando lo siguiente en el servidor Replica podrás importar la copia a la base de datos:

# mysql base_de_datos_replicacion < /tmp/dump.sql

Posteriormente, edita configuración de MySQL del servidor Replica:

# nano /etc/mysql/mysql.conf.d/mysqld.cnf

Deberás descomentar, modificar o introducir las siguientes líneas (Precaución: añadir una base de datos por opción):

  server-id = 2
  log_bin = /var/log/mysql/mysql-bin.log
  binlog_do_db = base_de_datos_replicacion
  relay-log = /var/log/mysql/mysql-relay-bin.log

Recuerda modificar el número de ID de servidor si es para una tercera replica o superior.

Cuando hayas guardado los cambios en el archivo de configuración, reinicia MySQL de la Replica para aplicar cambios:

# systemctl restart mysql

Ahora tendrás que configurar la replicación, ejecuta el siguiente comando en el servidor Replica y deberás modificarlo para indicar la dirección IP del servidor Origen, el usuario creado anteriormente para la replicación, comprobar archivo origen y modificar posición anotada durante la Configuración de servidor Replica:

# mysql -u root -e "CHANGE REPLICATION SOURCE TO SOURCE_HOST='master',SOURCE_USER='usuario_replicacion',SOURCE_PASSWORD='contraseña',SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=157"

En versiones anteriores de MySQL a la 8.0, cambia "CHANGE REPLICATION SOURCE TO" por el uso del comando "CHANGE MASTER TO".

Por último, arranca replicación ejecutando lo siguiente de nuevo el servidor Replica ("SLAVE" en versiones anteriores):

# mysql -u root -e "START REPLICA"

Puedes visualizar o mostrar el estado de la replicación ejecutando lo siguiente:

# mysql -u root -e "SHOW REPLICA STATUS\G" | grep State

Deberías obtener el siguiente resultado si tiene toda la información del servidor Origen:

   Replica_IO_State: Waiting for source to send event
 Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates

Comprobación de replicación

Para comprobar que la replicación funciona correctamente, puedes probar simplemente insertar información en el servidor Origen. Ejecutando lo siguiente añadirías información en la tabla creada de ejemplo en este artículo:

# mysql -u root -e "USE base_de_datos_replicacion; INSERT INTO tabla (id, usuario) VALUES ('02', 'usuario')"

Con el siguiente comando, ejecutado en el servidor Replica podrás consultar la tabla replicada:

# mysql -u root -e "USE base_de_datos_replicacion; SELECT * FROM tabla"

Con el escenario de ejemplo, deberías obtener el siguiente resultado en el servidor Replica al realizar el SELECT:

  +----+----------+
  | id | usuario  |
  +----+----------+
  | 1 | clouding  |
  | 2 | usuario   |
  +----+----------+
  1 row in set (0.00 sec)

Esperamos que este tutorial te haya sido de ayuda, si tienes consultas sobre esta u otra cuestión relacionada con tus servidores en Clouding, no dudes en contactar a soporte@clouding.io ¡Estamos para ayudarte!

Artículos en esta sección