Automatizar MySQL backups

Vamos aprender como automatizar los bakups de MySQL en un servidor linux y enviar una copa de. seguridad a otro servidor.

Objetivo

Automatizar copias de seguridad de MySQL y externalizar copias en otro servidor.

Preparamos la base de datos

CREATE USER 'USER_NAME'@'localhost' identified by 'PASSWORD';

-- mysql v8
ALTER USER 'USER_NAME'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';  

GRANT ALL PRIVILEGES ON `DATABASE_NAME`.*  TO 'USER_NAME'@'localhost' with grant option;
GRANT PROCESS, SELECT, LOCK TABLES ON *.*  TO 'USER_NAME'@'localhost';

$ nano ~/.credenciales.cnf

Añadimos el usario y contraseña.

[mysqldump]
user=tu_usuario
password=tu_passwd

Securizamos el fichero.

$ chmod 600 ~/.credenciales.cnf

$ mysqldump --defaults-file=~/.credenciales.cnf DATABASE_NAME > ~/backups/DATABASE_NAME.sql

bash script

Vamos a crear un script que construirá el siguiente comando:

mysqldump --defaults-file=~/home/user/.credenciales.cnf dababasename > /home/user/backups/db_`date +\%Y\%m\%d_\%H\%M\%S`.sql

Una vez ejecutado el backup lo subiremos a otro servidor y nos avisará por email (con mandrillapp) de que ha finalizado el proceso.

#!/bin/bash
#
# Don't forget to add grant process to user
# GRANT PROCESS ON *.* TO $user;
#

DAYS_TO_KEEP=30    # 0 to keep forever
GZIP=1             # 1 = Compress
CREDENTIALS='/home/user/.credenciales.cnf'
DB_NAME='database'
BACKUP_PATH='/home/webmaster/backups'
TARGET='user@yourotherserver.com:/home/user/backups'
MANDRILAPP_KEY=''
EMAIL_FROM=info@yourdomain.com
EMAIL_TO=info@youremail.com

#----------------------------------------

# Create the backup folder
if [ ! -d $BACKUP_PATH ]; then
  mkdir -p $BACKUP_PATH
fi

date=$(date +%Y-%m-%d_%H-%M-%S)

if [ "$GZIP" -eq 0 ] ; then
  echo "Backing up database $DB_NAME without compression at $date"
  mysqldump --defaults-file=$CREDENTIALS --databases $DB_NAME > $BACKUP_PATH/$DB-$date.sql
  scp $BACKUP_PATH/$DB-$date.sql $TARGET
else
  echo "Backing up database $db with compression at $date"
  mysqldump --defaults-file=$CREDENTIALS --databases $DB | gzip -c > $BACKUP_PATH/$DB-$date.sql.gz
  echo "Send copy to $TARGET"
  scp $BACKUP_PATH/$DB-$date.sql.gz $TARGET
fi

# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
  echo "Deleting backups older than $DAYS_TO_KEEP days"
  find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
fi

MESSAGE='{"key": "$MANDRILAPP_KEY", "message": {"from_email": "$EAMIL_FROM", "subject": "Backup success", "text": "Backup success!", "to": [{ "email": "$EAMIL_TO", "type": "to" }]}}'

curl -sS -X POST "https://mandrillapp.com/api/1.0/messages/send" --header 'Content-Type: application/json' --data-raw "$MESSAGE"

Cron

$ crontab -e

0 0 * * * mysqldump --defaults-file=/home/USERNAME/.credenciales.cnf tu_db> /home/USERNAME/backup-$(date+ "%Y%m%d_%H%M%S").sql

De lunes a viernes cada hora entre las 08:00 y las 18:00

0 8-18 * * 1-5 webmaster ./home/webmaster/scripts/db-backup.sh > ./home/webmaster/logs/backups.log

Cada día a medianoche.

0 0 * * * 1-5 webmaster ./home/webmaster/scripts/db-backup.sh > ./home/webmaster/logs/backups.log