when you only have .MYD files left…

/ Leave a Comment

…sometimes it turns out ok. Recently, I encountered a situation where filesystem corruption ate parts of a website’s database. Backups? Nope. Here is what was in the /var/lib/mysql/database directory:

(several working complete tables)
wp_users.MYD

What was missing:
wp_users.MYI
wp_users.frm
(several other incomplete tables)

So what do you do when you’re missing your table definitions and indexes? Fortunately, you still have the data in the .MYD which means you should be able to restore the table.

The table definitions for wordpress are easy to find. For example, I just looked at the create statement in one of my wordpress sites, as follows:


CREATE TABLE `wp_users` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`user_status` int(11) NOT NULL DEFAULT '0',
`display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`ID`),
KEY `user_login_key` (`user_login`),
KEY `user_nicename` (`user_nicename`),
KEY `user_email` (`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Create the table, in this case wp_users, and you should now see all three MYISAM files; wp_users.MYD, wp_users.MYI, and wp_users.frm. Now, just copy the original wp_users.MYD into the /var/lib/mysql/(database)/ folder, overwrite the empty .MYD, and run myismchk -r wp_users. Doublecheck the table and hopefully, you should see all your data.

Leave a Reply