MySQL Restore and Recovery methods

Backup is for sissies! Let's have a look what we can do when we are not a sissy...

First of all: Your life is much easier when you have a proper backup process implemented and verified the restore procedure of your MySQL database.

But what if you have no backup in place and did a DROP TABLE. What shall we do?

We assume our data we just dropped are located on the following device:

# export IMAGE=/dev/hda1

First of all, power off your server. This avoids that the operating system writes data down to disk and overwrites your table you just dropped.

If power off is not possible, try to unmount the file system where the dropped table resides:

# umount -f /mnt/data

If you cannot unmount your disk because somebody or something is sitting on it try to find out who it is:

# lsof /mnt
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
bash    31638  oli  cwd    DIR    7,0     1024    2 /mnt

When you perform a unmount all the blocks belonging to this file system are flushed to the disk first. So you have the possibility that you destroy already part of your data.

If an unmount is not possible try to do at least an image copy of your device:

# dd if=$IMAGE of=/tmp/recovery_image.dd

To recover the data you have to mount the device read-only.

Remount a device read-only:

# mount -o remount,ro $IMAGE

Mount a device read-only:

# mount -o ro $IMAGE /mnt

Mount a the recovery_image read-only:

# mount -o loop,ro recovery_image.dd /mnt

What we have to know is the file system of our device. This can be found for example as follows:

# mount | grep mnt
/dev/loop0 on /mnt type ext3 (ro)

Now we have different possibilities to recover the files:

We were dropping a InnoDB table and were using innodb_files_per_table = 0. The dropped table is still in the InnoDB system tablespace. We have to recover our data with InnoDB recovery tools. This is handled in a later article.

We were dropping MyISAM tables or an InnoDB table with innodb_files_per_table = 1 or any other file like binary logs or backup tar files.

Then we have to do an operating system level file recovery first before we can do a data recovery.

This can be done in 2 ways:

1. 1. Fixing the inode structure again to make the file visible (undelete).
2. 2. Find the file by data carving.

The first method is pretty simple for ext2 and more difficult for ext3/4, xfs and other file systems.

For file carving there are some tools available.

Beside not doing backups, what can go wrong? One of our customers had a backup retention policy of just one week. But he found after 6 weeks that he has deleted by accident some data. So he wanted to get his backups back.

How to build static executables

Sometimes it happens that you have to do open-heart operations. You cannot bring your data to your tools but you have to bring your tools to your data. But in such a case you typically do not have a compiling environment on the target system nor do you want to install all the needed header files on your target.

What you do is you compile your tools on an other box an transfer it to the target. But then it can happen that you get some error messages that this and this library in this and this version does not work. So what we want to do is static executables.

To find out which libraries come into question you can run:

# ldd ext3grep
 linux-vdso.so.1 =>  (0x00007fff3f7ff000)
 libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x00007fe508218000)
 libm.so.6 => /lib/libm.so.6 (0x00007fe507f95000)
 libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00007fe507d7e000)
 libc.so.6 => /lib/libc.so.6 (0x00007fe5079fb000)
 /lib64/ld-linux-x86-64.so.2 (0x00007fe508541000)

and we clearly see, it is a dynamic linked executable:

 # file ext3grep
ext3grep: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.15, not stripped

To change this we copy the needed libraries and compile the executables statically:

# cd src

# cp `g++ -print-file-name=libstdc++.a` libstdc++.a
# cp `g++ -print-file-name=libm.a`      libm.a
# cp `g++ -print-file-name=libc.a`      libc.a

# g++ -static-libgcc -g -O2 -DUSE_MMAP=1 -I/usr/include/ext2fs -I/usr/include/et \
-include pch.h -o ext3grep ext3grep-custom.o ... ext3grep-locate.o -L./

When we do the check we see success:

# ldd ext3grep
        not a dynamic executable

# file ext3grep
ext3grep: ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), statically linked, for GNU/Linux 2.6.15, not stripped

Unfortunately this method does not work out all the times. What you can try then is to copy the libraries in question to the target system as well and bend the LD_LIBRARY_PATH to the location where your libraries reside

export LD_LIBRARY_PATH=/tmp/my_libs
./ext3grep

But this method it certain cases gives you core dumps, floating point exceptions or similar problems.

Literature

1. Building static executables on Linux
2. Linking libstdc++ statically

Literature

Undelete

1. HOWTO recover deleted files on an ext3 file system (with ext3grep)
2. Ext2fs Undeletion of Directory Structures mini-HOWTO
3. Linux Ext2fs Undeletion mini-HOWTO
4. ext3grep

Data Carving

1. Scalpel
2. Sleuthkit
3. PhotoRec
4. TestDisk
5. Foremost

Meta-Tools

1. ext3undel