恢复 mysql InnoDB 数据库

为了恢复一个崩溃了的 MySQL 服务进程,你所能做的唯一一件事就是重新启动。InnoDB 将自动地检查日志并完成数据库的前滚(roll-forward)到当前状态。同时,InnoDB 将自动回滚崩溃前未提交的事务。在恢复过程中,mysqld 将显示如下所示的提示:
[root@bj_29 ~]#tail -f /opt/mysql-data/ff_db2.rekoo.com.err
020204 23:08:31  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files…

InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 177573790
InnoDB: Doing recovery: scanned up to log sequence number 0 177638912
InnoDB: Doing recovery: scanned up to log sequence number 0 177704448
InnoDB: Doing recovery: scanned up to log sequence number 0 177769984
InnoDB: Doing recovery: scanned up to log sequence number 0 177835520
InnoDB: Doing recovery: scanned up to log sequence number 0 177901056
InnoDB: Doing recovery: scanned up to log sequence number 0 177966592
InnoDB: Doing recovery: scanned up to log sequence number 0 178032128
InnoDB: Doing recovery: scanned up to log sequence number 0 178097664
InnoDB: Doing recovery: scanned up to log sequence number 0 178163200
InnoDB: Doing recovery: scanned up to log sequence number 0 178228736
InnoDB: After this prints a line for every 10th scan sweep:
InnoDB: Doing recovery: scanned up to log sequence number 0 178884096

InnoDB: Doing recovery: scanned up to log sequence number 0 193302016
InnoDB: Doing recovery: scanned up to log sequence number 0 193957376
InnoDB: Doing recovery: scanned up to log sequence number 0 194612736
020204 23:08:40  InnoDB: Starting an apply batch of log records to the database.
..
InnoDB: Progress in percents: 0 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 7
3 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
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 0 195268096
InnoDB: Doing recovery: scanned up to log sequence number 0 195923456

InnoDB: Doing recovery: scanned up to log sequence number 0 203132416
InnoDB: Doing recovery: scanned up to log sequence number 0 203787776
InnoDB: Doing recovery: scanned up to log sequence number 0 204443136
InnoDB: 5 uncommitted transaction(s) which must be rolled back
InnoDB: Trx id counter is 0 129792
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 129400
InnoDB: Rolling back of trx id 0 129400 completed
InnoDB: Rolling back trx with id 0 129217
InnoDB: Rolling back of trx id 0 129217 completed
InnoDB: Rolling back trx with id 0 129098
InnoDB: Rolling back of trx id 0 129098 completed
InnoDB: Rolling back trx with id 0 128743
InnoDB: Rolling back of trx id 0 128743 completed
InnoDB: Rolling back trx with id 0 127939
InnoDB: Rolling back of trx id 0 127939 completed
InnoDB: Rollback of uncommitted transactions completed
020204 23:08:51  InnoDB: Starting an apply batch of log records to the database.
..
InnoDB: Progress in percents: 0 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 7
3 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
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file offset 0 40418561, file name ./donna-bin.001
020204 23:08:53  InnoDB: Flushing modified pages from the buffer pool…
020204 23:09:03  InnoDB: Started
mysqld: ready for connections
在某些数据库损坏的情况下,可能通过转储(dump)、撤销(drop)和重新建立一个或多个损坏的表就足够了。可怜通过 CHECK TABLE SQL 命令检查一个受损的表,虽然 CHECK TABLE 并不能发现所有的损坏类型。你可能使用 innodb_tablespace_monitor 来检查数据文件时的文件空间管理的完整性。
在某些情况下,数据库页面显示损坏,而实际上是由于操作系统的文件高速缓冲损坏,而磁盘上的数据文件还是好的。 这时最好先重起你的系统。这可能解决数据库页面错误。
如果出现数据库页面损坏,可以通过 SELECT INTO OUTFILE 从数据库中转储出表数据,通常大部分的数据并未受损坏。 但是这些损坏可能引起 SELECT * FROM table 或 InnoDB 后台操作崩溃或中断(assert),甚至是 InnoDB 的前滚(roll-forward)恢复崩溃。在 my.cnf 中有个设置选项可以强制 InnoDB 启动,以及防止后台操作的运行,因而你可以转储数据。例如,你可以 my.cnf 在中加入如下设置:

set-variable = innodb_force_recovery = 4 

innodb_force_recovery  参数不能用于数据库的其它方面!当设置值大于 0 时,作为安全尺度,InnoDB 禁止用户使用 INSERT, UPDATE, 或 DELETE 。

如果你确定表如引起回滚崩溃,你可以移除(drop)它。你也可以通过这个停止一个因导入大量数据或 ALTER TABLE 而引起的失控(runaway)回滚。你可以杀死 mysqld 进程,并使用 my.cnf 设置项 innodb_force_recovery=3 不使用回滚。然后就可以 DROP 那个引起失控(runaway)回滚的表。

下面较大的数意味着包含所有较低数所对就的安全防范。为了能够转储表设置至少为 4 ,这是相对安全的,仅仅只有一些损坏的页面数据掉失。Option 6 is more dramatic, because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

1 (SRV_FORCE_IGNORE_CORRUPT) 即使发现一个错误也启动服务;试着使用 SELECT * FROM table 跳过损坏的索引记录和页面,这将帮助转储表。
2 (SRV_FORCE_NO_BACKGROUND) prevent the main thread from running: 如果在清理过程中将发生崩溃,这将预防它。
3 (SRV_FORCE_NO_TRX_UNDO) 恢复时不运行事务回滚。
4 (SRV_FORCE_NO_IBUF_MERGE) 防止插入缓冲区的归并操作:如果他们将引起崩溃,最好不要操作他们;不要考虑表统计(table statistics)。
5 (SRV_FORCE_NO_UNDO_LOG_SCAN) 当启动数据库时不撤销日志(undo logs):InnoDB 将未完成的事务已提交。
6 (SRV_FORCE_NO_LOG_REDO) do not do the log roll-forward in connection with recovery.
mysql lnnoDB恢复是比较麻烦的,特别是大数据量的情况下。

发表回复