"Case" using Innodb_force_recovery to resolve MySQL server crash cannot restart issue

A background
A host of friends of a startup because the disk array is damaging the machine crash, restart the MySQL service times with the following error:

2. innodb:reading tablespace information from the. ibd files ...
   
4. innodb:restoring possible Half-written data pages from the Doublewrite
   
6. Innodb:buffer ...
   
8. Innodb:doing recovery:scanned up to log sequence number 9120034833
   
10. 150125 16:12:51 innodb:starting An apply batch of logs records to the database ...
    
12. 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 3 7 150125 16:12:51 [ERROR] mysqld got signal 11;
    
14. This could is because a bug. It is also possible the this binary
    
16. Or one of the libraries it was linked against IS corrupt, improperly built,
    
18. Or misconfigured. This error can also is caused by malfunctioning hardware.
    
20. To report this bug, see Http://kb.askmonty.org/en/reporting-bugs
    
22. We'll try our best to scrape up some info, that'll hopefully help
    
24. Diagnose the problem, but since we have already crashed,
    
26. Something is definitely wrong and this may fail.
    
28. Server Version:5.5.37-mariadb-log
    
30. key_buffer_size=268435456
    
32. read_buffer_size=1048576
    
34. Max_used_connections=0
    
36. max_threads=1002
    
38. Thread_count=0
    
40. It is possible this mysqld could use
    
42. Key_buffer_size + (read_buffer_size + sort_buffer_size) *max_threads = 2332093 K bytes of memory
    
44. Hope that.

Second Analysis
The main concern mysqld got signal 11 problem, from the log content analysis, the database in the machine crash cause log file corruption, restart can not be normal recovery, but also unable to provide services to the outside.

Three Solutions
Because the log is corrupted, here is unconventional means, first modify the Innodb_force_recovery parameter, so that mysqld skip the recovery step, will mysqld start, export data and then rebuild the database.
The innodb_force_recovery can be set to 1-6, and the large number contains the effect of all previous numbers.
1. (srv_force_ignore_corrupt): Ignores the corrupt page that is checked.
2. (srv_force_no_background): Prevent the main thread from running, such as the main thread needs to perform full purge operation, will cause crash.
3. (Srv_force_no_trx_undo): The transaction rollback operation is not performed.
4. (srv_force_no_ibuf_merge): does not perform the insert buffer merge operation.
5. (Srv_force_no_undo_log_scan): Do not look for the log, InnoDB storage engine will not commit the transaction as committed.
6. (Srv_force_no_log_redo): Do not roll forward operations.
Note
A when the set parameter value is greater than 0, the table can be select,create,drop, but the insert,update or delete operation is not allowed.
B when Innodb_purge_threads and Innodb_force_recovery are set up, a loop phenomenon occurs:

1. 150125 17:07:42 innodb:waiting for the background threads to start
   
2. 150125 17:07:43 innodb:waiting for the background threads to start
   
3. 150125 17:07:44 innodb:waiting for the background threads to start
   
4. 150125 17:07:45 innodb:waiting for the background threads to start
   
5. 150125 17:07:46 innodb:waiting for the background threads to start
   
6. 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

1. 150125 17:10:47 [Note] Crash recovery finished.
   
2. 150125 17:10:47 [Note] Server socket created on IP: ' 0.0.0.0 '.
   
3. 150125 17:10:47 [Note] Event scheduler:loaded 0 Events
   
4. 150125 17:10:47 [Note]/vdata/webserver/mysql/bin/mysqld:ready for connections.
   
5. Version: ' 5.5.37-mariadb-log ' socket: '/tmp/mysql.sock ' port:3306 Source distribution

Do a logical export of the database immediately, set Innodb_force_recovery to 0 after completion, innodb_purge_thread=1, and then rebuild the database.
In addition to MySQL version 5.5 as well as before, when Innodb_purge_threads =1,innodb_force_recovery >1 situation will appear above mentioned in the Circular report warning problem (=1 no problem),
Reason:
The MySQL source code shows that when Innodb_purge_threads and Innodb_force_recovery are set up a loop loop will appear

2. while (srv_shutdown_state = = Srv_shutdown_none) {
   
4. if (Srv_thread_has_reserved_slot (srv_master) = = ulint_undefined
   
6. || (Srv_n_purge_threads = = 1
   
8. && Srv_thread_has_reserved_slot (Srv_worker)
   
10. = = ulint_undefined)) {
    
12. Ut_print_timestamp (stderr);
    
14. fprintf (stderr, "innodb:waiting for the background threads to start\n");
16. Os_thread_sleep (1000000);
    
18. } else {
    
20. Break
    
22. }
    
24. }

So when you need to set innodb_force_recovery>1, you need to turn off Innodb_purge_threads, set to 0 (default).

Four Summary
MySQL crash or MySQL database server crash will cause a variety of problems, such as the error 1594 between the main standby (5.6 version of the Crash-safe , will avoid the greatest degree of E Rror 1594 problem, will write 5.6 new features introduced this feature),error 1236, log corruption , data file corruption , and so on, this case is just one of them, Carefully from the log to find the relevant error prompt, and gradually solve.

"Case" using Innodb_force_recovery to resolve MySQL server crash cannot restart issue