Script para generar respaldos MySQL mediante el CRON

Cuando tenemos varias bases de datos en nuestros servidor y ya por algun tiempo, es necesario contar con alguna herramienta que permita generar respaldos de forma automatica, con la finalidad de prevenir inconvenientes con la información que manejamos y poder recuperarnos de la eventualidad lo mas antes posible.

A continuación describiremos los pasos para poder generar nuestro script y asignarlos a las tareas automaticas de nuestro servidor.

1. Preparando todo

Generalmente el servicio de crond viene instalado por defecto en nuestro servidor y es el que se encarga de ejecutar programas, comandos o script en un horario determinado. Para saber el estado en el que se encuentra el servicio escriba la siguiente sentencia:

$ sudo service crond status

En caso de no contar con este servicio, sera necesario su instalación. Seguido sera necesario contar con una carpeta donde ingresaremos nuestros script, en mi caso dentro de la carpeta home

$ mkdir /home/miusuario/ejecutar

Creamos nuestra carpeta donde iremos guardando nuestros respaldos (lo ideal es contar con otro servidor donde tengamos estos respaldos)

$ mkdir /home/miusuario/respaldos

Para nuestro caso sera necesario contar con un usuario en MySQL que tenga los permisos suficientes para poder ejecutar los comando mysql y mysqldump, así como el permiso para generar los respaldos correspondientes a todas las bases de datos.

Verificamos que la fecha y hora de nuestro servidor sea la correcta para no tener algunos resultados no deseados,

$ date
mié ago 10 15:00:01 CDT 2015

y en caso de ser necesario definimos el horario correcto:

$ sudo date --set "2015-08-19 15:12:10"

Por ultimo, cuando ejecutemos el crond se ejecutara el editor por defecto, en la mayoría de los casos el editor vi es el que viene configurado, aquí dependerá de con cual se sienta uno más cómodo. En mi caso habilite el nano, he aquí la instrucción para habilitarlo momentáneamente:

$ sudo export EDITOR="nano"

Si deseamos que sea permanente habrá que agregarlo en el .bashrc o en el .bash_profile del usuario deseado.


2. Generamos nuestro script

Una vez que tenemos todo listo generamos nuestro script en la carpeta deseada (/home/miusuario/ejecutar):

$ nano /home/miusuario/ejecutar/respaldarMySQL.sh


#!/bin/bash

# Configurar variables de entorno
FECHA=`date +%d%m%Y-%H%M%S`
DIRECTORIO="/home/miusuario/respaldos"

# Obtener los nombres de las bases de datos
misbases=`mysql -uusuariomysql -pmiclave --skip-column-names -e'SHOW DATABASES'`

# Crear el respaldo de las bases de datos
for db in $misbases; do
    # Nombre del archivo de respaldo
    archivo="$db-$FECHA.sql.gz"
    if [ $db != "mysql" ] && [ $db != "information_schema" ]; then
        # Crear respaldo, comprimirlo
        # y si fue exitoso guardar una bitacora
        mysqldump -uusuariomysql -pmiclave --databases $db | gzip -9 > \
        $DIRECTORIO/$archivo && echo "Respaldo realizado con exito: \
        $archivo" >> $DIRECTORIO/bitacora.log
    fi
done

Nota: El caracter \ indica que la cadena continua con la linea inferior

Describiremos algunas lineas para que quede mas claro este script:

• La variable FECHA guardara la fecha y hora actual, que se concatenara para formar el nombre del archivo. (%d%m%Y-%H%M%S => 19082015-100001)
• DIRECTORIO sera la ruta donde guardaremos los respaldos
• misbases obtendra todas las bases de datos de nuestro servidor
• archivo tendra la concatenacion de la base de datos y la fecha y hora para formar el nombre de nuestro respaldo
• el if verificara que no sean las bases mysql e information_schema, las cuales no respaldaremos
• Ahora viene lo interesante:
• mysqldump generara el respaldo de cada una de las bases de datos
• el caracter | indica que una vez que se tenga el resultado de mysqldump sera como parametro del siguiente comando (gzip)
• gzip realiza la compresion del respaldo de la bd y con el caracter > se indica que se guardara en el archivo $DIRECTORIO/$archivo, si no existe el archivo se crea uno nuevo y si existe reemplazara al anterior.
• con el caracter && se indica que el siguiente comando se ejecutara solo si el anterior se ejecuto satisfactoriamente
• Si se ejecuto correctamente el respaldo y la compresion, se guarda el registro de que todo fue un exito en el archivo bitacora.log, con el caracter >> se indica que si el archivo bitacora.log no existe se creara uno nuevo y si existe se agregara una nueva linea al final del archivo
• Este proceso se realiza por cada una de las bases de datos

Para probar que funciona este script, simplemente lo ejecutamos y verificamos el resultado, no se olvide cambiar los permisos necesarios para que el archivo sea ejecutable.

3. Creamos el cron

Bueno hasta este momento ya tenemos listo el script  probado, procederemos a definir cuando se ejecutara para que se realicen los respaldos correspondientes, primero aqui una explicación de como se compone un archivo crontab:

.---------------------- Minuto (0 - 59)
| .-------------------- Hora (0 - 23)
| |  .----------------- Día del mes (1 - 31)
| |  | .--------------- Mes (1 - 12)
| |  | | .------------- Día de la semana (0 - 6) (0 = domingo)
| |  | | | .----------- Usuario
| |  | | | |    .------ Script o programa a ejecutar
↓ ↓  ↓ ↓ ↓ ↓    ↓
5 15 * * * user /home/user/ejecutar/miscript.sh
En este ejemplo se ejecutara miscript.sh a las 15:05 todos los días, todos los meses, cada dia de la semana como usuario user. Aqui se listan algunos caracteres especiales que se pueden usar:

• utilice un espacio para separar cada campo
• para varios valores separelos por comas
• para un rango de valores separelos por un guión
• el * se utiliza como comodin para incluir todos los valores
• el # al inicio de la linea se utiliza como comentario

Todos los comandos del archivo crontab deben ir en una sola linea por muy larga que sea esta, ya que no se reconocen los retorno de carros adicionales.

Ahora si creamos nuestro archivo crontab, el cual se ejectuara todos los dias de la semana de todos los meses a la 1:00am para realizar nuestro respaldo:

$ sudo crontab -e -u miusuario
00 1 * * * miusuario /home/miusuario/ejectuar/respaldarMySQL.sh

Guardamos nuestro archivo y listo, tenemos un archivo crontab preparado.

4. Algunos comentarios

Para consultar nuestros archivos crontab, utilicemos la siguiente instruccion

$ sudo crontab -l

Para editar el archivo crontab de algun usuario

$ sudo crontab -e -u miusuario

Los archivos crontab se guardan en la siguiente ruta /var/spool/cron, debe tener privilegios de root para consultar el listado.

5. Actualización (10/01/2019)

Para evitar que en nuestro script queden expuestas las credenciales de nuestro usuario que realizara el respaldo, podemos agregar las lineas de acceso al archivo my.cnf (generalmente esta en /etc/my.cnf) de la configuración del mysql, asi nuestro script solo tendra las lineas sin el usuario:

respaldarMySQL.sh

# Obtener los nombres de las bases de datos

misbases=`mysql --skip-column-names -e'SHOW DATABASES'`

.

.

.

# y si fue exitoso guardar una bitacora

        mysqldump --databases $db | gzip -9 > \

.

.

.

En nuestro archivo my.cnf agregamos las siguientes lineas:

[mysqldump]

# Usuario para el respaldo

user=usuariomysql

password=miclave

[mysql]

# Usuario para la consulta de las bases

user=usuariomysql

password=miclave

Reiniciamos nuestro servicio para que podamos ejecutar nuestro script, y listo.