Backup MySQL vol. 2

En este artículo vamos a seguir una estrategia distinta a la del primer artículo, esta consistirá en bloquear mediante iptables el tráfico a la base de datos y extraer los datos tabla por tabla, mediante esta técnica obtendremos el backup de forma mas rápida que mediante el método tradicional con mysqldump.

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 statusG;")
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 ;)