Mysql 数据库数据恢复笔记（转）

Mysql 数据库数据恢复笔记（转）

出处：sunmoon的思考空间。 http://sunmoon.kmblog.info/?p=218

 

问题情境：在数据库里执行SQL语句时不小心错误操作，将wordpress数据库里wp_posts表里的post_content字段的值统一更新成了网站的地址，致使内容丢失。这时发现本数据库恰好没有最近的备份，mysql服务的更新日志没有打开，但是二进制日志却存在。进一步检测，发现数据库及表的存储引擎设为非事务型MyISAM，而不是事务型的存储引擎InnoDB。数据库为5.0以上版本。现在想将post_content字段的值恢复到错误更新之前的状态，何解？

 

 

探索解决方案的过程：

1、先通过查看一些资料发现许多人发文说若只是存在二进制日志的话，可以用二进制日志恢复数据库。二进制日志存储了mysql数据库的所有操作记录，包括查询、创建、更新、删除等操作。二进制日志按照时间将每一次的操作以二进制的形式保存进日志里。通常有多个二进制文件，每个二进制文件都是增量记录操作（上次二进制日志备份之后数据库的所有操作）。而且利用mysqlbinlog.exe工具可以基于时间点来恢复数据。查询了mysqlbinlog的用法以及实践过以后，只有如下形式才能正常运行。

  在运行中输入：H:\wamp\mysql\bin\mysqlbinlog.exe –database=”mydatabase” –stop-date=”2010-06-07 9:30:00″ ../data/mysql-bin.000058|mysql -uroot –p123456

其中第一句是工具软件的路径和名称，必须有路径，否则找不到程序。后面–开头的是语法的选项，这里有两个选项设置database：要恢复的数据库，stop-date:恢复到的时间点。再后面是数据库的某个要执行的二进制文件路径与名称，可以是绝对地址或相对于mysqlbinlog.exe 所在位置的相对地址。最后面|之后的是数据库访问权限设置，-u表示用户名，-p表示密码。如果用户名、密码为root、123456，则写为如上形式。

问题：按照上述语句执行二进制日志恢复，将数据库添加和更新post_content值的那段日志都执行了一遍，数据库却仍没有任何改变。再查了一些资料，发现好些人碰到相同的问题；查询中偶然发现有人说非事务型的数据库不支持用二进制日志来恢复数据库，所以无效。

2、将数据库的二进制日志、所用工具、数据库保存到本地服务器，并到数据库中进入每个表，将该表的存储引擎改为InnoDB，而且数据库的存储引擎也改为InnoDB。再用上面的语句执行还是不行。能够顺利执行，但执行后没有对数据库造成任何改变。

3、然后想将二进制文件保存为sql文件，然后再将sql文件导入数据库或直接执行，以来重新执行以前的数据库操作来恢复数据。

先是用H:\wamp\mysql\bin\mysqlbinlog.exe –database=”mydatabase” –stop-date=”2010-06-07 9:30:00″ ../data/mysql-bin.000050 > c:\my.sql |mysql -uroot –p123456

即将数据库操作记录保存到C盘的my.sql文件里面。上述语句执行同样没有效果，即没有生成my.sql文件。多次执行仍没有效果。

4、最后经过对mysqlbinlog的详细了解，发现一个选项可以设置导出文件：result-file，用法是–result-file=”c:\my.sql”  。抱着尝试的心态运行下面的语句：

H:\wamp\mysql\bin\mysqlbinlog.exe –database=”mydatabase” –stop-date=”2010-06-07 9:30:00″ –result-file=”c:\my.sql” ../data/mysql-bin.000050 | mysql -uroot –p123456 ，然后文件输出成功了。查看之后，果然是所有操作记录，而且有丢失数据的创建和更新操作，热泪盈眶啦。

5、再然后由于一个sql文件过大，所以不得不采取mysql.exe中用source语法来导入操作，因为mysql数据库本身上传的sql文件不能超过2M左右，大了数据库报错。而my.sql 文件中有大量的数据库查询操作，占了大量数据，所以文件比较大。

然后利用如下命令来打开mysql控制台语法如下：

h:\wamp\mysql\bin\mysql.exe  -uroot –p123546

这样就会登录数据库。

然后在输出如下命令（前面的mysql>不是命令，而是登录之后的前缀）。

mysql>source c:\my.sql,就会让数据库执行文件里的所有操作。

只需要将与数据库里的丢失数据有关的所有操作重新执行一遍，那么相应的数据就会被恢复到最后执行的二进制日志操作时间。

 

6、经过不懈努力，终于将wp_posts表恢复到原先的数据状态，然后将本地数据库里该表导出为wp_posts.sql文件，到服务器上的数据库里加载执行（之前先删除老的wp_posts,否则报错）执行之后，wp_posts表就恢复到原先的状态。

7、感概：一句话，还是要及时的备份数据啊，特别是对数据库进行操作之前要备份数据库，以免数据操作出错恢复力度太大或者难以恢复啊。毕竟二进制日志恢复起来不太容易，而且非事务型数据库可能永远无法恢复。特别是二进制日志都没有的时候，就回天乏术了。