天天看點
傳回

MySQL kill程序後出現killed一.問題描述二.解決方案三.額外的一個報錯四.啟用innodb_force_recovery=3參考:

文章目錄

  • 一.問題描述
  • 二.解決方案
  • 三.額外的一個報錯
  • 四.啟用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

mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+-------------------------------------------------------+
| Id | User            | Host      | db   | Command | Time  | State                  | Info                                                  |
+----+-----------------+-----------+------+---------+-------+------------------------+-------------------------------------------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 13702 | Waiting on empty queue | NULL                                                  |
|  8 | root            | localhost | test | Query   |  3760 | System lock            | create table fact_sale_new as select * from fact_sale |
| 10 | root            | localhost | test | Query   |     0 | init                   | show processlist                                      |
+----+-----------------+-----------+------+---------+-------+------------------------+-------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> kill 8;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show processlist;
+----+-----------------+-----------+------+------------+-------+-------------------------+-------------------------------------------------------+
| Id | User            | Host      | db   | Command    | Time  | State                   | Info                                                  |
+----+-----------------+-----------+------+------------+-------+-------------------------+-------------------------------------------------------+
|  5 | event_scheduler | localhost | NULL | Daemon     | 13712 | Waiting on empty queue  | NULL                                                  |
|  8 | root            | localhost | test | Killed     |  3770 | System lock             | create table fact_sale_new as select * from fact_sale |
| 10 | root            | localhost | test | Query      |     0 | init                    | show processlist                                      |
| 16 | root            | localhost | test | Field List |     2 | Waiting for table flush | NULL                                                  |
+----+-----------------+-----------+------+------------+-------+-------------------------+-------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

二.解決方案

檢視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表列的備注:

desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field                      | Type                | Null | Key | Default             | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id                     | varchar(18)         | NO   |     |                     |       |#事務ID
| trx_state                  | varchar(13)         | NO   |     |                     |       |#事務狀态：
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事務開始時間；
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#innodb_locks.lock_id
| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事務開始等待的時間
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事務線程ID
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具體SQL語句
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事務目前操作狀态
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事務中有多少個表被使用
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#事務擁有多少個鎖
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事務鎖住的記憶體大小（B）
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事務鎖住的行數
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#事務更改的行數
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事務并發票數
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事務隔離級别
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性檢查
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外鍵檢查
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最後的外鍵錯誤
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |#
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |#
+—————————-+———————+——+—–+———————+——-+
————————————————

三.額外的一個報錯

在復原的過程中，錯誤日志報錯了:

-------------------------------------
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

此時檢視程序，沒發現異常

mysql> SHOW FULL PROCESSLIST;
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info                  |
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  138 | Waiting on empty queue | NULL                  |
|  8 | root            | localhost | test | Query   |    0 | init                   | SHOW FULL PROCESSLIST |
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
2 rows in set (0.00 sec)

然後檢視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參數，然後重新啟動mysql服務

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

這次啟動mysql服務花了一點時間，因為是復原完成後才啟動mysql的服務。

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

這次，終于正常了:

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;
Query OK, 0 rows affected (0.01 sec)

參考:

  1. https://blog.csdn.net/m0_37827567/article/details/82979767
  2. https://blog.csdn.net/zhang123456456/article/details/72854467
MySQL從小工到專家之路 # MySQL案例 mysql killed 事務復原 執行個體crash
上一篇: mysql state killed_MySQL程序殺掉後是killed
下一篇: python報錯killed,求教Channels中的問題--報錯took too long to shut down and was killed

繼續閱讀