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

Source: Internet
Author: User
Tags mysql version

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:

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

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

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.