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

This article mainly explains "how to achieve startup, shutdown and recovery of key features of Innodb". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to start, shut down and restore key features of Innodb.

The parameter innodb_fast_shutdown affects the behavior that the storage engine of the table is Innodb. The value of this parameter can be 0, 1, 2, the default value is 1, and the full dynamic local setting is supported.

Set to 1: when MySQL is closed, no merge operation is done to clean up dirty pages and insert buffers, and dirty pages are not flushed to disk.

Set to 0: the merge operation of cleaning up dirty pages and inserting buffers will be done, and all dirty pages will be flushed to disk, but this time the speed of closing is also the slowest.

Set to 2: no merge operation will be done to clean up dirty pages and insert buffers, nor will dirty pages be flushed to disk, but will be flushed to redo log and start next time.

Recover in mysql.

The parameter innodb_force_recovery affects the recovery status of the entire Innodb storage engine. This value defaults to 0, which means that all restore operations are performed when a restore is needed. When an effective recovery cannot be performed, such as when a data page occurs, the corruption,Mysql database may go down and write the error to the error log.

However, in some cases, a complete restore operation may not be required. For example, during an alter table operation, an accident occurs and a rollback operation is performed on the Innodb table when the database is restarted. For a large watch, it takes a long time, maybe even a few hours. At this point, you can restore yourself, such as deleting the table and re-importing data into the table from the backup, which may be faster than the rollback operation.

Innodb_force_recovery can set 6 non-zero values:

1 (SRV_FORCE_IGNORE_CORRUPT): ignore checked corrupt pages.

2 (SRV_FORCE_NO_BACKGROUND): blocking the running of the main thread, if the main thread needs to perform full purge operations, will result in crash.

3 (SRV_FORCE_NO_TRX_UNDO): no transaction rollback operation is performed.

4 (SRV_FORCE_NO_IBUF_MERGE): merge operations that insert buffers are not performed.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): without viewing the redo log, the InnoDB storage engine treats uncommitted transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO): roll forward is not performed.

Note: when innodb_force_recovery is greater than 0, you can perform select, create and drop operations on the table, but operations such as insert, update or delete are not allowed.

Let's do an experiment to simulate the occurrence of the fault.

View the relevant parameters:

Mysql > show variables like 'innodb_force%' +-- +-+ | Variable_name | Value | +-+-+ | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | +- -+-+ 2 rows in set (0.01sec) mysql > show variables like 'innodb_fast%' +-- +-+ | Variable_name | Value | +-- +-+ | innodb_fast_shutdown | 1 | +-- +-+ 1 row in set (0.01sec)

Manually open the transaction, prevent auto commit, and update 1000000 records without committing.

Mysql > start transaction;Query OK, 0 rows affected (0.00 sec) mysql > update rank_item set city=5000 limit 1000000 *, 1000000 rows affected (4 min 34.50 sec) Rows matched: 1000000 Changed: 1000000 Warnings: 0

Then kill drops the mysql process, starts mysql, and looks at the error log output.

2020-08-05T07:07:20.271772Z 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions2020-08-05T07:07:20.271806Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2020-08-05T07:07:20.271826Z 0 [Note] InnoDB: Rolling back trx with id 393530 901725 rows to undo2020-08-05T07:07:20.271841Z 0 [Note] InnoDB: Creating shared tablespace for temporary tablesInnoDB: Progress in percents: 12020-08-05T07:07:20.272012Z 0 [Note] InnoDB: Setting file'. / ibtmp1' size to 12 MB. Physically writing the file full; Please wait... 2020-08-05T07:07:20.344665Z 0 [Note] InnoDB: File'. / ibtmp1' size is now 12 MB.2020-08-05T07:07:20.346201Z 0 [Note] InnoDB: 96 redo rollback segment (s) found. 96 redo rollback segment (s) are active.2020-08-05T07:07:20.346228Z 0 [Note] InnoDB: 32 non-redo rollback segment (s) are active.2020-08-05T07:07:20.346492Z 0 [Note] InnoDB: Waiting for purge to start2020-08-05T07:07:20.396629Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2487709568662020-08-05T07:07:20.396654Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 30517ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.) 2020-08-05T07:07:20.397332Z 0 [Note] InnoDB: Loading buffer pool (s) from / data/mysql/data/ib_buffer_pool2020-08-05T07:07:20.398205Z 0 [Note] Plugin 'FEDERATED' is disabled.2020-08-05T07:07:20.405796Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key2020-08-05T07:07:20.405835Z 0 [Note] Server hostname (bind-address):' *' Port: 160082020-08-05T07:07:20.405919Z 0 [Note] IPv6 is available.2020-08-05T07:07:20.405943Z 0 [Note] -': 'resolves to'::' 2020-08-05T07:07:20.406018Z 0 [Note] Server socket created on IP:':'. 2020-08-05T07:07:20.471375Z 0 [Note] Event Scheduler: Loaded 0 events2020-08-05T07:07:20.471641Z 0 [Note] / usr/local/mysql/bin/mysqld: ready for connections.Version: '5.7.26' socket:'/ tmp/mysqld.sock' port: 16008 Source distribution2020-08-05T07:07:31.464532Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10068ms. The settings might not be optimal. (flushed=5784 and evicted=0 During the time.) 23 45 67 82020-08-05T07:08:13.440447Z 0 [Note] InnoDB: Buffer pool (s) load completed at 200805 15:08:13 9 10 11 12 13 14 15 16 18 19 20 21 22 23 24 26 27 28 30 31 33 33 34 35 36 37 38 39 41 42 45 46 47 48 49 50 51 53 54 55 56 57 58 56 57 59 62 63 65 66 68 69 71 72 73 75 77 78 79 80 81 82 83 84 86 87 88 89 90 91 92 93 94 94 96 97 99 1002020-08-05T07:12:12.083704Z 0 [Note] InnoDB: Rollback of trx with id 393530 completed2020-08-05T07:12:12.083776Z 0 [Note] InnoDB: Rollback of non-prepared transactions completed

You can see that the rollback process takes a long time.

Thank you for reading, the above is "how to achieve startup, shutdown and recovery of key features of Innodb" content, after the study of this article, I believe you have a deeper understanding of how to achieve startup, shutdown and recovery of key features of Innodb, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.