MySQL kill进程后出现killed

约定不等于承诺〃 2022-10-18 00:59 367阅读 0赞

### 文章目录 ###

*  一.问题描述
 *  二.解决方案
 *  三.额外的一个报错
 *  四.启用innodb\_force\_recovery=3
 *  参考:

# 一.问题描述 #

拷贝一个大表的表数据的时候，等待时间太久，就在前台通过CTRL+C的方式停掉了。

mysql> create table fact_sale_new as select * from fact_sale;
    ^C^C -- query aborted
    
    ^C^C -- query aborted
    ERROR 2013 (HY000): Lost connection to MySQL server during query
    No connection. Trying to reconnect...
    Connection id:    16
    Current database: test
    
    ^C^C -- query aborted
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted
    mysql>

通过show processlist查找到对应的进程，然后进行kill，结果kill完了，依旧在进程列表里，只是被标记为killed

# 二.解决方案 #

查看innodb的事务表，发现刚才kill的语句正在进行回滚操作。  
trx\_rows\_modified 代表锁影响的行数，当数值为0时，锁将会释放。

mysql> SELECT * FROM information_schema.INNODB_TRX\G
    *************************** 1. row ***************************
                        trx_id: 71735
                     trx_state: ROLLING BACK
                   trx_started: 2021-06-03 14:17:40
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 462200354
           trx_mysql_thread_id: 8
                     trx_query: create table fact_sale_new as select * from fact_sale
           trx_operation_state: rollback of SQL statement
             trx_tables_in_use: 1
             trx_tables_locked: 9
              trx_lock_structs: 1370030
         trx_lock_memory_bytes: 234823888
               trx_rows_locked: 502015315
             trx_rows_modified: 460830324       #代表锁影响的行数，当数值为0时，锁将会释放
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
     trx_adaptive_hash_latched: 0
     trx_adaptive_hash_timeout: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
           trx_schedule_weight: NULL
    1 row in set (0.00 sec)

最好的办法就是等待innodb自己将事务进行回滚，除此之外，也可以innodb\_force\_recovery=3不执行事务回滚操作 启动数据库 (慎用)

**INNODB\_TRX表列的备注:**

# 三.额外的一个报错 #

在回滚的过程中，错误日志报错了:

-------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
     insert 0, delete mark 0, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 138401, node heap has 0 buffer(s)
    Hash table size 138401, node heap has 0 buffer(s)
    Hash table size 138401, node heap has 0 buffer(s)
    Hash table size 138401, node heap has 0 buffer(s)
    Hash table size 138401, node heap has 1 buffer(s)
    Hash table size 138401, node heap has 0 buffer(s)
    Hash table size 138401, node heap has 1 buffer(s)
    Hash table size 138401, node heap has 1 buffer(s)
    173.64 hash searches/s, 108167.44 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number          338551704131
    Log buffer assigned up to    338551704131
    Log buffer completed up to   338551704131
    Log written up to            338551704131
    Log flushed up to            338551704131
    Added dirty pages up to      338551704131
    Pages flushed up to          338547869736
    Last checkpoint at           338547869736
    84290665 log i/o's done, 3753.20 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total large memory allocated 548143104
    Dictionary memory allocated 491209
    Buffer pool size   32768
    Free buffers       1026
    Database pages     3896
    Old database pages 1418
    Modified db pages  246
    Pending reads      0
    Pending writes: LRU 0, flush list 2, single page 0
    Pages made young 32417, not young 1432606662
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 8962186, created 4242134, written 5485858
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 348 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 3896, unzip_LRU len: 0
    I/O sum[21127]:cur[0], unzip sum[0]:cur[0]
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue

从报错日志来看，是mysql实例出现了问题，看来是回滚的时候出现了异常，然后报错了。

[root@hp2 ~]# service mysqld restart
    Shutting down MySQL...................................................................................................................................................................................................................................................................................................................................^C
    [root@hp2 ~]# 
    [root@hp2 ~]#

只能强制杀进程了

[root@hp2 ~]# ps -ef | grep mysqld
    root     25761     1  0 11:31 pts/2    00:00:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pid
    mysql    26015 25761 66 11:31 pts/2    03:21:22 /home/mysql8/mysql/bin/mysqld --basedir=/home/mysql8/mysql --datadir=/home/mysql8/mysql/data --plugin-dir=/home/mysql8/mysql/lib/plugin --user=mysql --log-error=/home/mysql8/mysql/mysql_error.log --pid-file=/home/mysql8/mysql/data/hp2.pid --port=3306
    root     27307 26545  0 16:35 pts/5    00:00:00 grep --color=auto mysqld
    [root@hp2 ~]# 
    [root@hp2 ~]# 
    [root@hp2 ~]# kill -9 26015
    [root@hp2 ~]# 
    [root@hp2 ~]# ps -ef | grep mysqld
    root     27317 26545  0 16:35 pts/5    00:00:00 grep --color=auto mysqld
    [root@hp2 ~]# 
    [root@hp2 ~]# 
    [root@hp2 ~]# service mysqld start
    Starting MySQL SUCCESS! 
    [root@hp2 ~]#

**我以为强制杀进程后，启动mysql就没问题了，结果登陆不上，错误日志报错如下:**

3 4 5 62021-06-03T08:37:45.806646Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
    2021-06-03T08:37:45.808695Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
    2021-06-03T08:37:45.808797Z 0 [System] [MY-010116] [Server] /home/mysql8/mysql/bin/mysqld (mysqld 8.0.25) starting as process 27941
    2021-06-03T08:37:45.810327Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
    2021-06-03T08:37:45.819286Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
    2021-06-03T08:37:45.879341Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:46.879804Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
     72021-06-03T08:37:47.880540Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:48.881364Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:49.882210Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:50.883154Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:51.884083Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
     82021-06-03T08:37:52.885124Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:53.885796Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:54.886681Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:55.887675Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
     92021-06-03T08:37:56.888684Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:57.889721Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:58.890804Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:37:59.891943Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
     102021-06-03T08:38:00.893099Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:01.894232Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:02.895428Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:03.896638Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
     112021-06-03T08:38:04.897903Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:05.899242Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:06.900686Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:07.902096Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
     122021-06-03T08:38:08.903490Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:09.904931Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:10.906337Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:11.907818Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
     132021-06-03T08:38:12.909311Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:13.910935Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:14.912520Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
    2021-06-03T08:38:15.914099Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
     142021-06-03T08:38:16.915254Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11

**杀掉所有mysql相关的进程，然后启动mysql服务**

[root@hp2 ~]# ps aux |grep mysql*
    root     27337  0.0  0.0  11828  1620 pts/5    S    16:35   0:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pid
    mysql    27588 81.1 10.9 1749344 874532 pts/5  Sl   16:35   3:54 /home/mysql8/mysql/bin/mysqld --basedir=/home/mysql8/mysql --datadir=/home/mysql8/mysql/data --plugin-dir=/home/mysql8/mysql/lib/plugin --user=mysql --log-error=/home/mysql8/mysql/mysql_error.log --pid-file=/home/mysql8/mysql/data/hp2.pid --port=3306
    root     28252  0.0  0.0 113560  1756 pts/5    S+   16:39   0:00 /bin/sh /usr/sbin/service mysqld restart
    root     28259  0.0  0.0  11688  1480 pts/5    S+   16:39   0:00 /bin/sh /etc/init.d/mysqld restart
    root     28275  0.1  0.0  11692  1544 pts/5    S+   16:39   0:00 /bin/sh /etc/init.d/mysqld start
    root     28283  0.0  0.0  11824  1600 pts/5    S+   16:39   0:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pid
    mysql    28543  0.6  3.5 1128632 284168 pts/5  Sl+  16:39   0:00 /home/mysql8/mysql/bin/mysqld --basedir=/home/mysql8/mysql --datadir=/home/mysql8/mysql/data --plugin-dir=/home/mysql8/mysql/lib/plugin --user=mysql --log-error=/home/mysql8/mysql/mysql_error.log --pid-file=/home/mysql8/mysql/data/hp2.pid --port=3306
    root     28736  0.0  0.0 112824   992 pts/1    S+   16:40   0:00 grep --color=auto mysql*
    [root@hp2 ~]# 
    [root@hp2 ~]# 
    [root@hp2 ~]# kill -9 27337 27588 28252 28259 28275 28283 28543 
    [root@hp2 ~]# 
    [root@hp2 ~]# 
    [root@hp2 ~]# ps aux |grep mysql* 
    root     28832  0.0  0.0 112824   992 pts/1    S+   16:41   0:00 grep --color=auto mysql*
    [root@hp2 ~]# 
    [root@hp2 ~]# 
    [root@hp2 ~]# service mysqld start
    Starting MySQL.......... SUCCESS! 
    [root@hp2 ~]# 
    [root@hp2 ~]# 
    [root@hp2 ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 8.0.25 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2021, Oracle and/or its affiliates.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> exit
    Bye
    [root@hp2 ~]# 
    [root@hp2 ~]#

# 四.启用innodb\_force\_recovery=3 #

通过第三步，我将mysql服务正常启动，但是依旧无法创建表

mysql>  CREATE TABLE `fact_sale_new` (
        ->    `id` bigint NOT NULL AUTO_INCREMENT,
        ->    `sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        ->    `prod_name` varchar(200) NOT NULL,
        ->    `sale_nums` int DEFAULT NULL,
        ->     PRIMARY KEY (`id`)
        ->  ) ENGINE=InnoDB;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

此时查看进程，没发现异常

然后查看innodb的事务表 innodb\_trx:  
连着查询了几次，trx\_rows\_modified的值没有发生变化，初步判断是上次mysql实例异常crash后出现了问题。  
从performance\_schema.data\_locks中可以看到锁的都是系统的表。

mysql> SELECT * FROM information_schema.INNODB_TRX\G
    *************************** 1. row ***************************
                        trx_id: 71735
                     trx_state: RUNNING
                   trx_started: 2021-06-03 17:26:06
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 106524135
           trx_mysql_thread_id: 0
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 7
              trx_lock_structs: 8
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 1
             trx_rows_modified: 106524127
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
     trx_adaptive_hash_latched: 0
     trx_adaptive_hash_timeout: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
           trx_schedule_weight: NULL
    1 row in set (0.00 sec)
    mysql> select * from performance_schema.data_locks\G
    *************************** 1. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:5:140420935904192
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 2
                 EVENT_ID: 1
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: innodb_ddl_log
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935904192
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 2. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:12:140420935904280
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 2
                 EVENT_ID: 1
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: columns
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935904280
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 3. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:16:140420935904368
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 2
                 EVENT_ID: 1
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: index_column_usage
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935904368
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 4. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:19:140420935904456
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 2
                 EVENT_ID: 1
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: indexes
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935904456
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 5. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:29:140420935904544
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 2
                 EVENT_ID: 1
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: tables
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935904544
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 6. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:30:140420935904632
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 2
                 EVENT_ID: 1
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: tablespace_files
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935904632
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 7. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:31:140420935904720
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 2
                 EVENT_ID: 1
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: tablespaces
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935904720
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 8. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:18446744069414584331:140420935904808
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 2
                 EVENT_ID: 1
            OBJECT_SCHEMA: 
              OBJECT_NAME: SDI_11
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935904808
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 9. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015241616:4294967294:1043:179:140420935901088
    ENGINE_TRANSACTION_ID: 71735
                THREAD_ID: 45
                 EVENT_ID: 14
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: tables
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: schema_id
    OBJECT_INSTANCE_BEGIN: 140420935901088
                LOCK_TYPE: RECORD
                LOCK_MODE: X,REC_NOT_GAP
              LOCK_STATUS: GRANTED
                LOCK_DATA: 5, 'fact_sale_new', 369
    *************************** 10. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015245040:29:140420935928832
    ENGINE_TRANSACTION_ID: 72198
                THREAD_ID: 45
                 EVENT_ID: 19
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: tables
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 140420935928832
                LOCK_TYPE: TABLE
                LOCK_MODE: IX
              LOCK_STATUS: GRANTED
                LOCK_DATA: NULL
    *************************** 11. row ***************************
                   ENGINE: INNODB
           ENGINE_LOCK_ID: 140421015245040:4294967294:1043:179:140420935925728
    ENGINE_TRANSACTION_ID: 72198
                THREAD_ID: 45
                 EVENT_ID: 19
            OBJECT_SCHEMA: mysql
              OBJECT_NAME: tables
           PARTITION_NAME: NULL
        SUBPARTITION_NAME: NULL
               INDEX_NAME: schema_id
    OBJECT_INSTANCE_BEGIN: 140420935925728
                LOCK_TYPE: RECORD
                LOCK_MODE: S,REC_NOT_GAP
              LOCK_STATUS: WAITING
                LOCK_DATA: 5, 'fact_sale_new', 369
    11 rows in set (0.01 sec)

修改配置文件，然后重启mysqld服务

innodb_force_recovery=3

**结果问题依旧,只能删除innodb\_force\_recovery=3参数，然后重新启动mysql服务**

ps aux |grep mysql*   
    kill -9 pid1 pid2
    service mysqld start

这次启动mysql服务花了一点时间，因为是回滚完成后才启动mysql的服务。

[root@hp2 ~]# service mysqld start
    Starting MySQL................................................ SUCCESS!

这次，终于正常了:

# 参考: #

1.  https://blog.csdn.net/m0\_37827567/article/details/82979767
2.  https://blog.csdn.net/zhang123456456/article/details/72854467