Ubuntu – Is there any way to get a MySQL database from a /var backup


I upgraded from Ubuntu 12.10 to Ubuntu 13.10.
After some time, I found that I didn't export all my MySQL databases. Some of them are missing. I still have a backup from my /var/ partition, but I cannot just copy the missing databases (directories) over from /var/lib/mysql/, it will not work. MySQL-daemon will fail to restart.

I cannot replace the entire current /var/lib/mysql/ with the backed-up one either, just so I could export the missing tables and move the new directory back and import the exported tables. The databases and tables did show up in phpMyAdmin, but all the content was empty.

The annoying thing is, the daemon will fail to restart after fiddling around quickly. MySQL has turned * into a sensitive little girl. when I move an exact copy of my (new, untouched and post-upgrade) mysql directory back, it still won't restart and I have to apt-get install mysql-server-5.5 --reinstall for it to work agian.

So, is there any way I can get a database back from backed up files? They are sitting there so conveniently, named exactly like the database, with files named exactly like the tables.

*) This used to work. Normally I export my stuff, so last time I did this was a few years back, but it used to work. Now MySQL acts like a crybaby. New is not always better.

At least MongoDB just eats whatever files you put in /var/lib/mongodb as long as it's proper bson.

Although the error log is very verbose, it doesn't really help me.

A hundred of these:

131120 5:23:48 [ERROR] Cannot find or open table some_database/some_table from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

And some other messages about InnoDB that I cannot reproduce because I accidentally refreshed the log in my editor while reinstalling mysql for the n-th time.

What is this InnoDB stuff anyway? It's like the weak little crybaby brother or sister of the grown and versatile MySQL. They made it the default in server 5.5. Probably with good reason, but so far it didn't make me any sandwiches.

Best Answer

InnoDB is a storage engine, just like MyISAM is.

The old method of copying files back still works with MyISAM databases, but InnoDB has some features (like adherence to ACID and finer locking controls) that come with the price of that no longer working.

This thread over on StackOverflow has some answers on how to restore an InnoDB database from files.

Related Question