Backup en MySQL

Todo administrador de sistemas que utilice MySQL se verá forzado a dumpear la base de datos para llevarla a otro servidor con mas recursos o para hacer un backup de la misma, en este artículo explicaré el modo de dumpeo mas sencillo pero también el que mas downtime implica.

Este método es idóneo para servidores que pueden permitirse un downtime en algún momento del día, de este modo programarse el backup mediante CRON a una hora prudente y nadie se enterará de lo ocurrido.

A continuación como backupear y restaurar la base de datos.

Backup de todas las bases de datos:

mysqldump -A -uroot -p'XXXX' > DB.sql

mysql -uroot -p'XXXX' < DB.sql

Solo una base de datos:

mysqldump DB -uroot -p'XXXX' > DB.sql

mysql -uroot -p'XXXX' < DB.sql

Solo una tabla(se puede restaurar en otra base de datos si es necesario):

mysqldump -u root -p'XXXX' DB TABLA > TABLA.sql
mysql -u root -p'XXXX' DB < TABLA.sql

BackUp solo de la estructura:

mysqldump --no-data DB  -uroot -p'XXXX' > ESTRUC.sql
mysql -u root -p'XXXX' DB < ESTRUC.sql

NOTA: Si solo se quiere restaurar una tabla hay que hacer un workaround grepeando las sentencias de inserción que impliquen dicha tabla.

grep 'INSERT INTO `TABLA`' DB.sql > DB_REDUCED.sql
mysql DB -uroot -p'XXXX' < DB_REDUCED.sql

Si andamos cortos de espacio en el servidor MySQL podemos enviar el dump directamente por red:

mysqldump DB -u root -p'XXXX' | gzip -c | ssh root@IP 'cat > ~/DB.sql.gz'

O incluso insertar directamente:

mysqldump DB -uroot -p'XXXX' | ssh root@IP 'cat | mysql DB -uroot -p'XXXX''

Si queremos programar el trabajo digamos a las 12:00 de la noche:

crontab -e
00 00 * * * mysqldump -A -uroot -p'XXXX' > DB.sql

Siguiendo con la línea de artículos sobre backups en MySQL vamos a explicar como realizar los backups mediante extracción de datos de las tablas ya que mediante el método tradicional es un proceso muy lento.

En este caso vamos a seguir otra estrategia, esta consistirá en bloquear mediante iptables el tráfico a la base de datos y extraer los datos tabla por tabla. Los scripts están pensados para sacar una imagen desde un slave ya existente y enganchar otro slave desde este punto, si quisieramos sacar los datos de un master deberíamos de modificar la línea SHOW SLAVE STATUS por SHOW MASTER STATUS.

NOTA: Algo muy importante a la hora de sacar una imagen de uno de los servidores es no dumpear ni restaurar las vistas ya que provocarán que el dumpeo lleve mas tiempo y el restore puede terminar rompiendo la intergridad de los datos de la tabla.

Primero filtraremos mediante iptables:

iptables -I INPUT 1 -p tcp --dport 3306 -j REJECT
iptables -I OUTPUT 1 -p tcp --sport 3306 -j REJECT

El script de backup será el siguiente:

#! /bin/bash

clear

echo -e "----------------------------------------"
echo -e "|   Script BackUp Table based by Kr0m  |"
echo -e "----------------------------------------"

U="root"
P="XXXX"
PATH="/home/backup/mysql"

/bin/mkdir -p $PATH 2>/dev/null
/bin/chown -R mysql:mysql $PATH

# clean old backup
echo -e "-- Removing old backups"
/bin/rm -rf $PATH/*
echo -e "++ Done"
#exit

echo -e "-- Stopping Slave syncronization"
/usr/bin/mysql -u$U -p$P -sre "stop slave"
echo -e "++ Done"
/usr/bin/sleep 1

echo -e "-- Getting Master Position"
POS=$(/usr/bin/mysql -u$U -p$P -sre "show slave status\G;")
echo "$POS" > $PATH/master.pos
echo -e "++ Done"

echo -e "-- Copying server config file"
/bin/cp /etc/mysql/my.cnf $PATH/
echo -e "++ Done"

VISTAS=$(/usr/bin/mysql -u$U -p$P mysql -sre "select table_name from information_schema.tables where table_type='VIEW';")

for DB in $(/usr/bin/mysql -u$U -p$P -sre 'show databases'); do
        if [ $DB != "performance_schema" ] && [ $DB != "information_schema" ] && [ $DB != "mysql" ] && [  $DB != "tmp" ]; then
                echo -e "---------------------"
                echo -e "-- Dumping structure for database: $DB"
                /bin/mkdir -p $PATH/$DB
                /bin/chown -R mysql:mysql $PATH/$DB
                /usr/bin/mysqldump -u$U -p$P $DB -d --opt > $PATH/$DB/$DB.sql
                TABLES=$(/usr/bin/mysql -u$U -p$P $DB -sre "show tables" )
                for TABLE in $TABLES; do
                        esvista=0

                        for VISTA in $VISTAS
                        do
                            if [ $TABLE = $VISTA ]; then
                                esvista=1
                            fi
                        done

                        if [ $esvista = 0 ]; then
                            echo -e "-- Dumping Table: $DB-$TABLE"
                            /usr/bin/mysql -u$U -p$P $DB -sre "set unique_checks=0; set foreign_key_checks=0; select * into outfile '$PATH/$DB/$TABLE.dat' from $TABLE;"
                            echo "++ Done"
                        else
                            echo -e "-- Skiping View: $TABLE done."
                        fi
                done
        fi
done

echo -e "---------------------"
esvista=0
echo -e "-- Enabling Slave syncronization"
/usr/bin/mysql -u$U -p$P -sre "start slave"
echo -e "++ Done"

El script de restauración será este:

#! /bin/bash

clear
echo -e "-----------------------------------------"
echo -e "|   Script Restore Table based by Kr0m  |"
echo -e "-----------------------------------------"

U="root"
P='XXXX'
PATH="/home/backup/mysql"

echo -e "-- Stopping Slave syncronization"
/usr/bin/mysql -u$U -p$P -sre "stop slave"
echo -e "++ Done"
/usr/bin/sleep 1

for DB in $(/bin/ls -l $PATH|/bin/grep -v 'master.pos'|/usr/bin/awk -F " " '{print$9}'); do
    if [ $DB != "information_schema" ] && [ $DB != "mysql" ] && [ $DB != "tmp" ]; then
        echo -e "-- Removing old database"
        /usr/bin/mysql -u$U -p$P -sre 'DROP DATABASE '$DB';' 2>/dev/null
        echo -e "-- Creating database"                                 
        /usr/bin/mysql -u$U -p$P -sre 'CREATE DATABASE '$DB' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;'
        
        echo -e "-- Restoring structure for database: $DB"
        /usr/bin/mysql -u$U -p$P $DB < $PATH/$DB/$DB.sql

        VISTAS=$(/usr/bin/mysql -u$U -p$P mysql -sre "select table_name from information_schema.tables where table_type='VIEW';" )
        TABLES=$(/usr/bin/mysql -u$U -p$P $DB -sre "show tables")
        for TABLE in $TABLES; do
            esvista=0
            for VISTA in $VISTAS
            do  
                if [ $TABLE = $VISTA ]; then
                    esvista=1
                fi
            done
            
            if [ $esvista = 0 ]; then
                echo -e "-- Restoring Table: $DB-$TABLE"
                /usr/bin/mysql -u$U -p$P $DB -sre "set unique_checks=0; set foreign_key_checks=0; load data infile '$PATH/$DB/$TABLE.dat' IGNORE into table $TABLE"
                echo "++ Done"
            else
                echo -e "-- Skiping View: $TABLE done."
            fi
        done
    fi
    echo -e "---------------------"
done

Eliminamos las reglas del firewall:

iptables -D INPUT 1
iptables -D OUTPUT 1

De este modo el backup o la puesta en marcha de otro slave será mucho mas rápida que mediante mysqldump ;)