這篇文章給大家分享的是有關(guān)MySQL中如何設(shè)置innodb_force_recovery的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。
創(chuàng)新互聯(lián)公司成立十多年來，這條路我們正越走越好，積累了技術(shù)與客戶資源，形成了良好的口碑。為客戶提供做網(wǎng)站、成都做網(wǎng)站、網(wǎng)站策劃、網(wǎng)頁設(shè)計(jì)、國際域名空間、網(wǎng)絡(luò)營銷、VI設(shè)計(jì)、網(wǎng)站改版、漏洞修補(bǔ)等服務(wù)。網(wǎng)站是否美觀、功能強(qiáng)大、用戶體驗(yàn)好、性價(jià)比高、打開快等等，這些對(duì)于網(wǎng)站建設(shè)都非常重要，創(chuàng)新互聯(lián)公司通過對(duì)建站技術(shù)性的掌握、對(duì)創(chuàng)意設(shè)計(jì)的研究為客戶提供一站式互聯(lián)網(wǎng)解決方案，攜手廣大客戶，共同發(fā)展進(jìn)步。
mysql> show variables like '%innodb_force_recovery%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_force_recovery | 0 |
+-----------------------+-------+
該參數(shù)默認(rèn)值為0，代表當(dāng)發(fā)生需要恢復(fù)時(shí)，機(jī)型所有的恢復(fù)操作，當(dāng)不能進(jìn)行有效恢復(fù)時(shí)，如數(shù)據(jù)頁發(fā)生了corruption，MySQL數(shù)據(jù)庫可能法師宕機(jī)(crash)，并把錯(cuò)誤寫入錯(cuò)誤日志中。
該參數(shù)還可以設(shè)置為6個(gè)非零的值：1-6。大的數(shù)字表示包含了前面所有小數(shù)字表示的影響
1 srv_force_ignore_corrupt: 忽律檢查到corrupt頁
2 srv_force_no_background: 阻止Master Thread線程的運(yùn)行，如Master Thread線程需要進(jìn)行full purge操作，而這會(huì)導(dǎo)致crash。
3 srv_force_no_trx_undo: 不進(jìn)行事務(wù)的回滾操作
4 srv_force_no_ibuf_merge: 不進(jìn)行插入緩沖的合并操作
5 srv_force_no_undo_log_scan: 不查看撤銷日志(undo log),InnoDB存儲(chǔ)引擎會(huì)將未提交的事務(wù)視為已提交
6 srv_force_no_log_redo 不進(jìn)行前滾操作
注意：
當(dāng)參數(shù)innodb_force_recovery設(shè)置大于0的值，用戶可以對(duì)表進(jìn)行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，因?yàn)榛貪L了數(shù)據(jù)，所以啟動(dòng)時(shí)間會(huì)加長(zhǎng)，如果回滾數(shù)據(jù)非常多，啟動(dòng)時(shí)間就會(huì)很慢。
重新做一次實(shí)驗(yàn)，innodb_force_recovery設(shè)置為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. |
因?yàn)闆]有進(jìn)行回滾，所以啟動(dòng)很快就完成了。但是用戶應(yīng)當(dāng)小心當(dāng)前數(shù)據(jù)庫的狀態(tài)，并自己確認(rèn)是否不需要回滾事務(wù)的操作。
感謝各位的閱讀！關(guān)于“mysql中如何設(shè)置innodb_force_recovery”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
重慶分公司
重慶分公司
