Cómo configurar una base de datos esclava de MySQL

La replicación de MySQL permite que varias copias de la misma base de datos estén en varios servidores al mismo tiempo, copiando automáticamente los datos del servidor principal al servidor "esclavo".

Gracias a este mecanismo, todas las declaraciones ejecutadas en el maestro, que de alguna manera cambian los datos, pueden grabarse, transmitirse y ejecutarse en las bases de datos replicadas.

Esta guía lo ayudará a instalar MySQL en su servidor, configurar correctamente una réplica y verificar su funcionamiento con datos de prueba.

Primero, conéctese a su servidor a través de una conexión SSH. Si aún no lo ha hecho, se recomienda seguir nuestra guía para conectarse de forma segura con el protocolo SSH. En el caso de un servidor local, vaya al siguiente paso y abra la terminal de su servidor.

Instalación de MySQL

Para configurar la replicación Maestro/Esclavo en ambos servidores, instale una versión de MySQL, luego ejecute el siguiente procedimiento de instalación en ambas máquinas, antes de continuar con el siguiente capítulo.

Actualice los repositorios de distribución para asegurarse de descargar la versión más actualizada de MySQL:

$ sudo apt update

Si ya ha lanzado este comando antes, vaya al siguiente paso.

Ahora proceda con la instalación de MySQL:

$ sudo apt install mysql-server

Para asegurarse de que la instalación fue exitosa, verifique la versión de MySQL:

$ mysqld --version

Si el procedimiento se realizó con éxito, la versión de MySQL instalada se imprimirá en la pantalla.

Continúe con la protección de MySQL ejecutando un script incluido en MySQL, que aumentará la seguridad y limitará el acceso a sus bases de datos:

$ sudo mysql_secure_installation

En este punto, un procedimiento guiado lo guiará a través de la configuración del nivel de seguridad de MySQL.

Primero, se le preguntará si desea habilitar el sistema de validación de contraseña. Si está habilitado, al configurar la contraseña de un usuario, la contraseña se evalúa y si no cumple con los criterios de seguridad, se rechaza con un error.

Más tarde se le preguntará si desea cambiar la contraseña de root con una de su elección (si habilita el sistema de validación de contraseña, debe ingresar una contraseña que cumpla con los criterios de seguridad).

Se recomienda seguir varias prácticas recomendadas para crear una contraseña segura. Esto incluye:

• el uso de letras mayúsculas y minúsculas

• el uso de letras y números

• el uso de caracteres no alfanuméricos como @ # $% ˆ &!

• el uso de contraseñas que nunca antes se usaron.

Finalmente, elija si desea eliminar los usuarios anónimos, probar las bases de datos y si desea deshabilitar el inicio de sesión remoto con el usuario raíz. Para un nivel de seguridad adecuado, se recomienda confirmar todos estos cambios.

En este punto, confirme las actualizaciones de la tabla de permisos mostrada para aplicar todos los nuevos criterios de seguridad.

Finalmente reinicie el servicio para aplicar los cambios:

$ sudo service mysql restart

Configuración de la replicación Maestro/Esclavo

Después de instalar en ambos servidores (Maestro y Esclavo), proceda configurando primero el servidor Maestro.

Primero, edite el archivo de configuración en /etc/mysql/mysql.conf.d/mysqld.cnf de la siguiente manera:

server-id               = 1
	
	log_bin                 = /var/log/mysql/mysql-bin.log
	
	expire_logs_days        = 10
	
	max_binlog_size   = 100M

Dado que el servidor Esclavo necesariamente tendrá que conectarse de forma remota al servidor Maestro, debe asegurarse de que el servicio MySQL pueda aceptar conexiones externas, cambiando la dirección de enlace de la siguiente manera:

bind-address            = 0.0.0.0

Una vez completado, reinicie el servicio para aplicar los cambios:

$ sudo service mysql restart

Para propósitos de demostración y evaluación, cree una base de datos de prueba en el servidor maestro (aquí llamado "tutorial") con una tabla llamada "números" con datos ficticios. Después de completar el procedimiento de configuración, verifique si todos los datos ingresados ​​​​aquí están sincronizados con el servidor Esclavo:

$ sudo mysql -u root -p

mysql> CREATE DATABASE tutorial;
	Query OK, 1 row affected (0.00 sec)
	
	mysql> USE tutorial;
	Database changed
	
	mysql> CREATE TABLE numbers ( value REAL );
	Query OK, 0 rows affected (0.09 sec)
	
	mysql> INSERT INTO numbers VALUES ( 1 ), ( 2 ), ( 3 );
	Query OK, 3 rows affected (0.04 sec)
	
	Records: 3  Duplicates: 0  Warnings: 0

Así que asegúrese de que los valores que acaba de ingresar estén presentes en la tabla de prueba:

mysql> SELECT * FROM numbers;
		
	+-------+
	| value |
	+-------+
	|     1 |
	|     2 |
	|     3 |
	+-------+
	3 rows in set (0.00 sec)

La tabla de números debe contener 3 líneas de ejemplo como se muestra arriba.

Luego proceda creando datos de acceso para que el usuario sea utilizado por la base de datos Esclavo:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'usr_replica'@'%' IDENTIFIED BY 'SMDipmf#23$42';
	mysql> FLUSH PRIVILEGES;

Antes de continuar con la configuración del servidor Esclavo, se imprimirá en pantalla el estado actual del Maestro y se guardará la siguiente información:

mysql> SHOW MASTER STATUS;
	
	+------------------+----------+--------------+------------------+-------------------+
	
	| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
	
	+------------------+----------+--------------+------------------+-------------------+
	
	| mysql-bin.000001 |     1238 | |               | |
	
	+------------------+----------+--------------+------------------+-------------------+
	
	1 row in set (0.00 sec)

Necesitará estos datos cuando configure el servidor Esclavo.

Proceda a salir del shell de MySQL:

mysql> exit
	Bye

Luego guarde un volcado completo de la base de datos para cargarlo más tarde en el servidor Esclavo:

$ sudo mysqldump -u root -p --opt tutorial > slave-init.sql
	Enter password:

Transfiera el archivo recién creado a la máquina esclava y proceda con su configuración, modificando el archivo /etc/mysql/mysql.conf.d/mysqld.cnf:

server-id               = 2
	
	log_bin                 = /var/log/mysql/mysql-bin.log
	
	expire_logs_days        = 10
	
	max_binlog_size   = 100M

Tenga en cuenta que la identificación del servidor se ha cambiado usando el número 2 y también recuerde cambiar la dirección de enlace como se muestra aquí:

bind-address            = 0.0.0.0

Ahora reinicie el Esclavo para aplicar los cambios:

$ sudo service mysql restart

Ahora proceda creando una base de datos con el mismo nombre que el servidor Maestro:

$ sudo mysql -u root -p

	mysql> create database tutorial;
	
	Query OK, 1 row affected (0.00 sec)
	mysql> exit
	
	Bye

Ahora importe la copia de seguridad recién creada e inicie el Esclavo usando la información previamente guardada por la base de datos Maestra (MASTER_LOG_FILE y MASTER_LOG_POS):

$ sudo mysql -u root tutorial < /root/slave.sql
	$ sudo mysql -u root -p 
	mysql> CHANGE MASTER TO MASTER_HOST='195.231.4.201', MASTER_USER='usr_replica', MASTER_PASSWORD='SMDipmf#23$42', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 1238;
	
	Query OK, 0 rows affected, 2 warnings (0.10 sec)
	mysql> START SLAVE;
	
	Query OK, 0 rows affected (0.00 sec)

A partir de este momento, las bases de datos se sincronizan en tiempo real y puede salir del shell de MySQL.

Verificando el correcto funcionamiento de la replicación

Para verificar el funcionamiento de la replicación puede intentar insertar datos en el Maestro y verificar si llegan correctamente a la base de datos del Esclavo. Comience ingresando nuevos datos en el Maestro de la siguiente manera:

$ sudo mysql -u root -p

mysql> use tutorial;
	Reading table information for completion of table and column names 
	Turn off this feature to get a quicker startup with -A   
	Database changed 
	
	mysql> INSERT INTO numbers VALUES ( 4 ) , ( 5 );
	
	Query OK, 2 rows affected (0.03 sec)
	Records: 2 
	Duplicates: 0 
	Warnings: 0

Luego proceda consultando la base de datos Esclavo:

$ sudo mysql -u root -p

	mysql> use tutorial;
	
	Reading table information for completion of table and column names
	
	Turn off this feature to get a quicker startup with -A
	  changed
	
	mysql> SELECT * FROM numbers;
	
	+-------+
	| value |
	+-------+
	|     1 |
	|     2 |
	|     3 |
	|     4 |
	|     5 |
	+-------+
	
	5 rows in set (0.00 sec)

Como puede ver, los nuevos valores (4 y 5) han llegado a la base de datos Slave, por lo que la sincronización está funcionando correctamente.