innodb_force_recovery設定

raysuen發表於2017-05-25
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章