共計 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”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！