Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [SCRIPT] Mysql backup ======

====== 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:
<code>
server-id               = 1
log_bin                 = /home/mysql/log/mysql-bin.log
expire_logs_days        = 5
max_binlog_size         = 200M
</code>
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:
<code>
GRANT SELECT, RELOAD, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* to backup_user@localhost identified by 'dummypassword' ;
</code>
===== Cuerpo del script =====
<file bash 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

</file>

===== Variables de interes =====
Las variables que hay que cambiar para que esto funcione:
^ Nombre de variable ^ Valor por defecto ^ Descripcion ^
|BACKUPUSER | <code>backup_user</code> | Usuario que realiza el backup |
|BACKUPPASS|<code>dummypassword</code> |Password que se usará para conectar a la bbdd |
|BACKUPPATH|<code>/home/backup/mysql</code> | Destino de los dumps |
|IGNOREDDBB| <code>"Database|mysql|information_schema"</code> |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|<code>root:admins</code>|Usuario y grupo a los que pertenecerá el backup cuando finalice | 
|MYCNF|<code>/etc/mysql/my.cnf</code>|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:
<code>
nice --adjustment=19 $ZIP $ZIPOPS $BACKUPPATH/$i-$MYDATE.sql
</code>


====== 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:
  - Crear una nueva bbdd
  - Importar en esta bbdd
  - 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í:
<code>
grep "CHANGE MASTER TO MASTER_LOG_FILE" database.sql
</code>
que debería decirnos algo como:
<code>
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000210', MASTER_LOG_POS=36793426;
</code>

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:
<code>
mysqlbinlog --database=DATABASE_NAME --start-position=MASTER_LOG_POS mysql-bin.xxxxxx > DATABASE_NAME.recover.sql
</code>
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:
<code>
mysql -u root -p DATABASE_NAME < DATABASE_NAME.recover.sql
</code>

===== Backup de permisos =====
Adicionalmente se realiza un backup de los grants de los usuarios en el fichero:
<code>$BACKUPPATH/grants-$MYDATE.sql</code>