Oops je viens de DELETE une table

By | 12 février 2015

Strip-Bas-ed-eonnée-effacée-650-final1 Ce commitStrip m’a donné une idée d’article. Qui n’a jamais effectué une action irréversible en production pensant être en dev ? L’action à laquelle je pense plus particulièrement est la suppression d’une table, d’une base ou tout autre moment de solitude …

Pas grave je backup

Bien entendu tout le monde backup sa base de données, enfin j’espère, sinon la suite de cet article ne vous intéressera pas. Peu importe le type de backup que vous faites, qu’il soit physique ou logique, en général il sera fait la nuit, en évitant les heures de changement d’heure.

Le problème, c’est que votre DELETE vous l’avez fait à 17h30, avant de rentrer chez vous, et que votre backup date d il y a 12h … Je vous laisse expliquer la suite à votre boss 🙂

Activer les binlog

J’ai pris pour habitude de toujours activer les binlog sur mes serveurs de production, qu’ils aient ou non des slaves (au passage un slave ne doit pas être considéré comme un backup).

Les binlog (binary log) sont des fichiers de log qui contiennent toutes les requêtes DDL (Data Definition Language) et DML (Data Manipulation Language), dont la requête qui va supprimer votre table ;).

Activer les binlog:

$ vim /etc/mysql/my.cnf
  server-id         = 1
  log_bin           = /var/log/mysql/mysql-bin.log
  expire_logs_days  = 2
  max_binlog_size   = 1G
$ service mysql restart

Préparer une base

Je vais récuperer une base de données d’exemple sur le site MySQL, et l’injecter sur ma vm (je vous fournis les commandes si vous souhaitez les rejouer):

$ wget http://downloads.mysql.com/docs/sakila-db.tar.gz
$ tar -xzvf sakila-db.tar.gz
$ mysql < sakila-db/sakila-schema.sql
$ mysql < sakila-db/sakila-data.sql

On backup quand même:

$ mysqldump sakila --master-data=2 > sakila.sql

Vous l’avez certainement remarqué, j’ai utilisé l’option –master-data qui va enrichir notre backup des informations de binlog, pour connaître le fichier et la position du binlog au moment du backup.

Insérons quelques nouvelles données:

mysql> INSERT INTO category(name) VALUES('MySQL'),('PHP'),('NoSQL');

 Oops

Maintenant que notre base est sauvegardée, nous allons effectuer un oops (mais un vrai !):

mysql> DROP SCHEMA sakila;

Mais je ne m’en fais pas pour vous .. vous avez votre backup de ce matin 😀

Commençons par récupérer l’état des binlog au moment du backup et stocker les requêtes dans un fichier à part, en prenant soin d’exclure la requête Oops:

$ head -n30 sakila.sql | grep MASTER
  -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=13108611;

$ mysqlbinlog /var/log/mysql/mysql-bin.000001 --start-position=13108611 
  | grep -v 'DROP SCHEMA sakila' > after_backup.sql

Et maintenant, plus qu’a restaurer votre backup et les binlog:

$ mysql sakila < sakila.sql
$ mysql sakila < after_backup.sql

Votre weekend est sauvé !