Shulou(Shulou.com)05/31 Report--

This article is about how to set up innodb_force_recovery in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Mysql > show variables like'% innodb_force_recovery%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_force_recovery | 0 | |

+-+ +

The default value of this parameter is 0, which means that when recovery occurs, all recovery operations of the model cannot be performed effectively. For example, corruption,MySQL database may be down (crash) when a data page occurs, and errors are written to the error log.

This parameter can also be set to 6 non-zero values: 1-6. The large number representation contains the influence of all the previous small number representations.

1 srv_force_ignore_corrupt: suddenly checked to the corrupt page

2 srv_force_no_background: prevents Master Thread threads from running, such as Master Thread threads that need to perform full purge operations, which can lead to crash.

3 srv_force_no_trx_undo: no transaction rollback operation

4 srv_force_no_ibuf_merge: merge without inserting buffers

5 srv_force_no_undo_log_scan: without viewing the undo log (undo log), the InnoDB storage engine will treat uncommitted transactions as committed

6 srv_force_no_log_redo does not roll forward

Note:

When the parameter innodb_force_recovery is set to a value greater than 0, users can perform select,create and drop operations on the table, but DML operations such as insert\ update\ delete are not allowed.

Simulated fault

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 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 100952 612 pts/2 S + 15:00 0:00 tail-f / data/3306/logs/mysql_ray.err

Root 5889 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 100952 612 pts/2 S + 15:00 0:00 tail-f / data/3306/logs/mysql_ray.err

Root 5895 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

Here you see 581632 row operations to undo. Because the data is rolled back, the startup time will be longer. If you roll back a lot of data, the startup time will be very slow.

Do the experiment again, and innodb_force_recovery is set to 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 100952 612 pts/2 S + 15:00 0:00 tail-f / data/3306/logs/mysql_ray.err

Root 5916 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 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 100952 612 pts/2 S + 15:00 0:00 tail-f / data/3306/logs/mysql_ray.err

Root 5916 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 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.

Because there was no rollback, the startup was completed quickly. However, the user should be careful about the state of the current database and confirm for himself whether there is no need to roll back the transaction.

Thank you for reading! This is the end of the article on "how to set up innodb_force_recovery in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.