innodb_force_recovery导出坏表的数据

闲着也闲着,搞点破坏!

1、准备测试环境

CREATE TABLE `test` (
  `a` INT(11) NOT NULL AUTO_INCREMENT,
  `b` INT(11) DEFAULT NULL,
  `c` INT(11) DEFAULT NULL,
  `d` INT(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY(`b`)
) ENGINE=InnoDB;
 
delimiter $$
CREATE PROCEDURE `sp_test`()
BEGIN
SET AUTOCOMMIT=0;
SET @x = 0;
ins: loop
    SET @x = @x + 1;
    IF @x = 1000 THEN
    leave ins;
    END IF;
INSERT INTO test(b,c,d) VALUES(@x,@x,@x);
END loop ins;
END$$
 
CALL sp_test$$
commit$$

用vi打开test.ibd文件,删除几行

2、重启mysql,使读取的数据来源于disk

[root@dg2-1 bin]# mysqladmin shutdown
[root@dg2-1 bin]# ./mysqld_safe --user=mysql &
 
mysql> SELECT * FROM test;
ERROR 2013 (HY000): Lost connection TO MySQL server during query
mysql> 110625 14:36:44 mysqld_safe NUMBER OF processes running now: 0
110625 14:36:44 mysqld_safe mysqld restarted
mysql> SELECT * FROM test WHERE a <2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying TO reconnect...
Connection id:    1
CURRENT DATABASE: luoxuan
 
mysql> SELECT * FROM test WHERE b <2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying TO reconnect...
Connection id:    1
CURRENT DATABASE: luoxuan

error log:

110625 14:36:44  InnoDB: Page checksum 3212252115, prior-to-4.0.14-form checksum 1590363636
InnoDB: stored checksum 757154944, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 67119104 595591170, low 4 bytes OF lsn at page END 0
InnoDB: Page NUMBER (IF stored TO page already) 165248,
InnoDB: SPACE id (IF created WITH >= MySQL-4.1.1 AND stored already) 2957649
InnoDB: DATABASE page corruption ON disk OR a failed
InnoDB: file READ OF page 7.
InnoDB: You may have TO recover FROM a backup.
InnoDB: It IS also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: AND rebooting your computer removes the
InnoDB: error.
InnoDB: IF the corrupt page IS an INDEX page
InnoDB: you can also try TO fix the corruption
InnoDB: BY dumping, dropping, AND reimporting
InnoDB: the corrupt TABLE. You can USE CHECK
InnoDB: TABLE TO scan your TABLE FOR corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because OF a corrupt DATABASE page.
110625 14:36:44 mysqld_safe NUMBER OF processes running now: 0
110625 14:36:44 mysqld_safe mysqld restarted

2、设置innodb_force_recovery

设置范围从1-6,只有在6的情况下,数据才能部分读出。
error log:

110625 15:13:51  InnoDB: Started; log SEQUENCE NUMBER 0 0
InnoDB: !!! innodb_force_recovery IS SET TO 6 !!!
110625 15:13:51 [Note] Recovering after a crash USING mysql-bin
110625 15:13:51 [Note] Starting crash recovery...
110625 15:13:51 [Note] Crash recovery finished.
110625 15:13:52 [Note] Event Scheduler: Loaded 0 events
110625 15:13:52 [Note] /usr/LOCAL/mysql/libexec/mysqld: ready FOR connections.
Version: '5.1.57-log'  socket: '/tmp/mysql.sock'  port: 3306  SOURCE distribution

3、后期处理

部分读取:

mysql> SELECT * FROM test LIMIT 1;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    1 |    1 |    1 |
+---+------+------+------+
1 ROW IN SET (0.12 sec)

想取全部记录,mysqld就会重启

mysql> SELECT * FROM test;
ERROR 2013 (HY000): Lost connection TO MySQL server during query
mysql> 110625 15:13:51 mysqld_safe NUMBER OF processes running now: 0
110625 15:13:51 mysqld_safe mysqld restarted

当读到212不会报错,但读213就报错了,对213以后记录,都损坏了,如果没有备份,能还原就这么一些

mysql> SELECT * FROM test LIMIT 212;(成功导出)
mysql> SELECT * FROM test LIMIT 213;
ERROR 2013 (HY000): Lost connection TO MySQL server during query
mysql> 110625 15:25:05 mysqld_safe NUMBER OF processes running now: 0
110625 15:25:05 mysqld_safe mysqld restarted

参考文献:

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/

One Response to “innodb_force_recovery导出坏表的数据”

  1. yzhkpli说道:

    当读到212不会报错,但读213就报错了===求问这个值怎么测试出来的?如果表中有1百万条记录,如何快速判断出能恢复出的数据数量?

Post a Comment