ERROR 2013 (HY000): Lost connection to MySQL server

 

在撤销 LOAD DATA LOCAL INFILE 后，执行其他 sql 语句出现以下错误：

mysql> show databases;
No connection. Trying to reconnect...
Connection id:    5
Current database: *** NONE ***

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server

根据错误查看了，socket 和 my.cnf 配置文件

more /etc/mysql/my.cnf  | grep sock
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
socket      = /var/run/mysqld/mysqld.sock
socket      = /var/run/mysqld/mysqld.sock
socket      = /var/run/mysqld/mysqld.sock

socket 运行正常，尝试重启 mysql 服务：

sudo  service mysql restart

然而无果，最后只能根据 my.cnf 配置查看error.log 日志

171024 9:23:11 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
171024 9:23:11 [Note] Plugin 'FEDERATED' is disabled.
171024 9:23:11 InnoDB: The InnoDB memory heap is disabled
171024 9:23:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
171024 9:23:11 InnoDB: Compressed tables use zlib 1.2.8
171024 9:23:11 InnoDB: Using Linux native AIO
171024 9:23:11 InnoDB: Initializing buffer pool, size = 500.0G
InnoDB: mmap(549453824000 bytes) failed; errno 12
171024 9:23:11 InnoDB: Completed initialization of buffer pool
171024 9:23:11 InnoDB: Fatal error: cannot allocate memory for the buffer pool
171024 9:23:11 [ERROR] Plugin 'InnoDB' init function returned error.
171024 9:23:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
171024 9:23:11 [ERROR] Unknown/unsupported storage engine: InnoDB
171024 9:23:11 [ERROR] Aborting
171024 9:23:11 [Note] /usr/sbin/mysqld: Shutdown complete
.....
InnoDB log sequence number xxxx

LSN(log sequence number) 用于记录日志序号，它是一个不断递增的 unsigned long long 类型整数

在 my.cnf 配置文件中增加 innodb_force_recovery = 6 并重启 mysql 服务。

确认数据无误，备份出所有数据库:

mysqldump -u root -p --all-databases > all-databases.sql

最后，新建 mysql 实例并导入数据（backup）。

处理问题思路，先从错误码进行分析，解决的同时也要查看错误日志。

参考资料:

• How to move the InnoDB log sequence number (LSN) forward
• Corrupted innodb table crashing mysql instance how to recover table ?
• MySQL · 引擎特性 · InnoDB redo log漫游

转载于:https://www.cnblogs.com/xiezero/p/8213471.html