Restore MySQL databases from raw *.frm files

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              |

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
  • Sergio Márquez

    This is excellent, thanks a lot

    • Neha

      Hello Help me for recover my database if you know solution. i have same problem but show me some of tables in database. i have try all steps restart. stop. start.

  • Neha

    Hello I have same problem and used same method but show some tables in database and other left but i have done all step. My server centos 6. i have try all restart, start , stop commands but not show all tables in database only show few of them. what to do now? please help me.

  • you need to run mysql_upgrade command before running mysqldump

  • Benjamin Nelson

    I spent the last seven hours trying to recover a mysql db from a hard drive with an OS that wouldn’t boot. I couldn’t make it work until I found this post. The trick was using cp -pr and then chown. Thanks very much for taking the time to write it, it was a really big help.

  • jpap

    An easier way that avoids having to run a VM is as follows:

    1. Create a new database: CREATE DATABASE dbrestore;
    2. Shut down mysql
    3. Erase the raw dbrestore files, e.g. in /opt/local/var/db/mysql5/dbrestore/
    4. Copy your backup into the same folder as #3
    5. Start up mysql
    6. Use a INSERT INTO orig.X SELECT FROM dbrestore.Y query to copy data from the backup to the original table(s).