Hace un tiempo escribi un artículo explicando cómo instrumentar Replicación Master-Slave en Mysql para 2 boxes Debian Squeeze y prometi regresar sobre el tema de la Replicación con formato Master-Master. Este artículo viene a demostrar que soy un hombre de palabra =P.
Antes de comenzar con el tutorial, considero importante dedicar algunas palabras a la Replicación de datos en Mysql. Recomiendo, además, la lectura del anterior artículo ya que gran parte de lo que veremos aquí fue escrito en él desde que Mysql no tiene una solución específica para Replicar Master-Master y arribamos a este modelo duplicando el trabajo realizado al replicar Master-Slave: es decir, creamos replicacion de master (box1) a slave (box2) y luego repetimos el proceso de master (box2) a slave (box1) de suerte que arribamos -en este caso- a una interface con 2 bases de datos sobre las cuales se pueden realizar tareas INSERT/UPDATE/DELETE, etc que impactarán de inmediato en la contigua.
La replicación supone un mecanismo para mantener contenidos alojados en Bases de Datos en sincronía y es, por lo tanto, una estrategia de valor a la hora de escalar. Mysql ofrece la posibilidad de Replicar de una Base de Datos llamada Master a otra Slave. Este modelo, como comentamos en el artículo en que desarrollamos dicha metodología, tiene sus limitaciones y sus beneficios. Las limitaciones se dan en el campo de la funcionalidad de un Slave, que sólo permite operaciones de lectura (SELECT) dejando los writes a cargo del Master. Sin embargo, es bien sabido que en sitios de alto tráfico un alto porcentaje de la interacción con la Base de datos es de tipo lectura: los usuarios se pasean por nuestro sitio y eventualmente escriben la Base de Datos.
Es así que tener Servidores de Bases de Datos que sólo permitan lectura puede ser una buena estrategia dado que se podría, por ejemplo, direccionar todo el tráfico de usuarios no conectados a esos servidores liberando a los servidores encargados de escribir en Base de Datos del groso del tráfico. Recordemos que un Master puede tener tantos Slaves como podamos colocarle.
Claro está que con el tráfico llegará la necesidad de poder responder a interacciones write con la Base de Datos lo cual supondrá poseer mas de una BD en que puedan realizarse INSERTS/UPDATES/DELETES. Si bien Mysql no otorga una solución específica en materia de Replicación para sincronizar Bases de Datos Write, ésto puede realizarse muy facilmente con una vuelta de tuerca a la Replicación Master-Slave. Con el tiempo y el tráfico creciente nos encontraremos con una arquitectura de BDS Write sincronizadas que a su vez tendrán Slaves para lectura. Las posibilidades para hacer HA escalando horizontalmente son varias y la Replicación en Mysql es uno de varios métodos disponibles.
Dichas estas palabras, comencemos con el armado de nuestra arquitectura MASTER-MASTER
Nuestro Escenario
Para este tutorial usaremos 2 boxes con Lampp instalados sobre Debian Squeeze. Estos son los valores con que trabajaremos:
Box Lampp 1 (server1) = 192.168.1.31
Box Lampp 2 (server2) = 192.168.1.32
Base de datos instalada en ambos servers: blog (la BD de un WordPress)
Usuario Mysql = daniel
Clave Mysql = demichele
Asumimos Lampp funcionando con phpmyadmin instalado para disponer de una interface gráfica con que comprobar la replicación así como la base de datos a replicar instalada en los 2 servidores en estado idéntico.
Configuración Box 1 (192.168.1.31)
Comenzaremos con el primer Box modificando el archivo de configuración de mysql ubicado en /etc/mysql/my.cnf. Mi experiencia con replicación me ha enseñado que lo mejor es empezar por poner a andar un Master-Slave y, una vez que éste funciona, realizar el proceso inverso en el Box 2. Internet está plagada de tutoriales en que se muestran los ficheros my.cnf de los boxes involucrados y las sentencias cruzadas de GRANT REPLICATION SLAVE. Lo cierto es que, si uno copia y pega los contenidos de los archivos modificando los valores y corre los GRANT REPLICATION SLAVE, luego la replicación muy posiblemente no funcionará: nos encontraremos en Google buscando por qué obtenemos Slave_IO_Running: No con SHOW SLAVE STATUSG;
Recordemos: la replicación Master-Master es en realidad replicación Master-Slave en 2 sentidos, por lo que comenzar logrando Master-Slave es tener la mitad del trabajo resuelto.
Vamos a la consola:
En el archivo my.cnf comenzaremos por quitar la restricción a localhost comentando la línea bind-address = 127.0.0.1 que limita el alcance del servicio a la interface local. Vamos a comentar esta línea con # de modo que quede:
#bind-address = 127.0.0.1
Bajamos un poco más en el archivo dentro de la cabecera [mysqld] hasta llegar a la sección Logging and Replication y buscamos la línea comentada #server-id = 1. Allí modificamos como sigue:
Hasta aquí hemos identificado a este servidor con id 1, indicado dónde se encuentra el log del mismo y definido la Base de Datos que nos interesa Replicar hacia el Box 2.
A continuación debemos asignar al usuario mysql (daniel) permisos de Replicación en el Box 2. Volvemos a la consola:
Obtendremos una tabla como la siguiente. Es recomendable anotar los valores de File y Position dado que los utilizaremos al final del tutorial para sincronizar los 2 Boxes a partir de la posición de los Logs.
Salimos del prompt de mysql quitando el lock a las tablas
Pasamos al Box 2 (192.168.1.32)
Continuaremos con los pasos del primer tutorial debido a que estamos intentando realizar Replicación Master-Slave como primer paso. Encontraremos, sin embargo, algo nuevo en la configuración de my.cnf del Box 2 que luego repetiremos en el Box 1: el uso de auto_increment_increment y auto_increment_offset, de vital importancia para el correcto funcionamiento de la Replicación Master-Master.
Vamos a la consola del Box 2 a editar la sección Logging and Replication dentro del header [mysqld]:
Aquí nos encontramos con una primera novedad respecto al tutorial de Replicación Master-Slave. Esta consiste en la necesidad de resolver el escenario para campos autoincrement. Supongamos que tenemos 2 Boxes que pueden escribir vía INSERT y manejan un alto tráfico al punto en que se solicitan 2 INSERTS al mismo tiempo en una tabla con autoincrement. El box que primero reciba el INSERT ocupará el próximo índice disponible y deberá comunicarle al otro/s que la posición está tomada y el incremento debe continuar a partir del valor asignado. Esta "comunicación" no será inmediata dado que siempre existe Lag entre los boxes. Con esto nos hacemos una idea del problema al que nos enfrentamos:
- el box 1 recibe la orden de insert al mismo tiempo que el box 2
- el próximo índice es -digamos- 1000
- el box 1 escribe la Base de datos usando el índice y comunica al box 2 que continue a partir del 1001
- pero cuando la sincronización llega al Box 2, éste ya ha escrito su base de datos utilizando el índice 1000
- al llegar la sincronización nos encontramos con un DUPLICATE KEY ERROR
Los parametros auto_increment_increment y auto_increment_offset configurados debidamente en cada Box nos salvarán de este problema mediante un mecanismo extremadamente intuitivo: el primero seteado en 2 con offset de 2 tramitará los INSERTS en tablas con AI tomando el próximo índice disponible (ejemplo 14) y escribiendo 16 mientras que asignaremos al otro box un offset de 1 de modo que autoincremente naturalmente. En sintesis, el Lag entre boxes deja de importar debido a que cada Base de Datos posee un criterio para tomar índices que evitará la colisión. Más información sobre estos parametros puede ser encontrar en el sitio de Mysql.
Comenzando a trabajar la Replicación Bidireccional
Hasta aquí hemos configurado my.cnf en el Box 1, creado una cuenta de usuario mysql (daniel) con privilegio de Replicar Slave desde el Box 1 en el Box 2 y, por último, configuramos my.cnf del Box 2 para indicarle quien era su Master y proporcionar la información necesaria.
Lo que haremos ahora es darle a la cuenta mysql daniel privilegio para hacer Replication Slave pero desde el Box 2 hacia el Box 1 y, finalmente, editaremos my.cnf del Box 1 para decirle que es Slave del Box 2. En pocas palabras: repetiremos lo hecho hasta aquí pero en sentido inverso.
En la consola del Box 2 (192.168.1.32)
Obtendremos un cuadro simil el anterior. Anotemos los valores de File y Position para el Box 2 (los que anotamos antes pertenecían al Box 1). Tenemos 2 cuentas mysql con el user daniel cruzadas que le permiten Replicar a los slave definidos. Ha llegado el momento de convertir a nuestro Master (192.168.1.31) en un Slave de 192.168.1.32.
Transformando al Master 1 en Slave
Volveremos a editar my.cnf de Server 1 para darle las mismas instrucciones que le hemos dado en Server 2:
Con esto, hemos basicamente creado una arquitectura circular (la topología de la replicación Master-Master es, de hecho, circular). Le decimos al Box 1 que tiene server id 1 y debe trabajar con determinada base de datos. Creamos una cuenta mysql desde el Box 1 en que le damos a un usuario permisos para Replicar en un Slave del Box 2. Le decimos al Box 2 (editando my.cnf) donde está su Master (192.168.1.31). Luego repetimos toda la operación en sentido inverso cuidando que los valores de auto_increment_increment y offset estén debidamente seteados. El resultado: los boxes interactúan como Master y Slave entre sí.
Sincronizando los Boxes
Ahora debemos sincronizar los boxes de modo que cada Servidor sepa a partir de qué log mysql trabajar y desde qué posición comenzar (esta es la información que obtuvimos en las tablas al arrojar la sentencia SQL SHOW MASTER STATUS).
En el box 1 (192.168.1.31) ingresamos a mysql como root:
En el Box 2 (192.168.1.32):
Por último reiniciamos mysql en ambos boxes vía /etc/init.d/mysql restart y podemos probar en cada box si la replicación está funcionando ingresando como root mysql con:
De la información del SLAVE nos interesa que los valores Slave_IO_Running y Slave_SQL_Running estén en YES en ambos casos. Si todo está ok podemos probar la Replicación ingresando al phpmyadmin de uno de los Servers para agregar un valor y verificar que éste se haya replicado en la BD del otro Server.
Lo que los tutoriales nunca te explican
En mi camino para lograr una buena Replicación Master-Master he leído y probado muchas cosas. El inglés no es -por fortuna- un problema para mi ya que conozco el idioma bastante bien. Este tutorial, al igual que otros que he escrito y seguiré escribiendo es largo. Debo confesar que nada me gusta más cuando ando buscando cómo hacer algo que un tutorial en que se limitan a darme los comandos a utilizar para obtener lo que busco. Sin embargo, algunas cuestiones que revisten una complejidad media/alta como la Replicación merecen una explicación detallada de qué se hace y por qué se hace.
A continuación voy a listar algunas posibles causas por las cuales la replicación suele no funcionar y que no he leído en ningún lado.
1) La cuenta de usuario mysql (daniel) a la que le asignamos permisos para Replicar no es el usuario utilizado para conectar a mysql ni tiene permisos en local.
Esto es tan simple como suena: si estamos usando un user y pass mysql que ya existian y poseían permisos para trabajar con el Blog o Sitio web, entonces usaremos esa misma información cuando hagamos GRANT REPLICATION SLAVE.
Lo cierto es que muchas veces cuando intentamos poner a andar replicación le damos GRANT a un usuario que no existe, creandoló en el acto y luego nos encontramos con que el sitio anda bien pero no hay replicación (ni la habrá dado que el usuario al que se le asignó el privilegio de replicar no es el que se está utilizando para interactuar con la BD).
Otra cosa común es que al correr la sentencia mysql GRANT REPLICATION SLAVE ON *.* TO 'daniel'@'192.168.1.32' IDENTIFIED BY 'demichele', como hemos hecho en este ejemplo estamos otorgando privilegios a un usuario cuyo ámbito es el otro Box y no el local. Una buena forma de saber si estamos haciendo las cosas bien es intentar loguearse a mysql y phpmyadmin del Box 1 con el usuario en cuestión. Si el usuario y clave no existían, luego de haberlo creado habremos agregado el User Daniel con Host 192.168.1.32 estando en el Box 1 (192.168.1.31).
Para resolver este inconveniente comenzamos por verificarlo. Nos logueamos a mysql como root y corremos SELECT User,Host FROM mysql.user; Mysql responderá con una tabla en que se listarán los Usuarios y Hosts en que tienen incumbencia. Si la combinación de User: Daniel Host: localhost no se encuentra, entonces debemos crearla para poder operar en cada box con este nuevo usuario (porque el usuario debe tener los privilegios necesarios para conectarse a la aplicación en localhost y, además, los agregados para Replicar Slave en Box con que se conecta).
Agregar el usuario a localhost es tan simple como:
Realizada esta operación el usuario tiene ALL PRIVILEGES en localhost + REPLICATION SLAVE y ALL PRIVILEGES en el server remoto. Esta operación debe ser realizada en ambos boxes
2) No hay comunicación entre los servidores porque el puerto 3306 está cerrado en el Firewall.
Comentamos la línea bind-address en los ficheros my.cnf de cada servidor para permitir a mysql trabajar con otras máquinas en la red (o fuera de ella). Sin embargo, esto puede no ser suficiente y a veces necesitaremos abrir el puerto con iptables. Diagnosticar la comunicación entre boxes es muy sencillo: tras haberle dado GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'192.168.1.32'; desde el Box 1, lo siguiente debería ser posible:
Si obtenemos el prompt mysql tenemos conectividad. Si en cambio obtenemos un mensaje del tipo ERROR 1130 (HY000): Host 'server1' is not allowed to connect to this MySQL server tendremos que abrir el puerto en el Firewall del siguiente modo:
3) GRANT ¿a quién y sobre qué?
Muchos tutoriales sobre Replicación se despachan a la hora de otorgar privilegios para Replicar Slave con sentencias del tipo:GRANT REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY 'clave';
Esto se leería -con un poco de humor- más o menos así: "Dale permisos para replicar slave al user en todas las bases de datos en todos los servidores". En este tutorial hemos utilizado la expresión *.* porque sólo poseemos una base de Datos con que trabajaremos. Contamos además con las bases de datos que mysql y phpmyadmin ha instalado pero optamos por excluirlas en los ficheros my.cnf haciendo uso de binlog_ignore_db que puede utilizarse tantas veces como sea preciso para no incluir en la Replica algunas base de datos.
Si estuvieramos en un entorno en que trabajamos con VirtualHosts y contamos con una cantidad considerable de Bases de Datos, asignarle a un usuario permisos para Replicar Slave en todas ellas sería una mala idea. Es así que una sentencia de esta clase se adaptaría mejor:
GRANT REPLICATION SLAVE ON mibasededatos TO 'user'@'%' IDENTIFIED BY 'clave';
Por último el comodín % es también innecesario desde que uno puede o bien querer apuntar a un Host remoro como lo hemos hecho, o bien a localhost. Nuevamente una sentencia más discreta:
GRANT ALL ON mibasededatos TO 'user'@'localhost' IDENTIFIED BY 'clave';
O bien definir la IP del server destino, como lo hemos hecho aquí.
Eso es todo por ahora, la Replicación es un tema que da para mucho y éste -con seguridad- no será el último capitulo.
Artículo publicado originalmente en mi Blog: http://www.danieldemichele.com.ar/2011/11/25/replicacion-master-master-en-mysql/
Antes de comenzar con el tutorial, considero importante dedicar algunas palabras a la Replicación de datos en Mysql. Recomiendo, además, la lectura del anterior artículo ya que gran parte de lo que veremos aquí fue escrito en él desde que Mysql no tiene una solución específica para Replicar Master-Master y arribamos a este modelo duplicando el trabajo realizado al replicar Master-Slave: es decir, creamos replicacion de master (box1) a slave (box2) y luego repetimos el proceso de master (box2) a slave (box1) de suerte que arribamos -en este caso- a una interface con 2 bases de datos sobre las cuales se pueden realizar tareas INSERT/UPDATE/DELETE, etc que impactarán de inmediato en la contigua.
La replicación supone un mecanismo para mantener contenidos alojados en Bases de Datos en sincronía y es, por lo tanto, una estrategia de valor a la hora de escalar. Mysql ofrece la posibilidad de Replicar de una Base de Datos llamada Master a otra Slave. Este modelo, como comentamos en el artículo en que desarrollamos dicha metodología, tiene sus limitaciones y sus beneficios. Las limitaciones se dan en el campo de la funcionalidad de un Slave, que sólo permite operaciones de lectura (SELECT) dejando los writes a cargo del Master. Sin embargo, es bien sabido que en sitios de alto tráfico un alto porcentaje de la interacción con la Base de datos es de tipo lectura: los usuarios se pasean por nuestro sitio y eventualmente escriben la Base de Datos.
Es así que tener Servidores de Bases de Datos que sólo permitan lectura puede ser una buena estrategia dado que se podría, por ejemplo, direccionar todo el tráfico de usuarios no conectados a esos servidores liberando a los servidores encargados de escribir en Base de Datos del groso del tráfico. Recordemos que un Master puede tener tantos Slaves como podamos colocarle.
Claro está que con el tráfico llegará la necesidad de poder responder a interacciones write con la Base de Datos lo cual supondrá poseer mas de una BD en que puedan realizarse INSERTS/UPDATES/DELETES. Si bien Mysql no otorga una solución específica en materia de Replicación para sincronizar Bases de Datos Write, ésto puede realizarse muy facilmente con una vuelta de tuerca a la Replicación Master-Slave. Con el tiempo y el tráfico creciente nos encontraremos con una arquitectura de BDS Write sincronizadas que a su vez tendrán Slaves para lectura. Las posibilidades para hacer HA escalando horizontalmente son varias y la Replicación en Mysql es uno de varios métodos disponibles.
Dichas estas palabras, comencemos con el armado de nuestra arquitectura MASTER-MASTER
Nuestro Escenario
Para este tutorial usaremos 2 boxes con Lampp instalados sobre Debian Squeeze. Estos son los valores con que trabajaremos:
Box Lampp 1 (server1) = 192.168.1.31
Box Lampp 2 (server2) = 192.168.1.32
Base de datos instalada en ambos servers: blog (la BD de un WordPress)
Usuario Mysql = daniel
Clave Mysql = demichele
Asumimos Lampp funcionando con phpmyadmin instalado para disponer de una interface gráfica con que comprobar la replicación así como la base de datos a replicar instalada en los 2 servidores en estado idéntico.
Configuración Box 1 (192.168.1.31)
Comenzaremos con el primer Box modificando el archivo de configuración de mysql ubicado en /etc/mysql/my.cnf. Mi experiencia con replicación me ha enseñado que lo mejor es empezar por poner a andar un Master-Slave y, una vez que éste funciona, realizar el proceso inverso en el Box 2. Internet está plagada de tutoriales en que se muestran los ficheros my.cnf de los boxes involucrados y las sentencias cruzadas de GRANT REPLICATION SLAVE. Lo cierto es que, si uno copia y pega los contenidos de los archivos modificando los valores y corre los GRANT REPLICATION SLAVE, luego la replicación muy posiblemente no funcionará: nos encontraremos en Google buscando por qué obtenemos Slave_IO_Running: No con SHOW SLAVE STATUSG;
Recordemos: la replicación Master-Master es en realidad replicación Master-Slave en 2 sentidos, por lo que comenzar logrando Master-Slave es tener la mitad del trabajo resuelto.
Vamos a la consola:
carp@server1:~$ su root
ingresamos password de root
root@server1:/# pico /etc/mysql/my.cnf
En el archivo my.cnf comenzaremos por quitar la restricción a localhost comentando la línea bind-address = 127.0.0.1 que limita el alcance del servicio a la interface local. Vamos a comentar esta línea con # de modo que quede:
#bind-address = 127.0.0.1
Bajamos un poco más en el archivo dentro de la cabecera [mysqld] hasta llegar a la sección Logging and Replication y buscamos la línea comentada #server-id = 1. Allí modificamos como sigue:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = blog
salvamos los cambios y reiniciamos mysql
root@server1:/# /etc/init.d/mysql restart
Hasta aquí hemos identificado a este servidor con id 1, indicado dónde se encuentra el log del mismo y definido la Base de Datos que nos interesa Replicar hacia el Box 2.
A continuación debemos asignar al usuario mysql (daniel) permisos de Replicación en el Box 2. Volvemos a la consola:
root@server1:/# mysql -u root -p
clave de root mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'daniel'@'192.168.1.32' IDENTIFIED BY 'demichele';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'192.168.1.32';
mysql> FLUSH PRIVILEGES;
mysql> USE blog;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
Obtendremos una tabla como la siguiente. Es recomendable anotar los valores de File y Position dado que los utilizaremos al final del tutorial para sincronizar los 2 Boxes a partir de la posición de los Logs.
Salimos del prompt de mysql quitando el lock a las tablas
mysql> UNLOCK TABLES;
mysql> QUIT;
root@server1:/#
Pasamos al Box 2 (192.168.1.32)
Continuaremos con los pasos del primer tutorial debido a que estamos intentando realizar Replicación Master-Slave como primer paso. Encontraremos, sin embargo, algo nuevo en la configuración de my.cnf del Box 2 que luego repetiremos en el Box 1: el uso de auto_increment_increment y auto_increment_offset, de vital importancia para el correcto funcionamiento de la Replicación Master-Master.
Vamos a la consola del Box 2 a editar la sección Logging and Replication dentro del header [mysqld]:
root@server2:/# pico /etc/mysql/my.cnf
#bind-address = 127.0.0.1
server-id = 2
master-host = 192.168.1.31
master-user= daniel
master-password= demichele
master-connect-retry= 60
auto_increment_increment = 2
auto_increment_offset = 2
binlog_do_db = blog
binlog_ignore_db = base_que_no_quiero_afectar
Guardamos los cambios y reiniciamos Mysql:
root@server2:/# /etc/init.d/mysql restart
Aquí nos encontramos con una primera novedad respecto al tutorial de Replicación Master-Slave. Esta consiste en la necesidad de resolver el escenario para campos autoincrement. Supongamos que tenemos 2 Boxes que pueden escribir vía INSERT y manejan un alto tráfico al punto en que se solicitan 2 INSERTS al mismo tiempo en una tabla con autoincrement. El box que primero reciba el INSERT ocupará el próximo índice disponible y deberá comunicarle al otro/s que la posición está tomada y el incremento debe continuar a partir del valor asignado. Esta "comunicación" no será inmediata dado que siempre existe Lag entre los boxes. Con esto nos hacemos una idea del problema al que nos enfrentamos:
- el box 1 recibe la orden de insert al mismo tiempo que el box 2
- el próximo índice es -digamos- 1000
- el box 1 escribe la Base de datos usando el índice y comunica al box 2 que continue a partir del 1001
- pero cuando la sincronización llega al Box 2, éste ya ha escrito su base de datos utilizando el índice 1000
- al llegar la sincronización nos encontramos con un DUPLICATE KEY ERROR
Los parametros auto_increment_increment y auto_increment_offset configurados debidamente en cada Box nos salvarán de este problema mediante un mecanismo extremadamente intuitivo: el primero seteado en 2 con offset de 2 tramitará los INSERTS en tablas con AI tomando el próximo índice disponible (ejemplo 14) y escribiendo 16 mientras que asignaremos al otro box un offset de 1 de modo que autoincremente naturalmente. En sintesis, el Lag entre boxes deja de importar debido a que cada Base de Datos posee un criterio para tomar índices que evitará la colisión. Más información sobre estos parametros puede ser encontrar en el sitio de Mysql.
Comenzando a trabajar la Replicación Bidireccional
Hasta aquí hemos configurado my.cnf en el Box 1, creado una cuenta de usuario mysql (daniel) con privilegio de Replicar Slave desde el Box 1 en el Box 2 y, por último, configuramos my.cnf del Box 2 para indicarle quien era su Master y proporcionar la información necesaria.
Lo que haremos ahora es darle a la cuenta mysql daniel privilegio para hacer Replication Slave pero desde el Box 2 hacia el Box 1 y, finalmente, editaremos my.cnf del Box 1 para decirle que es Slave del Box 2. En pocas palabras: repetiremos lo hecho hasta aquí pero en sentido inverso.
En la consola del Box 2 (192.168.1.32)
root@server2:/# mysql -u root -p
clave de root mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'daniel'@'192.168.1.31' IDENTIFIED BY 'demichele';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'192.168.1.31';
mysql> FLUSH PRIVILEGES;
mysql> USE blog;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS
Obtendremos un cuadro simil el anterior. Anotemos los valores de File y Position para el Box 2 (los que anotamos antes pertenecían al Box 1). Tenemos 2 cuentas mysql con el user daniel cruzadas que le permiten Replicar a los slave definidos. Ha llegado el momento de convertir a nuestro Master (192.168.1.31) en un Slave de 192.168.1.32.
Transformando al Master 1 en Slave
Volveremos a editar my.cnf de Server 1 para darle las mismas instrucciones que le hemos dado en Server 2:
root@server1:/# pico /etc/mysql/my.cnf
#bind-address = 127.0.0.1
server-id = 1
master-host = 192.168.1.32
master-user= daniel
master-password= demichele
master-connect-retry= 60
auto_increment_increment = 1
auto_increment_offset = 2
binlog_do_db = blog
binlog_ignore_db = base_que_no_quiero_afectar
Guardamos los cambios y reiniciamos mysql
root@server1:/# /etc/init.d/mysql restart
Con esto, hemos basicamente creado una arquitectura circular (la topología de la replicación Master-Master es, de hecho, circular). Le decimos al Box 1 que tiene server id 1 y debe trabajar con determinada base de datos. Creamos una cuenta mysql desde el Box 1 en que le damos a un usuario permisos para Replicar en un Slave del Box 2. Le decimos al Box 2 (editando my.cnf) donde está su Master (192.168.1.31). Luego repetimos toda la operación en sentido inverso cuidando que los valores de auto_increment_increment y offset estén debidamente seteados. El resultado: los boxes interactúan como Master y Slave entre sí.
Sincronizando los Boxes
Ahora debemos sincronizar los boxes de modo que cada Servidor sepa a partir de qué log mysql trabajar y desde qué posición comenzar (esta es la información que obtuvimos en las tablas al arrojar la sentencia SQL SHOW MASTER STATUS).
En el box 1 (192.168.1.31) ingresamos a mysql como root:
root@server1:/# mysql -u root -p
Ingresamos clave de root mysql
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.32', MASTER_USER='daniel', MASTER_PASSWORD='demichele', MASTER_LOG_FILE='VALOR_OBTENIDO_EN_BOX2', MASTER_LOG_POS=VALOR_OBTENIDO_EN_BOX2;
mysql> SLAVE START;
mysql> QUIT;
En el Box 2 (192.168.1.32):
root@server2:/# mysql -u root -p
Ingresamos clave de root mysql
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.31', MASTER_USER='daniel', MASTER_PASSWORD='demichele', MASTER_LOG_FILE='VALOR_OBTENIDO_EN_BOX1', MASTER_LOG_POS=VALOR_OBTENIDO_EN_BOX1;
mysql> SLAVE START;
mysql> QUIT;
Por último reiniciamos mysql en ambos boxes vía /etc/init.d/mysql restart y podemos probar en cada box si la replicación está funcionando ingresando como root mysql con:
root@server1:/# mysql -u root -p
Ingresamos clave de root mysql
mysql> SLAVE START;
mysql> SHOW SLAVE STARTG
De la información del SLAVE nos interesa que los valores Slave_IO_Running y Slave_SQL_Running estén en YES en ambos casos. Si todo está ok podemos probar la Replicación ingresando al phpmyadmin de uno de los Servers para agregar un valor y verificar que éste se haya replicado en la BD del otro Server.
Lo que los tutoriales nunca te explican
En mi camino para lograr una buena Replicación Master-Master he leído y probado muchas cosas. El inglés no es -por fortuna- un problema para mi ya que conozco el idioma bastante bien. Este tutorial, al igual que otros que he escrito y seguiré escribiendo es largo. Debo confesar que nada me gusta más cuando ando buscando cómo hacer algo que un tutorial en que se limitan a darme los comandos a utilizar para obtener lo que busco. Sin embargo, algunas cuestiones que revisten una complejidad media/alta como la Replicación merecen una explicación detallada de qué se hace y por qué se hace.
A continuación voy a listar algunas posibles causas por las cuales la replicación suele no funcionar y que no he leído en ningún lado.
1) La cuenta de usuario mysql (daniel) a la que le asignamos permisos para Replicar no es el usuario utilizado para conectar a mysql ni tiene permisos en local.
Esto es tan simple como suena: si estamos usando un user y pass mysql que ya existian y poseían permisos para trabajar con el Blog o Sitio web, entonces usaremos esa misma información cuando hagamos GRANT REPLICATION SLAVE.
Lo cierto es que muchas veces cuando intentamos poner a andar replicación le damos GRANT a un usuario que no existe, creandoló en el acto y luego nos encontramos con que el sitio anda bien pero no hay replicación (ni la habrá dado que el usuario al que se le asignó el privilegio de replicar no es el que se está utilizando para interactuar con la BD).
Otra cosa común es que al correr la sentencia mysql GRANT REPLICATION SLAVE ON *.* TO 'daniel'@'192.168.1.32' IDENTIFIED BY 'demichele', como hemos hecho en este ejemplo estamos otorgando privilegios a un usuario cuyo ámbito es el otro Box y no el local. Una buena forma de saber si estamos haciendo las cosas bien es intentar loguearse a mysql y phpmyadmin del Box 1 con el usuario en cuestión. Si el usuario y clave no existían, luego de haberlo creado habremos agregado el User Daniel con Host 192.168.1.32 estando en el Box 1 (192.168.1.31).
Para resolver este inconveniente comenzamos por verificarlo. Nos logueamos a mysql como root y corremos SELECT User,Host FROM mysql.user; Mysql responderá con una tabla en que se listarán los Usuarios y Hosts en que tienen incumbencia. Si la combinación de User: Daniel Host: localhost no se encuentra, entonces debemos crearla para poder operar en cada box con este nuevo usuario (porque el usuario debe tener los privilegios necesarios para conectarse a la aplicación en localhost y, además, los agregados para Replicar Slave en Box con que se conecta).
Agregar el usuario a localhost es tan simple como:
root@server1:/# mysql -u root -p
clave de root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> QUIT;
Realizada esta operación el usuario tiene ALL PRIVILEGES en localhost + REPLICATION SLAVE y ALL PRIVILEGES en el server remoto. Esta operación debe ser realizada en ambos boxes
2) No hay comunicación entre los servidores porque el puerto 3306 está cerrado en el Firewall.
Comentamos la línea bind-address en los ficheros my.cnf de cada servidor para permitir a mysql trabajar con otras máquinas en la red (o fuera de ella). Sin embargo, esto puede no ser suficiente y a veces necesitaremos abrir el puerto con iptables. Diagnosticar la comunicación entre boxes es muy sencillo: tras haberle dado GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'192.168.1.32'; desde el Box 1, lo siguiente debería ser posible:
root@server1:/# mysql -u daniel -h 192.168.1.32 -p
Ingresamos la clave demichel
Si obtenemos el prompt mysql tenemos conectividad. Si en cambio obtenemos un mensaje del tipo ERROR 1130 (HY000): Host 'server1' is not allowed to connect to this MySQL server tendremos que abrir el puerto en el Firewall del siguiente modo:
root@server1:/# /sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
root@server1:/# iptables-save
3) GRANT ¿a quién y sobre qué?
Muchos tutoriales sobre Replicación se despachan a la hora de otorgar privilegios para Replicar Slave con sentencias del tipo:GRANT REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY 'clave';
Esto se leería -con un poco de humor- más o menos así: "Dale permisos para replicar slave al user en todas las bases de datos en todos los servidores". En este tutorial hemos utilizado la expresión *.* porque sólo poseemos una base de Datos con que trabajaremos. Contamos además con las bases de datos que mysql y phpmyadmin ha instalado pero optamos por excluirlas en los ficheros my.cnf haciendo uso de binlog_ignore_db que puede utilizarse tantas veces como sea preciso para no incluir en la Replica algunas base de datos.
Si estuvieramos en un entorno en que trabajamos con VirtualHosts y contamos con una cantidad considerable de Bases de Datos, asignarle a un usuario permisos para Replicar Slave en todas ellas sería una mala idea. Es así que una sentencia de esta clase se adaptaría mejor:
GRANT REPLICATION SLAVE ON mibasededatos TO 'user'@'%' IDENTIFIED BY 'clave';
Por último el comodín % es también innecesario desde que uno puede o bien querer apuntar a un Host remoro como lo hemos hecho, o bien a localhost. Nuevamente una sentencia más discreta:
GRANT ALL ON mibasededatos TO 'user'@'localhost' IDENTIFIED BY 'clave';
O bien definir la IP del server destino, como lo hemos hecho aquí.
Eso es todo por ahora, la Replicación es un tema que da para mucho y éste -con seguridad- no será el último capitulo.
Artículo publicado originalmente en mi Blog: http://www.danieldemichele.com.ar/2011/11/25/replicacion-master-master-en-mysql/