mysql常见故障汇总和处理

Love The Way You Lie 2023-02-16 04:19 65阅读 0赞

微信公众号搜索 DevOps和k8s全栈技术 ，关注之后，在后台回复 k8s视频，就可获取k8s免费视频和文档，也可扫描文章最后的二维码关注公众号。

## 性能故障 ##

**1. 数据库主机LOAD飙高**  
(1)一般情况下导致MySQL服务器LOAD突然飙高，可能的五种情况如下：

– 全表扫描的SQL语句；
    – SELECT操作语句的执行计划走错；
    – 存在UPDATE/DELETE 语句没有索引可选择，而导致堵塞其他SQL语句的执行；
    – 存在修改表结构或OPTIMIZE 语句执行；
    – 大数据量的导入或导出，尤其数据库的逻辑备份操作；
    – 业务量大到超过服务器处理能力（我们大家都高度关注业务发展，以及公司业务特点，
    – 还有与开发和运营保持良好联系，很难出现未知的业务突然爆发性增长）；

(2)要解决LOAD飙高，必须先找到造成飙高的真实原因，请登陆数据库服务器后，执行如下命令：

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>SHOW PROCESSLIST;</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>3</code></td> 
   <td><code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>FROM</code>&nbsp;<code>INFORMATION_SCHEMA.PROCESSLIST&nbsp;</code><code>WHERE</code>&nbsp;<code>COMMAND &lt;&gt; ‘sleep’&nbsp;</code><code>AND</code>&nbsp;<code>TIME</code><code>&gt;100;</code></td> 
  </tr> 
 </tbody> 
</table>

(3)若一直处在执行状态，且执行时间比较久，可以分析下SQL语句执行计划：

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>mysql&gt;EXPLAIN SQL语句</code></td> 
  </tr> 
 </tbody> 
</table>

(4)检查io请求状态  
使用Linux命令iostat查看r/s(读请求)，w/s(写请求)，avgrq-sz(平均请求大小),await(IO等待),，svctm(IO响应时间)，若执行计划不合理，则可以根据SQL类型选择是否与应用负责人联系。首先，查找造成服务器LOAD飙高的PID，特别是DELETE 或UPDATE等会堵住其他SQL语句的PID，然后进入MySQL命令行工具中，对一些SQL先记录下来，再适情考虑执行：KILL SQL PID

**2. 请求处理波动，应用大量请求超时失败**  
(1)检查数据库的响应状态,使用tcprstat

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>tcprstat -l $ip_addr -t $interval -n $</code><code>count</code></td> 
  </tr> 
 </tbody> 
</table>

按照汇报结果，切分是否为数据库端问题还是非数据库端问题.

(2)分析关键指标

com_select/insert/update/delete
    innodb_rows_read/update/delete/insert
    innodb_logicial_read/physical read

使用SHOW STATUS和 orzdba -com 可以获取采样指标

(3)转向分析slow query， 检查可疑SQL

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>EXPLAIN SQL语句;</code></td> 
  </tr> 
 </tbody> 
</table>

如有长时间运行异常SQL, 可以Kill SQL PID.

(4)重点关注索引的使用问题

复制故障

**1. 复制中断**

(1)备机无法连接到主服务器，可能是网络问题，也可能是主服务器的mysqld已停止；
    (2)主键冲突；
    (3)主从服务器数据不一致；
    (4)其他原因；

为使复制继续，我们可以进行如下处理：

(1)stop slave ;
    (2)start slave;
    (3)检查服务是否正常：show slave status\G

若是主健冲突或数据不一致的情况，则需要额外处理：  
(1)停止slave进程

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>STOP SLAVE;</code></td> 
  </tr> 
 </tbody> 
</table>

(2)设置事务号，事务号从Retrieved\_Gtid\_Set获取

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>SET</code>&nbsp;<code>@@SESSION.GTID_NEXT=&nbsp;</code><code>'xxxxxxxxxxx'</code></td> 
  </tr> 
 </tbody> 
</table>

(3)设置空事务

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>BEGIN</code><code>;&nbsp;</code><code>COMMIT</code><code>;</code></td> 
  </tr> 
 </tbody> 
</table>

(4)恢复事务号

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>SET</code>&nbsp;<code>SESSION GTID_NEXT = AUTOMATIC;</code></td> 
  </tr> 
 </tbody> 
</table>

(5)启动slave进程

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>START SLAVE;</code></td> 
  </tr> 
 </tbody> 
</table>

实例故障

**1.MySQL实例假死**  
(1)再次确认mysql的运行状态

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>SHOW PROCESSLIST;是否有大量请求在等待处理</code></td> 
  </tr> 
 </tbody> 
</table>

此时，为校验是否真处于MysQL假死状态，那么可以用test库中任意执行创建表或更新数据的语句，若回车键后没有响应，则一般可以断定MySQL 是否已经处于假死状态。

(2)检查mysql实例的进程的内存/交换分区状态  
使用linux 命令: free -m  
使用linux 命令: top

结合当前业务压力，检查内存消耗现状，消耗速度，是否开始使用交换分区,如内存消耗过快且业务压力不大，并发不高，转向对slow query的定位。

(3)检查mysql实例的进程分区使用情况和IO状态  
使用Linux命令: df -hT  
使用Linux命令: iostat -x

确认分区有足够空间，如异常，则进入对应目录，检查实例的数据和日志存放和增长情况，特别是对日志进行清理处理，或通过调整逻辑卷的大小解决问题。

检查iostat汇报值中 %user, %iowait, %idle 值是否异常，如异常且当前业务压力不大，转向对slow query的定位，同时要求主机运维人员协查物理存储的健康状况。

(4)检查mysqld的CPU使用情况  
使用Linux命令: top  
使用Linux命令:ps -eo pid,user,comm,pcpu,pmem,vsz | grep mysqld

结合当前业务压力和连接数，确认mysql进程的CPU占用率是否异常，如当前业务压力不大，并发不高，转向对slow query的定位。

(5)检查实例主机的网络通信  
使用Linux命令: ping 检测到应用主机，到复制从机的RTT  
使用Linux命令: iptraf 确认实例主机的网卡带宽和速度  
如以上操作发现异常，需要主机运维人员使用tcpdump进行抓包分析

(6) Slow query 分析  
打印slow query日志，分析和定位造成故障的SQL, 必要时，kill 掉出现问题的SQL ID

**2.应用报连接池满**  
(1) 检查io请求状态  
使用Linux命令: iostat  
查看r/s(读请求),avgrq-sz(平均请求大小),svctm(IO响应时间)  
(2)运行orzdba对照SQL执行数量(QPS-sel栏和TPS-iud栏)和逻辑读消耗(Hit%-lor栏)  
(3) 转向slow query分析查看行扫描Query\_time和Rows\_examined栏，如无明显定位和结论，进入下一步全面分析  
(4)使用pt-query-digest分析全面slow query，着重分析索引的合法性  
(5) 检查General log  
(6) 检查Binlog, 对于DML操作, 通过mysqlbinlog工具解析binlog检查  
(7) 使用Linux命令 tcpdump抓包分析，配合pt-query-digest做进一步分析排查

**3. MySQL请求线程堵塞**  
(1)检查 processlist

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>processlist 主要查看&nbsp;</code><code>Time</code><code>, State 栏的汇报值</code></td> 
  </tr> 
 </tbody> 
</table>

(2)使用pstack 保存当前mysqld实例的栈调用，以备进一步分析

其它故障

**1. 各种日志的标准存放路径和查看方法**  
(1)常规后台进程报错日志log-error路径

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>show variables&nbsp;</code><code>like</code>&nbsp;<code>'%log_error%'</code><code>;</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>2</code></td> 
   <td><code>| log_error&nbsp;&nbsp;&nbsp;&nbsp; | /paic/mymon/data/mysqldata/mymon/mysql.err |</code></td> 
  </tr> 
 </tbody> 
</table>

文本文件，可以直接查看，类似oracle的alert日志。

(2)慢查询日志slow\_query\_log\_file，其设定值long\_query\_time

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>show variables&nbsp;</code><code>like</code>&nbsp;<code>'%slow_query_log_file%'</code><code>;</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>2</code></td> 
   <td><code>| slow_query_log_file | /paic/mymon/data/mysqldata/mymon/cnsh230234-slow.log |</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>3</code></td> 
   <td><code>show variables&nbsp;</code><code>like</code>&nbsp;<code>'%long_query_time%'</code><code>;</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>4</code></td> 
   <td><code>| long_query_time | 10.000000 |</code></td> 
  </tr> 
 </tbody> 
</table>

文本文件，可以看出sql文本及sql的执行时间

(3)数据库二进制日志文件binlog  
查看方法mysqlbinlog mysql-bin.000001 |more  
可以直接看到事务号和sql脚本

**2. mysql进程报错，类似ORA-**  
例如：071221 11:12:12 \[ERROR\] Got error 127 when reading table ‘./download\_utf8/source\_uesrdown’

查看127报错原因

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>cnsh230234: &gt;perror 127</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>2</code></td> 
   <td><code>OS error code 127:&nbsp;&nbsp;</code><code>Key</code>&nbsp;<code>has expired</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>3</code></td> 
   <td><code>MySQL error code 127: Record file&nbsp;</code><code>is</code>&nbsp;<code>crashed</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>4</code></td> 
   <td><code>参考处理办法 Repair&nbsp;</code><code>Table</code>&nbsp;<code>source_uesrdown ; Repair&nbsp;</code><code>Table</code>&nbsp;<code>userday_money;</code></td> 
  </tr> 
 </tbody> 
</table>

**3. 忘记root密码，重置方法****如下**

(1)/etc/init.d/mysql stop
    (2)mysqld_safe –skip-grant-tables &
    (3)mysql -uroot -p
    (4)update mysql.user set password=password(“”yourpasswd”") where user=”"root”"
    (5)flush privileges
    (6)quit

往期精彩文章

[kubernetes面试题汇总][kubernetes]

[kubernetes全栈技术+企业案例演示【带你快速掌握和使用k8s】][kubernetes_k8s]

[DevOps视频和资料免费领取][DevOps]

[kubernetes技术分享-可用于企业内部培训][kubernetes_-]

[kubernetes集群中部署EFK日志管理系统][kubernetes_EFK]

[Kubernetes中部署MySQL高可用集群][Kubernetes_MySQL]

[Prometheus+Grafana+Alertmanager搭建全方位的监控告警系统-超详细文档][Prometheus_Grafana_Alertmanager_-]

[k8s1.18多master节点高可用集群安装-超详细中文官方文档][k8s1.18_master_-]

[Kubernetes Pod健康检查-livenessProbe和readinessProbe][Kubernetes Pod_-livenessProbe_readinessProbe]

[kubernetes pod生命周期管理-postStart和preStop][kubernetes pod_-postStart_preStop]

[运维常见问题汇总-tomcat篇][-tomcat]

[运维常见问题汇总-tomcat部署java项目大量close\_wait解决方案][-tomcat_java_close_wait]

[关于linux内核参数的调优，你需要知道][linux]

[kubernetes挂载ceph rbd和cephfs][kubernetes_ceph rbd_cephfs]

[helm安装和使用-通过helm部署k8s应用][helm_-_helm_k8s]

[kubernetes持久化存储volume][kubernetes_volume]

## 技术交流群    ##

**扫码加群????**

![format_png][]

**微信：**luckylucky421302

## 微信公众号    ##

**                                    ** **长按指纹关注公众号????**

![format_png 1][]

**参考：**

http://www.housong.net/mysql-normal-trouble-shooting.html

**                                       点击在看少个 bug????**

[kubernetes]: https://blog.csdn.net/weixin_38320674/article/details/106369123
[kubernetes_k8s]: http://mp.weixin.qq.com/s?__biz=MzU0NjEwMTg4Mg%3D%3D&chksm=fb638a31cc14032772d778f1a8fa5dc73f9aba6c99546c9ab25d9f91f706be3cff34572f0c1a&idx=1&mid=2247484708&scene=21&sn=2a7230ade0ce0bd9059e92752e03ba18#wechat_redirect
[DevOps]: https://blog.csdn.net/weixin_38320674/article/details/106435859
[kubernetes_-]: https://blog.csdn.net/weixin_38320674/article/details/106346164
[kubernetes_EFK]: https://blog.csdn.net/weixin_38320674/article/details/105852646
[Kubernetes_MySQL]: https://blog.csdn.net/weixin_38320674/article/details/106089098
[Prometheus_Grafana_Alertmanager_-]: https://blog.csdn.net/weixin_38320674/article/details/106294097
[k8s1.18_master_-]: https://blog.csdn.net/weixin_38320674/article/details/106089099
[Kubernetes Pod_-livenessProbe_readinessProbe]: https://blog.csdn.net/weixin_38320674/article/details/106394498
[kubernetes pod_-postStart_preStop]: https://blog.csdn.net/weixin_38320674/article/details/106485489
[-tomcat]: https://blog.csdn.net/weixin_38320674/article/details/105926564
[-tomcat_java_close_wait]: http://mp.weixin.qq.com/s?__biz=MzU0NjEwMTg4Mg%3D%3D&chksm=fb638c56cc140540a8015c902c5b38ae6fc9245ab09687e63084b0c3c4a10cb1fe8c4627efd4&idx=1&mid=2247484227&scene=21&sn=6900363f032604263553ed5f6b04b81f#wechat_redirect
[linux]: https://blog.csdn.net/weixin_38320674/article/details/105985731
[kubernetes_ceph rbd_cephfs]: https://blog.csdn.net/weixin_38320674/article/details/106561781
[helm_-_helm_k8s]: https://blog.csdn.net/weixin_38320674/article/details/106515349
[kubernetes_volume]: https://blog.csdn.net/weixin_38320674/article/details/106536618
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9yQTJRUVZHa2Q4UUJJRkwwTkFIeFJ5dDVrR2ZBTDJYcjg4N2J1aWJkT1Y4dGlhTWlhdmljTDBDaWJkRHc5dnRpY3ZGSUo3UDlieTBiNDZqY1owVldRY3BwTnNjQS82NDA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9yQTJRUVZHa2Q4VGNma1o0TVdRY0RDMTZBaWJkZmhpYUJDUEM2MUd5ZzJwUUdFMTB3MjdLS2dpYnF0TWljaWFiTkpuUkFLeFBVaWFIaWNIUGpyMkJDbFQwNEJaOUEvNjQw?x-oss-process=image/format,png