Configurar Réplica Maestro – Esclavo en MySql Server

 

Sé que este artículo se aleja un poco de lo que suelo escribir por aquí pero la clase magistral que me ha dado mi gran amigo Pedro Sanz sobre MySql bien se merece una entrada en el blog :-)

 

Hoy hemos tenido que montar para un cliente un servidor de réplica de MySql en el que era imperioso que los datos se espejeran de forma inmediata entre el servidor maestro y el esclavo.

Una réplica de MySql Server hace uso del fichero binario de transacciones para almacenar en el maestro todos los cambios reaizados (UPDATES, DELETES, CREATE, etc..) para que un servidor externo lo lea y replique exactamente los mismos cambios en su propia base de datos.

De este modo tenemos uno o más servidores MySql esclavos haciendo las mismas transacciones que el maestro para así tener los mismos datos en diferentes servidores cosa que se realmente útil y, no sólo ante caídas, ya que es posible configurar nuestra aplicación para compartir las SELECT entre distintos nodos de MySql y mejorar así el rendimiento.

 

Bueno, menos teoría y vamos al lío!.

Lo primero que debemos hacer es configurar el servidor maestro para que almacene el log binario y asignarle un identificador. Para ello editamos el my.cnf añadiendo (o editando si ya las tiene) las siguientes entradas:

#Identificador único del servidor maestro
server-id=1
#Nombre del fichero binario donde se almacenarán las transacciones
log-bin=mysql-bin
sync_binlog=1
#Tamaño del fichero de log tras lo que se truncara
max-binlog-size=500M
expire_logs_days=4
innodb_flush_log_at_trx_commit=1

Como siempre que modificamos un my.cnf hay que reiniciar el servicio de MySql para que acepte los cambios.

Luego tenemos que hacer lo propio con el (o los) esclavo(s). Modificar el my.cnf con los siguientes parámetros y luego reiniciar el servicio:

#Indentificador único del esclavo
server-id=2
relay-log=mysqld-relay-bin
max-relay-log-size=500M
relay_log_purge=1

¡Muy bien! Ya tenemos un servidor maestro y un esclavo pero ahora necesitamos crear un usuario para que el esclavo se conecte al maestro y pueda leer el log de transacciones. Para ello vamos a crear un nuevo usuario llamado “replicador” (el nombre y pass puede variar, jeje) en el master con privilegios de “REPLICATION SLAVE“:

CREATE USER replicador IDENTIFIED BY 'elpassword';

Y luego le damos los permisos de REPLICATION SLAVE:

GRANT REPLICATION SLAVE ON *.* TO 'replicador'@'%' IDENTIFIED BY 'elpassword';
FLUSH PRIVILEGES;

Ok! Ya tenemos los servidores bien configurados y el usuario que usaremos como replicador por lo que lo próximo que tenemos que hacer es crear una copia inicial o “snapshot” de la base de datos que queremos replicar para luego poder indicar al servidor esclavo desde dónde tiene que empezar a leer.

Para hacer el snapshot primero ejecutamos las siguientes consultas:

FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Ten en cuenta que al hacer “READ LOCK” estamos bloqueando la tabla para que nadie cambie nada por lo que lo que viene a continuación deberíamos hacerlo lo más rápidamente posible.

El SHOW MASTER STATUS muestra dos valores que debemos anotar que son el “File” y “Position“. Necesitaremos indicarselos al servidor de réplica una vez hayamos cargado la copia inicial.

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     492 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

¡Muy bien! Vamos al servidor esclavo y lo terminamos de configurar.

Lo primero es cargar una copia de seguridad de base de datos que queremos replicar del master al esclavo (puedes usar el método que quieras, mysqldump, HeidSql, MySql Workbench…) y, una vez restaurada, configurar el esclavo e iniciarlo.

En este ejemplo vamos a suponer que el servidor maestro está alojado en 10.0.1.10. Fíjate que tenemos que indicar el File y la Position que hemos obtenido antes del SHOW MASTER STATUS:

CHANGE MASTER TO MASTER_HOST='10.0.1.10', MASTER_USER='replicador', MASTER_PASSWORD='elpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=492, MASTER_PORT=3306;

START SLAVE;

Para terminar volvemos al maestro y desbloqueamos de nuevo las tablas para que puedan volver a editar datos:

UNLOCK TABLES;

¡Ya está! Si no ha pasado nada raro tendremos el servidor esclavo con la carga inicial funcionando y todo cambio en el master se replicara por arte de magia.

Si queremos saber el estado del servidor de réplica podemos usar la consulta:

SHOW SLAVE STATUS\G

Que nos mostrará un listado de datos. Yo miro el valor “Seconds_Behind_Master” que indica que “retraso” tiene el servidor esclavo respecto al maestro (si es NULL es que no va. Revisa el “Slave_IO_State” y “Last_Error”).

Ahora empieza a meter datos en la base de datos maestra y verás como ellos solitos aparecen en la esclava… ¡brujería! :-)

 

Espero que esto os sea útil.

Nos vemos Compilando!!

5 thoughts on “Configurar Réplica Maestro – Esclavo en MySql Server”

  1. Enhorabuena por tu blog.
    Este artículo me parece muy interesante. Puesto que de mysql se trata, estoy dudando entre usar EF con MySql o con SQL Server. Se supone que un ORM debe ser independiente del tipo de BD que vayas a usar pero creo que Entity Framework no es un producto muy maduro todavía como para estar haciendo pruebas. Ojalá tuviera la sencillez de Ruby on Rails para poder cambiar de base de datos en un abrir y cerrar de ojos.

    Quiero empezar a desarrollar una aplicación pero tengo dudas si utilizar mysql (por el tema de precios en hosting dedicado) o Sql Server. ¿Qué opinión tienes?

  2. ¡Gracias Álvaro!

    La verdad es que no he usado nunca EF contra MySql y, aunque no debería dar problemas, yo no me arriesgaría.

    Si tu problema es el precio del hosting con Sql Server podías pasarte por Domitienda.com y echarle un ojo a lo que ofrecemos. Desde el plan Básico ya tienes .NET 4.0 y SQL SERVER 2008 por 4.95€/mes (y ahora está al 30% de dto).

    Un saludo y gracias por comentar :-)

  3. Hola Álvaro, trabajé en un proyecto de una red social propia usando MVC2 EF y MySQL.

    Era la primera versión de EF y el rendimiento era bastante prohibitivo. Programas rapidísimo pero el consumo de CPU no es equivalente. Ha cambiado muchísimo y se ha mejorado bastante. Yo he sufrido durante años trabajar en tecnologías Microsoft con bases de datos MySQL, y mi consejo es que si partes de 0 con la infraestructura de base de datos te centres en SQL Server. No lo dudes. Está mucho mejor documentado, muchísimo mejor integrado con EF… no hay color, si eliges MySQL tienes hacer muchas cosas manualmente e invertir un tiempo de investigación que podrías usar para tu propia aplicación y no para usar el MySQL con el EF.

    Un consejo de un sufridor!

    Buen aporte Víctor y saludos!

  4. ¡Gracias por la info Alberto!

    La verdad es que he trabajado poco con MySql pero estoy empezando a darle al tema ya que cada vez es más habitual utilizarlo con tecnologías Microsoft.

    ¡Gracias por pasarte por aquí! :)

  5. Ha sido un agradable descubrimiento a través del usuario de twitter de domitienda!

    Aunque puede ser que ya lo conozcáis o lo estéis utilizando dejo esto a mano también de guía de inaciación para los que van a empezar con MySQL y .Net.

    Lo mejor para trabajar con MySQL y .Net es utilizar las librerías de Enterprise Library 4.1, te ahorran un tiempo indecible. Las liberó directamente Patterns And Practices de Microsoft. El MySQL provider de EL 4.1 están en Enterprise Library Contrib.

    Lo malo es que soportan máximo el MySQL Connector 6.2.4, el superior a este el 6.4 solo funciona para .Net 4.0 que está soportado en EL 5 y aún no ha salido el MySQL provider para EL 5, yo estoy buscando tiempo para colaborar con el equipo porque para MVC3 + MySQL me gustaría tenerlo, ya que MV3 sólo trabaja con System.Data 4.0.

    De todas formas Jérémi Bourgault, el administrador de EL Contrib, me anunció que Patterns & Practices no va a soportar en nuevas versiones el punto de vista de los providers, y que las futuras versiones solo trabajarán bases de datos a través de EF. Asi que parece ser que en el futuro habrá que morir en los ORM y pensar que nuestras nuevas aplicaciones deberán usar un ORM o utilizar tecnologías obsoletas :( Con lo que me gusta optimizar los comandos directamente…

    Os dejo los enlaces por si tenéis curiosidad, a mi personalmente me han reducido a la mitad el tiempo invertido para trabajar con bases de datos a través de providers.

    http://entlib.codeplex.com/
    http://entlibcontrib.codeplex.com/

Leave a Reply