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

How to use innodb_force_recovery to solve the problem that MySQL server crash cannot be restarted? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

A background

The host of a startup friend reported the following error when restarting the MySQL service because the disk array damaged the machine crash:

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

InnoDB: Doing recovery: scanned up to log sequence number 9120034833

150125 16:12:51 InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 150125 16:12:51 [ERROR] mysqld got signal 11

This could be because you hit a bug. It is also possible that this binary

Or one of the libraries it was linked against is corrupt, improperly built

Or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help

Diagnose the problem, but since we have already crashed

Something is definitely wrong and this may fail.

Server version: 5.5.37-MariaDB-log

Key_buffer_size=268435456

Read_buffer_size=1048576

Max_used_connections=0

Max_threads=1002

Thread_count=0

It is possible that mysqld could use up to

Key_buffer_size + (read_buffer_size + sort_buffer_size) * max_threads = 2332093 K bytes of memory

41 Hope that.

Second analysis

Mainly concerned with the problem of mysqld got signal 11, from the log content analysis, the database in the machine crash caused damage to the log file, can not recover normally after reboot, let alone normal external services.

Three solutions

Because the log has been corrupted, unconventional measures are used here, first modify the innodb_force_recovery parameters to make mysqld skip the recovery step, start the mysqld, export the data, and then rebuild the database.

Innodb_force_recovery can be set to 1-6, and large numbers contain the effects of all previous numbers.

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.

Be careful

A when the setting parameter value is greater than 0, you can select,create,drop the table, but operations such as insert,update or delete are not allowed.

B A loop phenomenon occurs when innodb_purge_threads and innodb_force_recovery are set together:

150125 17:07:42 InnoDB: Waiting for the background threads to start

150125 17:07:43 InnoDB: Waiting for the background threads to start

150125 17:07:44 InnoDB: Waiting for the background threads to start

150125 17:07:45 InnoDB: Waiting for the background threads to start

150125 17:07:46 InnoDB: Waiting for the background threads to start

150125 17:07:47 InnoDB: Waiting for the background threads to start

Modify the following two parameters in my.cnf

Innodb_force_recovery=6

Innodb_purge_thread=0

Restart MySQL

150125 17:10:47 [Note] Crash recovery finished.

150125 17:10:47 [Note] Server socket created on IP: '0.0.0.09.

150125 17:10:47 [Note] Event Scheduler: Loaded 0 events

150125 17:10:47 [Note] / vdata/webserver/mysql/bin/mysqld: ready for connections.

Version: '5.5.37 MariaDB Version log' socket:' / tmp/mysql.sock' port: 3306 Source distribution

Logically export the database immediately, set innodb_force_recovery to 0, innodb_purge_thread=1 when finished, and then rebuild the database.

In addition, the cyclic reporting warning problem mentioned above occurs when innodb_purge_threads = 1 pure database forceful recovery > 1 in MySQL version 5.5 and before (= 1 has no problem)

Reason:

The source code of MySQL shows that a loop loop occurs when innodb_purge_threads and innodb_force_recovery are set together.

While (srv_shutdown_state = = SRV_SHUTDOWN_NONE) {

If (srv_thread_has_reserved_slot (SRV_MASTER) = = ULINT_UNDEFINED

| | (srv_n_purge_threads = = 1) |

& & srv_thread_has_reserved_slot (SRV_WORKER)

= = ULINT_UNDEFINED)) {

Ut_print_timestamp (stderr)

Fprintf (stderr, "InnoDB: Waiting for the background threads to start\ n")

Os_thread_sleep (1000000)

} else {

Break

}

}

So when you need to set innodb_force_recovery > 1, you need to turn off innodb_purge_threads and set it to 0 (the default).

Four summaries

MySQL crash or MySQL database server crash will cause a variety of problems, such as error 1594 between master and slave (when crash-safe is enabled in version 5.6, the problem of error 1594 will be avoided as much as possible.), error 1236, log corruption, data file corruption, etc., this case is only one of them, careful from the log to find the relevant error tips, step by step to solve.

This is the answer to the question about how to use innodb_force_recovery to solve the problem that MySQL server crash cannot be restarted. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.