Transparencias del curso "Replicación en MySQL"

Os presento las transparencias del curso de Replicación de MySQL que acabo de terminar y subir a Slideshare.

Contenido:

Maestro/Esclavo Maestro/Maestro Circular MMM MySQL Proxy

Arquitectura Maestro / Maestro en Mysql

La arquitectura Maestro/Maestro es muy sencilla tanto de entender como implementar. Cuando vimos anteriormente Maestro/Esclavo, vimos que el Maestro se utilizaba para escrituras, mientras que en lecturas teníamos N servidores. En ese caso la lectura no es problema, hay suficiente hardware procesando peticiones, pero ¿qué pasa con la escritura?. Según el número de usuarios aumente y la carga de escrituras sea mayor, dicho servidor terminará por no dar a basto ralentizando el buen funcionamiento de nuestras aplicaciones. Maestro/Maestro viene a solucionarnos este problema.

En este caso, H1 y H2 reciben las peticiones de escritura. Los dos deben tener los datos sincronizados, para ello se sigue el siguiente esquema:

• H1 es maestro de H2 (por lo tanto H2 esclavo de H1)
• H2 es maestro de H1 (por lo tanto H1 es esclavo de H2)

De esta forma, todo lo escrito en H1 se replicará a H2 y viceversa.

La configuración es parecida a la del esquema Maestro/Esclavo, solo que tenemos que hacerlo dos veces :)

1- Dumpeamos la BD del H1 con los datos del binary log actual y la posición:

#H1> mysqldump BD --master-data=2 > dump_file

2- La importamos en H2:

#H2> mysql BD < dump_file

3- Configuramos my.cnf de H1 (10.10.10.1) y creamos el usuario de replicación:

log-bin=mysql-bin
binlog-do-db=BD
binlog-ignore-db=mysql
binlog-ignore-db=test
server-id=1

mysql> grant replication slave on *.* to 'replication'@10.10.10.2 identified by 'slave';

4- Configuramos my.cnf de H2 (10.10.10.2):

log-bin=mysql-bin
binlog-do-db=BD
binlog-ignore-db=mysql
binlog-ignore-db=test
server-id=2

mysql> grant replication slave on *.* to 'replication'@10.10.10.1 identified by 'slave';

5- Configuramos H2 como esclavo de H1:

mysql> CHANGE MASTER TO MASTER_HOST = ‘10.10.10.1’, MASTER_USER = ‘replication’, MASTER_PASSWORD = ‘slave’, MASTER_LOG_FILE = ‘master_log_file’, MASTER_LOG_POS = master_log_pos;

El master_log_file y master_log_pos los sacamos del fichero dump_file.

También es posible sacarlo de otras formas, por ejemplo el comando "show master status" nos muestra en que fichero y posición se encuentra el log binario.

6- Configuramos H1 como esclavo de H2:

mysql> CHANGE MASTER TO MASTER_HOST = ‘10.10.10.2’, MASTER_USER = ‘replication’, MASTER_PASSWORD = ‘slave’, MASTER_LOG_FILE = ‘master_log_file’, MASTER_LOG_POS = master_log_pos;

Listo, ya está montada la replicación. Como se ve, tantos pasos se pueden resumir en:

A es esclavo de B, B es esclavo de A. Listo ^_^

Ahora si ponemos un balanceador delante que reparta las escrituras (round-robin) ya tendremos el problema de carga solucionado. Es importante que esta parte no es mucho más escalable de esta forma, debido a que como ya se comentó, un server solo puede ser esclavo de un único maestro. Por lo que una arquitectura maestro/maestro tendrá como máximo dos hosts implicados.

Ahora imaginemos que nuestra base de datos tiene un campo ID autoincremental, que identifica un artículo. ¿Qué pasa si se escribe al mismo tiempo en las dos bases de datos? Que H1 enviará una modificación al H2 con el ID p.ej. 3000, y el H2 al H1 otro artículo completamente distinto con ID 3000. Ya la hemos liado, duplicados de IDs.

Para evitar esto se suele configurar cada uno de los servidores para que use auto incrementales salteados. De esta forma, si H1 usase solo autoincrementales pares y H2 impares, nunca se daría ese problema. Para hacer eso se indica en my.cnf lo siguiente:

H1:

auto_increment_increment = 2
auto_increment_offset = 1

H2:

auto_increment_increment = 2
auto_increment_offset = 2

Y listo, problema solucionado! Eso si, esto no es a prueba de manazas. Si alguien (o alguna aplicación) mete el autoincremental a mano saltándose estas normas, el lío estará garantizado igualmente.

Y eso es todo :)

Monitorizar la replicación de Mysql

Imaginemos que tenemos una infraestructura Maestro/Esclavo y el esclavo lo usamos para lecturas, backup o simplemente para dar un servicio mínimo si el Maestro se cae. Imaginemos que dicha replicación lleva caída un mes y el Maestro se rompe. Cuando te das cuenta es demasiado tarde y entonces toca imaginarse como huir sin que te pillen :)

Monitorizar si la replicación es correcta es sencillo, ya que MySQL nos puede dar en segundos el desfase que existe entre un host y otro a la hora de replicar los cambios. Con "SHOW SLAVE STATUS\G" tendremos la información que necesitamos:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.60.1.3
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 327326
Relay_Log_File: mysqld-relay-bin.000061
Relay_Log_Pos: 327463
Relay_Master_Log_File: mysql-bin.000021
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: 327326
Relay_Log_Space: 327463
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: 0

Cada entrada en el log lleva un timestap indicando el momento exacto en el que es escrito en el Master. Por lo tanto, el "Seconds_behind_master" hace una simple resta, entre nuestro timestamp y el del master para saber la diferencia.

Dicho esto, la solución es tan sencilla como tirar de Bash Scripting:

#!/bin/sh
treshold=1200

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin

if [ -z $1 ] ; then
echo "Usage: $0 /path/to/mysql.sock"
exit 255
fi seconds=mysql -S $1 -N -e "show slave status\G" | grep Seconds_Behind_Master | cut -d: -f 2

if [ -z $seconds ] ; then
exit 255
fi

if [ $seconds = "NULL" ]; then
echo Replication kaput!!!
exit 255
fi

if [ $seconds -gt $treshold ]; then
echo Slave behind the master more than $treshold seconds!
fi

Autor del Script

Pero claro, antes de realizar estas comprobaciones se deben tener en cuenta una serie de requisitos previos:

• Los equipos deben tener la hora sincronizada :)
• A poder ser, deben estar LAN. Si tenemos una WAN con mucha latencia y encima microcortes, olvídate de esto.

Si se busca en internet hay múltiples formas de hacerlo, pero esta es la más sencilla. Y si le añadimos algun "exit 0" y "exit 1" tenemos un checker para Nagios :P Y también es recomendable añadir otro grep más para comprobar si los threads SQL e IO están en funcionamiento (Slave_IO_Running, Slave_SQL_Running)...

Si Mikel, esta entrada va por ti xD

Replicación desincronizada

A veces puede darse el caso de una desincronización entre los distintos servidores que forman parte nuestra infraestuctura. Esto puede causar tanto la caida del host desincronizado como la caida de casi todo el sistema si estamos ante una replicación circular. Dichas desincronizaciones pueden deberse a muchas causas, fallos en MySQL, en nuestra aplicación, en la red, en la configuración de los sistemas, etc. O como no, un fallo humano, como por ejemplo borrar o modificar tablas y registros en un servidor Esclavo.

Cuando esto ocurre se pasa siempre por dos fases:

1- Histeria

2- Solución

La fase de histeria es bien conocida, así que iremos directamente a la solución.

Si analizamos los logs del servidor desincronizado, podemos ver cual es la query problematica y descrubir la razón del problema:

Sep 11 11:13:16 test2 mysqld[6776]: 090911 11:13:16 [ERROR] Slave: Error 'Table 't' already exists' on query. Default database: 'mysql'. Query: 'CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin)', Error_code: 1050
Sep 11 11:13:16 test2 mysqld[6776]: 090911 11:13:16 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000003' position 421

Como se puede leer, la query enviada por el Maestro es un "CREATE TABLE t...) pero el Esclavo no puede hacerlo ya que la tabla YA existe. O alguien la ha creado a mano en el Esclavo por equivocación o el anterior DROP TABLE no llegó a sincronizarse. Apuesto por la primera, la manaza de alguien :P

Además nos proporciona más información valiosa, como el log binario que se está leyendo así como la posición en la que se encontraba, valores importantes si deseamos reiniciar la sincronizacion con un dump completo.

Como ese comando, el CREATE TABLE, ya no es necesario que se sincronize, ya que la tabla en realidad ya existe porque algún manazas la creo en el Esclavo, vamos a decirle que lo ignore y siga con la replicación:

mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; Query OK, 0 rows affected (0.00 sec) mysql> start slave;

Traducido quiere decir:

• Para de funcionar como Esclavo.
• Ignora la última query recibida del Maestro.
• Vuelve a funcionar como Esclavo

¡Y listo!

MySQL SandBox, el mejor entorno de pruebas

Juguetear con MySQL es divertido, pero la preparación de los distintos servidores un coñazo. Distintos sockets, distintos puertos, establecer los usuarios y permisos… Existe una utilidad para convertir MySQL en una zona de juego, SandBox:

Sandbox

Esta herramienta te permite por ejemplo, mediante un simple comando, crear una estructura de replicación circular con 20 servidores MySQL :P

Instalación

Hacen falta dos cosas, SandBox y un tar.gz con los binarios de Mysql (que se descargan de la propia web de MySQL). Una vez que lo tenemos, procedemos a instalar SandBox:

Como Root:

perl Makefile.PL
make
make test
make install

Como Usuario:

export PATH=$HOME/usr/local/bin:$PATH
export PERL5LIB=$HOME/usr/local/lib/perl5/site_perl/5.8.8
perl Makefile.PL PREFIX=$HOME/usr/local
make
make test
make install

Puesta en marcha

Imaginemos que queremos montar un sistema de replicación con un maestro y 10 esclavos :) Lo que antes era un infierno ahora se resume en:

make_replication_sandbox --how_many_slaves=10 /tmp/mysql-5.1.37-linux-i686-glibc23.tar.gz
installing and starting master
installing slave 1
installing slave 2
installing slave 3
installing slave 4
[...]
replication directory installed in $HOME/sandboxes/rsandbox_5_1_37

Listo!

Para ejecutar un comando en todos los servidores:

/home/sandboxes/use_all "show databases"
master
Database
information_schema
mysql
test
server: 1:
Database
information_schema
mysql
test
...

Cada MySQL escucha en un puerto y tiene un socket propio. Los puestos los podeis saber con un simple netstat:

[punisher@miguel sandboxes]$ netstat -putan
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:23552 0.0.0.0:* LISTEN 9986/mysqld
tcp 0 0 0.0.0.0:23553 0.0.0.0:* LISTEN 10609/mysqld
tcp 0 0 0.0.0.0:23554 0.0.0.0:* LISTEN 10706/mysqld
tcp 0 0 0.0.0.0:23555 0.0.0.0:* LISTEN 10803/mysqld
tcp 0 0 0.0.0.0:23556 0.0.0.0:* LISTEN 10900/mysqld
tcp 0 0 0.0.0.0:23557 0.0.0.0:* LISTEN 10998/mysqld
tcp 0 0 0.0.0.0:23558 0.0.0.0:* LISTEN 11096/mysqld
tcp 0 0 0.0.0.0:23559 0.0.0.0:* LISTEN 11194/mysqld
tcp 0 0 0.0.0.0:23560 0.0.0.0:* LISTEN 11292/mysqld
tcp 0 0 0.0.0.0:23561 0.0.0.0:* LISTEN 11389/mysqld
tcp 0 0 0.0.0.0:23562 0.0.0.0:* LISTEN 11487/mysqld

Para saber los sockets:

[punisher@miguel ~]$ ls -la /tmp/mysql_sandbox235*

srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23552.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23553.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23554.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23555.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23556.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23557.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23558.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23559.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23560.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23561.sock
srwxrwxrwx 1 punisher users 0 sep 7 16:44 /tmp/mysql_sandbox23562.sock
Para pararlos/arrancar todos:

$HOME/sandboxes/start_all $HOME/sandboxes/stop_all

Para acceder a uno en particular:

Con la herramienta mysql (clave de root msandbox) o mediante unos script:

$HOME/sandboxes/rsandbox_5_1_37/m (master)
$HOME/sandboxes/rsandbox_5_1_37/s1 (slave 1)
$HOME/sandboxes/rsandbox_5_1_37/s2 (slave 2)
etc.