MySQL – Replicación master/escalvo y master/master|MySQL – master/slave and master/master replication

mysqlreplication

 

[lang_es]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.[/lang_es] 

[lang_es]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

mysql_master-slave

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 [email protected]'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-bin
expire_logs_days = 10
max_binlog_size = 100M
server_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-bin
relay_log=mysql-relay-bin
read_only=1
server_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: 0
Master_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: 1
1 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:
    • STOP SLAVE;
    • 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/MASTER

Esto 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=2
auto_increment_offset=1

y en el otro host pondremos:

auto_increment_increment=2
auto_increment_offset=2

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

En breve estaré publicando en detalle una replicación master/master con más de 2 hosts, utilizando el encadenamiento de escalvos.

[/lang_es]

[lang_en]Howdy! I’m not discovering nothing here, but I’ll try to create a simple 6(six) steps tutorial to create a master/slave solution based on MySQL. All the information used along this post is available all through-out the Internet, so it can be seen as a gathering :D

At the end of this post, I’ll explain how to alter config to ignore certain DBs and/or tables from replication. I’ll also explain how to transform our master/slave solution into a master/master solution without the well-known problem of auto_increment collision between hosts.

So… let start!

There are 2(two) important steps we should comply before we start. I numbered them after 0 and 0.1 as they are not part of the replication steps. It’s well known among sysadmin the excess of confidence while deploying any solution involving production servers. [/lang_en]

[lang_en]Step 0:

Lets make a backup of our databases and service configuration.

There are several way to do it, but I will use a simple dump for this example.

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

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

Another way to save databases would be backing up your MySQL data directory. This directory may vary it position depending on wich GNU/Linux Distribution you are using. For this tutorial I’m using a Debian GNU/Linux based distribution.

Step 0.1:

Lets take a second look at our backup and ensure it is legible and usable in case of a disaster! :D We certainly don’t want to discover it’s useless in middle of a crisis. Document all steps performed so others can undo or comprehend what and how we did it.

Ok… did this, we are ready to start our replication process…

Master/Slave replication in 6 steps

mysql_master-slave

In this tutorial, we’re going to create a 2 host solution, one master with one slave. Slave will be configured as a read-only system so INSERTS or UPDATES will be forbidden by configuration preventing replication to collapse.

This configuration allows any kind of SELECT, so it can be used for complex queries or backup dumps without compromising the main host.

It’s important to ensure that both hosts(master and slave) aren’t listening exclusively at localhost(127.0.0.1) or replication won’t work, as hosts will reject any connection request coming from any other host rather than itself.

Promoting a slave host to master it’s very simple, and will be explained at the end of this post.

If this solution is about to be deployed over and large DB and a production server it is recommended to notify users that DB will be off-line for several  minutes before we start(off-line time it will depend on host power and DB size).

Main DB host(future master) must be stopped because we need to apply a read lock to ensure DB will not be modified  until we obtain binary log file and position needed for replication.

Some useful stuff to keep in mind:

  • Each SLAVE can “look” at only one MASTER at a time.
  • One MASTER can serve multiple SLAVES.
  • Each host involved in the solution must have a unique server ID(server_id)
  • A SLAVE may propagate it’s MASTER transactions so it become a MASTER for other SLAVES(log_slave_updates). This process is called slave chain.

For this example we are going to use this values:

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

Step 1(on Master):

We connect to MySQL and create a user for replication:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO [email protected]'192.168.1.2' IDENTIFIED BY 'pass';

Done that, we must change service configuration to setup this host as master. As we said before, in this case configuration file is at /etc/mysql/my.cnf

We need to add these 4 lines to my.cnf under “[mysqld]“ section:

log-bin=mysql-bin
expire_logs_days = 10
max_binlog_size = 100M
server_id=1

First 3(tres) lines setup the name, maximum length and DTL for binary log files used by our slaves. If you want to store binary-logs in a separate directory you may include an absolute path along with filename. If no path is declared, binary logs will be stored in the same directory MySQL sotres data files. Binary log files will be enumerated sequentially. Binary logs will be saved for 10(ten) days in files of 100(one hundred)MB each.

Here it is an example of a binary log file: mysql-bin.000001

Paso 2(en Master):

Once we setup our master, we need to restart the service:

service mysql restart

or we can do it by the old-school way:

/etc/init.d/mysql restart

If you’ve copy all options

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.

[/lang_en]

3 Replies to “MySQL – Replicación master/escalvo y master/master|MySQL – master/slave and master/master replication”

  1. No, Lo que planteas no es tan sencillo, ya que un equipo puede ser esclavo de un solo master. Si bien podrías armarlo como tú lo planteas, ya que un master sí puede tener múltiples esclavos, todos los clientes deberían escribir en tu servidor, y eso no creo que sea lo que quieres hacer. Me explico?
    O sea, esta solución no está pensada para lo que tú deseas armar. Un esclavo va a replicar cualquier acción que se genere en el master, pero un master ignorará cualquier acción que se genere en el esclavo. Para lo que tú deseas hacer, lo más sano sería implementar un sistema de dump(no se qué tamaño tienen las bases de datos en cuestión), pero lo realizas en las horas de la noche y las copias a tu server para respaldar. Esta solución está pensada para tener replicada N veces una o más bases de datos y tener un sistema de tolerancia de fallas o recuperación rápida ante desastres. También te puede servir para tener diferentes servidores para consultas que son esclavos de un mismo master que está encargado de ingresar los registros.

  2. Buenos días, necesitaría me des una recomendación de cual camino elegir, te planteo lo siguiente, tengo varios clientes con sus servidores propios (de configuración básica) y el mio medianamente grande, mi idea es replicar las bases de datos de mis clientes en mi servidor y de ahí hacer los respectivos backups de cada base de datos, por lo que entiendo para el caso, mi servidor seria el master y el de mis clientes los esclavos?, estoy en lo correcto?. Gracias

  3. Si a alguien le da problemas conectandose al Master desde el esclavo, que se asegure que no tiene capadas la conexiones remotas

    Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces. If the server was started with –skip-networking, it will not accept TCP/IP connections at all. If the server was started with –bind-address=127.0.0.1, it will listen for TCP/IP connections only locally on the loopback interface and will not accept remote connections.

    Extraido de http://dev.mysql.com/doc/refman/5.1/en/can-not-connect-to-server.html

Leave a Reply

Your email address will not be published. Required fields are marked *