Este pequeño script sirve para hacer un backup de las bbdd's que tengamos en mysql en ficheros independientes.

Lo más conveniente siempre es tener habilitado tanto el “binary logs” como el “server-id”, aunque ambas opciones son comunmente usadas para la replicación de mysql, en nuestro caso intentamos obtener un backup consistente y minimizar la pérdida de datos.

Editar el my.cnf correspondiente a tu distribución y habilitar las siguientes opciones:

server-id               = 1
log_bin                 = /home/mysql/log/mysql-bin.log
expire_logs_days        = 5
max_binlog_size         = 200M

Con esto tenemos un “master”, pero sin slave, por supuesto y la restauración la podremos hacer casi atómica.

Para ejecutar un backup no hace falta usar root, solo los siguientes privilegios:

GRANT SELECT, RELOAD, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* to backup_user@localhost identified by 'dummypassword' ;

backup_mysql.sh

#!/bin/bash
BACKUPUSER="backup_user"
BACKUPPASS="PASSWORD"
BACKUPPATH="/home/backup/mysql"
MYDATE="$(date +%Y%m%d)"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
MYCNF="/etc/mysql/my.cnf"
ZIP="/bin/bzip2"
ZIPOPS="-9f"
IGNOREDDBB="Database|mysql|information_schema"
 
DBLIST=""
CHMOD="440"
CHOWN="root:admins"
 
BINLOGINDEX="/home/mysql/log/mysql-bin.index"
 
list_databases()
{
        DBLIST="`echo "show databases ;" | $MYSQL -u "$BACKUPUSER" --password="$BACKUPPASS" | egrep -v "$IGNOREDDBB"`"
         return 1
}
 
dump_databases()
{
        for i in $DBLIST
        do
                $MYSQLDUMP --master-data=2 -u "$BACKUPUSER" --password="$BACKUPPASS" $i > $BACKUPPATH/$i-$MYDATE.sql
                $ZIP $ZIPOPS $BACKUPPATH/$i-$MYDATE.sql
        done
        return 1
}
 
dump_grants()
{
        mysql -p$BACKUPPASS --batch --skip-column-names --execute="SELECT DISTINCT CONCAT('SHOW GRANTS FOR ',user,'@\'',host,'\';') AS query FROM user" mysql | mysql -p$BACKUPPASS --batch --skip-column-names mysql | perl -p -e '$_ =~ s/$/;/; END { print "FLUSH PRIVILEGES;n" }' > $BACKUPPATH/grants-$MYDATE.sql
        $ZIP $ZIPOPS $BACKUPPATH/grants-$MYDATE.sql
}
 
 
binlog_backup()
{
        local let LINES=$(cat $BINLOGINDEX | wc -l)
        let LINES--
        tar cjfv $BACKUPPATH/MYSQL_BINLOGS-$MYDATE.tar.bz2 $(head -$LINES $BINLOGINDEX | xargs)
}
 
 
 
purge_binlogs()
{
        local LOGBIN="$(cat $MYCNF | grep -v ^# | grep log_bin | awk -F= '{print $2}')"
        local BINLOGNAME="$(basename $LOGBIN | awk -F. '{print $1}')"
        local BINLOGPATH="$(dirname $LOGBIN)"
        local let MINAGE="$(cat $MYCNF | grep -v ^# | grep expire | awk -F\= '{print $2}')"
        let MINAGE=$((${MINAGE}+2))
        local LASTBINLOG="$(find $BINLOGPATH -mtime +$MINAGE -name "*$BINLOGNAME*" | tail -1)"
        if [[ "$LASTBINLOG" ]]
        then
                local LASTBINLOG="$(basename $LASTBINLOG)"
                echo "PURGE BINARY LOGS TO "$LASTBINLOG";" | $MYSQL -u "$BACKUPUSER" --password="$BACKUPPASS"
        fi
}
 
list_databases
dump_databases
dump_grants
purge_binlogs
 
find $BACKUPPATH -type f -exec chmod $CHMOD {} ;
find $BACKUPPATH -type f -exec chown $CHOWN {} ;
 
exit 0

Las variables que hay que cambiar para que esto funcione:

backup_user

dummypassword

/home/backup/mysql

"Database|mysql|information_schema"

root:admins

/etc/mysql/my.cnf

El script dejará en el directorio destino un fichero por cada bbdd que exista en mysql exceptuando las de la lista “IGNOREDDBB”. Si no se quiere comprimir el dump para “salvar” CPU, eliminar la lista:

nice --adjustment=19 $ZIP $ZIPOPS $BACKUPPATH/$i-$MYDATE.sql

Quizás la parte más compleja de los backups es la restauración, al menos en este caso puede dar más de un quebradero de cabeza. Intentaré que sea rápido y usar los binlogs.

Sin binlogs. Dependiendo de qué nos hayamos cargado (o el DBA), si lo que se ha roto es una tabla, lo mejor que se puede hacer es:

1. Crear una nueva bbdd
2. Importar en esta bbdd
3. Hacer un drop table de la bbdd rota y un “INSERT … FROM”

Si lo que se ha roto es la bbdd entera, entonces nos da igual, DROP & IMPORT.

Con binlogs.

Parto del final del punto anterior, es decir, acabamos de recuperar una bbdd, ya sea en una bbdd aparte o en la misma bbdd que había petado. Tomando este dump, hay que localizar la linea que nos da el identificador de movimiento que necesitamos para terminar la restauración, se puede localizar así:

grep "CHANGE MASTER TO MASTER_LOG_FILE" database.sql

que debería decirnos algo como:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000210', MASTER_LOG_POS=36793426;

Hay que localizar el mysql binlog que nos ha reportado antes. Para obtener los datos del mismo, existe la utilidad “mysqlbinlog”, que podemos usar como sigue:

mysqlbinlog --database=DATABASE_NAME --start-position=MASTER_LOG_POS mysql-bin.xxxxxx > DATABASE_NAME.recover.sql

Con esto tenemos en el fichero DATABASE_NAME.recover.sql todos los movimientos de la bbdd desde que se realizo el backup hasta el momento de ejecutar el comando. Si queremos recuperar hasta un movimiento anterior al actual (por que se haya ejecutado alguna orden de manera deliberada), lo único que se puede es buscar esta orden en el fichero y cortarlo “a mano”

Cuando tengamos el fichero final, solo hay que ejecutar una recuperación normal:

mysql -u root -p DATABASE_NAME < DATABASE_NAME.recover.sql

Adicionalmente se realiza un backup de los grants de los usuarios en el fichero:

$BACKUPPATH/grants-$MYDATE.sql