MySQL – Replicación master/escalvo y master/master

No voy a explicar nada que ya no esté ampliamente desarrollado en la web, pero que a veces hay que recurrir a múltiples portales y foros para lograr dejar una solución en funcionamiento y de forma óptima.

Así que en este post trataré de explicar de la forma más sencilla y clara posible los pasos para armar una solución de bases de datos basada en MySQL con replicación de hosts en formato Master/Slave, Master/Master y comó agregar filtros como especificar qué bases de datos deseo replicar y/o qué tablas deseo ignorar en dichos procesos.

A pesar que mucha gente cree que este proceso es inestable, yo lo tengo funcionando y probado como para asegurarles que es estable, consistente y muy útil a la hora de garantizar accesibilidad a los datos y contar con uno o más fail-safe hosts. Otro uso interesante es utilizar los hosts esclavos para realizar consultas pesadas o los respaldos de las bases de datos, quitando así la carga de esos procesos sobre el master.

Así que basta de explicaciones inútiles y pasemos a los hechos…

Existen 2 pasos fundamentales antes de comenzar, los numero como 0 y 0.1 pues no forman parte del proceso de replicación, pero pueden hacer la diferencia entre explicar los cambios realizados y las ventajas de la nueva solución, y explicar al jefe que sería un buen momento para comenzar con una base de datos vacía y tratar de encontrar algún respaldo que sirva para algo. 

Paso 0:

Respaldar la base de datos de producción y la configuración actual.

Existen varias formas de hacerlo, pero para dejar algo como ejemplo, pondré un dump.

mysqldump -uuser_name -p --all-databases > ~/dbdump.sql

cp /etc/mysql/my.cnf ~/my.cfg.backup

Otra forma puede ser respaldar el directorio de datos del MySQL.

El directorio dónde se encuentra la configuración del MySQL y el directorio de datos puede variar dependiendo de la distribución. En este caso, el ejemplo está armado en una distribución basada en Debian GNU/Linux.

Paso 0.1:

Revisar el respaldo que esté usable.  Dejar prevista una forma rápida, y si puede ser documentada de los pasos necesarios para deshacer los cambios realizados y volver al estado anterior con el menor impacto posible.

Puede que parezca un abuso recordar estos pasos, pero está más que comprobado que cuanto más experiencia tenemos como sysadmin, menos caso hacemos a las normas de seguridad antes de aplicar soluciones, pues estamos muy confiados que nada pasará :)

Replicación Master/Slave en 6 pasos

En este caso vamos a armar una solución basada en 2 hosts, uno como master y el otro como slave. El host seleccionado como esclavo estará limitado a lectura, prohibiendo que se escriban datos y así hacer peligrar la replicación. El esclavo puede ser utilizado para consultas exclusivamente, liberando al master de parte de la carga.

Es importante verificar que los hosts no estén “escuchando” solo en localhost, de lo contrario la replicación no funcionará, pues los servidores rechazarán conexiones que provengan de otros hosts de la red.

Promocionar un esclavo a master es sencillo, y también se mostrará al final de este caso.

Si esta solución va a ser implementada sobre un host en producción y la base de datos es grande, conviene avisar que el acceso a la misma será interrumpido durante un periodo de tiempo(eso dependerá de la potencia del host y el tamaño de las bases de datos.

La razón por la que se deberá suspender el servicio es porque para poder establecer el master se debe hacer un dump de la base de datos con un read lock para garantizar que no se modifique nada antes de establecer los valores iniciales para la sincronización.

Puede que parezca un abuso recordar estos pasos, pero está más que comprobado que cuanto más experiencia tenemos como sysadmin, menos caso hacemos a las normas de seguridad antes de aplicar soluciones, pues estamos muy confiados que nada pasará :)

Algunas consideraciones básicas:

Cada host ESCLAVO puede tener un sólo MASTER Cada host de la solución debe tener un único ID server Un MASTER puede ser “observado” por varios ESCLAVOS Un ESCLAVO puede propagar cambios desde su MASTER y ser a su vez el

MASTER de otros ESCLAVOS si se habilita log_slave_updates en la configuración creando así una “cadena”. (Esto queda para otro post)

Para el ejemplo vamos a utilizar los siguientes parámetros:

Host 1: master (192.168.1.1)Host 2: slave(192.168.1.2)

Paso 1(en Master):

Creamos un usuario para la replicación:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replicador@'192.168.1.2' IDENTIFIED BY 'clave';

Una vez hecho eso vamos a modificar la configuración del MySQL, en este caso en /etc/mysql/my.cnf

Vamos a agregar las siguientes lineas al archivo dentro de la sección “[mysqld]“:

log-bin=mysql-binexpire_logs_days = 10max_binlog_size = 100Mserver_id=1

Las primeras 3(tres)lineas establecen el nombre, tamaño máximo y vida del archivo en el que se guardará el log binario de transacciones que utilizará el esclavo. Puede incluir un camino si se desea especificar un directorio diferente al del propio server(/var/lib/mysql para las distros basadas en Debian). Los archivos se numerarán secuencialmente y se creará un nuevo archivo al llegar al máximo tamaño establecido en la configuración(100MB por omisión). Los logs serán guardados por un tiempo(valor también establecido en la configuración del MySQL y que por omisión está establecido en 10 días).

Ejemplo de archivo de log binario: mysql-bin.000001

Paso 2(en Master):

Reinicamos el serivicio de MySQL y verificaremos el estado de master:

service mysql restart

o a la vieja usanza:

/etc/init.d/mysql restart

 Si las modificaciones fueron bien copiadas y todas las opciones son soportadas por la versión de MySQL corriendo en el host, éste reiniciará sin problemas… sino, no :P

Una vez reiniciado el servicio, accedemos como root y verificamos que el servicio haya levantado como master:

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

Los valores desplegados por este comando van a variar para cada caso, debe figurar el archivo de log binario que está siendo utilizado y la posición del índice del mismo. Las últimas 2(dos) columnas, si aparecen vacías es que la replicación es completa, incluye todas las bases de datos y sus respectivas tablas, inclusive la base del servicio(mysql).

Nuestra configuración del master está lista.

Paso 3(en Esclavo):

Los pasos son muy similares que los realizados en el master, salvo que debemos modificar algunos parámetros de la configuración.

Modificaremos la configuración del equipo que actuará como esclavo. Vamos a agregar las siguientes lineas al archivo dentro de la sección “[mysqld]“:

log-bin=mysql-binrelay_log=mysql-relay-binread_only=1server_id=2

Uno de los valores más importantes es el “server_id” que debe ser diferente al utilizado en el master u otros esclavos.  Los otros 3(tres) valores representan:

log-bin : Nombre del archivo de log binario de transacciones. relay_log : registro de transacciones del propio esclavo. read_only : Al estar en 1, previene que se escriban datos en el host. Solo permitirá

leer información de sus tablas.

Paso 4(en Esclavo):

Reinicamos el serivicio de MySQL:

# service mysql restart

o a la vieja usanza:

# /etc/init.d/mysql restart

Al igual que en el master, si las modificaciones fueron bien copiadas y todas las opciones son soportadas por la versión de MySQL corriendo en el host, éste reiniciará sin problemas… sino, no :P

Nuestra configuración del slave está lista. Ahora debemos replicar los datos existentes en caso de tratarse de un host en producción.

Paso 5(en Master):

Nos conectamos al MySQL como root y realizamos la exportación de los datos, lo siguiente será obtener el log binario y posición actual del master. Para esto debemos asegurarnos que nadie va a realizar operaciones entre que exportamos los datos y obtenemos dichos valores.

mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 | 107 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

Con esto ya podemos exportar nuestras bases de datos sin riesgo de perder la referencia. Esto se debe a que el primer comando ejecutado(‘FLUSH TABLESWITH READ LOCK’) hace un bloqueo general a nivel de bases de datos, diferente al obtenido por el un simple ‘LOCK TABLES’. Hasta que se desbloquen las tablas nadie podrá hacer uso de las bases de datos. Por ese motivo, las referencias a las transacciones no se verán afectadas. Los valores obtenidos con ‘SHOW MASTER STATUS’ los usaremos desde el esclavo más adelante.

Obtenido esa información y tranquilos de tener las bases bloqueadas, salimos del cliente y realizamos el dump:

mysqldump -uroot -p --all-databases --lock-all-tables > ~/dbdump_replicador.sql

Una vez finalizado el proceso, podemos desbloquear las bases de datos y habilitar el master para que continúen trabajando.

mysql>UNLOCK TABLES;

Ahora debemos copiar el archivo de dump de la base de datos al host esclavo.

Paso 6(en Esclavo):

# mysql -uuser -ppassword < ~/dbdump_replicador.sql

Este proceso puede tomar algún tiempo dependiendo de la potencia del host y el tamaño del archivo. Cuando termine debemos conectarnos al server como root con el cliente MySQL y asginar el master a este esclavo utilizando los datos obtenidos en el paso anterior.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='replicador', MASTER_PASSWORD='clave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;mysql> START SLAVE;

Esto asigna el master e inicializa el servicio de esclavo. Si bien la linea es bastante explicativa, y los valores asignados fueron tomados de los datos surgidos en los pasos anteriores,  explicaré cada uno de los campos cargados en la linea:

MASTER_HOST: IP o FQDN correspondiente al master. MASTER_USER: Usuario que se creo para la replicación(establecido en el Paso

1). MASTER_PASSWORD: Contraseña para el usuario de replicación(establecido en

el Paso 1). MASTER_LOG_FILE: Archivo de transacciones del master(obtenido en el Paso

5) MASTER_LOG_POSITION: Posición dentro del archivo de

transacciones(obtenido en el Paso 5).

Para verificar el estado del esclavo usaremos el comando:

mysql> SHOW SLAVE STATUS\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.1 Master_User: replicador Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: mysql-relay-bin.00008

Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 107 Relay_Log_Space: 452 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec)

Si obtenemos una salida similar a esta significa que ya tenemos funcionando el serivicio master-esclavo y que el esclavo está 100% sincronizado.Algunas líneas a tener en cuenta:

Last_SQL_Error: Mientras esté vacío todo estará bien, en caso de contener errores habrá que ver qué error ocurrió y se debe solucionar y reinicar el servicio de esclavo con:

o STOP SLAVE;o START SLAVE;

Seconds_Behind_Master: Este campo es numérico, y nos dice cuántos segundos, aproximadamente, el esclavo está atrasado respecto a su master. No debería ser demsiado alto, o al menos no mantenerse en un valor alto.

Con esto tendremos funcionando un sistema de replicación master/esclavo. Los equipos no tienen por qué estar en la misma red, pero si se llegara a utilizar esta solución desde una red abierta, es deseable agregar seguridad para evitar poner en riesgo los hosts. MySQL ofrece la autenticación de sus hosts utilizando certificados SSL, también sería deseable colocarlos detrás de firewalls.

Promover un esclavo a master:

Suponiendo que tengamos un problema con el host master, debemos promover nuestro esclavo para que trabaje como master. Eso se logra modificando el archivo de configuración en el eslcavo, reiniciar el servicio y para la replicación con el master.

En el archivo de configuración del MySQL  comentar/quitar la linea que hacía al servicio read-only:

 

read_only=1

Luego, reiniciar el servicio:

# service mysql restart

o a la vieja usanza:

# /etc/init.d/mysql restart

Una vez hecho eso, ingresamos al servidor con el cliente como root y paramos la replicación:

mysql> STOP SLAVE;

Desde este momento las aplicaciones pueden apuntar a este host como motor de la base de datos y seguiran trabajando como si nada hubiera pasado.

NOTA: No existe la forma de volver a hacer un “downgrade” de master a esclavo, por lo que la forma de volver a contar con un sistema de replicación es replicar los 6 pasos anteriores utilizando el nuevo master e invirtiendo los roles.

Replicación MASTER/MASTEREsto es lo mismo que una replicación master/esclavo, con la diferencia que ambos host cumplirán los 2(dos) roles:

master esclavo

El único detalle, que no es menor, viene de la mano de poder soportar la concurrencia y numeración de registros para evitar que ambos host numeren utilizando un registro con el mismo valor.

Para solucionar esto, MySQL ofrece 2 parámetros en su configuración con el que se puede designar la forma de numerar para cada host. Este tipo de soluciones debe ser implementada con mucho cuidado y un estudio de la situación actual y posible crecimiento de la solución.

En el caso de solo contar con 2(dos) hosts, cada host será master y esclavo de su contraparte, pero si se pretende establecer una solución de 3 o más hosts, ya será necesario establecer un sistema de esclavos en cadena para poder mantener el sistema master/master funionando.

Puede leer más sobre el este tema en el sitio de MySQL.

Las variables de configuración para poder utilizar tablas con “auto_increment” en sistemas de replicación master/master son:

auto_increment_increment: Establece el incremento entre 2(dos) numeraciones. Por omisión es 1.

auto_increment_offset: determina el valor a partir del cuál comenzará la numeración. Por omisión es 1.

Entonces, lo que podemos hacer es hacer que uno de los host inicie la numeración de 1(uno) y utilice un incremento de 2(dos), lo que hará que todos los registros cargados desde ese host tendrán números impares(1,3,5,etc.) y al otro host le decimos que inicie de 2(dos) y también use un incrementode 2(dos), numerando sus registros con pares(2,4,6,etc.).

En la sección “[mysqld]” del uno de los hosts pondremos:

auto_increment_increment=2auto_increment_offset=1

y en el otro host pondremos:

auto_increment_increment=2auto_increment_offset=2

Reinicamos los servicios en ambos hosts y listo! a partir de ese momento la numeración seguirá esas reglas.