共计 5119 个字符，预计需要花费 13 分钟才能阅读完成。
这篇文章给大家分享的是有关 mysql 中如何设置 innodb_force_recovery 的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。
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.
因为没有进行回滚，所以启动很快就完成了。但是用户应当小心当前数据库的状态，并自己确认是否不需要回滚事务的操作。
感谢各位的阅读！关于“mysql 中如何设置 innodb_force_recovery”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
