MySQL数据库崩溃与恢复-日常问题

将mysql进行备份和恢复时（可以去看我的之前文章数据库的备份和迁移方法），启动运行后发生问题，可能由于相关表异常导致服务挂了，下面是进行排查和解决。

一、查找原因

1.1、启动日志报错

/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x55aa83c85d6d]
/usr/sbin/mysqld(handle_fatal_signal+0x32b) [0x55aa82b8353b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730) [0x7fa2b2a8a730]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x10b) [0x7fa2b21a07bb]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x121) [0x7fa2b218b535]
/usr/sbin/mysqld(+0xf7a11c) [0x55aa828c611c]
/usr/sbin/mysqld(+0x273cf00) [0x55aa84088f00]
/usr/sbin/mysqld(Fil_shard::do_io(IORequest const&, bool, page_id_t const&, page_size_t const&, unsigned long, unsigned long, void*, void*)+0xd45) [0x55aa8409eab5]
/usr/sbin/mysqld(fil_io(IORequest const&, bool, page_id_t const&, page_size_t const&, unsigned long, unsigned long, void*, void*)+0x57) [0x55aa8409eb37]
/usr/sbin/mysqld(buf_read_page_low(dberr_t*, bool, unsigned long, unsigned long, page_id_t const&, page_size_t const&, bool)+0x198) [0x55aa83ff4ea8]
/usr/sbin/mysqld(buf_read_page(page_id_t const&, page_size_t const&)+0x46) [0x55aa83ff5456]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::read_page()+0x35) [0x55aa83fc5185]
/usr/sbin/mysqld(Buf_fetch_normal::get(buf_block_t*&)+0xa0) [0x55aa83fce940]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::single_page()+0x52) [0x55aa83fceef2]
/usr/sbin/mysqld(buf_page_get_gen(page_id_t const&, page_size_t const&, unsigned long, buf_block_t*, Page_fetch, char const*, unsigned long, mtr_t*, bool)+0x1cd) [0x55aa83fcf74d]
/usr/sbin/mysqld(btr_cur_search_to_nth_level(dict_index_t*, unsigned long, dtuple_t const*, page_cur_mode_t, unsigned long, btr_cur_t*, unsigned long, char const*, unsigned long, mtr_t*)+0x605) [0x55aa83fa8945]
/usr/sbin/mysqld(row_ins_clust_index_entry_low(unsigned int, unsigned long, dict_index_t*, unsigned long, dtuple_t*, que_thr_t*, bool)+0x38e) [0x55aa83e8b2ee]
/usr/sbin/mysqld(row_ins_clust_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, bool)+0xbe) [0x55aa83e8f7ae]
/usr/sbin/mysqld(row_ins_step(que_thr_t*)+0x1b9) [0x55aa83e90599]
/usr/sbin/mysqld(+0x25561e4) [0x55aa83ea21e4]
/usr/sbin/mysqld(ha_innobase::write_row(unsigned char*)+0x26f) [0x55aa83d6e9cf]
/usr/sbin/mysqld(handler::ha_write_row(unsigned char*)+0x1a8) [0x55aa82c8af18]
/usr/sbin/mysqld(write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*)+0x60d) [0x55aa82ebf6ad]
/usr/sbin/mysqld(Sql_cmd_insert_values::execute_inner(THD*)+0x7d9) [0x55aa82ec08b9]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x2e5) [0x55aa82a9f7b5]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xac8) [0x55aa82a3f7e8]
/usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x43c) [0x55aa82a43f5c]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1f20) [0x55aa82a46310]
/usr/sbin/mysqld(do_command(THD*)+0x19c) [0x55aa82a4709c]
/usr/sbin/mysqld(+0x12289f0) [0x55aa82b749f0]
/usr/sbin/mysqld(+0x28739c4) [0x55aa841bf9c4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3) [0x7fa2b2a7ffa3]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7fa2b22624cf]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa1f01cbdd8): insert into t_third_interface_record(`interfaceFrom`, `interfaceUrl`, `interfaceReqParam`, `interfaceRespParam`,                                              `interfaceType`, `createTime`, `responseTime`)             value ('app', 'https://127.0.0.1/gateway/membership/api/common/getLechangeConfig', '', '{"code":"0","data":{"androidCode":"1","realmName":"2","iosCode":"3"},"errMsg":"","success":true}', 'post',                    now(), '176')
Connection ID (thread ID): 228
Status: NOT_KILLED

1.2、解决办法

• 修改mysql配置文件/etc/my.cnf

  [mysqld]
  innodb_force_recovery=6
  
  

  解释

  1 （SRV_FORCE_IGNORE_CORRUPT）
  
  使服务器即使检测到损坏的页面也可以运行 。尝试 跳过损坏的索引记录和页，这有助于转储表。 SELECT * FROM tbl_name
  
  2 （SRV_FORCE_NO_BACKGROUND）
  
  阻止主线程和任何清除线程运行。如果在清除操作期间发生意外退出，则此恢复值将阻止它。
  
  3 （SRV_FORCE_NO_TRX_UNDO）
  
  崩溃恢复后 不运行事务 回滚。
  
  4 （SRV_FORCE_NO_IBUF_MERGE）
  
  防止插入缓冲区合并操作。如果它们会导致崩溃，请不要这样做。不计算表 统计信息。此值可能会永久损坏数据文件。使用此值后，准备删除并重新创建所有二级索引。设置 InnoDB为只读。
  
  5 （SRV_FORCE_NO_UNDO_LOG_SCAN）
  
  启动数据库时 不查看撤消日志： InnoDB即使未完成的事务也视为已提交。此值可能会永久损坏数据文件。设置InnoDB为只读。
  
  6 （SRV_FORCE_NO_LOG_REDO）
  
  不进行与恢复有关的重做日志前 滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态，这反过来可能会使B树和其他数据库结构遭受更多破坏。设置 InnoDB为只读。
  
  

  参考：https://blog.youkuaiyun.com/qq_37746897/article/details/110133801