Distran.org

Ca n’a pas loupé, le dernier post au sujet de la sauvegarde de MySQL est plus que d’actualité.

En effet, hier, suite à une maj de mysql-server:

 # portupgrade mysql-server

MySQL était alors plantée avec le message suivant:

110817 21:02:23 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
110817 21:02:23 InnoDB: The InnoDB memory heap is disabled
110817 21:02:23 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110817 21:02:23 InnoDB: Compressed tables use zlib 1.2.3
110817 21:02:23 InnoDB: Initializing buffer pool, size = 256.0M
110817 21:02:23 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
110817 21:02:23 [ERROR] Plugin 'InnoDB' init function returned error.
110817 21:02:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110817 21:02:23 [ERROR] Unknown/unsupported storage engine: InnoDB
110817 21:02:23 [ERROR] Aborting

110817 21:02:23 [Note] /usr/local/libexec/mysqld: Shutdown complete

110817 21:02:23 mysqld_safe mysqld from pid file /var/db/mysql/tripaxland4thot.pid ended

Etant donné que le service est en production et que les bases sont relativement petites, la restauration fut immédiate:

# rm -rf /var/db/mysql/*
# ls -la /root/_archives/mysql/db.all.201108*
-rw-r--r--  1 root  wheel  3627458 Aug 15 01:01 /root/_archives/mysql/db.all.20110815.sql.gz
-rw-r--r--  1 root  wheel  3639055 Aug 16 01:01 /root/_archives/mysql/db.all.20110816.sql.gz
-rw-r--r--  1 root  wheel  3651217 Aug 17 01:01 /root/_archives/mysql/db.all.20110817.sql.gz
# zcat /root/_archives/mysql/db.all.20110817.sql.gz | mysql
# /usr/local/etc/rc.d/mysql start

Et tout refonctionne 🙂

Avoir une base de données MySQL c’est bien, la sauvegarder c’est mieux.

Voici un petit script permettant de s’éviter des nuits de sueur après un crash de sa DB.

Le délai de conservation est de 90 jours.

#!/bin/sh

DUMP=/usr/local/bin/mysqldump
MYSQL=/usr/local/bin/mysql
OPTS="--opt"
DATE=`date "+%Y%m%d"`
ARCH_DIR=/root/_archives/mysql
DAYS=90

if [ ! -d $ARCH_DIR ]
then
        echo "error: no archive directory. Please create it or change it."
        exit 1
fi

$DUMP $OPTS  --all-databases | gzip > $ARCH_DIR/db.all.$DATE.sql.gz

if [ $? -ne 0 ]
then
        echo "error: check log"
        exit 2
fi

echo "show databases" | $MYSQL -N | while read db
do
        $DUMP $OPTS $db | gzip > $ARCH_DIR/db.$db.$DATE.sql.gz
done

find $ARCH_DIR -type f -name "db.*" -mtime +$DAYS -exec rm {} \;

exit 0

Ce script est à exécuter en crontab pour avoir une sauvegarde journalière par exemple:

tripaxland4thot# crontab -l
# Daily MySQL DUMP
1 1 * * * /root/_tools/dump_mysql.sh  > /tmp/dump_mysql.log 2>&1