Backup MySQL vol. 2

In this article, we will follow a different strategy from the first article. This will consist of blocking traffic to the database using iptables and extracting data table by table. With this technique, we will obtain the backup faster than with the traditional method using mysqldump.

The scripts are designed to take an image from an existing slave and hook up another slave from this point. If we wanted to take the data from a master, we would have to modify the line SHOW SLAVE STATUS to SHOW MASTER STATUS.

NOTE: Something very important when taking an image from one of the servers is not to dump or restore the views as they will cause the dump to take longer and the restore may end up breaking the integrity of the table data.

First, we will filter using iptables:

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

The backup script will be as follows:

#! /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"

The restoration script will be this:

#! /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

We remove the firewall rules:

iptables -D INPUT 1
iptables -D OUTPUT 1

This way, the backup or the start-up of another slave will be much faster than with mysqldump ;)