从删库到跑路or恢复，记一次MySQL数据库文件损坏恢复经历

一、 前言

2018年5月28日，北京晴有轻度沙尘暴。 坐上公交车走在上班的路上，想起老罗经常说起的一句话：想成盛田昭夫时代的索尼，想成乔布斯时代的苹果，于是继续研读着 《日本制造：盛田昭夫的日式经营学》。

到了人大西门在西区食堂吃了个早餐，穿过人民大学很快就来到了公司。坐在工位上打开电脑登上QQ，不一会运营的CC的头像就开始闪动，“mooc平台登录不了”，“你看看”。又一会领导的头像开始闪动，“xxx说慕课平台不能登录了”。 额… 这事都惊动领导了？

二、 排查问题

打开chrome浏览器开始预览，等了好久代理服务器才反馈。

Time out！ 

使用 SecureCRT 连接了一下服务器，首先重新启动了一下Nginx代理服务器。 

service nignx stop // 关闭Nginx服务  
service nginx start // 开启Nginx服务 

去前台刷新了几下没有恢复。 那就在重启一下php吧，于是就： 

service php-fpm stop // 关闭PHP服务  
service php-fpm start // 开启PHP服务 

又去前台试了试还是没有恢复。（有人会问为什么不直接用 service xxx restart 来重启各服务呢？ 我也不知道为什么，个人爱好吧！）那只有一种可能数据库出问题了。

打开 Navicat 连接了一下数据库，发现可以正常连接而且可以看到所有的表，随便打开了一张表能看到里面的数据，但是弹出了一个错误的提示。 

Got error 28 from storage engine 

大概是这个错误提示，当时也没在意，心想反正提示错误了那就重启一下物理服务器吧，这里是物理服务器！！！随后执行了这个命令（为什么不直接重启MySQL服务呢？ 事后想了想我也不知道为什么。 而且如果当时注意看看这个错误，是因为磁盘空间问题引起的，也许后面就不会有那么多惊心动魄了！） 

reboot // 重启物理服务器 

执行完以后所有的服务都正常关闭了，只有Mysql数据库服务。 

Shutdown MySQL ………………………………………………. 

引号已经5排了，实在是等不下去了。 断电！！！（MySQL没有安全关闭，直接断电会出问题的！！！）。

三、 恢复进程

等了一会，物理服务器启动起来了。一切的应用服务都正常启动了，只看到在启动MySQL数据库的时候出现了。 

The server quit without updating pid file (/var/lib/mysql/localhost.localdomain.pid) 

等到全部服务加载完成以后手动又进行了一次MySQL数据库启动： 

service mysql start 

依然报前面那样的错误，此时心里开始紧张了起来。 Google了一下这个错误，网上提供了几种解决的方案：

1、 Mysql权限问题 

chown -R mysql:mysql /var/lib/mysql/*  
chmod -R 660 /var/lib/mysql/* 

2、 Mysql 服务已开启 

ps -ef|grep mysqld // 查看是否有mysqld进程  
kill -9 进程号 // 强制杀死进程 

3、 残余数据影响了Mysql服务的启动

    删除数据库目录(我的数据库目录为rpm安装默认目录：/var/lib/mysql)下的 mysql-bin.index 文件

4、 Mysql配置文件(默认为：/etc/my.cnf)

    配置文件里面没有配置数据库目录，这个问题一般在刚安装MySQL时候会出现

5、 skip-federated字段问题

    MySQL配置文件注释掉skip-federated字段

6、 selinux的问题

    centos6.8以上默认会开启selinux服务，加强版军用级防火墙。为了查问题可以直接关掉

/usr/sbin/setenforce 0 

以上解决方案全部都已经使用过了，都没有解决问题，依然开启服务会报错。 此时的心开始凉了。

回头看了看往期的备份，xxxx_20171208.sql。 都快2018年6月份了，我的上次备份竟然是17年12月份的，半年了！都半年没备份过了！ （我视乎隐约的感觉前段时间是有备份的，备份的服务器硬盘好像被我清理了）。

进入到数据库目录下，看到了除了上述说的 mysql-bin.index 文件以外还有其他的几个文件：mysql-bin.~rec~ 、 ib_logfile1、 ib_logfile0、 ibdata1 想了想是不是这几个也是一些残余文件，全部删了试试。 尝试把这几个文件转移到了其他的目录（使用的mv命令）模拟删除效果，同时还相当于备份。 

service mysql start 

竟然MySQL数据库服务正常启动了！ 心里的喜悦涌了上来，赶紧使用Navicat连接一下看看，能够正常连接，看到了数据库。 打开数据库以后所有的表都没有了！ 此时心又酸了起来。 一转眼11:30了，时间过的可真快啊，同事叫着一起吃饭，此时的我已经全无吃饭的心情了。

恢复表结构

把刚才移走的几个文件又恢复到了原目录里，既然恢复MySQL进程现在没什么希望了，那就想办法恢复数据吧。 进入到数据库目录（/var/lib/mysql）下找到了我的数据库名字以目录的形式存放。 进去该目录以后发现里面都是以扩展名为：xxxx表.frm文件，这些不都是我的数据库表吗？ 里面是不是就存放了所有的数据？ 是不是直接拿这些文件就可以恢复数据呢？Google了一下，果然有这方面的文章，大致说： “frm可以恢复表结构，同时InnoDB数据库引擎和MyISAM数据库引擎恢复的方式不一样”。

1、 InnoDB数据库引擎

1. 在一个正常的MySQL数据库服务器（new_server）下建立数据库（new_db），该数据库的名称和异常服务器（old_server）数据库(old_db)保持一致。
2. 在new_db数据库中建立一张表与old_db的表名称（t_user）一致。
3. 将new_server服务器的MySQL数据库服务关闭。
4. 从old_server服务器下old_db的数据库目录下复制t_user.frm文件到new_server服务器下new_db的数据库目录下替换t_user.frm文件。
5. 开启new_server服务器的MySQL数据库服务。
6. 使用连接工具连接new_server就可以看到new_db下的表及表结构。

2、 MyISAM数据库引擎

    其他和InnoDB数据库引擎操作基本一致，只是在new_server服务器下new_db的数据库目录下创建两个空的文件：t_user.MYD 和 t_user.MYI。

我使用的数据库为InnoDB引擎，无奈的我以上两种方法都使用了，没有恢复任何表结构更没有数据，也许可能是我操作有问题吧。 此时看到了目录下有一个文件： ibdata1 Google了一下，可以和xxx.frm配合使用，又一次将new_server服务器的MySQL数据库服务关闭。 直接把old_server服务器下old_db的数据库目录下复制ibdata1文件到new_server服务器下new_db的数据库目录下替换ibdata1文件。 

service mysql start 

新的服务器也出现了这样的错误，导致错误的很大原因可能是ibdata1文件损坏引起的。

今天北京的天气已经达到了35摄氏度，但此时我的心已经凉了一半了，虽然没有按时备份数据及服务器异常崩溃造成数据丢失比直接删库的责任小了点，但是也办法向公司交代，真的需要开始准备 “离职申请” 了吗？

binlog日志

打开微信

    我：你们公司用的是什么数据库，是MySQL吗

    好友LZ：是的

    我：公司的MySQL坏了，启动不了了； 数据没有备份； 有什么好办法把数据拿回来吗

    好友LZ：你们之前数据的binlog还有吗；通过这个应该可以恢复

    我：都有

    好友LZ：我也没弄过数据恢复，都是DBA搞，感觉应该可以的；你先查查看网上有没有解决方案，我这会在上线。

    我：嗯

本来想说：“你能不能问问好友LZ你们DBA遇到过这种情况吗，帮忙给个方案”；最后还是没有好意思开出口。 不过binlog这个名字让我突然想起了数据库目录（/var/lib/mysql）下面几个较大的文件。

这十几个文件就是binlog日志文件，每台服务器上面的个数应该不一样，这个文件只有每次重启MySQL服务或者刷新日志（MySQL命令：show master logs）的时候才会新增一个。看了一下我最近的几个文件，2018年1月16、 2018年3月18、 2018年4月18、 2018年5月28这几个时间点产生了新的文件，说明MySQL服务器这几个日期都进行过关闭又开启的操作。

binlog使用：

    binlog文件简介（网上摘抄）

    MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

    binlog作用（网上摘抄）

    MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

    数据恢复，通过使用mysqlbinlog工具来使恢复数据。

使用binlog恢复数据之前需要确定MySQL是否开启binlog日志：

show variables like 'log_%'; 

状态 OFF 为未开启，状态 ON 表示已开启。

可以通过MySQL配置文件（默认路径：/etc/my.cnf）开启或关闭binlog日志。 

vi /etc/my.cnf 

使用加上#可以关闭，去掉开启。 修改后需要重启MySQL服务（service mysql restart）才可以生效。

恢复数据（binlog日志方式）

初试mysqlbinlog工具

看到上面的那么多mysql-bin文件，很显然使用centos6.5下rpm方式安装的MySQL默认是打开binlog日志的。 这时我们就需要用到MySQL的 mysqlbinlog 工具，想使用它首先需要确保已经安装MySQL服务，然后我们需要找到它的位置。

find / -name mysql 

    2 表示为MySQL可执行文件的目录

    3 表示为MySQL的数据库目录

那我们先简单的使用一下：

cd /var/lib/mysql  
mysqlbinlog mysql-bin.000001 > mysql-bin.000001.sql 

很显然我在使用mysqlbinlog的时候是，直接执行的mysqlbinlog命令，前面并没有增加任何路径。 因为默认centos系统会将/usr/bin这个目录配置到环境标量中，若我们使用的是rpm方式安装的MySQL，默认是安装到/usr/bin目录下的。 可以直接在任何路径下使用/usr/bin目录里的文件。 执行完上面的语句后会发现在当前目录生成一个mysql-bin.000001.sql的文件， 打开文件可以看到很多sql语句。

对于我当前的情况来看并不需要把所有的binlog都处理一遍，上面提到我上次的备份是在2017年12月8日的时候（xxxx_20171208.sql）因此我只需要从 mysql-bin.000009 这个binlog文件开始就可以了。

首先我在另外一台服务器上面重新搭建了一个MySQL服务，把mysql-bin.000009以后的几个binlog都拷贝到了这台新的服务器上面去。（服务器出现任何问题，建议不要对该服务器做任何操作，换一台新的电脑或服务来处理，为了保护数据的完整性！）

使用备份文件恢复数据

在新的MySQL上面建了一个和以前一样名称的数据库。

    mysql -u数据库用户名 -p数据库密码 数据库名称 --default-character-set=utf8 < xxxx_20171208.sql

    例：mysql -uroot -proot xxxx --default-character-set=utf8 < xxxx_20171208.sql

使用binlog恢复数据

这时数据库有了，数据表及表结构也有了，那就开始恢复数据吧。   

mysqlbinlog mysql-bin.000009 | mysql -uroot -proot 

回车马上就出错了，遇到了两种错误，一种是PRIMARY的错误，一种是找不到记录的错误。 mysqlbinlog在执行mysql-bin.000009文件里的插入语句时出错了。 看了一下mysql-bin.000009文件的创建时间是2017年11月12日，我的备份文件是2017年12月8日，他们两个时间差了二十几天，执行上面恢复语句肯定会出现重复插入的问题，数据库里的某些表是由PRIMARY KEY的约束的，所以会导致PRIMARY错误。 这时我们需要用到mysqlbinlog的参数： start-datetime 和 end-datetime，顾名思义一个是开始时间一个是结束时间。

mysqlbinlog --start-datetime="2017-12-08 10:00:00" mysql-bin.000009 | mysql -uroot -proot 

看了一下我备份的xxxx_20171208.sql大致是2017年12月8日的10左右，没有添加 end-datetime 参数的话默认为该binglog文件下的最后一个时间点。 执行了以后报了一个找不到记录的异常。 应该是执行删除或更新语句的时候没有找到某条记录，时间还是不对。 于是我就查看了数据库的日志表，最后的时间是2017年12月8日9点32分41秒，又执行了一次。 

mysqlbinlog --start-datetime="2017-12-08 09:32:41" mysql-bin.000009 | mysql -uroot -proot 

依然报错，那怎么办呢，难道这个方法不行？ 

mysqlbinlog mysql-bin.000009 > mysql-bin.000009.sql 

此时打开mysql-bin.000009.sql里面拥有大量的sql语句，发现好多条sql语句在这个时间点下。 看来使用参数来控制行不通。 还好mysqlbinlog工具给我们提供了另外两个参数start-position 和 end-position

修改了一下命令： 

mysqlbinlog --start-position="123456" mysql-bin.000009 | mysql -uroot -proot 

果然一切都正常了，执行这个命令需要很久，它要把你这段时间所有的增加、删除、更新都执行一遍。 这里可能还会遇到一个问题，我的这个MySQL服务器里面这有一个数据库，MySQL的binlog文件记录的是所有数据库的增加、删除、更新记录，那怎样只针对某个数据库来操作呢？ 这时我们需要用到mysqlbinlog的database参数。

mysqlbinlog --database=xxxx --start-position="123456" mysql-bin.000009 | mysql -uroot -proot 

半年的数据，就这么一个一个的binlog文件进行处理的，从晚上6点到夜里的12点完成所有文件的恢复，数据量不是很大，服务器的性能也不是太高，中间出了点问题，不过都是服务器中断的问题。 最后把所有的数据全部恢复了回来，这心惊肉跳的一天！

这是工作7年来出的最大一次事故，去年给自己定的一个目标今年写12篇有质量的文章反馈给互联网，都快过半年了一篇还没有写，没想到第一篇竟然是以这种方式书写的。 不知道这篇算不算是有质量，希望能帮到更多的人。

总结

遇到问题不要盲目，保持清醒的头脑，找清问题，整理好思路才能更有效的解决问题。 对于数据平时不要怕麻烦，注意备份。

备注

我的服务器及各软件的版本

• 操作系统：** centos6.5
• MySQL：** 5.5.49
• 安装MySQL方式：** rpm