Sincronización de base de datos MySQL

1. Sincronización de base de datos MySQL
2. Configurar el cortafuegos de la máquina de origen
3. Configurar base de datos de origen
4. Crear usuario de replicación
5. Configurar servidor esclavo

Este artículo tiene como objetivo demostrar cómo lograr la sincronización de la base de datos con fines de copia de seguridad y mantenimiento de registros.

Sincronización de base de datos MySQL

Al manejar grandes cantidades de datos, puede ser crucial considerar escenarios en los que los datos pueden verse comprometidos.

La razón por la que se comprometió puede depender de muchos factores que deben considerarse al diseñar o planificar diferentes estrategias para abordar este problema en particular.

Optar por implementar medidas de búsqueda es sumamente importante; dependiendo del valor de los datos, puede costarle a una empresa o establecimiento una gran cantidad de pérdidas en caso de que los datos se pierdan por alguna falla técnica sin ninguna reserva de respaldo ya que los datos se pierden permanentemente.

La solución

La solución a este problema puede variar en función de diferentes condiciones y restricciones. Con todo, uno de los enfoques más comunes para la base de datos se encuentra a continuación.

1. Modelo cliente/servidor: en lugar de utilizar bases de datos, utilice aplicaciones que se comuniquen directamente con el servidor para acceder/almacenar datos.
2. Modelo maestro/esclavo: un servidor se asigna a la escritura de datos (maestro) y el otro (puede haber más de uno) se dedica exclusivamente a leer los datos (esclavo).
3. Modelo fuera de línea: después de un período de tiempo fijo, haga lo requerido localmente y envíelo al servidor.

En este artículo, hablaremos sobre la implementación del modelo maestro/esclavo.

requisitos previos

1. Dos máquinas actúan como servidores que ejecutan Ubuntu (20.0+). Cada máquina debe tener un usuario que no sea administrador y permisos sudo configurados correctamente. También requiere que los firewalls estén configurados con UFW.
2. MySQL está instalado en ambas máquinas. Preferiblemente la última versión (8.0+).

Configurar el cortafuegos de la máquina de origen

Con el cortafuegos configurado con UFW, el cortafuegos bloqueará cualquier conexión entrante desde el servidor de réplica. Para solucionar esto, ejecute los siguientes comandos en la máquina de origen.

sudo ufw allow REPLICA_SERVER_IP_ADDR to any port MySQL_PORT

Que, si se ejecuta con éxito, mostrará el resultado:

Rule Added

Tenga en cuenta que en el siguiente comando:

1. REPLICA_SERVER_IP_ADDR - Dirección IP del servidor réplica.
2. MySQL_PORT - Puerto del servidor MySQL (el valor predeterminado es 3306).

En el comando anterior, estamos agregando una regla al UFW que le indica que permita cualquier conexión entrante proveniente de la IP REPLICA_SERVER_IP_ADDR al puerto MySQL_PORT.

Configurar base de datos de origen

Algunas de sus configuraciones deben modificarse para que la base de datos de origen continúe con la replicación de datos.

Para hacer eso, necesitamos acceder al archivo de configuración del servidor MySQL llamado mysqld.cnf. En Ubuntu 20+, se puede encontrar en el directorio /etc/mysql/mysql.conf.d/.

Abra este archivo con su editor de texto preferido. Alternativamente, use el siguiente comando:

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

En el archivo mysql.cnf, busque la siguiente línea:

. . .
bind-address            = 127.0.0.1
. . .

La propiedad bind-address dicta dónde la base de datos aceptará conexiones de 127.0.0.1 que representa localhost; esto significa que el servidor solo aceptará solicitudes de conexión de la máquina donde está instalado el servidor actualmente.

Para aceptar conexiones desde el servidor de réplica, debemos cambiar la dirección dada a la propiedad bind-address. Cambiarlo a la dirección IP del servidor de origen hará el truco.

Después de cambiar la dirección IP, su archivo debería verse así:

. . .
bind-address            = SRC_SERVER_IP_ADDR
. . .

El SRC_SERVER_IP_ADDR es la dirección IP de la máquina de origen.

A continuación, debemos asignar al maestro una identificación única. Cada servidor será parte de un entorno replicado, por lo que a cada servidor se le debe asignar una identificación única para garantizar que no haya conflictos entre los servidores.

Para hacerlo, busque server-id en el archivo mysql.cnf. El resultado se verá de la siguiente manera:

. . .
# server-id             = 1
. . .

El signo # indica que esta línea está actualmente comentada; elimine el signo y elija cualquier valor entero para asignar al servidor. Después de hacer los cambios, la línea se verá de la siguiente manera:

. . .
server-id             = 10
. . .

Después de lo cual, busque la directiva log_bin y elimínela. El log_bin es necesario para que el servidor de réplica lea para comprender cómo se supone que debe replicar la base de datos.

Después de hacer los cambios, la línea debería verse de la siguiente manera:

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

Por último, busca la directiva binlog_do_db; esta línea también estará comentada, descoméntala y establece su valor como el nombre de la base de datos que deseas replicar. Después de la modificación, se verá así:

. . .
binlog_do_db          = DB_NAME

# For multiple DBs
binlog_do_db          = DB_NAME_1
binlog_do_db          = DB_NAME_2
. . .

Alternativamente, puede excluir una determinada base de datos y replicar el resto usando binlog_ignore_db.

. . .
binlog_ignore_db          = DB_NAME_TO_IGNORE
. . .

Después de realizar estos cambios en mysqld.cnf, guarde el archivo y reinicie el servicio mysql.

Puede hacerlo usando el siguiente comando.

sudo systemctl restart mysql

Crear usuario de replicación

El servidor de réplica debe proporcionar un nombre de usuario y una contraseña para conectarse con el servidor de origen. Los servidores de réplica pueden conectarse usando cualquier perfil de usuario disponible en la máquina de origen, pero en este tutorial crearemos un usuario dedicado para la replicación.

Siga los pasos para crear un usuario con fines de replicación.

En su máquina de origen, ejecute el siguiente comando:

sudo mysql -u USER -p PASS

Dónde:

1. USUARIO - Nombre de usuario del usuario
2. PASS - Contraseña del usuario

Dentro del indicador de MySQL, puede crear un nuevo usuario con el siguiente comando:

mysql> CREATE USER 'REPLICA_USER'@'REPLICA_IP' IDENTIFIED WITH mysql_native_password BY 'PASS';

Dónde:

1. REPLICA_USER: el nombre de usuario del usuario del servidor de réplica
2. REPLICA_IP - Dirección IP de la máquina réplica
3. PASS - Contraseña del usuario

Después de que el usuario se haya creado con éxito, debemos otorgarle los permisos apropiados. El usuario debe tener como mínimo el ESCLAVO DE REPLICACIÓN.

Otorgue la aplicación de usuario usando el siguiente comando:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'REPLICA_USER'@'REPLICA_IP';

Luego, debemos vaciar los privilegios para limpiar la memoria y los cachés usados ​​usando los comandos GRANT y CREATE USER.

mysql> FLUSH PRIVILEGES;

Y finalmente, debemos verificar el estado de nuestra base de datos y usar el bloqueo de lectura para evitar que ocurran operaciones de escritura mientras la captura del estado del registro está en progreso.

Para hacerlo, ejecute los siguientes comandos:

mysql> FLUSH TABLES WITH READ LOCK;

Luego verifique el estado del servidor usando el siguiente comando:

mysql> SHOW MASTER STATUS;

Lo que devolverá la salida en el siguiente formato:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      899 | db           |                  |                   |
| mysql-bin.000002 |      900 | db2          |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
2 rows in set (0.01 sec)

Después de esto, puedes replicar toda la base de datos usando mysqldump:

sudo mysqldump -u root db > db.sql

Se generará un archivo llamado new_db.sql, que puede transferir a la máquina esclava para completar el proceso de copia de seguridad.

A medida que se realiza la copia de seguridad, puede desbloquear las tablas.

mysql> UNLOCK TABLES

Configurar servidor esclavo

En el servidor esclavo, debe crear una nueva base de datos donde se replicará la base de datos. Puedes hacerlo de la siguiente manera:

mysql> CREATE DATABASE DATABASE_NAME

Ahora importe el sqldump exportado desde la máquina de origen.

sudo mysql db < /PATH/db.sql

Una vez importada la base de datos a la máquina esclava, configure las directivas en mysqld.cnf de la siguiente manera:

server-id = 11
log_bin = /data/mysql/mysql-bin.log
binlog_do_db = DATABASE_NAME

Después de realizar los cambios necesarios, reinicie el servicio MySQL.

sudo systemctl restart mysql

Finalmente, para comenzar a replicar datos desde el servidor, ejecute los siguientes comandos:

En las indicaciones de MySQL después de iniciar sesión, haga lo siguiente:

mysql> CHANGE REPLICATION SOURCE TO
mysql> SOURCE_HOST='SRC_SERVER_IP_ADDR',
mysql> SOURCE_USER='REP_USER',
mysql> SOURCE_PASSWORD='PASS',
mysql> SOURCE_LOG_FILE='mysql-bin.000001',
mysql> SOURCE_LOG_POS=899;

Y finalmente, inicie el servidor esclavo.

mysql > START REPLICA

Ahora el servidor comenzará a replicar los cambios del servidor de origen.

Para ver el estado del servidor de réplica, use el siguiente comando:

SHOW REPLICA STATUS\G

El \G reformatea la salida para una mejor legibilidad.

La salida del comando anterior tiene el siguiente formato: