MySQL 数据库崩溃(crash)的常见原因和解决办法---发表到 《数据和云》 公众号

《数据和云》 公众号:MySQL 数据库崩溃(crash)的常见原因和解决办法

检查 MySQL 数据库的启动时间

Linux 系统中的 systemd 和 mysqld_safe 会在 mysqld 进程 crash 后自动重新启动 MySQL 的服务,需要注意的是使用 kill -9 杀死 mysqld 进程系统会自动重新启动,而只使用 kill 命令则不会重新启动,因为执行 kill 命令,系统会发送一个 SIGTERM 信号给 mysqld,mysql 数据库会正常关闭,日志中会出现类似下面的记录:

2020-10-26T09:06:48.435181Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

MySQL 数据库 crash 后都会重新启动,因此我们有时可能不知道 MySQL 数据库已经 crash 过了,但我们可以从mysql数据库启动时间上找到线索,下面介绍四种检查 MySQL 数据库启动时间的方法。

检查 MySQL 服务状态

scutech@scutech:~$ service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2020-10-21 05:54:18 NDT; 4 days ago
  Process: 774 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
  Process: 708 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 791 (mysqld)
    Tasks: 27 (limit: 2328)
   CGroup: /system.slice/mysql.service
           └─791 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

显示 MySQL 数据库已经运行 4 天多。

检查 MySQL 中的 uptime 状态

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 428334 |
+---------------+--------+
1 row in set (0.32 sec)

这个值是以秒为单位,下面换算成以天为单位是 4 天多。

mysql> select 428334/60/60/24;
+-----------------+
| 428334/60/60/24 |
+-----------------+
|  4.957569444444 |
+-----------------+
1 row in set (0.01 sec)

查询 uptime 状态的另一种方法是使用 mysqladmin version 或在 mysql 客户端里用 “\s” 进行查询。

使用 ps 检查进程启动时间

使用 ps 命令查询发现 mysqld 启动了4天23小时3分种54秒

scutech@scutech:~$ ps -eo pid,user,args,etime|grep mysqld
  791 mysql    /usr/sbin/mysqld --daemoniz  4-23:03:54

检查 MySQL 日志

找关键字 “ready for connections”,可以查到启动信息。

2020-10-21T08:24:18.986765Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.28-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

MySQL 数据库 crash 的常见原因

MySQL 数据库 crash 的最常见原因有两个,一个是 mysql 的 bug , 另一个是 mysql 申请系统资源失败或内存泄漏。

MySQL 的 bug

MySQL数据库 crash 的最常见的一个原因当然是 MySQL 的bug。95% 的 bug 都是和具体的 sql 相关,通常是 MySQL crash 前执行的最后一个 sql 有问题,因此定位 bug 时应打开 general query log ,根据最后一个 sql 来查找线索。
当你确定了 crash 的原因后,应该检查一下 MySQL 的 bug 库(https://bugs.mysql.com),通常采用 Advanced search,看看有没有类似的问题。如果你找到了可能与你相关的 bug,确认它是否修复了。如果已经修复了,那么把 MySQL 升级到 bug 已经修复的版本。
在这里插入图片描述
在每个版本的 Release Notes 里面有一节 Bugs Fixed ,可以查到修复的 bug 。

MySQL 申请系统资源失败或内存泄漏

内存不足或 MySQL 申请系统资源失败外都会造成 MySQL 崩溃,例如磁盘空间满了,磁盘上的文件 corrupt 等。此时需要定位 crash 的根本原因有下面几种方法:

  1. 仔细阅读 MySQL 的错误日志,这个日志里面的一些程序调试信息看起来很让人困惑,但静下心来仔细看,很多时候会找到线索;
  2. 打开 general query log ,找到最后一个 sql 访问的表或索引,检查这个表或索引,如果有问题就重建,通常可以解决问题。
  3. 使用 strace、pstack、pmap、gdb 分析 mysqld 的代码,可能需要打开 core dump;
  4. 使用 CMake 的选项 -DWITH_DEBUG=1 重新编译 mysqld,然后运行重新编译后的 mysqld,查看 trace 文件、error log 进行排错。

MySQL 内存占用的计算

全局内存
innodb_buffer_pool_size innodb_log_buffer_size thread_cache_size table_open_cache table_definition_cache key_buffer_size
线程内存
binlog_cache_size thread_stack
单次操作内存
join_buffer_size read_buffer_size read_rnd_buffer_size tmp_table_size sort_buffer_size

计算公式
MySQL 8 中最大内存占用参考值计算公式:

SELECT ( @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size 
+ @@max_connections * (@@binlog_cache_size + @@thread_stack + @@read_buffer_size 
+ @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@tmp_table_size ) 
) / 1024 /1024 AS MAX_MEM_MB; 

innodb_buffer_pool_size

  • key_buffer_size
  • max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
  • max_connections*2MB

临时解决可以使用下面的命令释放缓存:

echo 1 > /proc/sys/vm/drop_caches 

0:0是系统默认值,默认情况下表示不释放内存,由操作系统自动管理
1:释放页缓存
2:释放dentries和inodes
3:释放所有缓存
从长远看还是要修改对应的参数进行解决。

MySQL 客户端的内存泄漏

MySQL 客户端的内存泄漏时通常会有下面的提示

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory

这通常是客户端收到的返回结果集太大造成的,解决办法有两种:

  1. 检查正在运行的 SQL ,看看您真的需要这么大的返回结果集吗?
  2. 允许 mysql 时加上 --quick 选项,这会减少客户端单次收到的返回集,但会增加 mysqld 的负载。
姚远ACE CSDN认证博客专家 ACE 华为云 MVP
Oracle ACE,华为云 MVP,Oracle10g,12c OCM; MySQL 5.6,5.7,8.0 OCP;CCNA; EMC Certified; IBM P Certified; RHCE; SQLServer 764; DB2 Certified; TOEIC 890;获得过两次国家部级科技进步奖;发明过两项计算机专利。微信:yaoyuanace 邮箱:yaoyuanace(at)qq.com
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页