mysql 表损坏，强制恢复

mysql 表损坏，不要数据恢复启动

1. tail -f /var/log/mysql.log

   Trying to get some variables.
   Some pointers may be invalid and cause the dump to abort.
   Query (0): 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.
   2020-11-25T08:03:33.475166Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 5010)
   2020-11-25T08:03:33.475446Z 0 [Warning] Changed limits: table_open_cache: 1995 (requested 2000)
   2020-11-25T16:03:33.652991+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
   2020-11-25T16:03:33.653036+08:00 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
   2020-11-25T16:03:33.655364+08:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
   2020-11-25T16:03:33.655449+08:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29) starting as process 7458 ...
   2020-11-25T16:03:33.665369+08:00 0 [Note] InnoDB: PUNCH HOLE support available
   2020-11-25T16:03:33.665461+08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
   2020-11-25T16:03:33.665476+08:00 0 [Note] InnoDB: Uses event mutexes
   2020-11-25T16:03:33.665485+08:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
   2020-11-25T16:03:33.665511+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
   2020-11-25T16:03:33.665521+08:00 0 [Note] InnoDB: Using Linux native AIO
   2020-11-25T16:03:33.666545+08:00 0 [Note] InnoDB: Number of pools: 1
   2020-11-25T16:03:33.666771+08:00 0 [Note] InnoDB: Using CPU crc32 instructions
   2020-11-25T16:03:33.668987+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
   2020-11-25T16:03:33.679952+08:00 0 [Note] InnoDB: Completed initialization of buffer pool
   2020-11-25T16:03:33.682875+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
   2020-11-25T16:03:33.695209+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
   2020-11-25T16:03:33.696992+08:00 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 160341019
   2020-11-25T16:03:33.697035+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 160357079
   2020-11-25T16:03:33.697624+08:00 0 [Note] InnoDB: Database was not shutdown normally!
   2020-11-25T16:03:33.697654+08:00 0 [Note] InnoDB: Starting crash recovery.
   2020-11-25T16:03:33.705764+08:00 0 [Note] InnoDB: Starting an apply batch of log records to the database...
   InnoDB: Progress in percent: 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
   2020-11-25T16:03:34.208584+08:00 0 [Note] InnoDB: Apply batch completed
   2020-11-25T16:03:34.317602+08:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
   2020-11-25T16:03:34.317693+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
   2020-11-25T16:03:34.317808+08:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
   2020-11-25T16:03:34.353962+08:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
   2020-11-25T16:03:34.355577+08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
   2020-11-25T16:03:34.355620+08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
   2020-11-25T16:03:34.356444+08:00 0 [Note] InnoDB: Waiting for purge to start
   2020-11-25T16:03:34.357530+08:00 0 [ERROR] InnoDB: Corruption of an index tree: table `vb_tab`.`running_log` index `PRIMARY`, father ptr page no 20, child page no 22
   PHYSICAL RECORD: n_fields 14; compact format; info bits 32
    0: len 4; hex 8000942f; asc    /;;
    1: len 6; hex 000000027c6a; asc     |j;;
    2: len 7; hex 23000001fe0194; asc #      ;;
    3: len 30; hex 37623932373534342d326563322d313165622d383764622d303030633239; asc 7b927544-2ec2-11eb-87db-000c29; (total 36 bytes);
    4: len 5; hex 99a7f2f170; asc     p;;
    5: len 30; hex 7b2276756c6e65726162696c6974795f6e616d65223a2022584d4c5c7535; asc {"vulnerability_name": "XML\u5; (total 170 bytes);
    6: len 30; hex 323234363265396365343932613864396234643838303431396132653232; asc 22462e9ce492a8d9b4d880419a2e22; (total 32 bytes);
    7: len 4; hex 80004e4b; asc   NK;;
    8: len 30; hex 5b2268747470733a2f2f31302e31302e31302e37322f62776170702f696e; asc ["https://10.10.10.72/bwapp/in; (total 62 bytes);
    9: len 0; hex ; asc ;;
    10: len 4; hex 32303332; asc 2032;;
    11: SQL NULL;
    12: len 2; hex 8000; asc   ;;
    13: SQL NULL;
   2020-11-25T16:03:34.366477+08:00 0 [Note] InnoDB: n_owned: 0; heap_no: 2; next rec: 480
   PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    0: len 4; hex 80008cd9; asc     ;;
    1: len 4; hex 00000014; asc     ;;
   2020-11-25T16:03:34.367129+08:00 0 [Note] InnoDB: n_owned: 0; heap_no: 6; next rec: 142
   2020-11-25T16:03:34.367153+08:00 0 [ERROR] [FATAL] InnoDB: You should dump + drop + reimport the table to fix the corruption. If the crash happens at database startup. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. Then dump + drop + reimport.
   2020-11-25 16:03:34 0x7f1b6effd700  InnoDB: Assertion failure in thread 139755803105024 in file ut0ut.cc line 918
   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.
   08:03:34 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=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 = 405574 K  bytes of memory
   Hope that's ok; if not, decrease some variables in the equation.
   
   Thread pointer: 0x7f1b74000900
   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 = 7f1b6effcdb0 thread_stack 0x40000
   /usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf3dcfb]
   /usr/sbin/mysqld(handle_fatal_signal+0x461)[0x7e8041]
   /lib64/libpthread.so.0(+0xf5f0)[0x7f1b985935f0]
   /lib64/libc.so.6(gsignal+0x37)[0x7f1b96f7d337]
   /lib64/libc.so.6(abort+0x148)[0x7f1b96f7ea28]
   /usr/sbin/mysqld[0x7b8476]
   /usr/sbin/mysqld(_ZN2ib5fatalD1Ev+0xfd)[0x12a9cad]
   /usr/sbin/mysqld[0x12b3a4c]
   /usr/sbin/mysqld[0x12b3dcd]
   /usr/sbin/mysqld(_Z12btr_compressP9btr_cur_tmP5mtr_t+0xfaa)[0x12bb6aa]
   /usr/sbin/mysqld(_Z26btr_cur_compress_if_usefulP9btr_cur_tmP5mtr_t+0x1cc)[0x12c829c]
   /usr/sbin/mysqld(_Z26btr_cur_pessimistic_deleteP7dberr_tmP9btr_cur_tmbP5mtr_t+0x4a4)[0x12cdeb4]
   /usr/sbin/mysqld[0x120f975]
   /usr/sbin/mysqld(_Z14row_purge_stepP9que_thr_t+0x721)[0x1212331]
   /usr/sbin/mysqld(_Z15que_run_threadsP9que_thr_t+0x87c)[0x11be15c]
   /usr/sbin/mysqld(_Z9trx_purgemmb+0x679)[0x1272049]
   /usr/sbin/mysqld(srv_purge_coordinator_thread+0xded)[0x124a3bd]
   /lib64/libpthread.so.0(+0x7e65)[0x7f1b9858be65]
   /lib64/libc.so.6(clone+0x6d)[0x7f1b9704588d]
   
   Trying to get some variables.
   Some pointers may be invalid and cause the dump to abort.
   Query (0): 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.
   

2. 由上面日志知道是表索引损坏 running_log [ERROR] InnoDB: Corruption of an index tree: table vb_tab.running_log index PRIMARY, father ptr page no 20, child page no 22
   PHYSICAL RECORD: n_fields 14; compact format; info bits 32
3. 官方修复链接： http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
   
4. 修改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为只读。
   

5. 重启成功

   [root@localhost ~]# systemctl restart mysqld 
   [root@localhost ~]# 
   [root@localhost ~]# 
   [root@localhost ~]# systemctl status mysqld 
   ● mysqld.service - MySQL Server
      Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
      Active: active (running) since Wed 2020-11-25 16:17:55 CST; 23s ago
        Docs: man:mysqld(8)
              http://dev.mysql.com/doc/refman/en/using-systemd.html
     Process: 10572 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
     Process: 10551 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
    Main PID: 10575 (mysqld)
      CGroup: /system.slice/mysqld.service
              └─10575 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
   
   Nov 25 16:17:54 localhost.localdomain systemd[1]: Starting MySQL Server...
   Nov 25 16:17:55 localhost.localdomain systemd[1]: Started MySQL Server.
   [root@localhost ~]# 
   
   

6. 进入mysql 尝试check 一下状态和repair修复表

   mysql> check table running_log;
   ERROR 2006 (HY000): MySQL server has gone away
   No connection. Trying to reconnect...
   ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
   ERROR: 
   Can't connect to the server
   mysql> repair table running_log;
   

7. 操作都不成功，时间有限数据也不重要，没有去查如何修复表的索引，就只能忍痛，删除表了。

   mysql> drop tables running_log;
   No connection. Trying to reconnect...
   Connection id:    2
   Current database: vackbot
   
   Query OK, 0 rows affected (0.00 sec)
   

8. 恢复/etc/my.cnf配置文件，重启mysql服务即可
9. 总结如果遇到重要数据，谨慎操作。

• 表文件缺失官方修复参考： https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html
• 表索引修复参考： https://blog.csdn.net/taoliujun/article/details/6832230
• 表drop后无法创建参考： https://blog.csdn.net/hero_hope/article/details/82256458