¿Requieres de una instalación o configuración de Linux o sus servicios?
¿Un desarrollo WEB empresarial a la medida?
¿Un curso o capacitación a la medida?
Revisa el sitio de SERVICIOS de LinuxTotal

Respaldos en MySQL usando replicaci�n 

Copyright © 2005-2019 LinuxTotal.com.mx
Se concede permiso para copiar, distribuir y/o modificar este documento siempre y cuando se cite al autor y la fuente de linuxtotal.com.mx y según los términos de la GNU Free Documentation License, Versión 1.2 o cualquiera posterior publicada por la Free Software Foundation.

---

Traducido por: sergio.gonzalez.duran@gmail.com
Artículo original en inglés: Live Backups of MySQL Using Replication por Russell Dyer
Parecer ser que el sitio anterior desapareció y existe ahora esta nueva referencia del mismo autor: mysqlresources.com/documentation/replication

Una de las dificultades con una base de datos MySQL grande y activa es la de realizar respaldos limpios sin tener que desconectar el servidor. De otro modo, un respaldo puede alentar el sistema y muy posiblemente pudiera haber inconsistencias con los datos, ya que tablas relacionadas pueden estar cambiando, mientras otra esta siendo respaldada. Si desconectas el servidor MySQL asegura la consistencia en los datos, pero el desconectar y dejar sin servicio por algún momento o más a muchos usuarios puede resultar inaceptable en varios ambientes empresariales, sencillamente se demanda un servicio de la base de datos todo el tiempo. Un simple método alternativo que asegure la confiabilidad en los respaldos sin tener que apagar el servicio MySQL de una manera regular o diaria es configurar la repliación para MySQL.

Típicamente, es una configuración del sistema donde un servidor MySQL, conocido en este contexto como servidor maestro o 'master', hospeda los datos y maneja las solicitudes de los clientes, mientras que otro servidor MySQL (un servidor esclavo 'slave') contiene una copia de los datos y duplica todas las sentencias SQL en donde los datos hayan cambiado del servidor maestro justo cuando estos suceden. Hay muchos usos para la replicación (ejemplo, balanceo de carga - load balancing), pero en lo que concierne a este artículo es usar la replicación para el respaldo de datos. Puedes configurar un servidor por separado para que sea el esclavo (slave) y entonces una vez al día apagar la replicación para realizar un respaldo limpio a partir del servidor esclavo. Cuando termine, la replicación se reinicia y el esclavo automáticamente interrogará al maestro por los cambios de datos que hubiese mientras estuvo fuera de línea. La replicación es una excelente característica y es parte integral de MySQL. Tan solo necesitas configurarla.

El Proceso de Replicación

Antes de explicar como configurar la replicación en MySQL, déjame explicar de manera rápida los pasos que toma MySQL para mantener un servidor replicado. El proceso es diferente dependiendo de la versión de MySQL. Para propósitos de este artículos, mis comentarios serán sobre la versión 4.0 o superior, ya que la mayoría de los sistemas hoy en día usan estás últimas versiones.

Cuando la replicación se está realizando o ejecutando, conforme las sentencias SQL son ejecutadas en el servidor maestro, MySQL las registra en una bitácora binaria, binary log (bin.log) junto con otra bitácora que lleva un número de identificación de posición. El esclavo (slave) a su vez, a través de un proceso de IO, regularmente y muy a menudo lee la bítacora binaria del maestro para cualquier cambio que hubiese. Si encuentra un cambio, copia las nuevas sentencias a su archivo bitácora de relevo (relay.log). Entonces registra la nueva posición del número de identificación en un archivo llamado master.info en el servidor esclavo. El esclavo entonces regresa para checar el binary.log del maestro, usando el mismo proceso de IO. Cuando el servidor esclavo detecta un cambio en su archivo relay.log, a través de un proceso SQL el esclavo ejecuta la nueva sentencia SQL registrada o grabada en el relay.log. Como una medida de seguridad, el esclavo también consulta los datos del servidor maestro. Si la comparación arroja inconsistencias, el proceso de replicación es detenido y un mensaje de error es registrado en la bitácora del esclavo error.log. Si el resultado de la consulta es consistente, el nuevo número de identificación de posición es registrado en un archivo en el esclavo (relay-log.info) y el esclavo espera por otro cambio en el archivo relay.log.

Este proceso puede parecer confuso y complicado a primera vista, pero todo ocurre realmente aprisa, no es un desperdicio significativo en el servidor maestro, y asegura una replicación confiable. También, es sorprendentemente fácil de configurar. Solo requiere unas cuantas líneas de opciones que hay que añadir al archivo de configuración (my.cnf) tanto en el maestro como en el esclavo. Si estás trabajando con un nuevo servidor, necesitarás copiar la base de datos o bases del maestro al esclavo para ponerlo al día. Después es solo cuestión de comenzar el esclavo para que comienze a replicar.

Un usuario para replicar

Hay muy pocos pasos para configurar la replicación. El primero de ellos es establecer una cuenta de usuario que se usará solo para la replicación. Lo mejor es no usar una cuenta ya existente por razones de seguridad. Para crear la cuenta emite una sentencia SQL como la siguiente en el servidor maestro (master server), logueado como root o como un usuario que tenga privilegios de GRANT OPTION:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT
       ON *.*
       TO 'replicador'@'host_esclavo'
       IDENTIFIED BY 'mi_contraseña';

En esta sentencia SQL, el usuario replicador se le concede solo lo necesario para replicar. El nombre del usuario puede ser cualquiera. El nombre del equipo 'host_esclavo' que va entre comilla sencilla o apóstrofe puede ser la dirección IP o el nombre.

En el servidor esclavo (slave server) ingresa la misma sentencia, solo cambia el nombre del host o IP, de tal manera que sea la del maestro. De esta manera, si el maestro llegara a fallar y estuviera fuera de servicio por un tiempo, puedes redireccionar a los usuarios al esclavo por medio de DNS o por cualquier otro método. Cuando el maestro este de nuevo en servicio, puedes entonces usar la replicación para actualizarlo, temporalmente convirtiéndolo en el esclavo del anterior servidor esclavo que ahora sería maestro.

Por cierto, si todavía usas la versión 4.x de MySQL es posible que la sentencia GRANT mencionada arriba no funcione porque estos privilegios no existian en las primeras versiones 4.x y anteriores. Deberás actualizarte a la 5.0 o superior o revisa esta documentación de como actualizar las tablas GRANT.

Configurando los servidores

Una vez que el usuario replicador esta creado en ambos servidores, necesitaremos añadir algunas líneas a la configuración de MySQL tanto en el servidor maestro como en el esclavo. Dependiendo del tipo de sistema operativo, el archivo muy probablemente se llamara my.cnf o my.ini para Linux y Windows respectivamente. En sistemas del tipo Unix (Linux) el archivo de configuración generalmente se localiza en el directorio /etc. En sistemas Windows, esta localizado en c:\ o en c:\Windows. Usando un editor de texto, añade las siguientes líneas al archivo de configuración, bajo el encabezado de grupo [mysqld]

  server-id = 1
  log-bin = /var/log/mysql/bin.log

El número de identificación del servidor (server-id) es un número arbitrario que identifica al maestro. Cualquier número entero es bueno. Un número diferente debe ser asignado al esclavo para mantenerlos en orden. La segunda línea (log-bin) instruye a MySQL a realizar una bitácora binaria en la ruta y archivo indicado. La ruta actual y nombre de archivo dependen de ti, solo asegúrate que el directorio exista y que el usuario mysql sea el propietario, o al menos tenga permisos de escritura el directorio. Sino esta creado el directorio lo siguiente lo resuleve para el ejemplo mostrado.

  #> mkdir /var/log/mysql; chown mysql.mysql /var/log/mysql

Otra cosa a notar, es que el sufijo del archivo ".log" será remplazado automáticamente con un número índice (ejemplo ".000001") y nuevos archivos de log serán creados conforme el servidor sea reiniciado o las bitácoras sean vaciadas.

Para el servidor esclavo o slave, necesitaremos añadir unas cuantas líneas extras al archivo de configuración en /etc/my.cnf. Necesitamos proveer de información de como debe conectarse al servidor maestro, asi como más opciones de bitacorización (archivos log). Añadimos las siguientes líneas en el mismo grupo de encabezado [mysqld] en el esclavo:

  server-id = 2

  master-host = host_maestro,com    # o la IP del servidor maestro
  master-port = 3306
  master-user = replicador
  master-password = mi_contraseña
  
  log-bin = /var/log/mysql/bin.log
  log-bin-index = /var/log/mysql/log-bin.index
  log-error = /var/log/mysql/error.log
  
  relay-log = /var/log/mysql/relay.log
  relay-log-info-file = /var/log/mysql/relay-log.info
  relay-log-index = /var/log/mysql/relay-log.index

Pudiera parecer demasiado, pero es bastante entendible una vez que lo entiendes por partes. La primera línea es la identificación del número del servidor esclavo. Si configuras más de un servidor esclavo, se les da un número distinto a cada uno. Si solo vas a usar la replicación para respaldo de datos, muy probablemente no necesitaras más de un servidor esclavo. El siguiente conjunto de líneas proveen la información sobre el servidor maestro: el nombre del maestro o su IP, el número de puertoque por default es el 3306 para MySQL. Las siguientes dos líneas indican el usuario (creado previamente) y su contraseña para loguearse al servidor maestro.

Los dos últimos grupos de parámetros confuguran la bitacorización (logging). Se inicia la bitácora binaria (log-bin) tal como se hizo en el maestro, solo que esta vez en el esclavo. Este log es el que permite al esclavo y al maestro intercambiar roles, como se mencionó previamente. El log log-bin.index es para grabar el nombre del log binario actual que se está usando, recuérdese que su nombre real será algo como 'bin.000002' conforme el servidor se reinicia o las bitácoras son vaciadas este nombre cambia, y el index registra cual es el que esta en uso. La opción log-error establece una bitácora para registrar los errores. Es importante que tengas y uses esta opción ya que cualquier error en el proceso de la replicación se registrara aqui.

El último grupo de parámetros establecen la bitácora de relay y archivos relacionados mencionados previamente. La bitácora relay hace una copia de cada entrada en el archivo binario del servidor maestro, esto para efectos de rendimiento, la opción 'relay-log-info-file' es el archivo donde se registra posición del esclavo en la bitácora binaria del maestro, y por último 'relay-log-index' es para mantener la pista del nombre actual del archivo relay (por ejemplo relay.00001) en el uso de la replicación.

Copiando las bases de datos y comenzar la replicación

Si estás instalando un nuevo servidor maestro que no tiene datos todavía, entonces ya no hay nada más que hacer, sino reiniciar el servidor esclavo. Sin embargo, si estás configurando la replicación con un servidor existente que ya contiene datos en el, necesitarás entonces realizar un respaldo inicial de las bases de datos y copiar al servidor esclavo. Hay muchos métodos para realizar esto, para nuestro ejemplo, usaremos la utileria mysqldump para crear el respaldo mientras el servidor esta en servicio. Sin embargo, está el problema de mantener la consistencia de los datos en un servidor activo. Considerando el hecho de que una vez que la repliación este funcionado, quizás nunca tengas que sacar fuera de línea tu servidor MySQL para realizar respaldos, bien vale la pena bloquear a los usuarios una última vez para realizar un respaldo limpio y consistente. Ejecutar el servidor maestro para que solo root tenga acceso, podemos resetear la variable max_connections de la siguiente manera:

mysql> SHOW VARIABLES LIKE 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

mysql> SET GLOBAL max_connections = 0;

La primera sentencia SQL realmente noo es necesari, pero queremos conocer cuál es el valor actual de la variable max_connections para cuando la regresemos de nuevo a su valor original sepamos cuanto valía. Con la segunda sentencia establecemos el valor de la variable en 0, y aunque esto sugiere que ya no se podrán realizar más conexiones a la base de datos, una conexión de hecho, es siempre reservada para el usuario root. Por supuesto, esto solo prevendrá de nuevas conexiones pero las existentes previamente prevalecerán. Para ver si hay conexiones activas en este momento puedes emitir el comando:

mysql> SHOW PROCESSLIST;

Para terminar con cualquier proceso o conexión activa usa la sentencia KILL.

Una vez con acceso exclusivo al servidor, utilizar el comando mysqldump es generalmente muy rápido. Escribiremos lo siguiente desde la línea de comandos del shell Linux en nuestro servidor maestro:

#> mysqldump --user=root --password=mi_root_pwd --extended-insert --all-databases --master-data  > /tmp/backup.sql  

Esto creará un archivo de texto ('backup.sql') conteniendo todas las sentecias SQL para crear todas las bases de datos y tablas con datos. La opción --extended-insert creará sentencias INSERT de líneas múltiples y por lo tanto permitirá realizar un respaldo más rápido, esto para minimizar el tiempo en que los servicios de MySQL estarán fuera de línea. La opción --master-data bloquea todas las tablas durante el vaciado para prevenir que los datos sean cambiados, pero por otro lado permite que los usuarios continuen leyendo las tablas. Realmente con acceso exclusivo al servidor (al establecer las conexiones a 0 en el paso previo) esta característica no es necesaria. Sin embargo, esta opción también añade unas cuantas líneas como las siguientes al final del archivo:

--
-- Position to start replication from
--

CHANGE MASTER TO MASTER_LOG_FILE='bin.000846' ;
CHANGE MASTER TO MASTER_LOG_POS=427 ;

Cuando el archivo de respaldo sea ejecutado en el servidor esclavo, estas últimas líneas registrarán el nombre del archivo log binario del maestro y la posición al momento del respaldo, mientras las tablas estaban bloqueadas. Cuando la replicación inicie, revisará estos datos de bitácora y comenzará a registrar las sentencias SQL registradas a partir de este punto. Esto es para asegurar que cualquier cambio de datos mientras el servidor esclavo inicia no se pierda.

Para ejecutar el archivo de respaldo en el servidor esclavo, copia el archivo de respaldo al esclavo, asegúrate de que MySQL esta ejecutándose, después ingresa algo como lo siguiente en el servidor esclavo, desde el shell de Linux:

#> mysql --user=root --password=mi_root_pwd < /tmp/backup.sql

Esto ejecutará todas las sentencias SQL contenidas en el archivo de respaldo, las cuáles incluirán las sentencias CREATE E INSERT necesarias. Una vez que las bases de datos respaldadas estén cargadas en el servidor esclavo, ejecuta la siguiente sentencia SQL mientras estés logueado como root en el monitor de MySQL del esclavo:

mysql> START SLAVE;

Después de que esta sentencia sea ejecutada, el esclavo se conectará con el servidor maestro y obtendrá los cambios recientes que haya perdido desde que se realizó el respaldo. Desde este punto, estará continuamente actualizándose por medio de checar continuamente los logs binarios como se detalló previamente.

Respaldos con replicación

Una vez con la replicación ejecutándose, es muy fácil hacer un respaldo de los datos. Solo necesitas temporalmente detener al esclavo de replicarse ingresando la siguiente sentencia SQL logueándote al monitor de MySQL como root o cualquier otro usuario con privilegios SUPER:

mysql> STOP SLAVE;

El servidor esclavo sabe la psoición en la que se quedó en la bitácora binaria del servidor maestro. Asi que podemos tomarnos nuestro tiempo realizando el respaldo de las bases de datos replicadas en el servidor esclavo. Podemos usar cualquier utilidad de respaldo o método que se prefiera. Cuando el respaldo termine, entraremos la sentencia SQL que reinicia la replicación, como previamente se mencionó, se debe hacer como usuario root de MySQL:

mysql> START SLAVE;

Después de ejecutar la sentencia, debe haber bastante actividad en el servidor esclavo (claro depende de lo ocupado que haya estado el servidor maestro) mientras ejecuta las sentencias SQL que ocurrieron mientras estuvo fuera de línea. En un corto periodo de tiempo debe estar actualizado.

Respaldo automatizado

Si la replicación y el proceso de respaldo estan trabajando apropiadamente, podemos escribir un script de shell simple que detenga la repliación, respalde los datos en el servidor esclavo, y después reinicie la replicación de nuevo en el servidor esclavo. El shell script mencionado deberá ser similar al siguiente:

#!/bin/sh

fecha=`date +%Y%m%d`

mysqladmin --user=root --password=mi_root_pwd stop-slave

mysqldump --user=root --password=mi_root_pwd --lock-all-tables --all-databases > /respaldos/mysql/respaldo-$fecha.sql

mysqladmin --user=root --password=mi_root_pwd start-slave

En este ejemplo, estamos usando la utilería mysqladmin para detener y reiniciar la replicación en el esclavo. En la primera línea, habrás notado que estamos capturando la fecha con la función del sistema date y formateándola de modo 20080929, año, mes, día. Esta variable es usada después junto con mysqldump en el script para alterar el nombre del archivo de salida, uno distinto para cada día. Modifica el nombre y la ruta donde se guardan los respaldos a tus preferencias. Nota que la función date y el código de formato están entre comilla grave (`) y no es un apóstrofe ('). Puedes encontrar más información sobre la función date y respaldos en este otro artículo de LinuxTotal.com.mx.

Este es un script muy simple. Puedes escribir algo más elaborado y por ejemplo verificar por errores. También se le podría agregar compresión al archivo de respaldo, y después mandarla a una unidad de respaldo removible como una cinta o un DVD. Una vez que tengas tu script listo, pruébalo, si funciona correctamente, puedes añadir a tu configuración de crontab o alguna otroa utilidad de planeación de tareas.

Puedes consultar el manual de cron de LinuxTotal.com.mx para más información sobre la programación de tareas.

Conclusión

Replicación es una característica administrativa muy útil en MySQL. Es una excelente forma de asegurarse de buenos respaldos de manera regular. Hay más opciones y sentencias SQL disponibles para repliación de las que pude presentar en este artículo. Las cubro individualmente en mi libro MySQL in a Nutshell. Para sistemas de bases de datos más grandes y activos, quizás sea para ti más conveniente añadir más servidores esclavos para asegurar la protección de los datos. La configuración y conceptos son los mismos para múltiples esclavos como lo es para uno solo. Ahora bien, para sistemas extremedamente activos y bases de datos enormes, puedes considerar comprar una solución de software como la ofrecida por Emic. Su software cuesta un poco, pero hace un excelente trabajo administrando servidores esclavos para manejo de respaldos y balanceo de cargas especialmente.

Comentarios del traductor

He realizado varias instalaciones de repliación de servidores MySQL, algunas veces sucede que el proceso de repliación no quiere comenzar después de seguir todos los pasos de este manual. Lo que debes hacer es resincronizar la bitácoras del esclavo con el maestro. En el servidor esclavo logueate como root y ejecuta lo siguiente:

mysql> CHANGE MASTER TO MASTER_LOG_FILE = 'bin.000001';
mysql> CHANGE MASTER TO MASTER_LOG_POS = 321;

Ahora bien lo anterior es un ejemplo, para obtener los valores reales de tu servidor maestro, emite la siguiente sentencia:

mysql> SHOW MASTER STATUS;

Como última recomendación te recomiendo que instales MySQL Workbench que lo descargas gratis desde mysql.com, esta herramienta gráfico tiene un monitor del status de la replicación, donde puedes ver el estado de tu maestro y servidores esclavos en una sola pantalla.

---

¿Requieres de una instalación o configuración de Linux o sus servicios?
¿Un desarrollo WEB empresarial a la medida?
¿Un curso o capacitación a la medida?
Revisa el sitio de SERVICIOS de LinuxTotal