The study of data recovery method from accidentally deleted database of MySQL.

Testing environment

Here are CentOS 5.6 x86_64 and mysql 5.0.77
There was created testing database with a couple of tables as MyISAM and InnoDB, as well as a couple of stored procedures to test 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) DETERMINIS TIC
RETURN CONCAT ('Hello,', s ,'!');

Let us 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
Testing scenario was chosen very simple in order to consider just the fundamental possibility of recovery.

Once, we have 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)
Let us emulate deleting of database using a simple command:

# Rm-rf / var / lib / mysql / *
According to the advice of this article, we do not restart the server and do not terminate the mysql in order to keep the open file descriptors (otherwise, information will be lost and recovery may require a direct intervention in the FS).
Immediately, we can check that described at the link above does not work, because the socket has been deleted:

# Mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/ var / lib / mysql / mysql.sock' (2)
If we try to connect through tcp, then we still would be disappointed, as the mysql already has no information about 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

/ *! 40 101 SET @ OLD_CHARACTER_SET_CLIENT = @ @ CHARACTER_SET_CLIENT * /;
/ *! 40 101 SET @ OLD_CHARACTER_SET_RESULTS = @ @ CHARACTER_SET_RESULTS * /;
/ *! 40 101 SET @ OLD_COLLATION_CONNECTION = @ @ COLLATION_CONNECTION * /;
/ *! 40 101 SET NAMES utf8 * /;
/ *! 40 103 SET @ OLD_TIME_ZONE = @ @ TIME_ZONE * /;
/ *! 40 103 SET TIME_ZONE = '+00:00' * /;
/ *! 40 014 SET @ OLD_UNIQUE_CHECKS = @ @ UNIQUE_CHECKS, UNIQUE_CHECKS = 0 * /;
/ *! 40 014 SET @ OLD_FOREIGN_KEY_CHECKS = @ @ FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 * /;
/ *! 40 101 SET @ OLD_SQL_MODE = @ @ SQL_MODE, SQL_MODE ='NO_AUTO_VALUE_ON_ZERO' * /;
/ *! 40 111 SET @ OLD_SQL_NOTES = @ @ SQL_NOTES, SQL_NOTES = 0 * /;
/ *! 40 103 SET TIME_ZONE = @ OLD_TIME_ZONE * /;

/ *! 40 101 SET SQL_MODE = @ OLD_SQL_MODE * /;
/ *! 40 014 SET FOREIGN_KEY_CHECKS = @ OLD_FOREIGN_KEY_CHECKS * /;
/ *! 40 014 SET UNIQUE_CHECKS = @ OLD_UNIQUE_CHECKS * /;
/ *! 40 101 SET CHARACTER_SET_CLIENT = @ OLD_CHARACTER_SET_CLIENT * /;
/ *! 40 101 SET CHARACTER_SET_RESULTS = @ OLD_CHARACTER_SET_RESULTS * /;
/ *! 40 101 SET COLLATION_CONNECTION = @ OLD_COLLATION_CONNECTION * /;
/ *! 40 111 SET SQL_NOTES = @ OLD_SQL_NOTES * /;
Sometimes, mysqldump cannot write to a table mysql.time_zone_name:

mysqldump: Couldn't execute '/ *! 40 103 SET TIME_ZONE =' +00:00 '* /': Table 'mysql.time_zone_name' doesn't exist (1146) but this can be done by - skip-tz-utc

Data recovery

Let us take a look at MySQL, how it stores information about databases. MySQL considers the database as a directory, which stores inside the table determinations, indexes, and data (in a directory is stored only the table determinations in the case of InnoDB and the data is stored in a separate file). If we want that MySQL will see our database – it is enough to see the directory inside / var / lib / mysql /
We have to restore all the files that were there in order to get the tables and data inside the database.
Procedures and functions are not stored in the main database, but they are stored in the mysql database in a table proc - so this database must also be restored.
A task gets easier by the fact that the mysqld runs and keeps the open file descriptors for deleted files, and the system does not delete the file until a descriptor will close. File system / proc provides access to these files through the links in / 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)
...
Let us use this to find and recover the database names, just filter out 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 can see databases now:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Mysql |
| Prod |
+--------------------+
3 rows in set (0.00 sec)
However, mysqldump will still export the void. We will try to fix it and restore the other files, which still are open by the process. Let us 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 void, if we ask to export a table - it will indicate that this 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 description table files are not opened by the process and accessing to them is rare, respectively “restore” these files was not possible in the previous steps. The decision if any tables are available for export is made on the basis of description table files, so automatic exports did not work.

However, the lack of description file does not prevent the direct receiving 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 as the tables can have blobs and the complex structure, then this method of receiving data is not convenient. Because InnoDB tables store only description file in a directory with database, which could not be restored in this procedure - so the selection from them is available only if you remember all table names.
The last method can help to “save” some amount of important information in the case of hectic rush.

Summary

The possibility to recover completely the database that is outlined in this article did not work (busted!).
Do not trust all magic methods of information recovery that are posted in the vast net. Let us make backups, make a plan of data recovery and always check the possible scenarios of data recovery before unrecoverable happens.