[minipost] How to fix MySQL lost table description from .frm files after emergency migration of /var/lib/mysql

TuxTIP_mergedIn May 2014, networkgeekstuff.com got a small problem when the hosting BeagleBone Black went dead and the old Raspberry PI environment was on that point already used for another project. I was forced to migrate quickly to a virtual server hosting company that I use. Actually it was a performance boost and quick quick, my MySQL and Apache migration scripts for backup recovery made the transition in ~2 hours. But last week I noticed that my MySQL backup system had troubles with two WordPress tables.

 

The MySQLdump was telling me that two tables do not exist with this message:

root@gserver:~/scripts/tachicoma_remote_backup# mysqldump -h localhost -u mysqlbackuper -p<removed> wordpressikdata > wordpressikdata.sql
mysqldump: Got error: 1146: Table ‘wordpressikdata.wp_rfr2b_options‘ doesn’t exist when using LOCK TABLES

root@gserver:~/scripts/tachicoma_remote_backup# mysqldump -h localhost -u mysqlbackuper -p<removed> wordpressikdata > wordpressikdata.sql
mysqldump: Got error: 1146: Table ‘wordpressikdata.wp_rfr2b_options‘ doesn’t exist when using LOCK TABLES

But when I looked at /var/lib/mysql/wordpressikdata, the files for these tables are there, and show tables showed these tables as well.

The first clue that something is wrong provided only mysqlcheck….

Also if you tried to get a table desciption from mysql with mysql> description wp_rfr2b_options; … it would fail

So how to fix this? My only files that I had were these two:
wp_rfr2b_options.frm
wp_rfr2b_target.frm
… but these are binary and we need to find their description to again define and insert these tables to MySQL

Luckly for us, there exists a utility from MySQL called mysqlfrm that can read the binary files and give you the needed CREATE TABLE commands for MySQL automatically. On Debian (you need to be debian testing branch when this was written in Jun 2014) you can install mysql-utilities package to get this tool. Then simply use it like this on the file:

The system had a little problem to identify the character set, but you can simply delete these parts if you know (like I do) that your default MySQL character set is good enough (like UTF8). In the end, after some manual modification, I had my tables definitions.

In summary, hopefully this will help someone. For me this is a self-documentation really as this issue today took me a few hours to investigate and find a solution, hopefully it will save some time to any of you guys out there.

 

If you enjoyed this blog, please share.

About Peter Havrila

Author's Profile