今天机房mysql服务器异常关机,重新启动后报错如下:
2017-09-08 09:35:20 1808 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table './cdb/passport_account.MYI'; try to repair it 2017-09-08 09:35:20 1808 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table './cdb/passport_account.MYI'; try to repair it 2017-09-08 09:35:20 1808 [ERROR] Got an error from thread_id=115353, /usr/local/src/mysql-5.x.30/storage/myisam/mi_update.c:223 2017-09-08 09:35:20 1808 [ERROR] MySQL thread id 115353, OS thread handle 0x7ff560e3d700, query id 40381677 192.168.5.202 csr updating
修复方法如下:
mysql> repair table passport_account; +----------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+--------+----------+----------+ | cdb.passport_account | repair | status | OK | +----------------------+--------+----------+----------+ 1 row in set (3 min 34.72 sec)
恢复正常。
ps:有朋友遇到到执行SQL的时候,产生临时数据占满了/tmp磁盘空间,可以把临时文件选择放在空间大的磁盘目录下面,解决方法如下:
mysql> show variables like 'tmpdir'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | tmpdir | /tmp | +---------------+-------+ 1 row in set (0.00 sec) mysql> set global tmpdir = '/Data/xxx/mysqltmp'; ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable
如果有报错可以配置文件
vim /etc/mysql/my.cnf
此处可以修改 tmpdir 的属性值,也可以修改 query_cache_limit 的数值,建议使用后者
重启MySQL就ok了