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

In 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.

mysql> show tables;
+----------------------------+
| Tables_in_wordpressikdata  |
+----------------------------+
| wp_commentmeta             |
| wp_comments                |
| wp_gallery_galleries       |
| wp_gallery_galleriesslides |
| wp_gallery_slides          |
| wp_links                   |
| wp_options                 |
| wp_postmeta                |
| wp_posts                   |
| wp_rfr2b_options           |
| wp_rfr2b_target            |
| wp_term_relationships      |
| wp_term_taxonomy           |
| wp_terms                   |
| wp_usermeta                |
| wp_users                   |
+----------------------------+
16 rows in set (0.00 sec)

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

networkgeek@testserver:~$ mysqlcheck -u root -p wordpressikdata              
Enter password:
wordpressikdata.wp_commentmeta                     OK
wordpressikdata.wp_comments                        OK
wordpressikdata.wp_gallery_galleries               OK
wordpressikdata.wp_gallery_galleriesslides         OK
wordpressikdata.wp_gallery_slides                  OK
wordpressikdata.wp_links                           OK
wordpressikdata.wp_options                         OK
wordpressikdata.wp_postmeta                        OK
wordpressikdata.wp_posts                           OK
wordpressikdata.wp_rfr2b_options
Error    : Table 'wordpressikdata.wp_rfr2b_options' doesn't exist
status   : Operation failed
wordpressikdata.wp_rfr2b_target
Error    : Table 'wordpressikdata.wp_rfr2b_target' doesn't exist
status   : Operation failed
wordpressikdata.wp_term_relationships              OK
wordpressikdata.wp_term_taxonomy                   OK
wordpressikdata.wp_terms                           OK
wordpressikdata.wp_usermeta                        OK
wordpressikdata.wp_users                           OK

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:

root@testserver:~/recovery# mysqlfrm --diagnostic ./wp_rfr2b_
wp_rfr2b_options.frm  wp_rfr2b_target.frm
root@minidebian:~/recovery# mysqlfrm --diagnostic ./wp_rfr2b_target.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for ./wp_rfr2b_target.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `wp_rfr2b_target` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rss_content` text CHARACTER SET <UNKNOWN>,
  `rss_ad_campaign_name` varchar(300) CHARACTER SET <UNKNOWN> NOT NULL,
  `optin_fields` text CHARACTER SET <UNKNOWN>,
  `rss_extra` text CHARACTER SET <UNKNOWN>,
  `flag_ad_campaign` enum('0','1') CHARACTER SET <UNKNOWN> NOT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

#...done.

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.

CREATE TABLE `wordpressikdata`.`wp_rfr2b_options` (
  `option_name` varchar(750),
  `option_value` text,
PRIMARY KEY `PRIMARY` (`option_name`)
) ENGINE=InnoDB;

CREATE TABLE `wordpressikdata`.`wp_rfr2b_target` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rss_content` text,
  `rss_ad_campaign_name` varchar(300),
  `optin_fields` text,
  `rss_extra` text,
  `flag_ad_campaign` enum('0','1'),
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

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.