Use innodb_force_recovery to solve the problem that MySQL cannot be restarted after a crash.

Use innodb_force_recovery to solve the problem that MySQL cannot be restarted after a crash.

This article mainly introduces how to use innodb_force_recovery to solve the problem that MySQL crashes and cannot be restarted. This is a successful case and is not a panacea. You need to consider it as appropriate. If you need it, refer to the following:

Background

A startup friend's host reports the following error when restarting the MySQL service due to disk array damage to the machine crash:

The Code is as follows:

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 19 20 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 37 38 39 40 150125 16:12:51 [ERROR] mysqld got signal 11;

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

Or one of the libraries it was linked against is too upt, 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 cocould use up

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

41 Hope that.

2. Analysis

The main concern is the problem of mysqld got signal 11. From the log Content Analysis, the database crash on the machine causes log files to be damaged and cannot be restored after restart, making it unable to provide external services normally.

Solution 3

Because the log is damaged, we use unconventional methods here. First, modify the innodb_force_recovery parameter so that mysqld skips the recovery step, starts mysqld, exports the data, and then recreates the database.

Innodb_force_recovery can be set to 1-6. A large number contains the effects of all the preceding numbers.

1. (srv_force_ignore_0000upt): Ignore the checked upt page.

2. (SRV_FORCE_NO_BACKGROUND): prevents the running of the main thread. If the main thread needs to execute the full purge operation, crash may occur.

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

4. (SRV_FORCE_NO_IBUF_MERGE): Insert buffer merging is not performed.

5. (SRV_FORCE_NO_UNDO_LOG_SCAN): If you do not view redo logs, the InnoDB Storage engine considers uncommitted transactions as committed.

6. (SRV_FORCE_NO_LOG_REDO): do not roll back.

Note:

A. When the value of the parameter is greater than 0, select, create, and drop operations can be performed on the table, but insert, update, or delete operations are not allowed.

B When innodb_purge_threads and innodb_force_recovery are set together, a loop occurs:

The Code is as follows:

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:

The Code is as follows:

Innodb_force_recovery = 6

Innodb_purge_thread = 0

Restart MySQL

The Code is as follows:

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

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

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

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

Version: '5. 5.37-MariaDB-log' socket: '/tmp/mysql. sock' port: 3306 Source distribution

Perform logical export on the database immediately, set innodb_force_recovery to 0 and innodb_purge_thread to 1, and then recreate the database.

In addition, MySQL version 5.5 and earlier, when innodb_purge_threads = 1, innodb_force_recovery> 1, the aforementioned cycle reports a warning problem (= 1 ),

Cause:

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

The Code is as follows:

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_sleeping (1000000 );

} Else {

Break;

}

}

Therefore, when innodb_force_recovery> 1 needs to be set, innodb_purge_threads needs to be disabled and set to 0 (default ).

Summary

MySQL crash or MySQL database server crash may cause various problems, such as error 1594 between the master and slave (enabling crash-safe in MySQL 5.6 will avoid the error 1594 problem to the maximum extent, in the future, I will write a new 5.6 feature to introduce this feature), error 1236, log corruption, Data File Corruption, and so on. This case is just one of the following, carefully looking for relevant error prompts from the log, step by step.