Replicar bases de datos Mysql en servidores locales o remotos

Mysql permite replicar bases de datos, dentro de un mismo servidor mysql (en la misma máquina), o entre B.D. localizadas en diferentes servidores en red local o remota. Para poder conseguir replicar uno de los servidores (Servidor M) tiene que ser el maestro y el otro servidor el esclavo (Servidor E).

• Servidor M (Servidor principal, sería el maestro)
• Servidor E (Servidor secundario, sería el servidor esclavo).

Cuando indiquemos en el manual Mysql> es porque tenemos que introducir los comandos dentro de la consola de Mysql.

Para entrar en la consola introducir el comando : mysql -u root -p

Ejemplo del comando en un servidor linux :

# /usr/local/mysql/bin/mysql -u root -p (Puede ser diferente la ruta en tu PC)

En ese momento el servidor mysql pide el password de root , lo introducimos y se accede a la consola.

La replicación de Base de Datos en mysql tiene varias características a considerar y son:

• Podemos replicar Bases de Datos en el mismo servidor (Diferentes servicios MySql), en Servidores diferentes en LAN (Red Local) o WAN (Servidores Remotos).
• Se puede configurar (etc/my.cnf), la replicación parcial o total de las tablas de la Base de Datos a replicar del Servidor Maestro al Servidor Esclavo/s.
• La replicación es UNIDIRECCIONAL, los datos se copian de una base de datos a otra en un solo sentido, eso quiere decir que solo en una base de datos se deben actualizar los datos (sentencias INSERT, UPDATE, DELETE), que es la base de datos maestra, y la base de datos esclava nunca debe recibir sentencias de actualización de las tablas que se replican, solo consultas (SELECT).
• De las tablas de la Base de Datos que no se replican, entre el Servidor Maestro y el Esclavo, se pueden realizar las sentencias (INSERT, UPDATE y DELETE), en la base de datos del Servidor Esclavo.
• Podemos tener sendos servidores esclavos para cada maestro, pero no varios maestros para un esclavo.
• La replicación copia exactamente todos los cambios que se van haciendo desde que se activa el sistema de replicación, es decir, antes de replicar hay que hacer un backup definitivo de la base de datos principal a la esclava, para que las 2 bases de datos tengan exactamente la misma información.
• Cada servidor esclavo debe tener permiso para conectar con el maestro y solicitar las actualizaciones.
• El servidor esclavo necesita una cuenta en el servidor maestro para que pueda conectarse. En el servidor maestro, configure una cuenta como ésta :
• Mysql> GRANT REPLICATION SLAVE ON *.* TO ‘usuario_esclavo’@'host_esclavo’ IDENTIFIED BY ‘contraseña_esclavo’;
• El servidor maestro crea un hilo de proceso para manejar cada esclavo. En el lado del servidor esclavo se crean 2 hilos para manejar las tareas de réplica. El primer hilo es de Entrada/Salida recibe los eventos para procesar del servidor maestro y los escribe en los registros de reenvío del esclavo. El segundo hilo el SQL lee los eventos de los registros de reenvío y los ejecuta.
• Es aconsejable que las réplicas de las Bases de Datos MySql sean de la misma versión y si es posible de la 5.x y activos los mismos motores en las 2 B.D.

La actualización de la información de la Base de datos Mysql Master (total o parcial de sus tablas), automáticamente Mysql actualiza unos ficheros de datos “mysql-bin.XXXXXX”. Una vez actualizados estos ficheros se envía un evento al servidor con la base de datos Esclava y ésta se comunica con el Servidor Esclavo para recibir la porción del fichero de “mysql-bin.XXXXXX” que le falta; no todo el fichero sino la porción que le falta por tratar solamente (esto se sabe por posiciones tratadas dentro del fichero, nº linea).

---

CONFIGURACIÓN DEL SERVIDOR MAESTRO MYSQL

Debajo de la etiqueta [mysqld] del fichero /etc/my.cnf del maestro añadir las líneas de la B.D. (empresa):

• log-bin=mysql-bin #Nombre de los ficheros transmitidos entre servidores
• server-id = 1 #Id del servidor (Diferente nº que todos los esclavos)
• replicate-do-db = empresa # Base de datos a replicar
• replicate-ignore-table = empresa.tabla1 # Tabla1 a no replicar
• replicate-ignore-table = empresa.tabla2 # Tabla2 a no replicar
• binlog-do-db=empresa # Base de datos a crear logs únicamente

Y reiniciar luego el servicio MySql del Servidor Maestro. Con esto ya es suficiente.

---

CONFIGURACIÓN DEL SERVIDOR ESCLAVO MYSQL

1.- Definir variables globales para el servidor mysql

Debajo de la etiqueta [mysqld] del fichero /etc/my.cnf del esclavo añadir las líneas: de la B.D. (empresa)

• server-id = 2 #Id del servidor (Diferente nº del maestro y los esclavos)
• replicate-do-db = empresa # Base de datos a replicar
• replicate-ignore-table = empresa.tabla1 # tabla1 a ignorar
• replicate-ignore-table = empresa.tabla2 # tabla2 a ingnorar
• binlog-do-db=empresa # Base de datos a crear logs únicamente

2.- Definir variables de replicación en el Servidor Esclavo:

Mysql>CHANGE MASTER TO;

• MASTER_HOST=’192.168.5.130′ (ip master)
• MASTER_PASSWORD=’xxxx’
• MASTER_LOG_FILE = ‘obtenido en SHOW MASTER STATUS del master’
• MASTER_LOG_POS = ‘obtenido en SHOW MASTER STATUS del master’

Al realizar este comando se crea un fichero llamado master.info que luego se puede editar para cambiar estos valores a mano. También se crea el fichero relay-log.info que muestra información de cual es el último archivo absorbido del master, ultima posición leída de este archivo, y log de esta última transferencia. Ejemplo:

/usr/local/mysql/data/relay-log.info

• ./Servidor-relay-bin.000011 [ultimo LOG guardado en /usr/local/myql/data]
• 406 [ ultima posición del LOG en /usr/local/mysql/data]
• mysql-bin.000122 [ÚLTIMO FICHERO RECIBIDO DEL MASTER]
• 269 [ÚLTIMA POSICIÓN TRATADA DEL MASTER]

Y reiniciar luego el servicio MySql del Servidor Esclavo. Con esto ya es suficiente.

3.- Arrancar el servicio Esclavo

El mandato para arrancar el Servicio del Esclavo (E) es :

Mysql>START SLAVE ;

El mandato para parar el Servicio del Esclavo (E) es :

Mysql>STOP SLAVE;

Estos comandos sirven para parar o arrancar la replicación en el esclavo, si se para no se replica. Se puede arrancar cuando uno quiera, seguirá por la última posición de replicación que tiene guardada (la última ok), es decir, no se perderían datos aunque el esclavo haya estado 2 horas o más parado, solo que tardará un poco más en absorber toda la información no replicada.

DONDE VER LOS ERRORES DE MYSQL

En caso de error al arrancar el servidor o pararlo, o bien posibles dudas sobre replicación, se pueden ver los logs de errores en:

• /usr/local/mysql/data/Servidor-Master.err o
• /usr/local/mysql/data/Servidor-Esclavo.err ,

Según la máquina

---

COMANDOS IMPORTANTES PARA VER Y CONTROLAR LA REPLICACIÓN

1.- Utilidades del Servidor Maestro

Estando dentro del servidor Maestro (M) :

Mysql>SHOW MASTER STATUS;

El master por cada cambio realizado en la Mysql principal (insert, update, delete) trabaja creando líneas de cambios en un fichero bin.

La sentencia SHOW MASTER STATUS : Indica el fichero .bin que está utilizando el master para guardar los cambios actualmente y por que posición va actualmente (línea dentro del fichero).

Ejemplo de resultado de SHOW MASTER STATUS/

• File = mysql-bin.000122 [Fichero bin actual ]
• Position = 269 [Última posición insertada]
• BinLog_Do_DB = empresa [Base de datos que trata únicamente]
• BinLog_Ignore_Db = “” [BD ignoradas, si BinLog_Do_Db <> "" el resto]

Estando dentro del servidor Maestro (M) :

Mysql> SHOW PROCESSLISTG;

Muestra el estado del flujo del servidor que se encarga de enviar al esclavo los ficheros de “mensajes” actualizaciones en la base de datos del maestro.

*************************** 10. row ***************************

• Id: 97
• User: replica
• Host: 192.168.5.130:48647
• db: NULL
• Command: Binlog Dump
• Time: 1262
• State: Has sent all binlog to slave; waiting for binlog to be updated
• Info: NULL

2.- Utilidades del Servidor Esclavo

Estando dentro del servidor Esclavo (E) :

Mysql> SHOW SLAVE STATUS;

Slave_IO_State: Waiting for master to send event

• Master_Host: 192.168.5.130 [Ip master]
• Master_User: replica [Usuario master para conectarse]
• Master_Port: 3306 [Puerto mysql]
• Connect_Retry: 60 [60'' Timeout para conectar los 2 servers]
• Master_Log_File: mysql-bin.000122 [Último Fichero master]
• Read_Master_Log_Pos: 269 [Última posición en el fichero master]
• Relay_Log_File: server-relay-bin.000011 [Último log en el esclavo]
• Relay_Log_Pos: 406 [Última posición en el log del esclavo]
• Relay_Master_Log_File: mysql-bin.000122 [Último fichero master]
• Slave_IO_Running: Yes [Muy importante, debe ser yes]
• Slave_SQL_Running: Yes [Muy importante, debe ser yes]
• Replicate_Do_DB: empresa [B.D se replica únicamente]
• Replicate_Ignore_DB:

Si los campos Slave_IO_Running : No y Slave_SQL_Running : No.

Por estar el servicio parado en el Servidor Esclavo (E), activar con el mandato :

Mysql> START SLAVE;

Estando aún dentro del Servidor Esclavo (E) :

Mysql> SHOW PROCESSLISTG;

El comando enseña para el esclavo los 2 flujos que hay trabajando, en concreto siempre habrá 2, el primero que lee del servidor master (flujo entre máquinas), y el segundo, que coge los datos recibidos y actualiza la base de datos (flujo interno de actualización).

El master guarda los cambios en la base de datos en un fichero log, en ciertas posiciones. Cuando hace esto manda un evento al proceso del esclavo. El esclavo recibe el evento y se conecta con el flujo 1 al servidor para recibir el log, una vez recibido el flujo 2 mete la información nueva al mysql propio.

*************************** 1. row ***************************

• Id: 1
• User: system user
• Host:
• db: NULL
• Command: Connect
• Time: 2752 (tiempo desde la última lectura del master, en segundos)
• State: Waiting for master to send event
• Info: NULL

*************************** 2. row ***************************

• Id: 2
• User: system user
• Host:
• db: NULL
• Command: Connect
• Time: 12
• State: Has read all relay log; waiting for the slave I/O thread to update it
• Info: NULL

RESETEAR o PURGAR LOS LOG’S EN EN SERVIDOR MAESTRO

Para inicializa los contadores y borra todos los “mysql-bin” temporales, el mandato es :

Mysql> RESET MASTER;

Para expirar los registros binarios. Podemos utilizar dicha sentencia después de ejecutar la sentecia :

Mysql> PURGE MASTER;

En cada uno de los esclavos para determinar qué registros binarios ya no son necesarios.

Mysql> SHOW SLAVE STATUS;

---