Realizar una copia de seguridad automática y remota de MySQL

Se trata de realizar realizar un script que haga la copia de seguridad de la base de datos, la comprima y la copie a un ordenador remoto. Este script se añade al cron para que sea una tarea que se repita periodicamente.

Evitar introducir la contraseña al ejecutar mysql y mysqldump

  1. Crea el siguiente archivo ~/.my.cnf [mysql]
    user=mysqluser
    password=secret

    [mysqldump]
    user=mysqluser
    password=secret
  2. Pon permisos de lectura/escritura únicamente al propietario: chmod 600 ~/.my.cnf

Prueba

  • Para probar si funciona podemos ir al shell de mysql: mysql Funciona si entramos directamente sin que se nos pida usuario ni contraseña

Evitar introducir la contraseña del ordenador remoto al copiar con la orden scp

  1. Comprobar que tenemos OpenSSL instalado: ssh -V
  2. Generar un par de claves privada/pública en el ordenador local: ssh-keygen Lo cual creará las claves: ~/.ssh/id_rsa y ~/.ssh/id_rsa.pub
  3. Copiar la clave pública generada al ordenador remoto: ssh-copy-id  -i  ~/.ssh/id_rsa.pub  usuario-remoto@ordenador-remoto.es Esto añadirá la clave pública en el archivo ~/.ssh/authorized-keys del ordenador remoto

Prueba

  • Para probar si funciona podemos ir al shell del ordenador remoto ssh usuario-remoto@ordenador-remoto.es Funciona si entramos directamente sin que se nos pida la contraseña.
  • Si no funcionase podemos probar de ejecutar las siguientes órdenes en el ordenador local: ssh-agent
    ssh-add
    Lo cual hará que la clave esté disponible cuando se necesite.

Crear el script

La copias de seguridad serán subcarpetas AAAAMMDD que se guardarán dentro de la carpeta /home/usuario-local/backups. Luego estas subcarpetas serán duplicadas en el ordenador remoto dentro de la carpeta /home/usuario-remoto/backups

Se volcarán todas las bases de datos a las que tenga acceso el usuario de MySQL, excepto aquellas indicadas en la variable SKIPDATABASES

  1. Crea el script siguiente /home/usuario-local/backups/mysql_backup.sh #!/bin/bash

    DATE=$(date +"%Y%m%d")
    BACKUP_DIR="/home/usuario-local/backups"
    MYSQL=/usr/bin/mysql
    MYSQLDUMP=/usr/bin/mysqldump
    SKIPDATABASES="Database|information_schema|performance_schema|mysql"
    RETENTION=7

    mkdir -p $BACKUP_DIR/$DATE
    databases=`$MYSQL -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

    for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
    done

    find $BACKUP_DIR/* -mtime +$RETENTION -exec rm {} \;

    scp -r "$BACKUP_DIR/$DATE" usuario-remoto@ordenador-remoto:~/backups
  2. Da permisos de ejecución al script: chmod u+x ~/backups/mysql_backup.sh

Prueba

  • Para ver si funciona podemos llamar al script desde la línea de comandos: ~/backups/mysql_backup.sh
  • Comprueba que exista la subcarpeta del día de hoy en la carpeta ~/backups del ordenador local
  • Comprueba lo mismo en el ordenador remoto

Programa la tarea en el cron

  1. Edita el archivo crontab: sudo nano /etc/crontab
  2. Añade la siguiente línea al final del archivo: 0 4 * * *   usuario-local   ~/backups/mysql_backup.sh
  3. Esto hará que cada día a las 04:00 se realice la llamada a nuestro script
  4. Reinicia el cron: sudo service cron restart

Prueba

  • Después de las 04:00 comprueba que exista la subcarpeta de la copia de seguridad tanto en el ordenador local como en el ordenador remoto

Anexo 1: Copiar sólo las bases de datos indicadas

Se volcarán sólo las bases de datos indicadas en DATABASES. De esta forma no es necesario ejecutar la orden mysql

#!/bin/bash

DATE=$(date +"%Y%m%d")
BACKUP_DIR="/home/usuario-local/backups"
MYSQLDUMP=/usr/bin/mysqldump
RETENTION=365
DATABASES="primera-bd
segunda-bd
tercera-bd
"

mkdir -p $BACKUP_DIR/$DATE
for db in $DATABASES; do
echo $db
$MYSQLDUMP --force --opt --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done
find $BACKUP_DIR/* -mtime +$RETENTION -exec rm {} \;
scp -r "$BACKUP_DIR/$DATE" usuario-remoto@ordenador-remoto:~/backups

Anexo 2: Añadir otros archivos a la copia de seguridad

Incluir esta línea después de la creación de la subcarpeta y antes de la copia al ordenador remoto


gzip -c /carpeta/fichero > "$BACKUP_DIR/$DATE/fichero.gz"

Anexo 3: Incluir el usuario y la contraseña de MySQL dentro del script (no recomendable)

Así no sería necesario crear el archivo ~/.my.cnf


MYSQL_USER="mysqluser"
MYSQL_PASSWORD="secret"

databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"

Fuentes

Comentarios

Proinf.net, ©2003-2017 ci 3.1.5 (CC) Esta obra está bajo una licencia de Creative Commons Este software está sujeto a la CC-GNU GPL