MySQL uses ext3grep to restore the Myisam table

MySQL does not have a flashback mechanism for Oracle. After you execute drop table xxx, this table is permanently deleted. You can only recover from the backup. If you do not have a backup, then you can only cry. This article provides a way of thinking, so that similar situations can be recovered.

We know that the MYISAM engine table of Mysql is located in the current Database directory and has three corresponding files, frm structure files, MYI index files, and MYD data files. When you drop the MYISAM engine table in Mysql, you actually need to rm the corresponding three files in the file system. Therefore, after you execute drop, if you can restore the above three files, the table will also be restored.

If you are using the EXT3 file system, you can use ext3grep to complete the above operations. For example:

1. Install ext3grep
First you need to install the e2fsprogs-libs, you can go to http://e2fsprogs.sourceforge.net/download the source code package

Tar-xzvf e2fsprogs-1.41.5.tar.gz
Cd e2fsprogs-1.41.5
Mkdir build; cd build
../Configure
Make
Make install
Make install-libs (e2fsprogs-libs)
Install ext3grep at http://code.google.com/p/ext3grep/downloads/list

Tar xfvz ext3grep-0.10.1.tar.gz
Cd ext3grep-0.10.1
./Configure
Make
Make install

Ext3grep-V
Running ext3grep version 0.10.1
Ext3grep v0.10.1, Copyright (C) 2008 Carlo Wood.
Ext3grep comes with absolutely no warranty;
This program is free software; your freedom to use, change
And distribute this program is protected by the GPL.
2. Delete the test table

Mysql> use test
Mysql> desc t;
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Id | int (10) | YES | NULL |
+ ------- + --------- + ------ + ----- + --------- + ------- +
1 row in set (0.01 sec)
 
Mysql> select * from t;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
+ ------ +
3 rows in set (0.00 sec)
 
Mysql> drop table t;
 
Mysql> quit
3. Stop mysql and umount the partition where the data file is located.

#/Etc/init. d/mysqld stop
Shutting down MySQL... [OK]
Cd
# Umount/dev/sda6
4. Use the ext3grep tool for recovery

# Ext3grep/dev/sda6 -- ls -- inode 2
Running ext3grep version 0.10.1
WARNING: I don't know what EXT3_FEATURE_COMPAT_EXT_ATTR is.
Number of groups: 1778
Loading group metadata... done
Minimum/maximum journal block: 29065730/29099045
Loading journal descriptors... sorting... done
The oldest inode block that is still in the journal, appears to be from 1243924942 = Tue Jun 2 14:42:22 2009
Number of descriptors in journal: 418; min/max sequence numbers: 13/36
Inode is Allocated
Loading sda6.ext3grep. stage2... done
The first block of the directory is 1539.
Inode 2 is directory "".
Directory block 1539:
. -- File type in dir_entry (r = regular file, d = directory, l = symlink)
|. -- D: Deleted; R: Reallocated
Indx Next | Inode | Deletion time Mode File name
================= + -------------- Data-from-inode ------ + ----------- + ======== =
0 1 d 2 drwxr-xr-x.
1 2 d 2 drwxr-xr-x ..
2 3 d 11 drwx ------ lost + found
3 4 r 12 rrw-r -- 1
4 5 r 13 rrw-r -- 5
5 end d 237569 drwxr-xr-x data
6 end r 15 D 1243919862 Tue Jun 2 13:17:42 2009 rrw-r -- 3.txt
We can see that the Inode corresponding to the data directory of the Mysql data file is 237569, and then the block corresponding to Inode is viewed.

# Ext3grep/dev/sda6 -- inode 237569
Running ext3grep version 0.10.1
No -- ls used; implying -- print.

Inode is Allocated
Group: 29
Generation Id: 3010341297
Uid/gid: 500/500
Mode: drwxr-xr-x
Size: 4096
Num of links: 4
Sectors: 8 (--> 0 indirect blocks ).

Inode Times:
Accessed: 1243930169 = Tue Jun 2 16:09:29 2009
File Modified: 1243930170 = Tue Jun 2 16:09:30 2009
Inode Modified: 1243930170 = Tue Jun 2 16:09:30 2009
Deletion time: 0

Direct Blocks: 968704
Loading sda6.ext3grep. stage2... done
There is no directory block associated with inode 237569.
View Direct Blocks: 968704. Check the block.

# Ext3grep/dev/sda6 -- ls -- block 968704
Running ext3grep version 0.10.1
WARNING: I don't know what EXT3_FEATURE_COMPAT_EXT_ATTR is.
Number of groups: 1778
Minimum/maximum journal block: 29065730/29099045
Loading journal descriptors... sorting... done
The oldest inode block that is still in the journal, appears to be from 1243925348 = Tue Jun 2 14:49:08 2009
Number of descriptors in journal: 236; min/max sequence numbers: 15/45
Group: 29

Block 968704 is a directory. The block is Allocated

. -- File type in dir_entry (r = regular file, d = directory, l = symlink)
|. -- D: Deleted; R: Reallocated
Indx Next | Inode | Deletion time Mode File name
================= + -------------- Data-from-inode ------ + ----------- + ======== =
0 1 d 237569 drwxr-xr-x.
1 3 d 2 drwxr-xr-x ..
2 3 r 237570 D 1243930170 Tue Jun 2 16:09:30 2009 rrw-r ----- mysql. err
3 4 d 237571 drwx ------ mysql
4 5 r 237641 rrw-r ----- ibdata1
5 7 d 237642 drwx ------ test
6 7 r 237646 D 1243926673 Tue Jun 2 15:11:13 2009 rrw-rw ---- mysql. pid
7 8 r 237647 rrw-r ----- ib_logfile0
8 end r 237648 rrw-r ----- ib_logfile1
Under the data directory, the Inode corresponding to the file is the table under the deleted test. Therefore, check the Inode corresponding to the test directory.

# Ext3grep/dev/sda6 -- inode 237642
Running ext3grep version 0.10.1
No -- ls used; implying -- print.

Inode is Allocated
Group: 29
Generation Id: 3010341370
Uid/gid: 500/500
Mode: drwx ------
Size: 4096
Num of links: 2
Sectors: 8 (--> 0 indirect blocks ).

Inode Times:
Accessed: 1243928606 = Tue Jun 2 15:43:26 2009
File Modified: 1243928606 = Tue Jun 2 15:43:26 2009
Inode Modified: 1243928606 = Tue Jun 2 15:43:26 2009
Deletion time: 0

Direct Blocks: 971511
Loading sda6.ext3grep. stage2... done
There is no directory block associated with inode 237642.

# Ext3grep/dev/sda6 -- ls -- block 971511
Running ext3grep version 0.10.1
WARNING: I don't know what EXT3_FEATURE_COMPAT_EXT_ATTR is.
Number of groups: 1778
Minimum/maximum journal block: 29065730/29099045
Loading journal descriptors... sorting... done
The oldest inode block that is still in the journal, appears to be from 1243925348 = Tue Jun 2 14:49:08 2009
Number of descriptors in journal: 236; min/max sequence numbers: 15/45
Group: 29

Block 971511 is a directory. The block is Allocated

. -- File type in dir_entry (r = regular file, d = directory, l = symlink)
|. -- D: Deleted; R: Reallocated
Indx Next | Inode | Deletion time Mode File name
================= + -------------- Data-from-inode ------ + ----------- + ======== =
0, 1, d, 237642, drwx ------.
1 end d 237569 drwxr-xr-x ..
2 3 r 237643 D 1243928606 Tue Jun 2 15:43:26 2009 rrw-r ----- u. frm
3 4 r 237649 D 1243928606 Tue Jun 2 15:43:26 2009 rrw-r ----- u. MYD
4 end r 237645 D 1243928606 Tue Jun 2 15:43:26 2009 rrw-r ----- u. MYI
5 end r 237644 D 1243926666 Tue Jun 2 15:11:06 2009 rrw-rw ---- t. frm
6 7 r 237650 D 1243926666 Tue Jun 2 15:11:06 2009 rrw-rw ---- t. MYI
7 end r 237651 D 1243926666 Tue Jun 2 15:11:06 2009 rrw-rw ---- t. MYD
OK. Now, the three files of table t that need to be restored have been released and are being restored.

# Ext3grep/dev/sda6 -- restore-inode 237650
Running ext3grep version 0.10.1
Restoring inode.237650
# Ext3grep/dev/sda6 -- restore-inode 237651
Running ext3grep version 0.10.1
Restoring inode.237651

# Ext3grep/dev/sda6 -- restore-inode 237644
Running ext3grep version 0.10.1
Restoring inode.237644

Cd RESTORED_FILES/
Mv inode.237644 t. frm
Mv inode.237650 t. MYI
Mv inode.237651 t. MYD
Ll
Total 20
-Rw-r ----- 1 root 8556 Jun 2 16:26 t. frm
-Rw-r ----- 1 root 21 Jun 2 16:26 t. MYD
-Rw-r ----- 1 root 1024 Jun 2 16:26 t. MYI
Mount the partition where the data file is located, and copy the recovered table t file to the original directory. Set permissions and start mysql.

Mount/dev/sda6/u01
Cp t. */u01/data/test
Chown-R mysql. mysql test/
/Etc/init. d/mysqld start
Starting MySQL. [OK]
Mysql> use test
Database changed

Mysql> select * from t;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
+ ------ +
3 rows in set (0.01 sec)
OK. The table has been restored.