User Tools

Site Tools


script_backup_mysql

Descripcion

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

Instrucciones

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.

Configuración de my.cnf

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.

Creacion de un usuario para backups

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' ;

Cuerpo del script

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

Variables de interes

Las variables que hay que cambiar para que esto funcione:

Nombre de variable Valor por defecto Descripcion
BACKUPUSER
backup_user
Usuario que realiza el backup
BACKUPPASS
dummypassword
Password que se usará para conectar a la bbdd
BACKUPPATH
/home/backup/mysql
Destino de los dumps
IGNOREDDBB
"Database|mysql|information_schema"
Listado de las bbdd que el script ignorará al realizar el backup, estas bbdd no se copiarán, cada bbdd separada por una pipe
CHOWN
root:admins
Usuario y grupo a los que pertenecerá el backup cuando finalice
MYCNF
/etc/mysql/my.cnf
Ruta hasta el fichero de configuración de mysql

Informacion Adicional

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

Restauración

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.

Recuperación standard

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.

Recuperación avanzada

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

Backup de permisos

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

$BACKUPPATH/grants-$MYDATE.sql
script_backup_mysql.txt · Last modified: 2012/03/30 19:01 by dodger