mysql - 我的 SQL 错误 #1015 - 无法锁定文件 (errno : 165 - Table is read only) innodb engine only

Question

我的SQL错误

#1015 - Can't lock file (errno: 165 - Table is read only)

仅限 innodb 引擎

首先MySql开始停止工作，几乎每天都需要重启

然后它开始以只读模式工作

MySQL版本是5.6

my.cnf内容

[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
max_allowed_packet=268435456
innodb_buffer_pool_size=134217728
innodb_force_recovery=4

我的日志

140819 05:48:57 mysqld_safe mysqld from pid file /var/lib/mysql/server1.mohdev.com.pid ended
140819 05:48:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-08-19 05:48:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-08-19 05:48:59 9876 [Note] Plugin 'FEDERATED' is disabled.
2014-08-19 05:48:59 9876 [Note] InnoDB: Started in read only mode
2014-08-19 05:48:59 9876 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-08-19 05:48:59 9876 [Note] InnoDB: The InnoDB memory heap is disabled
2014-08-19 05:48:59 9876 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-08-19 05:48:59 9876 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-08-19 05:48:59 9876 [Note] InnoDB: Using Linux native AIO
2014-08-19 05:48:59 9876 [Note] InnoDB: Using CPU crc32 instructions
2014-08-19 05:48:59 9876 [Note] InnoDB: Disabling background IO write threads.
2014-08-19 05:48:59 9876 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-08-19 05:48:59 9876 [Note] InnoDB: Completed initialization of buffer pool
2014-08-19 05:48:59 9876 [Note] InnoDB: Highest supported file format is Barracuda.
2014-08-19 05:48:59 9876 [Note] InnoDB: 5.6.17 started; log sequence number 2706168115
2014-08-19 05:48:59 9876 [Note] InnoDB: !!! innodb_force_recovery is set to 4 !!!
2014-08-19 05:48:59 9876 [Note] Server hostname (bind-address): '*'; port: 3306
2014-08-19 05:48:59 9876 [Note] IPv6 is not available.
2014-08-19 05:48:59 9876 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2014-08-19 05:48:59 9876 [Note] Server socket created on IP: '0.0.0.0'.
2014-08-19 05:48:59 9876 [Note] Event Scheduler: Loaded 0 events
2014-08-19 05:48:59 9876 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2014-08-19 05:49:22 9876 [ERROR] InnoDB: Failed to find tablespace for table '"epl_mdb"."jpxsqzjy_options"' in the cache. Attempting to load the tablespace with space id 1156.
2014-08-19 05:49:22 9876 [Warning] InnoDB: Allocated tablespace 1156, old maximum was 0
2014-08-19 05:49:22 9876 [ERROR] InnoDB: Failed to find tablespace for table '"epl_mdb"."jpxsqzjy_posts"' in the cache. Attempting to load the tablespace with space id 584.
2014-08-19 05:49:23 9876 [ERROR] InnoDB: Failed to find tablespace for table '"epl_mdb"."jpxsqzjy_term_taxonomy"' in the cache. Attempting to load the tablespace with space id 591.
2014-08-19 05:54:26 9876 [Note] /usr/sbin/mysqld: Normal shutdown

2014-08-19 05:54:26 9876 [Note] Giving 1 client threads a chance to die gracefully
2014-08-19 05:54:26 9876 [Note] Event Scheduler: Purging the queue. 0 events
2014-08-19 05:54:26 9876 [Note] Shutting down slave threads
2014-08-19 05:54:28 9876 [Note] Forcefully disconnecting 1 remaining clients
2014-08-19 05:54:28 9876 [Warning] /usr/sbin/mysqld: Forcing close of thread 3  user: 'leechprotect'

我试着添加

innodb-read-only=0

在 my.cnf 中但没有运气有什么建议么谢谢

Answer 1

你的配置中有这个:

innodb_force_recovery=4

http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html说:

As of 5.6.6, an innodb_force_recovery setting of 4 or greater places InnoDB in read-only mode.

该选项只有在尝试从损坏的表空间中提取数据时才会执行。您应该从 my.cnf 中删除该选项并重新启动。