MySQL 异常—— InnoDB 引擎故障

 

机房服务器异常掉电，通电后，MySQL无法启动。报错如下：

2020-05-11T10:30:09.270993Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:09.271053Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:10.271250Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:10.271344Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:11.271475Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:11.271517Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:12.271653Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:12.271727Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:13.271853Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:13.271915Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:14.272152Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:14.272215Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:15.272342Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:15.272415Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:16.272550Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:16.272612Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:17.272751Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:17.272819Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:18.272991Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:18.273046Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:19.273241Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:19.273323Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:20.275201Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:20.275299Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:21.277297Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:21.277358Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:22.277548Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:22.277591Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:23.278208Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:23.278303Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:24.278706Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:24.278769Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:25.280777Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:25.280853Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:26.282070Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:26.282163Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:27.283163Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:27.283234Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:28.283846Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:28.283915Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:29.284085Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:29.284173Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:30.284917Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:30.284954Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:31.286923Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:31.286989Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:32.287092Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:32.287189Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:33.287412Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:33.287483Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:34.287691Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:34.287756Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:35.287846Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:35.287918Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:36.288119Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:36.288149Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:37.288232Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:37.288339Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:38.288553Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:38.288609Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:39.288693Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:39.288755Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:40.288851Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:40.288911Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:41.288996Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:41.289055Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:42.289225Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:42.289291Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:43.289381Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:43.289452Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:44.289576Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:44.289611Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:45.289704Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:45.289780Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:46.289874Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:46.289939Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:47.290033Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:47.290134Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:30:48.290250Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-05-11T10:30:48.290347Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-05-11T10:31:05.585074Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-11T10:31:05.585152Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-05-11T10:31:05.585174Z 0 [Note] /opt/mysql/bin/mysqld (mysqld 5.7.26) starting as process 17257 ...
2020-05-11T10:31:05.588878Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-11T10:31:05.588903Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-11T10:31:05.588907Z 0 [Note] InnoDB: Uses event mutexes
2020-05-11T10:31:05.588910Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2020-05-11T10:31:05.588917Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-11T10:31:05.590072Z 0 [Note] InnoDB: Number of pools: 1
2020-05-11T10:31:05.590176Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2020-05-11T10:31:05.591215Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-05-11T10:31:05.597784Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-05-11T10:31:05.599903Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-05-11T10:31:05.611877Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-05-11T10:31:05.612999Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2282100175269
2020-05-11T10:31:05.758111Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282105417728
2020-05-11T10:31:05.897723Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282110660608
2020-05-11T10:31:06.055990Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282115903488
2020-05-11T10:31:06.215903Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282121146368
2020-05-11T10:31:06.372323Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282126389248
2020-05-11T10:31:06.532108Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282131632128
2020-05-11T10:31:06.683984Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282136875008
2020-05-11T10:31:06.712561Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282137734101
2020-05-11T10:31:06.712763Z 0 [Note] InnoDB: Database was not shutdown normally!
2020-05-11T10:31:06.712782Z 0 [Note] InnoDB: Starting crash recovery.
2020-05-11T10:31:06.725662Z 0 [Note] InnoDB: 2 transaction(s) which must be rolled back or cleaned up in total 6546 row operations to undo
2020-05-11T10:31:06.725681Z 0 [Note] InnoDB: Trx id counter is 4032256
2020-05-11T10:31:06.725685Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 2020-05-11T10:31:06.726071Z 0 [Warning] InnoDB: Retry attempts for reading partial data failed.
2020-05-11T10:31:06.726166Z 0 [ERROR] InnoDB: Tried to read 16384 bytes at offset 5160042496, but was only able to read 0
2020-05-11T10:31:06.726247Z 0 [ERROR] InnoDB: Operating system error number 5 in a file operation.
2020-05-11T10:31:06.726341Z 0 [ERROR] InnoDB: Error number 5 means "Input/output error"
2020-05-11T10:31:06.726348Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2020-05-11T10:31:06.726358Z 0 [ERROR] InnoDB: File (unknown): "read" returned OS error 105. Cannot continue operation
2020-05-11T10:31:06.726362Z 0 [ERROR] InnoDB: Cannot continue operation.
0 2020-05-11 18:31:07 0x7f7817fff700  InnoDB: Assertion failure in thread 140153775453952 in file log0recv.cc line 1922
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:31:07 UTC - mysqld got signal 6 ;
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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
​
key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=4096
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1635872 K  bytes of memory
Hope that"s ok; if not, decrease some variables in the equation.
​
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/opt/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf4fbe5]
/opt/mysql/bin/mysqld(handle_fatal_signal+0x4a4)[0x7d1f54]
/lib64/libpthread.so.0(+0xf5e0)[0x7f7834f275e0]
/lib64/libc.so.6(gsignal+0x37)[0x7f78339191f7]
/lib64/libc.so.6(abort+0x148)[0x7f783391a8e8]
/opt/mysql/bin/mysqld(_Z18ut_print_timestampP8_IO_FILE+0x0)[0x7c131e]
/opt/mysql/bin/mysqld[0x10a4e0b]
/opt/mysql/bin/mysqld(_Z22recv_recover_page_funcmP11buf_block_t+0x9e2)[0x10a9c12]
/opt/mysql/bin/mysqld(_Z20buf_page_io_completeP10buf_page_tb+0x43d)[0x11e999d]
/opt/mysql/bin/mysqld(_Z12fil_aio_waitm+0xfe)[0x125b84e]
/opt/mysql/bin/mysqld(io_handler_thread+0xd0)[0x1160a30]
/lib64/libpthread.so.0(+0x7e25)[0x7f7834f1fe25]
/lib64/libc.so.6(clone+0x6d)[0x7f78339dc34d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2020-05-11T10:32:09.705162Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-11T10:32:09.705233Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-05-11T10:32:09.705285Z 0 [Note] /opt/mysql/bin/mysqld (mysqld 5.7.26) starting as process 17672 ...
2020-05-11T10:32:09.709239Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-11T10:32:09.709269Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-11T10:32:09.709273Z 0 [Note] InnoDB: Uses event mutexes
2020-05-11T10:32:09.709276Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2020-05-11T10:32:09.709285Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-11T10:32:09.710851Z 0 [Note] InnoDB: Number of pools: 1
2020-05-11T10:32:09.710958Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2020-05-11T10:32:09.712230Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-05-11T10:32:09.718735Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-05-11T10:32:09.722759Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-05-11T10:32:09.734323Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-05-11T10:32:09.735820Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2282100175269
2020-05-11T10:32:09.880424Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282105417728
2020-05-11T10:32:10.021231Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282110660608
2020-05-11T10:32:10.181155Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282115903488
2020-05-11T10:32:10.338988Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282121146368
2020-05-11T10:32:10.495848Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282126389248
2020-05-11T10:32:10.652061Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282131632128
2020-05-11T10:32:10.801089Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282136875008
2020-05-11T10:32:10.829088Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2282137734101
2020-05-11T10:32:10.829294Z 0 [Note] InnoDB: Database was not shutdown normally!
2020-05-11T10:32:10.829302Z 0 [Note] InnoDB: Starting crash recovery.
2020-05-11T10:32:10.842734Z 0 [Note] InnoDB: 2 transaction(s) which must be rolled back or cleaned up in total 6546 row operations to undo
2020-05-11T10:32:10.842764Z 0 [Note] InnoDB: Trx id counter is 4032256
2020-05-11T10:32:10.842768Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 2020-05-11T10:32:10.843093Z 0 [Warning] InnoDB: Retry attempts for reading partial data failed.
2020-05-11T10:32:10.843188Z 0 [ERROR] InnoDB: Tried to read 16384 bytes at offset 5160042496, but was only able to read 0
2020-05-11T10:32:10.843228Z 0 [ERROR] InnoDB: Operating system error number 5 in a file operation.
2020-05-11T10:32:10.843255Z 0 [ERROR] InnoDB: Error number 5 means "Input/output error"
2020-05-11T10:32:10.843318Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2020-05-11T10:32:10.843340Z 0 [ERROR] InnoDB: File (unknown): "read" returned OS error 105. Cannot continue operation
2020-05-11T10:32:10.843343Z 0 [ERROR] InnoDB: Cannot continue operation.
2020-05-11T10:32:10.856592Z 0 [Warning] InnoDB: Retry attempts for reading partial data failed.
2020-05-11T10:32:10.856613Z 0 [ERROR] InnoDB: Tried to read 16384 bytes at offset 5146968064, but was only able to read 0
2020-05-11T10:32:10.856618Z 0 [ERROR] InnoDB: Operating system error number 5 in a file operation.
2020-05-11T10:32:10.856624Z 0 [ERROR] InnoDB: Error number 5 means "Input/output error"
2020-05-11T10:32:10.856627Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2020-05-11T10:32:10.856631Z 0 [ERROR] InnoDB: File (unknown): "read" returned OS error 105. Cannot continue operation
2020-05-11T10:32:10.856634Z 0 [ERROR] InnoDB: Cannot continue operation.
2020-05-11T10:32:21.046745Z 0 [ERROR] InnoDB: Waited for 10 seconds for 256 pending reads
2020-05-11T10:32:31.212563Z 0 [ERROR] InnoDB: Waited for 20 seconds for 256 pending reads
2020-05-11T10:32:41.369282Z 0 [ERROR] InnoDB: Waited for 30 seconds for 256 pending reads
2020-05-11T10:32:51.537779Z 0 [ERROR] InnoDB: Waited for 40 seconds for 256 pending reads
2020-05-11T10:33:01.692713Z 0 [ERROR] InnoDB: Waited for 50 seconds for 256 pending reads
2020-05-11T10:33:11.846935Z 0 [ERROR] InnoDB: Waited for 60 seconds for 256 pending reads
2020-05-11T10:33:22.003408Z 0 [ERROR] InnoDB: Waited for 70 seconds for 256 pending reads
2020-05-11T10:33:32.159642Z 0 [ERROR] InnoDB: Waited for 80 seconds for 256 pending reads
2020-05-11T10:33:42.317755Z 0 [ERROR] InnoDB: Waited for 90 seconds for 256 pending reads
2020-05-11T10:33:50.989827Z 0 [Warning] InnoDB: 10 threads created by InnoDB had not exited at shutdown!
​

异常分析

可能是掉电导致 InnoDB 引擎出现故障。

 

异常处理

调整参数

innodb_force_recovery = 1

如果不生效，尝试 2 - 6 几个数字，重启mysqld服务。

1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

 

数据备份

mysqldump -uroot -p123 test > test.sql

导出SQL脚本。或者用Navicat将所有数据库/表导入到其他服务器的数据库中。 注意：这里的数据一定要备份成功。然后删除原数据库中的数据。

 

删除ib_logfile0、ib_logfile1、ibdata1

备份MySQL数据目录下的ib_logfile0、ib_logfile1、ibdata1三个文件，然后将这三个文件删除

 

配置my.cnf

将my.cnf中innodb_force_recovery = 1或 2 - 6几个数字这行配置 删除

或者 配置为

innodb_force_recovery = 0

重启MySQL服务

将数据导入MySQL数据库

mysql -uroot -p123 test < test.sql; 或者用Navicat将备份的数据导入到数据库中。

此种方法下要注意的问题：

• ib_logfile0、ib_logfile1、ibdata1这三个文件一定要先备份后删除；

• 确认原数据导出成功了；

• 当数据导出成功后，删除原数据库中的数据时，如果提示不能删除，可在命令行进入MySQL的数据目录，手动删除相关数据库的文件夹或者数据库文件夹下的数据表文件，前提是数据一定导出或备份成功。