MySQL5.7 服務 crash 後無法啟動

事發背景

測試環境更換資料盤，直接采取線上将資料目錄暴力拷貝到新盤，然後将原服務關閉，啟用新盤。

服務是可以正常啟動的，但是沒多會開發就反應服務down了，錯誤日志輸出

2017-05-17 15:06:28 0x7ffdadff7700  InnoDB: Assertion failure in thread 140727522653952 in file trx0purge.cc line 167
InnoDB: Failing assertion: purge_sys->iter.trx_no <= purge_sys->rseg->last_trx_no
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.
07:06:28 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=2097152
max_used_connections=0
max_threads=1000
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 = 4117496 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7ffda8000900
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 = 7ffdadff6d98 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf32bd5]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x4a4)[0x77b844]
/lib64/libpthread.so.0(+0xf7e0)[0x7ffe223e17e0]
/lib64/libc.so.6(gsignal+0x35)[0x7ffe2108c5e5]
/lib64/libc.so.6(abort+0x175)[0x7ffe2108ddc5]
/usr/local/mysql/bin/mysqld[0x10c5da5]
/usr/local/mysql/bin/mysqld(_ZN20TrxUndoRsegsIterator8set_nextEv+0x322)[0x1093822]
/usr/local/mysql/bin/mysqld[0x1095ea3]
/usr/local/mysql/bin/mysqld(_Z9trx_purgemmb+0x6ad)[0x10974bd]
/usr/local/mysql/bin/mysqld[0x1076178]
/usr/local/mysql/bin/mysqld(srv_purge_coordinator_thread+0x86f)[0x107914f]
/lib64/libpthread.so.0(+0x7aa1)[0x7ffe223d9aa1]
/lib64/libc.so.6(clone+0x6d)[0x7ffe21142aad]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 0
Status: NOT_KILLED

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.           

檢視了提示的連結，可能的影響因素包括、

1、資料檔案或索引檔案有壞頁

2、mysqld服務在運作期間，有其它項目操作資料檔案和索引檔案

     這條和先前的操作有關，在mysqld服務運作期間我直接将資料檔案拷貝到了另一盤，這樣做很可能導緻副本的資料和索引是不一緻的

按照官網給出的建議對日志中的stack進行了解析

vim /data1/sql/mysqld.sym 
0xf32bd5
0x77b844
0x7f751eed67e0
0x7f751db815e5
0x7f751db82dc5
0x10c5da5
0x1093822
0x1095ea3
0x10974bd
0x1076178
0x107914f
0x7f751eeceaa1
0x7f751dc37aad

nm -D -n /usr/local/mysql/bin/mysqld > /data1/sql/mysqld.sym                         

resolve_stack_dump -s /data1/sql/mysqld.sym -n /data1/sql/mysqld.stack
0xf32bd5 my_print_stacktrace + 53
0x77b844 handle_fatal_signal + 1188
0x7f751eed67e0 _end + 486423968
0x7f751db815e5 _end + 466152357
0x7f751db82dc5 _end + 466158469
0x10c5da5 _Z23ut_dbg_assertion_failedPKcS0_m + 170
0x1093822 _ZN20TrxUndoRsegsIterator8set_nextEv + 802
0x1095ea3 _Z14trx_purge_stopv + 5363
0x10974bd _Z9trx_purgemmb + 1709
0x1076178 _Z26srv_get_active_thread_typev + 1048
0x107914f srv_purge_coordinator_thread + 2159
0x7f751eeceaa1 _end + 486391905
0x7f751dc37aad _end + 466899053           

找不出導緻crash的根本原因，暫時的解決方案是通過設定innodb_force_recovery來啟動

相關内容參考InnoDB Recovery

1、設定innodb_force_recovery 先後設定1和2都無效，最後設定為3後啟動成功

2、啟動後dump所有的資料庫，恢複到建立執行個體中