innodb_force_recovery设置

mysql> show variables like '%innodb_force_recovery%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_force_recovery | 0     |

+-----------------------+-------+

该参数默认值为0，代表当发生需要恢复时，机型所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption，MySQL数据库可能法师宕机(crash)，并把错误写入错误日志中。

该参数还可以设置为6个非零的值：1-6。大的数字表示包含了前面所有小数字表示的影响

1 srv_force_ignore_corrupt:     忽律检查到corrupt页

2 srv_force_no_background:      阻止Master Thread线程的运行，如Master Thread线程需要进行full purge操作，而这会导致crash。

3 srv_force_no_trx_undo:        不进行事务的回滚操作

4 srv_force_no_ibuf_merge:      不进行插入缓冲的合并操作

5 srv_force_no_undo_log_scan: 不查看撤销日志(undo log),InnoDB存储引擎会将未提交的事务视为已提交

6 srv_force_no_log_redo             不进行前滚操作

注意：

    当参数innodb_force_recovery设置大于0的值，用户可以对表进行select,create和drop操作，但insert\update\delete这类DML操作是不允许的。

模拟故障

Database changed mysql> start transaction     -> ; Query OK, 0 rows affected (0.00 sec) mysql> update t4 set VERSION=1; Query OK, 581632 rows affected (11.40 sec) Rows matched: 581632  Changed: 581632  Warnings: 0 [root@mysql5-7 ~]# ps aux | grep mysql     root      3928  0.0  0.0 106244  1448 pts/1    S    14:55   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf root      4585  0.0  0.1 126688  3012 pts/1    S+   14:55   0:00 mysql -uroot -px xxxx -S /data/3306/soket/mysql.sock mysql     4624 10.3 21.7 2269864 417796 pts/1  Sl   14:57   0:18 /usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/3306/logs/mysql_ray.err --open-files-limit=10240 --pid-file=/data/3306/logs/ray.pid --socket=/data/3306/soket/mysql.sock --port=3306 root      5884  0.0  0.0 100952   612 pts/2    S+   15:00   0:00 tail -f /data/3306/logs/mysql_ray.err root      5889  0.0  0.0 103260   832 pts/3    S+   15:00   0:00 grep mysql [root@mysql5-7 ~]# kill -9 3928 [root@mysql5-7 ~]# kill -9 4624 [root@mysql5-7 ~]# ps aux | grep mysql root      4585  0.0  0.1 126688  3012 pts/1    S+   14:55   0:00 mysql -uroot -px xxxx -S /data/3306/soket/mysql.sock root      5884  0.0  0.0 100952   612 pts/2    S+   15:00   0:00 tail -f /data/3306/logs/mysql_ray.err root      5895  0.0  0.0 103260   836 pts/3    S+   15:00   0:00 grep mysql 2017-05-25T07:01:10.663268Z 0 [Note] InnoDB: Database was not shutdown normally! 2017-05-25T07:01:10.663287Z 0 [Note] InnoDB: Starting crash recovery. 2017-05-25T07:01:10.695649Z 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 581632 row operations to undo 2017-05-25T07:01:10.695733Z 0 [Note] InnoDB: Trx id counter is 5888 2017-05-25T07:01:10.700667Z 0 [Note] InnoDB: Last MySQL binlog file position 0 62744449, file name ray-bin.000007 2017-05-25T07:01:10.806383Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2017-05-25T07:01:10.806472Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

这里看到 581632 row operations to undo，因为回滚了数据，所以启动时间会加长，如果回滚数据非常多，启动时间就会很慢。

重新做一次实验，innodb_force_recovery设置为3.

[root@mysql5-7 ~]# ps aux | grep mysql     root      4585  0.0  0.1 126640  3004 pts/1    S+   14:55   0:00 mysql -uroot -px xxxx -S /data/3306/soket/mysql.sock root      5884  0.0  0.0 100952   612 pts/2    S+   15:00   0:00 tail -f /data/3306/logs/mysql_ray.err root      5916  0.0  0.0 106244  1456 pts/3    S    15:01   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf mysql     6546  7.3 21.7 2269272 417740 pts/3  Sl   15:01   0:19 /usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/3306/logs/mysql_ray.err --open-files-limit=10240 --pid-file=/data/3306/logs/ray.pid --socket=/data/3306/soket/mysql.sock --port=3306 root      6586  0.0  0.0 103260   836 pts/3    S+   15:05   0:00 grep mysql [root@mysql5-7 ~]# kill -9 5916 [root@mysql5-7 ~]# kill -9 6546 [root@mysql5-7 ~]# ps aux | grep mysql     root      4585  0.0  0.1 126640  3004 pts/1    S+   14:55   0:00 mysql -uroot -px xxxx -S /data/3306/soket/mysql.sock root      5884  0.0  0.0 100952   612 pts/2    S+   15:00   0:00 tail -f /data/3306/logs/mysql_ray.err root      5916  0.0  0.0 106244  1456 pts/3    S    15:01   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf mysql     6546  7.3 21.7 2269272 417740 pts/3  Sl   15:01   0:19 /usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/3306/logs/mysql_ray.err --open-files-limit=10240 --pid-file=/data/3306/logs/ray.pid --socket=/data/3306/soket/mysql.sock --port=3306 root      6586  0.0  0.0 103260   836 pts/3    S+   15:05   0:00 grep mysql [root@mysql5-7 ~]# kill -9 5916 [root@mysql5-7 ~]# kill -9 6546 2017-05-25T07:06:05.298605Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2017-05-25T07:06:05.299344Z 0 [Note] InnoDB: 5.7.10 started; log sequence number 805301564 2017-05-25T07:06:05.299424Z 0 [Note] InnoDB: !!! innodb_force_recovery is set to 3 !!! 2017-05-25T07:06:05.300353Z 0 [Note] Plugin 'FEDERATED' is disabled. 2017-05-25T07:06:05.302471Z 0 [Note] Recovering after a crash using /data/3306/logs/ray-bin 2017-05-25T07:06:05.302981Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/3306/data/ib_buffer_pool 2017-05-25T07:06:05.303049Z 0 [Note] InnoDB: not started 2017-05-25T07:06:05.305844Z 0 [Note] Starting crash recovery... 2017-05-25T07:06:05.305926Z 0 [Note] Crash recovery finished.

因为没有进行回滚，所以启动很快就完成了。但是用户应当小心当前数据库的状态，并自己确认是否不需要回滚事务的操作。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28572479/viewspace-2139869/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28572479/viewspace-2139869/