前几天与ξLAZYζQQ聊天的时候告知我网站出问题了,我登录之后才发现确实出问题了,竟然提示安装界面,估计是数据库出问题或者网站文件的配置出问题了。
前天(20170706)终于空下来可以搞这个事情了,发现问题比较严重,通过查看文件wp_config.php,里面的数据库连接什么的配置都是对的,然后通过phpmyadmin看了下表都在啊,然后select 一把查一下,严重了,提示表不存在(Table wp_posts dosn't exist!),但是我明明可以看到表名啊。
查了下上次备份是16年12月份,我17年还做过把所有的文章的链接换成了静态的,搞了好几天呢,如果恢复那岂不是要Over,但是网上查了很多方法,都没解决,包括查询区分大小写(改配置文件)、数据库迁移文件拷贝少了。尝试通过重新导入备份的方式,还是提示表不存在,连创建都不让了。
后来实在是研究不出来是怎么回事,找了我们这边DBA同事,帮我研究了一下终于搞出办法了。
问题现象:
wordpress的所有表查询都提示表不存在(Table XXXX dosn't exist!)。查看数据库ibd(数据文件)和frm(表结构文件)文件都存在。
修复条件:需要可以直接操作linux的数据文件ibd和frm的权限。(我和温柔哥有关系,所以有root权限)
修复方法:
1. 备份所有文件,以防恢复失败。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | cd /var/lib/mysql/laycher tar zcvf laycher_2017075.tar.gz * mv wp_commentmeta.ibd wp_commentmeta.ibd_bak mv wp_comments.ibd wp_comments.ibd_bak mv wp_links.ibd wp_links.ibd_bak mv wp_options.ibd wp_options.ibd_bak mv wp_postmeta.ibd wp_postmeta.ibd_bak mv wp_posts.ibd wp_posts.ibd_bak mv wp_term_relationships.ibd wp_term_relationships.ibd_bak mv wp_term_taxonomy.ibd wp_term_taxonomy.ibd_bak mv wp_termmeta.ibd wp_termmeta.ibd_bak mv wp_terms.ibd wp_terms.ibd_bak mv wp_usermeta.ibd wp_usermeta.ibd_bak mv wp_users.ibd wp_users.ibd_bak |
2. 删除表(DROP TABLE)
1 2 3 4 5 6 7 8 9 10 11 12 | DROP TABLE `wp_commentmeta`; DROP TABLE `wp_comments`; DROP TABLE `wp_links`; DROP TABLE `wp_options`; DROP TABLE `wp_postmeta`; DROP TABLE `wp_posts`; DROP TABLE `wp_term_relationships`; DROP TABLE `wp_term_taxonomy`; DROP TABLE `wp_termmeta`; DROP TABLE `wp_terms`; DROP TABLE `wp_usermeta`; DROP TABLE `wp_users`; |
如果出现提示:错误代码: 1051 UNKNOWN TABLE 'laycher.wp_termmeta',属于正常现象。
只要查看数据文件frm后缀的都没有,只剩下ibd的就可以了。
3. 创建表空间(CREATE TABLE)
从以前备份的sql中,将创建表空间的拎出来。否则可能出现字符集编码不对,导致恢复过来的数据是乱码。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 | DROP TABLE IF EXISTS `wp_commentmeta`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_commentmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) DEFAULT NULL, `meta_value` longtext, PRIMARY KEY (`meta_id`), KEY `comment_id` (`comment_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=InnoDB AUTO_INCREMENT=424816 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_comments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_comments` ( `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0', `comment_author` tinytext NOT NULL, `comment_author_email` varchar(100) NOT NULL DEFAULT '', `comment_author_url` varchar(200) NOT NULL DEFAULT '', `comment_author_IP` varchar(100) NOT NULL DEFAULT '', `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_content` text NOT NULL, `comment_karma` int(11) NOT NULL DEFAULT '0', `comment_approved` varchar(20) NOT NULL DEFAULT '1', `comment_agent` varchar(255) NOT NULL DEFAULT '', `comment_type` varchar(20) NOT NULL DEFAULT '', `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0', `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', `comment_reply_ID` int(11) NOT NULL DEFAULT '0', `comment_mail_notify` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`comment_ID`), KEY `comment_post_ID` (`comment_post_ID`), KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`), KEY `comment_date_gmt` (`comment_date_gmt`), KEY `comment_parent` (`comment_parent`), KEY `comment_author_email` (`comment_author_email`(10)) ) ENGINE=InnoDB AUTO_INCREMENT=130099 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_links`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_links` ( `link_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `link_url` varchar(255) NOT NULL DEFAULT '', `link_name` varchar(255) NOT NULL DEFAULT '', `link_image` varchar(255) NOT NULL DEFAULT '', `link_target` varchar(25) NOT NULL DEFAULT '', `link_description` varchar(255) NOT NULL DEFAULT '', `link_visible` varchar(20) NOT NULL DEFAULT 'Y', `link_owner` bigint(20) unsigned NOT NULL DEFAULT '1', `link_rating` int(11) NOT NULL DEFAULT '0', `link_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `link_rel` varchar(255) NOT NULL DEFAULT '', `link_notes` mediumtext NOT NULL, `link_rss` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`link_id`), KEY `link_visible` (`link_visible`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_options`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_options` ( `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `option_name` varchar(191) DEFAULT NULL, `option_value` longtext NOT NULL, `autoload` varchar(20) NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`) ) ENGINE=InnoDB AUTO_INCREMENT=690978 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_postmeta`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_postmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) DEFAULT NULL, `meta_value` longtext, PRIMARY KEY (`meta_id`), KEY `post_id` (`post_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=InnoDB AUTO_INCREMENT=1507 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_posts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_posts` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_author` bigint(20) unsigned NOT NULL DEFAULT '0', `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_content` longtext NOT NULL, `post_title` text NOT NULL, `post_excerpt` text NOT NULL, `post_status` varchar(20) NOT NULL DEFAULT 'publish', `comment_status` varchar(20) NOT NULL DEFAULT 'open', `ping_status` varchar(20) NOT NULL DEFAULT 'open', `post_password` varchar(20) NOT NULL DEFAULT '', `post_name` varchar(200) NOT NULL DEFAULT '', `to_ping` text NOT NULL, `pinged` text NOT NULL, `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_content_filtered` longtext NOT NULL, `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0', `guid` varchar(255) NOT NULL DEFAULT '', `menu_order` int(11) NOT NULL DEFAULT '0', `post_type` varchar(20) NOT NULL DEFAULT 'post', `post_mime_type` varchar(100) NOT NULL DEFAULT '', `comment_count` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`), KEY `post_parent` (`post_parent`), KEY `post_author` (`post_author`), KEY `post_name` (`post_name`(191)) ) ENGINE=InnoDB AUTO_INCREMENT=1263 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_term_relationships`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_term_relationships` ( `object_id` bigint(20) unsigned NOT NULL DEFAULT '0', `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0', `term_order` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`object_id`,`term_taxonomy_id`), KEY `term_taxonomy_id` (`term_taxonomy_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_term_taxonomy`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_term_taxonomy` ( `term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint(20) unsigned NOT NULL DEFAULT '0', `taxonomy` varchar(32) NOT NULL DEFAULT '', `description` longtext NOT NULL, `parent` bigint(20) unsigned NOT NULL DEFAULT '0', `count` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`term_taxonomy_id`), UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`), KEY `taxonomy` (`taxonomy`) ) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_termmeta`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_termmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `meta_value` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`meta_id`), KEY `term_id` (`term_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_terms`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_terms` ( `term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL DEFAULT '', `slug` varchar(200) NOT NULL DEFAULT '', `term_group` bigint(10) NOT NULL DEFAULT '0', PRIMARY KEY (`term_id`), KEY `slug` (`slug`(191)), KEY `name` (`name`(191)) ) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_usermeta`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_usermeta` ( `umeta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) DEFAULT NULL, `meta_value` longtext, PRIMARY KEY (`umeta_id`), KEY `user_id` (`user_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=InnoDB AUTO_INCREMENT=1331 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `wp_users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `wp_users` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_login` varchar(60) NOT NULL DEFAULT '', `user_pass` varchar(255) NOT NULL DEFAULT '', `user_nicename` varchar(50) NOT NULL DEFAULT '', `user_email` varchar(100) NOT NULL DEFAULT '', `user_url` varchar(100) NOT NULL DEFAULT '', `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `user_activation_key` varchar(255) NOT NULL DEFAULT '', `user_status` int(11) NOT NULL DEFAULT '0', `display_name` varchar(250) NOT NULL DEFAULT '', PRIMARY KEY (`ID`), KEY `user_login_key` (`user_login`), KEY `user_nicename` (`user_nicename`), KEY `user_email` (`user_email`) ) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; |
4. 删除表数据(ALTER TABLE XXXXX DISCARD TABLESPACE)
此语句是删除ibd数据文件,只保留frm表结构。
1 2 3 4 5 6 7 8 9 10 11 12 | ALTER TABLE `wp_commentmeta` DISCARD TABLESPACE; ALTER TABLE `wp_comments` DISCARD TABLESPACE; ALTER TABLE `wp_links` DISCARD TABLESPACE; ALTER TABLE `wp_options` DISCARD TABLESPACE; ALTER TABLE `wp_postmeta` DISCARD TABLESPACE; ALTER TABLE `wp_posts` DISCARD TABLESPACE; ALTER TABLE `wp_term_relationships` DISCARD TABLESPACE; ALTER TABLE `wp_term_taxonomy` DISCARD TABLESPACE; ALTER TABLE `wp_termmeta` DISCARD TABLESPACE; ALTER TABLE `wp_terms` DISCARD TABLESPACE; ALTER TABLE `wp_usermeta` DISCARD TABLESPACE; ALTER TABLE `wp_users` DISCARD TABLESPACE; |
5. 还原ibd
1 2 3 4 5 6 7 8 9 10 11 12 | mv wp_commentmeta.ibd_bak wp_commentmeta.ibd mv wp_comments.ibd_bak wp_comments.ibd mv wp_links.ibd_bak wp_links.ibd mv wp_options.ibd_bak wp_options.ibd mv wp_postmeta.ibd_bak wp_postmeta.ibd mv wp_posts.ibd_bak wp_posts.ibd mv wp_term_relationships.ibd_bak wp_term_relationships.ibd mv wp_term_taxonomy.ibd_bak wp_term_taxonomy.ibd mv wp_termmeta.ibd_bak wp_termmeta.ibd mv wp_terms.ibd_bak wp_terms.ibd mv wp_usermeta.ibd_bak wp_usermeta.ibd mv wp_users.ibd_bak wp_users.ibd |
6. 导入数据(ALTER TABLE XXXXX IMPORT TABLESPACE)
此语句是导入ibd中的数据。
1 2 3 4 5 6 7 8 9 10 11 12 | ALTER TABLE `wp_commentmeta` IMPORT TABLESPACE; ALTER TABLE `wp_comments` IMPORT TABLESPACE; ALTER TABLE `wp_links` IMPORT TABLESPACE; ALTER TABLE `wp_options` IMPORT TABLESPACE; ALTER TABLE `wp_postmeta` IMPORT TABLESPACE; ALTER TABLE `wp_posts` IMPORT TABLESPACE; ALTER TABLE `wp_term_relationships` IMPORT TABLESPACE; ALTER TABLE `wp_term_taxonomy` IMPORT TABLESPACE; ALTER TABLE `wp_termmeta` IMPORT TABLESPACE; ALTER TABLE `wp_terms` IMPORT TABLESPACE; ALTER TABLE `wp_usermeta` IMPORT TABLESPACE; ALTER TABLE `wp_users` IMPORT TABLESPACE; |
7. 检查数据
检查select是否能正常查回,内容是否有乱码之类的。
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT * FROM `wp_commentmeta`; SELECT * FROM `wp_comments`; SELECT * FROM `wp_links`; SELECT * FROM `wp_options`; SELECT * FROM `wp_postmeta`; SELECT * FROM `wp_posts`; SELECT * FROM `wp_term_relationships`; SELECT * FROM `wp_term_taxonomy`; SELECT * FROM `wp_termmeta`; SELECT * FROM `wp_terms`; SELECT * FROM `wp_usermeta`; SELECT * FROM `wp_users`; |
8. 注意事项
注意文件权限和拥有者,如果是root用户拥有的话,也是不行的呢。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | [root@MyServer laycher]# ll total 31344 -rw-rw---- 1 mysql mysql 65 DEC 9 2016 db.opt -rw-rw---- 1 mysql mysql 8688 Jul 5 16:49 wp_commentmeta.frm -rw-rw---- 1 mysql mysql 9437184 Jul 5 16:52 wp_commentmeta.ibd -rw-rw---- 1 mysql mysql 13494 Jul 5 16:49 wp_comments.frm -rw-rw---- 1 mysql mysql 10485760 Jul 5 16:52 wp_comments.ibd -rw-rw---- 1 mysql mysql 13176 Jul 5 16:49 wp_links.frm -rw-rw---- 1 mysql mysql 114688 Jul 5 16:52 wp_links.ibd -rw-rw---- 1 mysql mysql 8698 Jul 5 16:49 wp_options.frm -rw-rw---- 1 mysql mysql 7340032 Jul 5 16:52 wp_options.ibd -rw-rw---- 1 mysql mysql 8682 Jul 5 16:49 wp_postmeta.frm -rw-rw---- 1 mysql mysql 327680 Jul 5 16:52 wp_postmeta.ibd -rw-rw---- 1 mysql mysql 9588 Jul 5 16:49 wp_posts.frm -rw-rw---- 1 mysql mysql 3145728 Jul 5 16:52 wp_posts.ibd -rw-rw---- 1 mysql mysql 8682 Jul 5 16:49 wp_termmeta.frm -rw-rw---- 1 mysql mysql 131072 Jul 5 16:52 wp_termmeta.ibd -rw-rw---- 1 mysql mysql 8666 Jul 5 16:49 wp_term_relationships.frm -rw-rw---- 1 mysql mysql 163840 Jul 5 16:52 wp_term_relationships.ibd -rw-rw---- 1 mysql mysql 8668 Jul 5 16:49 wp_terms.frm -rw-rw---- 1 mysql mysql 131072 Jul 5 16:52 wp_terms.ibd -rw-rw---- 1 mysql mysql 8768 Jul 5 16:49 wp_term_taxonomy.frm -rw-rw---- 1 mysql mysql 131072 Jul 5 16:52 wp_term_taxonomy.ibd -rw-rw---- 1 mysql mysql 8684 Jul 5 16:49 wp_usermeta.frm -rw-rw---- 1 mysql mysql 294912 Jul 5 16:52 wp_usermeta.ibd -rw-rw---- 1 mysql mysql 13064 Jul 5 16:49 wp_users.frm -rw-rw---- 1 mysql mysql 147456 Jul 5 16:52 wp_users.ibd |
其他说明:
此方法对我这正常,但是如果恢复不成功,请研究使用其他方法,此方法仅供参考。
通过此次事件,一定要注意备份的及时性啊!我是有备份插件,原先用的好好的,这半年多不知道为什么一直没有发邮件过来,我下次再研究研究。
(由于本人惫懒,文章分多天写完,所以中途有时间错乱,请见谅。)
>> 若为原创,转载请注明: 转载自Laycher's Blog
>> 本文链接地址: WordPress之MySql数据恢复
>> 订阅本站: http://feed.feedsky.com/laycher
每逢佳节倍思亲,看你博客很用心!
博客不错,大爱哦!