失眠网 > MySQL kill进程后出现killed

MySQL kill进程后出现killed

时间：2020-10-27 15:57:11

文章目录

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

一.问题描述

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

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

通过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: -06-03 14:17:40trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 46254trx_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: 50315trx_rows_modified: 460830324 #代表锁影响的行数，当数值为0时，锁将会释放trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1 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 mergesmerged operations:insert 0, delete mark 0, delete 0discarded operations:insert 0, delete mark 0, delete 0Hash 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 number338551704131Log buffer assigned up to 338551704131Log buffer completed up to 338551704131Log written up to 338551704131Log flushed up to 338551704131Added dirty pages up to338551704131Pages flushed up to338547869736Last checkpoint at 33854786973684290665 log i/o's done, 3753.20 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 548143104Dictionary memory allocated 491209Buffer pool size 32768Free buffers 1026Database pages3896Old database pages 1418Modified db pages 246Pending reads0Pending writes: LRU 0, flush list 2, single page 0Pages made young 32417, not young 14326066620.00 youngs/s, 0.00 non-youngs/sPages read 8962186, created 4242134, written 54858580.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 348 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 3896, unzip_LRU len: 0I/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 restartShutting down MySQL...................................................................................................................................................................................................................................................................................................................................^C[root@hp2 ~]# [root@hp2 ~]#

只能强制杀进程了

[root@hp2 ~]# ps -ef | grep mysqldroot257611 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.pidmysql 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=3306root27307 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 mysqldroot27317 26545 0 16:35 pts/5 00:00:00 grep --color=auto mysqld[root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# service mysqld startStarting MySQL SUCCESS! [root@hp2 ~]#

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

3 4 5 6-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.-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.-06-03T08:37:45.808797Z 0 [System] [MY-010116] [Server] /home/mysql8/mysql/bin/mysqld (mysqld 8.0.25) starting as process 27941-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.-06-03T08:37:45.819286Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.-06-03T08:37:45.879341Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:46.879804Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 117-06-03T08:37:47.880540Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:48.881364Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:49.882210Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:50.883154Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:51.884083Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 118-06-03T08:37:52.885124Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:53.885796Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:54.886681Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:55.887675Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 119-06-03T08:37:56.888684Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:57.889721Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:58.890804Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:37:59.891943Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 1110-06-03T08:38:00.893099Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:01.894232Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:02.895428Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:03.896638Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 1111-06-03T08:38:04.897903Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:05.899242Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:06.900686Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:07.902096Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 1112-06-03T08:38:08.903490Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:09.904931Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:10.906337Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:11.907818Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 1113-06-03T08:38:12.909311Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:13.910935Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:14.912520Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11-06-03T08:38:15.914099Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 1114-06-03T08:38:16.915254Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11

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

[root@hp2 ~]# ps aux |grep mysql*root27337 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.pidmysql 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=3306root28252 0.0 0.0 113560 1756 pts/5 S+ 16:39 0:00 /bin/sh /usr/sbin/service mysqld restartroot28259 0.0 0.0 11688 1480 pts/5 S+ 16:39 0:00 /bin/sh /etc/init.d/mysqld restartroot28275 0.1 0.0 11692 1544 pts/5 S+ 16:39 0:00 /bin/sh /etc/init.d/mysqld startroot28283 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.pidmysql 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=3306root28736 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* root28832 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 startStarting MySQL.......... SUCCESS! [root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.25 MySQL Community Server - GPLCopyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exitBye[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: -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: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1 row in set (0.00 sec)mysql> select * from performance_schema.data_locks\G*************************** 1. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:5:140420935904192ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: innodb_ddl_logPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904192LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 2. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:12:140420935904280ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: columnsPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904280LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 3. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:16:140420935904368ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: index_column_usagePARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904368LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 4. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:19:140420935904456ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: indexesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904456LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 5. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:29:140420935904544ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904544LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 6. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:30:140420935904632ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablespace_filesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904632LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 7. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:31:140420935904720ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablespacesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904720LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 8. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:18446744069414584331:140420935904808ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2EVENT_ID: 1OBJECT_SCHEMA: OBJECT_NAME: SDI_11PARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904808LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 9. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015241616:4294967294:1043:179:140420935901088ENGINE_TRANSACTION_ID: 71735THREAD_ID: 45EVENT_ID: 14OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: schema_idOBJECT_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:140420935928832ENGINE_TRANSACTION_ID: 72198THREAD_ID: 45EVENT_ID: 19OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935928832LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 11. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 140421015245040:4294967294:1043:179:140420935925728ENGINE_TRANSACTION_ID: 72198THREAD_ID: 45EVENT_ID: 19OBJECT_SCHEMA: mysqlOBJECT_NAME: tablesPARTITION_NAME: NULLSUBPARTITION_NAME: NULLINDEX_NAME: schema_idOBJECT_INSTANCE_BEGIN: 140420935925728LOCK_TYPE: RECORDLOCK_MODE: S,REC_NOT_GAPLOCK_STATUS: WAITINGLOCK_DATA: 5, 'fact_sale_new', 36911 rows in set (0.01 sec)

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

innodb_force_recovery=3

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

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

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

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

这次，终于正常了: