MySQL: Replicación de bases de datos en MySQL
Introducción.
El proceso de replicación de una base de datos consiste en replicar las consultas de actualización (tanto DML como DDL) en una base de datos maestra (master) sobre una o varias bases de datos esclavas (slave), de manera que tengamos una copia de las mismas a lo largo del tiempo.
MySQL soporta replicación unidireccional asíncrona, es decir, las consultas de actualización ejecutadas en el maestro son replicadas en los servidores esclavos. Esta replicación se realiza de forma transparente. Además es instantánea si los servidores esclavos están levantandos y en estado de replicación.
Antes de continuar hay que tener claro que las modificaciones de datos siempre deben realizarse en el maestro, si quereis poder modificar los datos en cualquier base de datos tendreis que iros a otras soluciones como MySQL Cluster.
Atención:
El presente documento se centra en un escenario en donde la replicación se realiza desde cero, es decir, el maestro y el esclavo
no estaban configurados previamente para realizar replicaciones.
Debe saber que si por algún motivo hay errores en la replicación hay que realizar otros pasos y tener más conocimientos más profundos de como funciona todo esto..
En estos casos, deberá documentarse más o recurrir a expertos.. si lo desea puede llamarnos.
¿Para qué nos puede interasar replicar bases de datos?
La replicación es útil para:
-
Copia de seguridad:
En condiciones normales, una base de datos replicada de forma correcta es válida como copia de seguridad.
Además se puede realizar copias de seguridad usando un servidor esclavo para así no interferir al servidor maestro.
-
Mejorar la escalabilidad:
Podríamos configurar nuestras aplicaciones para balancear las consultas de lectura (SELECT) entre los servidores replicados.
Podríamos usar herramientas como MySQL Proxy para balancear las consultas de lectura entre los servidores replicados y enviar las consultas de actualización de datos al maestro.
-
Alta disponibilidad:
En aplicaciones y entornos en donde sólo se requieren lecturas, podríamos configurar nuestras aplicaciones para balancear las consultas de lectura (SELECT) entre los servidores replicados de manera que si uno se cae se continue prestando servicio.
Conceptos necesarios de conocer relacionados con la replicación en MySQL
A continuación exponemos algunos conceptos necesarios para comprender algo mejor todo esto…
El Log Binario
El log binario es un archivo binario gestionado por el servidor de base de datos en el que se registran todas las sentencias SQL de modificación de datos o estructura.
En el caso de la replicación es importante saber que cada servidor esclavo se conecta al servidor maestro y le solicita que le envie las sentencias registradas en los logs binarios a partir de una posición, para ello, cada esclavo mantiene un archivo a modo de índice en donde registra la posición actual de la replicación.
Gracias a esto, podemos detener el esclavo (STOP SLAVE)
, que haya un corte de red, etc.. de manera que cuando se vuelva a iniciar la replicación (START SLAVE)
o se reestablezca la comunicación… Pase el tiempo que pase) el esclavo solicitará al maestro todas las sentencias a ejecutar desde su estado actual y las irá ejecutando secuencialmente de manera que en cuestión de segundos ambos servidores tendrán las bases de datos con el mismo contenido y estructura.
Master |
(Solicita Log Binario desde la posición X)
(Devuelve el Log Binario) |
Esclavo |
El log binario por defecto está ubicado en la carpeta <MYSQL_HOME>/data
y se llama igual que la máquina con el sufijo -bin
y un número como extensión.
Ejemplos: pccarlos-bin.00001, pccarlos-bin.00002, pccarlos-bin.00003
Pasos para poner en marcha la replicación
A continuación vamos a exponer los pasos a realizar la replicación de una base de datos bd_autentia
en un único servidor esclavo.
Si quisieramos configurar más esclavos, los pasos a realizar serían los mismos sobre cada uno de los esclavos.
Creamos de un usuario MySQL en el servidor maestro con privilegios de replicación
El servidor esclavo se autenticará frente al servidor maestro como un usuario normal.
Para crear el usuario debemos ejecutar desde la consola de comandos de mysql las siguientes sentencias SQL:
CREATE USER ‘<replication_user>’@'<slave_address>’ IDENTIFIED BY ‘<replication_user_password>’
GRANT REPLICATION SLAVE ON *.* TO ‘<replication_user>’@'<slave_address>’
Con la sentencia anterior el usuario sólo tendría permiso de acceso desde la máquina <slave_address>, en caso de no requerir esta medida de seguridad puedes sustituir el comodin % por el parámetro <slave_address>.
Configuración del servidor maestro
Deberemos agregar las siguientes lineas al final del archivo de configuración del servidor MySQL, por defecto: <MySQL_HOME>/my.ini
# Identificador único del servidor MySQL dentro de todos los servidores implicados en la replicación.
server-id=1
# Al especificar el parámetro log-bin estamos activando el log binario.
# No especificamos un valor para el parámetro de configuración (por defecto será <nombre_maquina>-bin).
log-bin=
# El log binario sólo tendrá las actualizaciones realizadas sobre la base de datos «bd_autentia»
# Si además quisieramos replicar otras bases de datos, duplicariamos este parámetro para cada base de datos.
binlog-do-db=bd_autentia
Configuración del servidor esclavo
Deberemos agregar las siguientes lineas al final del archivo de configuración del servidor MySQL, por defecto: <MySQL_HOME>/my.ini
# Identificador único del servidor MySQL dentro de todos los servidores implicados en la replicación.
server-id=2
# Nombre del archivo binario que almacena las instrucciones pendientes de ejecutar, por defecto: <host_name>-relay-bin.index
relay-log=
# Nombre o dirección IP del maestro.
master-host=<master_address>
# El esclavo se conecta a través de un usuario al maestro. Identificador del usuario
master-user=<replication_user>
# El esclavo se conecta a través de un usuario al maestro. Contraseña del usuario
master-password=<replication_user_password>
# Número de segundos que esperará el esclavo para reintentar conectarse al maestro en caso de una perdida de conexión.
master-connect-retry=50
# Número de reintentos de reconexión
master-retry-count=5000
Realizamos una copia de seguridad de la base de datos del maestro sobre el servidor el esclavo
Desde la consola ejecutamos los siguientes comandos:
- [maestro]:
<MYSQL_HOME>/bin/mysql -u root --password=<contraseña> -e "FLUSH TABLES WITH READ LOCK"
Para limpiar las caches y bloquear el acceso de cualquier aplicacion a la base de datos. - [maestro]:
<MYSQL_HOME>/bin/mysqldump --u root --password=<contraseña> --opt bd_autentia > backup.sql
Realizamos una copia completa de la base de datos en el archivobackup.sql
. - [esclavo]:
<MYSQL_HOME>/bin/mysql --user=root --password=<contraseña> bd_autentia < backup.sql
Para restaurar la copia de seguridad en el esclavo. - [esclavo]:
<MYSQL_HOME>/bin/mysqladmin -u root --password=<contraseña> shutdown
Detenemos el servidor esclavo - [maestro]:
<MYSQL_HOME>/bin/mysqladmin -u root --password=<contraseña> shutdown
Detenemos el servidor maestro (Se desbloquearán las tablas de las bases de datos previamente bloquadas) - [esclavo]:
<MYSQL_HOME>/bin/mysqld-nt --defaults-file="<MYSQL_HOME>\my.ini" MySQL
Iniciamos el servidor el cual tomará la nueva configuración. - [maestro]:
<MYSQL_HOME>/bin/mysqld-nt --defaults-file="<MYSQL_HOME>\my.ini" MySQL
Iniciamos el servidor el cual tomará la nueva configuración.
Probando la replicación
- En el servidor esclavo ejecute el comando
SHOW SLAVE STATUS
y observe que el mensaje que le muestra es un mensaje que indica que está esperando eventos del maestro… - Modifique algo en el maestro y verifique que instantaneamente se replica en el esclavo.
- Detenga el esclavo durante un tiempo, realize cambios (cree tablas, modifique registros..) en el maestro e inicie el esclavo. En cuestion de milisegundos ambas bases de datos deberían de ser iguales.
Otra información
Dentro del directorio <MYSQL_HOME>/bin
existe una herramienta a través de la cuál podemos extraer las sentencias ejecutadas.
Por ejemplo la siguiente sentencia exporta las sentencias ejecutadas durante un determinado dia:
mysqlbinlog ../data/pccarlos-bin.00002 --start-date="2008-03-01 00:00:00" --stop-date="2008-03-02 00:00:00" > statementsSQL.sql
Además, MySQL responde a los siguientes comandos de administración:
SHOW BINLOG EVENTS |
Muestra el contenido de los archivos de logs, es decir las sentencias SQL. |
SHOW BINARY LOGS |
Muestra el nombre y tamaño en bytes de los archivos binarios de logs. |
PURGE BINARY LOGS BEFORE '2008-02-01 21:00:00' |
Elimina los logs desde una determinada fecha |
RESET MASTER |
Borra todos los logs binarios. |
PURGE BINARY LOGS |
Elimina un archivo de log binario. |
SHOW SLAVE STATUS |
Muestra el estado de la replicación. |
|
Comando en donde todos los parámetros son opcionales y a través del cual podemos modificar el origen de la replicación.. (Es útil cuando hay errores) |
Referencias
- http://www.guebs.com/manuales/mysql-5.0/replication.html
- http://www.guebs.com/manuales/mysql-5.0/sql-syntax.html.
- http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html.
Conclusiones
En este tutorial hemos visto de forma sencilla los pasos a realizar para crear bases da datos replicadas en el escenario más simple,.. como todo, hacen falta horas de pruebas para ver hasta donde se puede llegar con todo esto y ver que posibilidades nos ofrece…
Recordar que esto no es un libro.. es un simple tutorial, hay mucho más en donde profundizar…. os lo dejo en vuestras manos 🙂
Carlos García Pérez. Creador de MobileTest, un complemento educativo para los profesores y sus alumnos.
cgpcosmad@gmail.com
Muy buena calidad de tutorial. ¡Se ve de lejos que te gusta la enseñanza!
PERO NO ES LO QUE BUSCABA
BUENA, DIGAME SEÑOR QUE PASAS SI LA BASE DE DATOS ESTA EN UN SERVIDOR, Y LA OTRA EN OTRA MAQUINA, Y QUE PASA SI EL SERVIDOR ESTA APAGADO, Y LOS CLIENTES SE CONECTARIAN ALA MAQUINA QUE TIENE LA OTRA COPIA ?, Y CUANDO SE PRENDA EL SERVIDOR, QUE PASA SE COPIA DE MANERA AUTOMATICA? EXPLIQUEME BIEN ESO.
SOY NOVATO EN ESTO DE REPLICACION.
UNA CONSULTA SABES SI HAY ALGUNA MANERA DE HACER UNA COPIA DE UNA BASE DE MYSQL Y MIGRARLO A POSTGRESQL, PERO LA BASE DE DATOS TIENE REGISTROS, ADEMAS TIENE PROCEDURES Y TRIGGERS Y FUNCTIONS.
SE PODRA MIGRAR TODO ?
EXISTE ALGUN PROGRAMA QUE PERMITA MIGRARLOS, BUENO DEJO MI CORREO :
sistemassistemas@hotmail.com
SALUDOS.
Hola, imaginemos que el master ha caído (avería hardware, por ejemplo) y el servicio lo queremos dar con el esclavo. Entiendo que ejecutamos «stop slave» y apuntamos la aplicación a la IP de la base de datos esclavo.
El técnico de hardware reparara el servidor maestro y queremos volver a la situación normal. ¿Cómo volvemos a girar la réplica? ¿Tenemos que parar la BD esclava, copiarla al master y volver a configurar la réplica?
Gracias.
hola, estoy aprendiendo Mysql,: tengo una tabla con los atributos de la siguiente forma Año, Mes, Total, deseo que realizar una consulta que me traiga los datos de la siguiente manera :
ano, mes1, mes2, mes3, mes4, mes5……
año1, y que en esta parte de muestren los totales
año2
año3