mafflog

I recently needed to restore data from a MySQL server where the host machine crashed and where I unfortunately didn’t have a proper dump backup – all I had was a backup of the MySQL data directory (/var/lib/mysql in case of Debian). After some googling I didn’t find a simple solution how to restore databases out of this backup. The solution which worked in the end was the following: I installed a fresh MySQL server in a virtual machine and replaced its data directory with the one from my backup (I had exactly the same MySQL versions on both machines). This allowed me to access the databases and create proper dumps which I could import in my real server later.

So, step one: in a virtual machine/spare server/local pc/whatever install a MySQL server and replace its data directory:

$ aptitude install mysql-server
$ /etc/init.d/mysql stop
$ mv /var/lib/mysql /var/lib/mysql.orig
$ cp -pr /tmp/backup/mysql /var/lib
$ chown -R mysql.mysql /var/lib/mysql

I also checked that file permissions match the normal permissions on Debian MySQL installations. Should be like this:

root@host:/var/lib/mysql# ls -al
drwx------  5 mysql mysql     4096 Mar  1 18:20 .
drwxr-xr-x 33 root  root      4096 Mar  1 18:20 ..
-rw-r--r--  1 root  root         0 Mar  1 18:07 debian-5.1.flag
-rw-rw----  1 mysql mysql 27262976 Mar  1 18:21 ibdata1
-rw-rw----  1 mysql mysql  5242880 Mar  1 18:21 ib_logfile0
-rw-rw----  1 mysql mysql  5242880 Mar  1 18:21 ib_logfile1
drwx------  2 mysql mysql     4096 Mar  1 18:20 database1
drwx------  2 mysql mysql     4096 Mar  1 18:21 database2
drwx------  2 mysql root      4096 Mar  1 18:08 mysql
-rw-------  1 root  root         6 Mar  1 18:08 mysql_upgrade_info

root@host:/var/lib/mysql# ls -al database1
drwx------ 2 mysql mysql 4096 Mar  1 18:20 .
drwx------ 5 mysql mysql 4096 Mar  1 18:20 ..
-rw-rw---- 1 mysql mysql   65 Mar  1 18:20 db.opt
-rw-rw---- 1 mysql mysql 8668 Mar  1 18:20 table1.frm
-rw-rw---- 1 mysql mysql  879 Mar  1 18:20 table2.frm
-rw-rw---- 1 mysql mysql 1520 Mar  1 18:20 table3.frm

Now you can try to start the server and look if your databases are readable:

$ /etc/init.d/mysql start
$ mysql -uroot -p -e "show databases;"
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| database1          |
| database1          |
| mysql              |
+--------------------+

Alright, if this works, simply dump your needed databases with mysqldump, transfer them to your server and import them normally.

$ mysqldump -uroot -p database1 > /tmp/database1.sql
$ scp /tmp/database1.sql user@server:/tmp

On the server:

$ mysql -uroot -p -e "create database database1;"
$ mysql -uroot -p database1 < /tmp/database1.sql

And don't forget to restore the temporary MySQL server to normal operation in case you need it later.

$ /etc/init.d/mysql stop
$ rm -rf /var/lib/mysql
$ mv /var/lib/mysql.orig /var/lib/mysql
$ /etc/init.d/mysql start