One background
Two analysis
The main concern is mysqld got signal 11. From the analysis of log content, the database can not recover normally after reboot and can not provide normal services to the outside world because of the damage of log files caused by crash.
Three solution
Because the log has been damaged, we adopt unconventional means to modify the innodb_force_recovery parameter to make mysqld skip the recovery step, start mysqld, export the data and rebuild the database.
innodb_force_recovery can be set to 1-6, with large numbers containing the effects of all the previous numbers.
1. (SRV_FORCE_IGNORE_CORRUPT): Ignore the corrupt page checked.
2. (SRV_FORCE_NO_BACKGROUND): Blocking the operation of the main thread, such as the need for the main thread to perform a full purge operation, results in crash.
3. (SRV_FORCE_NO_TRX_UNDO): Transaction rollback is not performed.
4. (SRV_FORCE_NO_IBUF_MERGE): The merge operation of insertion buffer is not performed.
5. (SRV_FORCE_NO_UNDO_LOG_SCAN): Without looking at the redo log, the InnoDB storage engine treats uncommitted transactions as committed.
6. (SRV_FORCE_NO_LOG_REDO): No rollforward operation is performed.
Attention
When the setting parameter value is greater than 0, the table can be selected, created, dropped operations, but insert,update or delete such operations are not allowed.
b When innodb_purge_threads and innodb_force_recovery are set together, a loop phenomenon occurs:
Modify the following two parameters in my.cnf
innodb_force_recovery=6
innodb_purge_thread=0
Restart MySQL
Immediately do the logical export to the database, after completion, set innodb_force_recovery to 0, innodb_purge_thread=1, and then rebuild the database.
In addition, MySQL version 5.5 and before, when innodb_purge_threads =1, innodb_force_recovery > 1, there will be the circular warning problem mentioned above (= 1 is no problem).
Reason:
MySQL source code shows that loop loops occur when innodb_purge_threads and innodb_force_recovery are set together
So when you need to set innodb_force_recovery > 1, you need to close innodb_purge_threads and set it to 0 (default).
Four summary
MySQL crash or MySQL database server crash can cause a variety of problems, such as between backups error 1594 (Version 5.6 opens crash-safe It will avoid the error 1594 problem to the greatest extent, and will write 5.6 new features to introduce this function in the future. error 1236 Log damage, data file damage, etc., this case is only one of them, carefully from the log to find the relevant error prompts, can be solved step by step.
The following error occurred when a friend of an entrepreneur restarted MySQL service because the disk array damaged the crash machine:
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.
Two analysis
The main concern is mysqld got signal 11. From the analysis of log content, the database can not recover normally after reboot and can not provide normal services to the outside world because of the damage of log files caused by crash.
Three solution
Because the log has been damaged, we adopt unconventional means to modify the innodb_force_recovery parameter to make mysqld skip the recovery step, start mysqld, export the data and rebuild the database.
innodb_force_recovery can be set to 1-6, with large numbers containing the effects of all the previous numbers.
1. (SRV_FORCE_IGNORE_CORRUPT): Ignore the corrupt page checked.
2. (SRV_FORCE_NO_BACKGROUND): Blocking the operation of the main thread, such as the need for the main thread to perform a full purge operation, results in crash.
3. (SRV_FORCE_NO_TRX_UNDO): Transaction rollback is not performed.
4. (SRV_FORCE_NO_IBUF_MERGE): The merge operation of insertion buffer is not performed.
5. (SRV_FORCE_NO_UNDO_LOG_SCAN): Without looking at the redo log, the InnoDB storage engine treats uncommitted transactions as committed.
6. (SRV_FORCE_NO_LOG_REDO): No rollforward operation is performed.
Attention
When the setting parameter value is greater than 0, the table can be selected, created, dropped operations, but insert,update or delete such operations are not allowed.
b When innodb_purge_threads and innodb_force_recovery are set together, a loop phenomenon occurs:
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.0'.
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-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
Immediately do the logical export to the database, after completion, set innodb_force_recovery to 0, innodb_purge_thread=1, and then rebuild the database.
In addition, MySQL version 5.5 and before, when innodb_purge_threads =1, innodb_force_recovery > 1, there will be the circular warning problem mentioned above (= 1 is no problem).
Reason:
MySQL source code shows that loop loops occur 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 close innodb_purge_threads and set it to 0 (default).
Four summary
MySQL crash or MySQL database server crash can cause a variety of problems, such as between backups error 1594 (Version 5.6 opens crash-safe It will avoid the error 1594 problem to the greatest extent, and will write 5.6 new features to introduce this function in the future. error 1236 Log damage, data file damage, etc., this case is only one of them, carefully from the log to find the relevant error prompts, can be solved step by step.