Accidentally deleted production base? What's next?

Recently, an article came to RSS that I wanted to issue a translation - So you just deleted your production database - what now? . However, comments on the article, and the last paragraph made us think - and how easy is it to restore the deleted database.

And in order not to produce unverified information - the translation turned into a study of the method of recovering information from a randomly deleted MySQL database.

Test environment

I got a virtual player with CentOS 5.6 x86_64 and mysql 5.0.77

A test database was created with a pair of tables, both MyISAM and InnoDB. And a couple of stored procedures to check their recovery:

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 .

Well, create there some data:

 ( 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 

The test script is selected very simple in order to consider the principal possibility of recovery.

Fur animal crept unnoticed

After we have all created and verified that the database responds and contains some information:

 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) 

We emulate a cleaning lady with a mop removing the base with the help of a simple command:

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

According to the tips from the article, we do not reboot the server and do not stop mysql in order to keep the file descriptors open (otherwise the information will be lost and recovery may require direct intervention in the file system).

You can immediately check that the one described by the link above does not work at least because the socket has been deleted:

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

If you try to connect via tcp, then we will still be disappointed, since mysql already knows nothing about the databases and mysqldump will give an empty output:

 # 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 * /;

Sometimes also mysqldump can swear that it cannot write to the table mysql.time_zone_name:

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

, but this is solved by the --skip-tz-utc parameter

')

Data recovery

So dive a bit into how MySQL stores database information. The base in terms of MySQL is the directory inside which the table definitions, indexes and data are stored (for the case of InnoDB, only the table definition is stored in the directory, and the data is stored in a separate file). For MySQL to see our database, it’s enough to see the directory inside / var / lib / mysql /

To get the tables and data inside the database - we must restore all the files that were there.

Procedures and functions are not stored in the main database, but lie in the mysql database in the proc table — so this database will also need to be restored.

The task is facilitated by the fact that the mysqld process is running and keeps open file descriptors on the deleted files, and the system will not delete the file until the handle is closed. The / proc file system provides access to these files via links in / proc / [pid] / fd / *

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

We use this to find and restore database names, filter only the directories inside / var / lib / mysql / and create them in the same place:

 # 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 now sees databases:

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

But mysqldump will still export the void. We will try to fix it, restore the rest of the files that are still open by the process. To do this, make links from / var / lib / mysql / to files in / 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
 ...

After this operation, mysqldump still returns emptiness, and if you ask to export a specific table, it will swear that such a table does not exist:

 # 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)

The problem is that the table description files are not constantly opened by the process and rarely are accessed; accordingly, it was impossible to “restore” these files in the previous steps. The decision on whether a particular table is available for export is made on the basis of the table description files, so automatic export failed.



However, the absence of a description file does not prevent the direct acquisition of data from the tables:

 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)

But since tables can have blobs and complex structure, this method of obtaining data is not convenient. Also because InnoDB tables store in the base directory only the description file, which is not restored in our procedure - therefore, a selection of them is possible only if you know all the table names for the memory.

The latter method can help "save" some small amount of important information in the case of trawling.

findings

The ability to fully restore the database described in the article was unworkable (busted!).

Do not trust all the magic methods of information recovery that flash on the open spaces of the network. Make backups, make a recovery plan and always check possible recovery scenarios before the irreparable happens.