问题描述
拷贝一个大表的表数据的时候，等待时间太久，就在前台通过CTRL+C的方式停掉了。
mysql> create table fact_sale_new as select * from fact_sale;^C^C -- query aborted^C^C -- query abortedERROR 2013 (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: 71735 trx_state: ROLLING BACK trx_started: 2021-06-03 14:17:40 trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_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: 9trx_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: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0 trx_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 to338551704131Log buffer completed up to 338551704131Log written up to338551704131Log flushed up to338551704131Added 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 pages 3896Old database pages 1418Modified db pages246Pending 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 mysqldroot 25761 10 11:31 pts/200:00:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pidmysql26015 25761 66 11:31 pts/203: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=3306root 27307 265450 16:35 pts/500:00:00 grep --color=auto mysqld[root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# kill -9 26015[root@hp2 ~]# [root@hp2 ~]# ps -ef | grep mysqldroot 27317 265450 16:35 pts/500:00:00 grep --color=auto mysqld[root@hp2 ~]# [root@hp2 ~]# [root@hp2 ~]# service mysqld startStarting 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 279412021-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: 112021-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: 112021-06-03T08:37:48.881364Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-06-03T08:37:49.882210Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-06-03T08:37:50.883154Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-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: 112021-06-03T08:37:53.885796Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-06-03T08:37:54.886681Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-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: 112021-06-03T08:37:57.889721Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-06-03T08:37:58.890804Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-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: 112021-06-03T08:38:01.894232Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-06-03T08:38:02.895428Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-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: 112021-06-03T08:38:05.899242Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-06-03T08:38:06.900686Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-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: 112021-06-03T08:38:09.904931Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-06-03T08:38:10.906337Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-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: 112021-06-03T08:38:13.910935Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-06-03T08:38:14.912520Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 112021-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 273370.00.0118281620 pts/5S16:35 0:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pidmysql27588 81.1 10.9 1749344 874532 pts/5Sl 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=3306root 282520.00.0 1135601756 pts/5S+ 16:39 0:00 /bin/sh /usr/sbin/service mysqld restartroot 282590.00.0116881480 pts/5S+ 16:39 0:00 /bin/sh /etc/init.d/mysqld restartroot 282750.10.0116921544 pts/5S+ 16:39 0:00 /bin/sh /etc/init.d/mysqld startroot 282830.00.0118241600 pts/5S+ 16:39 0:00 /bin/sh /home/mysql8/mysql/bin/mysqld_safe --datadir=/home/mysql8/mysql/data --pid-file=/home/mysql8/mysql/data/hp2.pidmysql285430.63.5 1128632 284168 pts/5Sl+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=3306root 287360.00.0 112824 992 pts/1S+ 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 288320.00.0 112824 992 pts/1S+ 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, 2021, 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: 71735 trx_state: RUNNING trx_started: 2021-06-03 17:26:06 trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 106524135 trx_mysql_thread_id: 0 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 7trx_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: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0 trx_schedule_weight: NULL1 row in set (0.00 sec)mysql> select * from performance_schema.data_locks\G*************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:5:140420935904192ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2 EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: innodb_ddl_log PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904192LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:12:140420935904280ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2 EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: columns PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904280LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:16:140420935904368ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2 EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: index_column_usage PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904368LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:19:140420935904456ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2 EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: indexes PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904456LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:29:140420935904544ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2 EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tables PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904544LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:30:140420935904632ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2 EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablespace_files PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904632LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 7. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:31:140420935904720ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2 EVENT_ID: 1OBJECT_SCHEMA: mysqlOBJECT_NAME: tablespaces PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904720LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 8. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:18446744069414584331:140420935904808ENGINE_TRANSACTION_ID: 71735THREAD_ID: 2 EVENT_ID: 1OBJECT_SCHEMA: OBJECT_NAME: SDI_11 PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935904808LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 9. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015241616:4294967294:1043:179:140420935901088ENGINE_TRANSACTION_ID: 71735THREAD_ID: 45 EVENT_ID: 14OBJECT_SCHEMA: mysqlOBJECT_NAME: tables PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: schema_idOBJECT_INSTANCE_BEGIN: 140420935901088LOCK_TYPE: RECORDLOCK_MODE: X,REC_NOT_GAPLOCK_STATUS: GRANTEDLOCK_DATA: 5, 'fact_sale_new', 369*************************** 10. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015245040:29:140420935928832ENGINE_TRANSACTION_ID: 72198THREAD_ID: 45 EVENT_ID: 19OBJECT_SCHEMA: mysqlOBJECT_NAME: tables PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_NAME: NULLOBJECT_INSTANCE_BEGIN: 140420935928832LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 11. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140421015245040:4294967294:1043:179:140420935925728ENGINE_TRANSACTION_ID: 72198THREAD_ID: 45 EVENT_ID: 19OBJECT_SCHEMA: mysqlOBJECT_NAME: tables PARTITION_NAME: NULLSUBPARTITION_NAME: NULL INDEX_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! 这次，终于正常了:
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_只是甲的博客-CSDN博客_mysql killed