Cómo configurar una replicación de base de datos MySQL en Ubuntu 18.04

Introducción

La replicación de MySQL es un proceso que copia automáticamente datos de un servidor de base de datos a uno o más servidores.

MySQL admite una variedad de topologías de replicación, siendo la topología maestro /esclavo una de las topologías más conocidas en las que un servidor de base de datos actúa como maestro, mientras que uno o más servidores actúan como esclavos. De forma predeterminada, la replicación es asincrónica donde el maestro envía eventos que describen los cambios a la base de datos en su registro binario y los esclavos solicitan eventos cuando están listos.

Este tutorial cubre un ejemplo básico de replicación maestro /esclavo de MySQL con un servidor maestro y un servidor esclavo en Ubuntu 18.04. Lo mismo ocurre con MariaDB.

Este tipo de topología de replicación es más adecuado para implementar réplicas de lectura para escalado de lectura, respaldo de base de datos en vivo para recuperación de desastres y trabajos de escaneo.

Prerrequisitos

Este ejemplo supone que tiene dos servidores que ejecutan Ubuntu 18.04, que pueden comunicarse entre sí a través de una red privada. Si su proveedor de alojamiento no ofrece direcciones IP privadas, puede usar direcciones IP públicas y configurar el firewall para permitir el tráfico en el puerto 3306 solo de fuentes confiables.

Los servidores de este ejemplo tienen las siguientes direcciones IP:

Master IP: 192.168.333.333

Slave IP: 192.168.444.444

Instalar MySQL

Los repositorios predeterminados de Ubuntu 18.04 incluyen MySQL versión 5.7. Para evitar problemas, es mejor instalar la misma versión de MySQL en ambos servidores.

Si desea establecer una contraseña para iniciar sesión en el servidor MySQL, luego cambie el método de autenticación, lea esta guía: Cómo instalar MySQL en Ubuntu 18.04 ; de lo contrario, siga leyendo para instalar MySQL con las configuraciones predeterminadas.

Instale MySQL en el servidor maestro (el servidor donde se encuentra la primera base de datos):

sudo apt-get update

sudo apt-get install mysql-server

Instale MySQL en el servidor esclavo (el servidor donde se encuentra la segunda base de datos) usando los mismos comandos:

sudo apt-get update

sudo apt-get install mysql-server

Configurar el servidor maestro

El primer paso es configurar el servidor MySQL principal. Realizaremos los siguientes cambios:

• Configure el servidor MySQL para escuchar en la IP privada.
• Establezca una ID de servidor única.
• Habilitar el registro binario

Para hacer esto, abra el archivo de configuración y descomente MySQL o configure lo siguiente:

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

Busque la línea bind-address = 127.0.0.1 y cámbiela ingresando la dirección IP del servidor maestro. También agregue las log_bin server-id y log_bin. En nuestro ejemplo:

bind-address = 192.168.333.333
 server-id = 1
 log_bin = /var/log/mysql/mysql-bin.log

Guarde y cierre el archivo.

Cuando termine, reinicie el servicio MySQL para que los cambios surtan efecto:

sudo systemctl restart mysql

El siguiente paso es crear un nuevo usuario de replicación. Inicie sesión en el servidor MySQL como root escribiendo:

sudo mysql

O, si ha establecido una contraseña para el usuario root, use el siguiente comando para iniciar sesión en el servidor MySQL:

sudo mysql -u root -p

Desde el indicador de MySQL, ejecute las siguientes consultas SQL que crearán el replica y otorgarán el REPLICATION SLAVE al usuario:

CREATE USER 'replica'@'192.168.444.444' IDENTIFIED BY 'replica_password';

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.444.444';

Asegúrese de cambiar la IP a su dirección IP esclava. También puede nombrar al usuario como desee.

Mientras aún está dentro del indicador de MySQL, ejecute el siguiente comando que imprimirá el nombre y la ubicación del archivo binario.

SHOW MASTER STATUS\G

*************************** 1. row ***************************
 File: mysql-bin.000002
 Position: 625
 Binlog_Do_DB:
 Binlog_Ignore_DB:
 Executed_Gtid_Set:
 1 row in set (0.00 sec)

Anote el nombre del archivo, "mysql-bin.000002" y la ubicación "625". Estos valores serán necesarios al configurar el servidor esclavo. Es probable que estos valores sean diferentes en su servidor.

Configurar el servidor esclavo

En cuanto al servidor maestro anterior, realizaremos los siguientes cambios en el servidor esclavo:

• Configurar el servidor MySQL para escuchar en IP privada
• Establecer una ID de servidor única
• Habilitar el registro binario

Abra el archivo de configuración de MySQL y edite las siguientes líneas:

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

Busque la línea bind-address = 127.0.0.1 y cámbiela ingresando la dirección IP del servidor esclavo. También agregue las log_bin server-id y log_bin. En nuestro ejemplo:

bind-address = 192.168.444.444
 server-id = 2
 log_bin = /var/log/mysql/mysql-bin.log

Guarde y cierre el archivo.

Reinicie el servicio MySQL:

sudo systemctl restart mysql

El siguiente paso es configurar los parámetros que utilizará el servidor esclavo para conectarse al servidor maestro. Inicie sesión en el shell de MySQL:

sudo mysql

O, si ha establecido una contraseña para el usuario root, use el siguiente comando para iniciar sesión en el servidor MySQL:

sudo mysql -u root -p

Primero, detenga los hilos esclavos:

STOP SLAVE;

Ejecute la siguiente consulta que configurará el esclavo para replicar al maestro:

CHANGE MASTER TO
 MASTER_HOST='192.168.333.333',
 MASTER_USER='replica',
 MASTER_PASSWORD='replica_password',
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=629;

Asegúrese de utilizar la dirección IP, el nombre de usuario y la contraseña correctos. El nombre y la ubicación del archivo de registro deben coincidir con los valores obtenidos del servidor maestro.

Cuando termine, inicie los subprocesos esclavos.

START SLAVE;

Prueba la configuración

En este punto, debe tener una configuración de replicación maestro /esclavo que funcione.

Para verificar que todo funcione como se esperaba, crearemos una nueva base de datos en el servidor principal:

sudo mysql

CREATE DATABASE replicatest;

Inicie sesión en el shell MySQL esclavo:

sudo mysql

Ejecute el siguiente comando para enumerar todas las bases de datos:

SHOW DATABASES;

Notará que la base de datos que creó en el servidor principal se replica en el esclavo:

+--------------------+
 | Database |
 +--------------------+
 | information_schema |
 | mysql |
 | performance_schema |
 | replicatest |
 | sys |
 +--------------------+
 5 rows in set (0.00 sec)

Conclusión

En este tutorial, le mostramos cómo crear una réplica de MySQL Master /Slave.