MySQL kill进程后出现killed
文章目录
- 一.问题描述
- 二.解决方案
- 三.额外的一个报错
- 四.启用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: 71735trx_state: ROLLING BACKtrx_started: 2021-06-03 14:17:40trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 462200354trx_mysql_thread_id: 8trx_query: create table fact_sale_new as select * from fact_saletrx_operation_state: rollback of SQL statementtrx_tables_in_use: 1trx_tables_locked: 9trx_lock_structs: 1370030trx_lock_memory_bytes: 234823888trx_rows_locked: 502015315trx_rows_modified: 460830324 #代表锁影响的行数，当数值为0时，锁将会释放trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0
trx_autocommit_non_locking: 0trx_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: 1172021-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: 1182021-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: 1192021-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: 11102021-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: 11112021-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: 11122021-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: 11132021-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: 11142021-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 - GPLCopyright (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: 71735trx_state: RUNNINGtrx_started: 2021-06-03 17:26:06trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 106524135trx_mysql_thread_id: 0trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 7trx_lock_structs: 8trx_lock_memory_bytes: 1136trx_rows_locked: 1trx_rows_modified: 106524127trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0
trx_autocommit_non_locking: 0trx_schedule_weight: NULL
1 row in set (0.00 sec)
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:5:140420935904192
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: innodb_ddl_logPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904192LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 2. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:12:140420935904280
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: columnsPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904280LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 3. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:16:140420935904368
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: index_column_usagePARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904368LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 4. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:19:140420935904456
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: indexesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904456LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 5. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:29:140420935904544
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904544LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 6. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:30:140420935904632
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablespace_filesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904632LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 7. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:31:140420935904720
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablespacesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904720LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 8. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:18446744069414584331:140420935904808
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: OBJECT_NAME: SDI_11PARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904808LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 9. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:4294967294:1043:179:140420935901088
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 45EVENT_ID: 14OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: schema_id
OBJECT_INSTANCE_BEGIN: 140420935901088LOCK_TYPE: RECORDLOCK_MODE: X,REC_NOT_GAPLOCK_STATUS: GRANTEDLOCK_DATA: 5, 'fact_sale_new', 369
*************************** 10. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015245040:29:140420935928832
ENGINE_TRANSACTION_ID: 72198THREAD_ID: 45EVENT_ID: 19OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935928832LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 11. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015245040:4294967294:1043:179:140420935925728
ENGINE_TRANSACTION_ID: 72198THREAD_ID: 45EVENT_ID: 19OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: schema_id
OBJECT_INSTANCE_BEGIN: 140420935925728LOCK_TYPE: RECORDLOCK_MODE: S,REC_NOT_GAPLOCK_STATUS: WAITINGLOCK_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!
这次，终于正常了:
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)
参考:
MySQL kill进程后出现killed
文章目录
- 一.问题描述
- 二.解决方案
- 三.额外的一个报错
- 四.启用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: 71735trx_state: ROLLING BACKtrx_started: 2021-06-03 14:17:40trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 462200354trx_mysql_thread_id: 8trx_query: create table fact_sale_new as select * from fact_saletrx_operation_state: rollback of SQL statementtrx_tables_in_use: 1trx_tables_locked: 9trx_lock_structs: 1370030trx_lock_memory_bytes: 234823888trx_rows_locked: 502015315trx_rows_modified: 460830324 #代表锁影响的行数，当数值为0时，锁将会释放trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0
trx_autocommit_non_locking: 0trx_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: 1172021-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: 1182021-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: 1192021-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: 11102021-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: 11112021-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: 11122021-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: 11132021-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: 11142021-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 - GPLCopyright (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: 71735trx_state: RUNNINGtrx_started: 2021-06-03 17:26:06trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 106524135trx_mysql_thread_id: 0trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 7trx_lock_structs: 8trx_lock_memory_bytes: 1136trx_rows_locked: 1trx_rows_modified: 106524127trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0
trx_autocommit_non_locking: 0trx_schedule_weight: NULL
1 row in set (0.00 sec)
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:5:140420935904192
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: innodb_ddl_logPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904192LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 2. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:12:140420935904280
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: columnsPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904280LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 3. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:16:140420935904368
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: index_column_usagePARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904368LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 4. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:19:140420935904456
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: indexesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904456LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 5. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:29:140420935904544
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904544LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 6. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:30:140420935904632
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablespace_filesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904632LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 7. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:31:140420935904720
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablespacesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904720LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 8. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:18446744069414584331:140420935904808
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: OBJECT_NAME: SDI_11PARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904808LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 9. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:4294967294:1043:179:140420935901088
ENGINE_TRANSACTION_ID: 71735THREAD_ID: 45EVENT_ID: 14OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: schema_id
OBJECT_INSTANCE_BEGIN: 140420935901088LOCK_TYPE: RECORDLOCK_MODE: X,REC_NOT_GAPLOCK_STATUS: GRANTEDLOCK_DATA: 5, 'fact_sale_new', 369
*************************** 10. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015245040:29:140420935928832
ENGINE_TRANSACTION_ID: 72198THREAD_ID: 45EVENT_ID: 19OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935928832LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL
*************************** 11. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015245040:4294967294:1043:179:140420935925728
ENGINE_TRANSACTION_ID: 72198THREAD_ID: 45EVENT_ID: 19OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: schema_id
OBJECT_INSTANCE_BEGIN: 140420935925728LOCK_TYPE: RECORDLOCK_MODE: S,REC_NOT_GAPLOCK_STATUS: WAITINGLOCK_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!
这次，终于正常了:
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)