Случайно удалили продакшн базу? Что дальше?

Недавно в RSS пришла статья, которую хотел оформить переводом — So you just deleted your production database — what now?. Однако комментарии к статье, да и последний абзац заставили задуматься — а на сколько просто восстановить удаленную базу данных.
И дабы не плодить непроверенной информации — перевод перевоплотился в исследование метода восстановления информации из случайно удаленной базы MySQL.

Тестовое окружение

Под руки попалась виртуалка с CentOS 5.6 x86_64 и mysql 5.0.77
Было создана тестовая база с парой таблиц, как MyISAM так и InnoDB. И пару stored процедур, чтобы проверить их восстановление:

DROP DATABASE prod;
CREATE DATABASE prod;
USE prod;
CREATE TABLE table1 (
 id INTEGER,
 v VARCHAR(50),
 PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE table2 (
 id INTEGER,
 v VARCHAR(50),
 PRIMARY KEY (id)
) ENGINE=InnoDB;

DELIMITER //

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
//

DELIMITER ;

CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');

* This source code was highlighted with Source Code Highlighter.

Ну и создать туда немного данных:

( for i in $(seq 1 100); do echo "insert into table1 values ($i, '`md5sum <<< "$i"`');"; done; ) >> test.sql
( for i in $(seq 1 100); do echo "insert into table2 values ($i, '`md5sum <<< "$i"`');"; done; ) >> test.sql

Сценарий тестирования выбран очень простой чтобы рассмотреть именно принципиальную возможность восстановления.

Пушной зверек подкрался незаметно

После того как мы все создали и проверили что база отвечает и содержит некую информацию:

mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
|      100 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from table2;
+----------+
| count(*) |
+----------+
|      100 | 
+----------+
1 row in set (0.00 sec)

mysql> select hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  | 
+----------------+
1 row in set (0.00 sec)

Эмулируем уборщицу со швабройудаление базы с помощью простой команды:

# rm -rf /var/lib/mysql/* 

Согласно советам из статьи мы не перезагружаем сервер и не останавливаем mysql чтобы остались открытыми файловые дескрипторы (иначе информация будет потеряна и для восстановления может понадобится прямое вмешательство в ФС).
Сразу можно проверить что описанное по ссылке выше не работает хотябы потому, что сокет был удален:

# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Если попробовать приконнектится через tcp, то нас все равно ждет разочарование, так как mysql уже ничего не знает о базах и mysqldump будет отдавать пустой вывод:

# mysql --protocol tcp <<< "show databases;"
Database
information_schema
# mysqldump --protocol tcp -A
-- MySQL dump 10.11
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version	5.0.77

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Иногда также mysqldump может ругаться что не может записать в таблицу mysql.time_zone_name:

mysqldump: Couldn't execute '/*!40103 SET TIME_ZONE='+00:00' */': Table 'mysql.time_zone_name' doesn't exist (1146)

, но это решается параметром --skip-tz-utc

Восстановление данных

Итак углубимся немного в то, как MySQL хранит информацию о базах. База в понятиях MySQL это директория внутри которой хранятся определения таблиц, индексы и данные (для случая с InnoDB в директории хранится только определение таблицы, а данные хранятся в отдельном файле). Чтобы MySQL увидел нашу базу — ему достаточно увидеть директорию внутри /var/lib/mysql/
Чтобы получить таблицы и данные внутри базы — мы должны восстановить все файлы которые там были.
Процедуры и функции не хранятся в основной базе, а лежат в базе mysql в таблице proc — поэтому эту базу тоже нужно будет восстановить.
Задача облегчается тем, что процесс mysqld запущен и держит открытые файловые дескрипторы на удаленные файлы, и система не удалит файл пока дескриптор не закроется. Файловая система /proc предоставляет доступ к этим файлам через линки в /proc/[pid]/fd/*

# ls -l /proc/2544/fd/
total 0
lr-x------ 1 root root 64 Jun 22 12:05 0 -> /dev/null
l-wx------ 1 root root 64 Jun 22 12:05 1 -> /var/log/mysqld.log
lrwx------ 1 root root 64 Jun 22 12:05 10 -> socket:[9786]
lrwx------ 1 root root 64 Jun 22 12:05 11 -> /tmp/ibo0UVMZ (deleted)
lrwx------ 1 root root 64 Jun 22 12:05 12 -> socket:[9787]
lrwx------ 1 root root 64 Jun 22 12:05 13 -> /var/lib/mysql/mysql/host.MYI (deleted)
...
lrwx------ 1 root root 64 Jun 22 12:05 28 -> /var/lib/mysql/prod/table1.MYI (deleted)
lrwx------ 1 root root 64 Jun 22 12:05 29 -> /var/lib/mysql/prod/table1.MYD (deleted)
...

Воспользуемся этим чтобы найти и восстановить имена баз, отфильтруем только директории внутри /var/lib/mysql/ и создадим их на прежнем месте:

# ls -l /proc/2544/fd/ | grep /var/lib/mysql/ | cut -d' ' -f11 | cut -d/ -f 5,6 | grep / | cut -d/ -f1 | sort -u
mysql
prod
# ls -l /proc/2544/fd/ | grep /var/lib/mysql/ | cut -d' ' -f11 | cut -d/ -f 5,6 | grep / | cut -d/ -f1 | sort -u | xargs -I{} mkdir -v /var/lib/mysql/{}
mkdir: created directory `/var/lib/mysql/mysql'
mkdir: created directory `/var/lib/mysql/prod'

mysql теперь видит базы:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| prod               | 
+--------------------+
3 rows in set (0.00 sec)

Но mysqldump будет все равно экспортировать пустоту. Постараемся это исправить, восстановим остальные файлы которые еще открыты процессом. Для этого сделаем ссылки из /var/lib/mysql/ на файлы в /proc:

# ls -l /proc/2544/fd/ | grep /var/lib/mysql/ | cut -d' ' -f9,11 | awk '{cmd="ln -s /proc/2544/fd/"$1" "$2;print(cmd);system(cmd);}'
ln -s /proc/2544/fd/13 /var/lib/mysql/mysql/host.MYI
ln -s /proc/2544/fd/14 /var/lib/mysql/mysql/host.MYD
...
ln -s /proc/2544/fd/3 /var/lib/mysql/ibdata1
...
ln -s /proc/2544/fd/38 /var/lib/mysql/prod/table1.MYD
ln -s /proc/2544/fd/9 /var/lib/mysql/ib_logfile1
...

После этой операции mysqldump все равно возвращает пустоту, а если попросить заэкспортить определенную таблицу — то будет ругаться что такой таблицы не существует:

# mysqldump --protocol tcp --skip-tz-utc prod table2
mysqldump: Couldn't find table: "table2"

# mysql --protocol tcp
mysql> use prod;
Database changed

mysql> show tables;
Empty set (0.00 sec)

Проблема заключается в том, что файлы описания таблиц не постоянно открыты процессом и обращение к ним идет редко, соответственно в предыдущих шагах «восстановить» эти файлы не получилось. Решение о наличии той или иной таблицы для экспорта принимается на основании файлов описания таблиц, поэтому автоматический экспорт потерпел крах.

Однако отсутствие файла описания не препятствует прямому получению данных из таблиц:

mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
|      100 | 
+----------+
1 row in set (0.00 sec)

mysql> select * from table1 limit 0,5;
+----+-------------------------------------+
| id | v                                   |
+----+-------------------------------------+
|  1 | b026324c6904b2a9cb4b88d6d61c81d1  - | 
|  2 | 26ab0db90d72e28ad0ba1e22ee510510  - | 
|  3 | 6d7fce9fee471194aa8b5b6e47267f03  - | 
|  4 | 48a24b70a0b376535542b996af517398  - | 
|  5 | 1dcca23355272056f04fe8bf20edfce0  - | 
+----+-------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from table2 limit 0,5;
+----+-------------------------------------+
| id | v                                   |
+----+-------------------------------------+
|  1 | b026324c6904b2a9cb4b88d6d61c81d1  - | 
|  2 | 26ab0db90d72e28ad0ba1e22ee510510  - | 
|  3 | 6d7fce9fee471194aa8b5b6e47267f03  - | 
|  4 | 48a24b70a0b376535542b996af517398  - | 
|  5 | 1dcca23355272056f04fe8bf20edfce0  - | 
+----+-------------------------------------+
5 rows in set (0.00 sec)

Но так как таблицы могут иметь блобы и сложную структуру, то такой метод получения данных не представляется удобным. Еще и потому что InnoDB таблицы хранят в директории с базой только файл описания, который не восстанавливается в нашей процедуре — поэтому выборка из них возможна только если вы знаете на память все имена таблиц.
Последний метод может помочь «спасти» некое небольшое количество важной информации в случае аврала.

Выводы

Возможность полного восстановления базы описаная в статье оказалась неработоспособной (busted!).
Не стоит доверять всем волшебным методам восстановления информации которые мелькают на просторах сети. Делайте бекапы, составьте план восстановления и всегда проверяйте возможные сценарии восстановления до того как произойдет непоправимое.