记一次linux服务器上MySQL挂掉后的错误定位和服务恢复

本文详细记录了一次解决MySQL因内存不足导致的启动失败问题的过程。作者在云服务器上运行MySQL时遇到500错误，通过日志定位到内存分配问题，并提供了两种解决方案：增加交换分区或调整innodb_buffer_pool_size参数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

平时写了一些日常偶尔会用到的小网站，比如剪贴板一类，因为不想单独花钱租服务器，就放在了为了干别的租用的云服务器上，app server和MySQL db放在一起。
一个月前发现请求后端的接口出现了500报错，查看tomcat日志发现是MySQL挂了。尝试直接连接mysql会出现如下报错：

Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)

明显是MySQL server没有启动。
尝试使用systemctl restart mysql命令恢复server的时候会报错无法启动，于是当时直接通过在my.cnf 文件中添加 innodb_force_recovery = 4 的方式强制重启了。但这个方法治标不治本，一个月之后，相同的问题再次出现。
这次我在 /var/log/mysql/error.log 中定位了问题的所在，发现启动失败的时候会有这么一段错误提示：

2020-06-07T02:12:42.366751Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2020-06-07T02:12:42.366871Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2020-06-07T02:12:42.590752Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-07T02:12:42.594425Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.30-0ubuntu0.16.04.1) starting as process 19575 ...
2020-06-07T02:12:42.602395Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-06-07T02:12:42.602454Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-06-07T02:12:42.602460Z 0 [Note] InnoDB: Uses event mutexes
2020-06-07T02:12:42.602468Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-06-07T02:12:42.602473Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2020-06-07T02:12:42.602477Z 0 [Note] InnoDB: Using Linux native AIO
2020-06-07T02:12:42.603014Z 0 [Note] InnoDB: Number of pools: 1
2020-06-07T02:12:42.603278Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-06-07T02:12:42.607128Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-06-07T02:12:42.607192Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2020-06-07T02:12:42.607201Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2020-06-07T02:12:42.607209Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-06-07T02:12:42.607215Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-06-07T02:12:42.607220Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-06-07T02:12:42.607227Z 0 [ERROR] Failed to initialize builtin plugins.
2020-06-07T02:12:42.607231Z 0 [ERROR] Aborting

2020-06-07T02:12:42.607246Z 0 [Note] Binlog end
2020-06-07T02:12:42.607731Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

可以看出应该是内存分配不够用了。也难怪，租的最便宜的server，内存只有512m，随便跑几个东西就要爆了。在网络上搜索解决办法的时候发现MySQL应对内存不够有非常好的解决方案：How To Add Swap on Ubuntu 14.04

当然，还有一种更快且更简单粗暴的解决方案，减少innodb的内存规划：
在 /etc/mysql/my.cnf (或者是/etc/mysql/mysql.conf.d/mysqld.cnf 中，如果是这样在你my.cnf 文件会通过include的方式引用后者) 中 [mysqld] 里加入

innodb_buffer_pool_size = 20M

然后重启

systemctl restart mysql

搞定。