mysql：设置innodb_force_recovery 导致无限loop

蔚落 2022-02-25 11:06 238阅读 0赞

当数据库设置innodb\_force\_recovery 参数并且重启的时候，

alter日志里面会打出无限多的Waiting for the background threads to start

那是因为 设置innodb\_force\_recovery 导致无限loop

原因：

mysql 原代码的脚本：

innodb\_purge\_thread 默认值是1

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;   
      \}   
  \}

解决的办法是 ：

在设置 innodb\_force\_recovery >2 的同时

也要加上：

innodb\_purge\_thread = 0

InnoDB: 1 transaction(s) which must be rolled back or cleaned up  
InnoDB: in total 27581870 row operations to undo  
InnoDB: Trx id counter is 14F00  
130507 14:13:12  InnoDB: Starting an apply batch of log records to the database...  
InnoDB: Progress in percents: 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 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55  
 56 57 58 59 60 61 62 63 64 65 66 67 68 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  
InnoDB: Apply batch completed  
InnoDB: Last MySQL binlog file position 0 479663790, file name /opt/mysql/log/mysql-bin.000392  
130507 14:13:17  InnoDB: Waiting for the background threads to start  
130507 14:13:18  InnoDB: Waiting for the background threads to start  
130507 14:13:19  InnoDB: Waiting for the background threads to start  
130507 14:13:20  InnoDB: Waiting for the background threads to start  
130507 14:13:21  InnoDB: Waiting for the background threads to start  
130507 14:13:22  InnoDB: Waiting for the background threads to start  
130507 14:13:23  InnoDB: Waiting for the background threads to start  
130507 14:13:24  InnoDB: Waiting for the background threads to start  
130507 14:13:25  InnoDB: Waiting for the background threads to start  
130507 14:13:26  InnoDB: Waiting for the background threads to start  
130507 14:13:27  InnoDB: Waiting for the background threads to start  
130507 14:13:28  InnoDB: Waiting for the background threads to start  
130507 14:13:29  InnoDB: Waiting for the background threads to start  
130507 14:13:30  InnoDB: Waiting for the background threads to start  
130507 14:13:31  InnoDB: Waiting for the background threads to start  
130507 14:13:32  InnoDB: Waiting for the background threads to start  
130507 14:13:33  InnoDB: Waiting for the background threads to start  
130507 14:13:34  InnoDB: Waiting for the background threads to start  
130507 14:13:35  InnoDB: Waiting for the background threads to start  
130507 14:13:36  InnoDB: Waiting for the background threads to start  
130507 14:13:37  InnoDB: Waiting for the background threads to start  
130507 14:13:38  InnoDB: Waiting for the background threads to start  
130507 14:13:39  InnoDB: Waiting for the background threads to start  
130507 14:13:40  InnoDB: Waiting for the background threads to start  
130507 14:13:41  InnoDB: Waiting for the background threads to start  
130507 14:13:42  InnoDB: Waiting for the background threads to start  
130507 14:13:43  InnoDB: Waiting for the background threads to start  
130507 14:13:44  InnoDB: Waiting for the background threads to start  
130507 14:13:45  InnoDB: Waiting for the background threads to start  
130507 14:13:46  InnoDB: Waiting for the background threads to start  
130507 14:13:47  InnoDB: Waiting for the background threads to start  
130507 14:13:48  InnoDB: Waiting for the background threads to start  
130507 14:13:49  InnoDB: Waiting for the background threads to start  
130507 14:13:50  InnoDB: Waiting for the background threads to start  
130507 14:13:51  InnoDB: Waiting for the background threads to start  
130507 14:13:52  InnoDB: Waiting for the background threads to start  
130507 14:13:53  InnoDB: Waiting for the background threads to start  
130507 14:13:54  InnoDB: Waiting for the background threads to start  
130507 14:13:55  InnoDB: Waiting for the background threads to start  
130507 14:13:56  InnoDB: Waiting for the background threads to start  
130507 14:13:57  InnoDB: Waiting for the background threads to start  
130507 14:13:58  InnoDB: Waiting for the background threads to start  
130507 14:13:59  InnoDB: Waiting for the background threads to start  
130507 14:14:00  InnoDB: Waiting for the background threads to start  
130507 14:14:01  InnoDB: Waiting for the background threads to start  
130507 14:14:02  InnoDB: Waiting for the background threads to start  
130507 14:14:03  InnoDB: Waiting for the background threads to start  
130507 14:14:04  InnoDB: Waiting for the background threads to start  
130507 14:14:05  InnoDB: Waiting for the background threads to start  
130507 14:14:06  InnoDB: Waiting for the background threads to start  
130507 14:14:07  InnoDB: Waiting for the background threads to start  
130507 14:14:08  InnoDB: Waiting for the background threads to start  
130507 14:14:09  InnoDB: Waiting for the background threads to start  
130507 14:14:10  InnoDB: Waiting for the background threads to start  
130507 14:14:11  InnoDB: Waiting for the background threads to start  
130507 14:14:12  InnoDB: Waiting for the background threads to start  
130507 14:14:13  InnoDB: Waiting for the background threads to start  
130507 14:14:14  InnoDB: Waiting for the background threads to start  
130507 14:14:15  InnoDB: Waiting for the background threads to start  
130507 14:14:16  InnoDB: Waiting for the background threads to start  
130507 14:14:17  InnoDB: Waiting for the background threads to start  
130507 14:14:18  InnoDB: Waiting for the background threads to start  
130507 14:14:19  InnoDB: Waiting for the background threads to start  
130507 14:14:20  InnoDB: Waiting for the background threads to start  
130507 14:14:21  InnoDB: Waiting for the background threads to start  
130507 14:14:22  InnoDB: Waiting for the background threads to start  
130507 14:14:23  InnoDB: Waiting for the background threads to start  
130507 14:14:24  InnoDB: Waiting for the background threads to start  
130507 14:14:25  InnoDB: Waiting for the background threads to start  
130507 14:14:26  InnoDB: Waiting for the background threads to start  
130507 14:14:27  InnoDB: Waiting for the background threads to start  
130507 14:14:28  InnoDB: Waiting for the background threads to start  
130507 14:14:29  InnoDB: Waiting for the background threads to start  
130507 14:14:30  InnoDB: Waiting for the background threads to start  
130507 14:14:31  InnoDB: Waiting for the background threads to start  
130507 14:14:32  InnoDB: Waiting for the background threads to start  
130507 14:14:33  InnoDB: Waiting for the background threads to start  
130507 14:14:34  InnoDB: Waiting for the background threads to start  
130507 14:14:35  InnoDB: Waiting for the background threads to start  
130507 14:14:36  InnoDB: Waiting for the background threads to start  
130507 14:14:37  InnoDB: Waiting for the background threads to start  
130507 14:14:38  InnoDB: Waiting for the background threads to start  
130507 14:14:39  InnoDB: Waiting for the background threads to start  
130507 14:14:40  InnoDB: Waiting for the background threads to start  
130507 14:14:41  InnoDB: Waiting for the background threads to start  
130507 14:14:42  InnoDB: Waiting for the background threads to start  
130507 14:14:43  InnoDB: Waiting for the background threads to start  
130507 14:14:44  InnoDB: Waiting for the background threads to start  
130507 14:14:45  InnoDB: Waiting for the background threads to start  
130507 14:14:46  InnoDB: Waiting for the background threads to start  
130507 14:14:47  InnoDB: Waiting for the background threads to start  
130507 14:14:48  InnoDB: Waiting for the background threads to start  
130507 14:14:49  InnoDB: Waiting for the background threads to start  
130507 14:14:50  InnoDB: Waiting for the background threads to start  
130507 14:14:51  InnoDB: Waiting for the background threads to start  
130507 14:14:52  InnoDB: Waiting for the background threads to start  
130507 14:14:53  InnoDB: Waiting for the background threads to start  
130507 14:14:54  InnoDB: Waiting for the background threads to start  
130507 14:14:55  InnoDB: Waiting for the background threads to start  
130507 14:14:56  InnoDB: Waiting for the background threads to start  
130507 14:14:57  InnoDB: Waiting for the background threads to start  
130507 14:14:58  InnoDB: Waiting for the background threads to start  
130507 14:14:59  InnoDB: Waiting for the background threads to start  
130507 14:15:00  InnoDB: Waiting for the background threads to start  
130507 14:15:01  InnoDB: Waiting for the background threads to start  
130507 14:15:02  InnoDB: Waiting for the background threads to start  
130507 14:15:03  InnoDB: Waiting for the background threads to start  
130507 14:15:04  InnoDB: Waiting for the background threads to start  
130507 14:15:05  InnoDB: Waiting for the background threads to start  
130507 14:15:06  InnoDB: Waiting for the background threads to start  
130507 14:15:07  InnoDB: Waiting for the background threads to start  
130507 14:15:08  InnoDB: Waiting for the background threads to start  
130507 14:15:09  InnoDB: Waiting for the background threads to start  
130507 14:15:10  InnoDB: Waiting for the background threads to start  
130507 14:15:11  InnoDB: Waiting for the background threads to start  
130507 14:15:12  InnoDB: Waiting for the background threads to start  
130507 14:15:13  InnoDB: Waiting for the background threads to start  
130507 14:15:14  InnoDB: Waiting for the background threads to start  
130507 14:15:15  InnoDB: Waiting for the background threads to start  
130507 14:15:16  InnoDB: Waiting for the background threads to start  
130507 14:15:17  InnoDB: Waiting for the background threads to start  
130507 14:15:18  InnoDB: Waiting for the background threads to start  
130507 14:15:19  InnoDB: Waiting for the background threads to start  
130507 14:15:20  InnoDB: Waiting for the background threads to start  
130507 14:15:21  InnoDB: Waiting for the background threads to start  
130507 14:15:22  InnoDB: Waiting for the background threads to start  
130507 14:15:23  InnoDB: Waiting for the background threads to start  
130507 14:15:24  InnoDB: Waiting for the background threads to start  
130507 14:15:25  InnoDB: Waiting for the background threads to start  
130507 14:15:26  InnoDB: Waiting for the background threads to start  
130507 14:15:27  InnoDB: Waiting for the background threads to start  
130507 14:15:28  InnoDB: Waiting for the background threads to start  
130507 14:15:29  InnoDB: Waiting for the background threads to start  
130507 14:15:30  InnoDB: Waiting for the background threads to start  
130507 14:15:31  InnoDB: Waiting for the background threads to start  
130507 14:15:32  InnoDB: Waiting for the background threads to start  
130507 14:15:33  InnoDB: Waiting for the background threads to start  
130507 14:15:34  InnoDB: Waiting for the background threads to start  
130507 14:15:35  InnoDB: Waiting for the background threads to start  
130507 14:15:36  InnoDB: Waiting for the background threads to start  
130507 14:15:37  InnoDB: Waiting for the background threads to start  
130507 14:15:38  InnoDB: Waiting for the background threads to start  
130507 14:15:39  InnoDB: Waiting for the background threads to start  
130507 14:15:40  InnoDB: Waiting for the background threads to start  
130507 14:15:41  InnoDB: Waiting for the background threads to start  
130507 14:15:42  InnoDB: Waiting for the background threads to start  
130507 14:15:43  InnoDB: Waiting for the background threads to start  
130507 14:15:44  InnoDB: Waiting for the background threads to start  
130507 14:15:45  InnoDB: Waiting for the background threads to start  
130507 14:15:46  InnoDB: Waiting for the background threads to start  
130507 14:15:47  InnoDB: Waiting for the background threads to start  
130507 14:15:48  InnoDB: Waiting for the background threads to start  
130507 14:15:49  InnoDB: Waiting for the background threads to start  
130507 14:15:50  InnoDB: Waiting for the background threads to start  
130507 14:15:51  InnoDB: Waiting for the background threads to start  
130507 14:15:52  InnoDB: Waiting for the background threads to start  
130507 14:15:53  InnoDB: Waiting for the background threads to start  
130507 14:15:54  InnoDB: Waiting for the background threads to start  
130507 14:15:55  InnoDB: Waiting for the background threads to start  
130507 14:15:56  InnoDB: Waiting for the background threads to start  
130507 14:15:57  InnoDB: Waiting for the background threads to start  
130507 14:15:58  InnoDB: Waiting for the background threads to start  
130507 14:15:59  InnoDB: Waiting for the background threads to start  
130507 14:16:00  InnoDB: Waiting for the background threads to start  
130507 14:16:01  InnoDB: Waiting for the background threads to start  
130507 14:16:02  InnoDB: Waiting for the background threads to start  
130507 14:16:03  InnoDB: Waiting for the background threads to start  
130507 14:16:04  InnoDB: Waiting for the background threads to start  
130507 14:16:05  InnoDB: Waiting for the background threads to start  
130507 14:16:06  InnoDB: Waiting for the background threads to start  
130507 14:16:07  InnoDB: Waiting for the background threads to start  
130507 14:16:08  InnoDB: Waiting for the background threads to start  
130507 14:16:09  InnoDB: Waiting for the background threads to start  
130507 14:16:10  InnoDB: Waiting for the background threads to start  
130507 14:16:11  InnoDB: Waiting for the background threads to start  
130507 14:16:12  InnoDB: Waiting for the background threads to start  
130507 14:16:13  InnoDB: Waiting for the background threads to start  
130507 14:16:14  InnoDB: Waiting for the background threads to start  
130507 14:16:15  InnoDB: Waiting for the background threads to start  
130507 14:16:16  InnoDB: Waiting for the background threads to start  
130507 14:16:17  InnoDB: Waiting for the background threads to start  
130507 14:16:18  InnoDB: Waiting for the background threads to start  
130507 14:16:19  InnoDB: Waiting for the background threads to start  
130507 14:16:20  InnoDB: Waiting for the background threads to start  
130507 14:16:21  InnoDB: Waiting for the background threads to start  
130507 14:16:22  InnoDB: Waiting for the background threads to start  
130507 14:16:23  InnoDB: Waiting for the background threads to start  
130507 14:16:24  InnoDB: Waiting for the background threads to start  
130507 14:16:25  InnoDB: Waiting for the background threads to start  
130507 14:16:26  InnoDB: Waiting for the background threads to start  
130507 14:16:27  InnoDB: Waiting for the background threads to start  
130507 14:16:28  InnoDB: Waiting for the background threads to start  
130507 14:16:29  InnoDB: Waiting for the background threads to start  
130507 14:16:30  InnoDB: Waiting for the background threads to start  
130507 14:16:31  InnoDB: Waiting for the background threads to start  
130507 14:16:32  InnoDB: Waiting for the background threads to start  
130507 14:16:33  InnoDB: Waiting for the background threads to start  
130507 14:16:34  InnoDB: Waiting for the background threads to start  
130507 14:16:35  InnoDB: Waiting for the background threads to start  
130507 14:16:36  InnoDB: Waiting for the background threads to start  
130507 14:16:37  InnoDB: Waiting for the background threads to start  
130507 14:16:38  InnoDB: Waiting for the background threads to start  
130507 14:16:39  InnoDB: Waiting for the background threads to start  
130507 14:16:40  InnoDB: Waiting for the background threads to start  
130507 14:16:41  InnoDB: Waiting for the background threads to start  
130507 14:16:42  InnoDB: Waiting for the background threads to start  
130507 14:16:43  InnoDB: Waiting for the background threads to start  
130507 14:16:44  InnoDB: Waiting for the background threads to start  
130507 14:16:45  InnoDB: Waiting for the background threads to start  
130507 14:16:46  InnoDB: Waiting for the background threads to start  
130507 14:16:47  InnoDB: Waiting for the background threads to start  
130507 14:16:48  InnoDB: Waiting for the background threads to start  
130507 14:16:49  InnoDB: Waiting for the background threads to start  
130507 14:16:50  InnoDB: Waiting for the background threads to start  
130507 14:16:51  InnoDB: Waiting for the background threads to start  
130507 14:16:52  InnoDB: Waiting for the background threads to start  
130507 14:16:53  InnoDB: Waiting for the background threads to start  
130507 14:16:54  InnoDB: Waiting for the background threads to start  
130507 14:16:55  InnoDB: Waiting for the background threads to start  
130507 14:16:56  InnoDB: Waiting for the background threads to start  
130507 14:16:57  InnoDB: Waiting for the background threads to start  
130507 14:16:58  InnoDB: Waiting for the background threads to start  
130507 14:16:59  InnoDB: Waiting for the background threads to start  
130507 14:17:00  InnoDB: Waiting for the background threads to start  
130507 14:17:01  InnoDB: Waiting for the background threads to start  
130507 14:17:02  InnoDB: Waiting for the background threads to start  
130507 14:17:03  InnoDB: Waiting for the background threads to start  
130507 14:17:04  InnoDB: Waiting for the background threads to start  
130507 14:17:05  InnoDB: Waiting for the background threads to start  
130507 14:17:06  InnoDB: Waiting for the background threads to start  
130507 14:17:07  InnoDB: Waiting for the background threads to start  
130507 14:17:08  InnoDB: Waiting for the background threads to start  
130507 14:17:09  InnoDB: Waiting for the background threads to start  
130507 14:17:10  InnoDB: Waiting for the background threads to start  
130507 14:17:11  InnoDB: Waiting for the background threads to start  
130507 14:17:12  InnoDB: Waiting for the background threads to start  
130507 14:17:13  InnoDB: Waiting for the background threads to start  
130507 14:17:14  InnoDB: Waiting for the background threads to start  
130507 14:17:15  InnoDB: Waiting for the background threads to start  
130507 14:17:16  InnoDB: Waiting for the background threads to start  
130507 14:17:17  InnoDB: Waiting for the background threads to start  
130507 14:17:18  InnoDB: Waiting for the background threads to start  
130507 14:17:19  InnoDB: Waiting for the background threads to start  
130507 14:17:20  InnoDB: Waiting for the background threads to start  
130507 14:17:21  InnoDB: Waiting for the background threads to start  
130507 14:17:22  InnoDB: Waiting for the background threads to start  
130507 14:17:23  InnoDB: Waiting for the background threads to start  
130507 14:17:24  InnoDB: Waiting for the background threads to start  
130507 14:17:25  InnoDB: Waiting for the background threads to start  
130507 14:17:26  InnoDB: Waiting for the background threads to start  
130507 14:17:27  InnoDB: Waiting for the background threads to start  
130507 14:17:28  InnoDB: Waiting for the background threads to start  
130507 14:17:29  InnoDB: Waiting for the background threads to start