MySQL故障排查与生产环境优化

一、MySQL逻辑架构图

• 客户端和连接服务

• 核心服务功能

• 存储引擎层

• 数据存储层

---

二、MySQL单实例常见故障

  2.1 故障一

    故障现象

ERROR 2002(HY000):Can‘t connect to local MySQL Server through socket '/data/mysql/mysql.sock'

    问题分析

• 数据库未启动、数据库出现故障或者数据库端口被防火墙拦截

    解决问题

• 启动数据库或者防火墙开放数据库监听端口

  2.2 故障二

    故障现象

ERROR 1045(28000):Access denied for user 'root'@'localhost'(using password:NO)

    问题分析

• 密码不正确或者没有权限访问

    解决方法

• 修改my.cnf主配置文件，在[mysqld]下添加skip-grant-tables
  • update更新user表authentication_string字段
  • 重新授权

  2.3 故障三

    故障现象

在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题

    问题分析

• DNS解析慢、客户端连接过多

    解决方法

• 修改my.cnf主配置文件（增加skip-name-resolve参数）
• 数据库授权禁止使用主机名

  2.4 故障四

    故障现象

Can't opeb file:'xxx_forums.MYI'.(errno:145)

    问题分析

• 服务器非正常关机，数据库所在空间已满，或一些其他位置原因
• 对数据库表造成了损坏：例如磁盘空间满，数据库关机
• 因拷贝数据库导致文件的数组发生改变，例如：用root数据迁移时，属组可能会发生改变

    解决方法

• 修复数据表（myisamchk、phpMyAdmin）
• 修改文件的属组

  2.5 故障五

    故障现象

ERROR 1129（HY000）:Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors;unblock with 'mysqladmin flush-hosts'

    问题分析

• 超出最大连接错误数量限制

    解决方法

• 清除缓存（flush-hosts关键字）
• 修改mysql配置文件（max_connect_errors=1000）

  2.6 故障六

    故障现象

Too many connections

    问题分析

• 连接数超出MySQL的最大连接限制

    解决方法

• 修改MySQL配置文件（max_connections=1000）

    临时修改参数

set GLOBAL max_connections=10000;                //设置全局最大连接数为10000

  2.7 故障七

    故障现象

Warning:World-writable config file '/etc/my.cnf' is ignored ERROR!MySQL is runnning but PID file could not be found

    问题分析

• MySQL的配置文件/etc/my.cnf权限问题

    解决方法

chmod 644 /etc/my.cnf

   2.8 故障八

    故障现象

InnoDB：Error:page 14178 log sequence number 29455369832
​
InnoDB:is in the future!Current system log sequence number 29455369832

    问题分析

• innodb数据文件损坏

    解决方法

• 修改my.cnf配置文件(innodb_force_recovery=4)
• 启动数据库后备份数据文件
• 利用备份文件恢复数据

问题：一张表高达几十个G，如何优化？

我们可以通过分库分表

---

三、MySQL主从环境常见故障

  3.1 故障一

    故障现象

    从库的Slave_IO_Running为NO

The slave I/O thread stopsbecause master and slave have equal MySQL server ids;thease ids must be different for replication to work(or the --replicate-same-server-id option must be used on slave but this does not always make sense;please check the manual before using it).

    问题解析：主库和从库的server-id值一样

    解决方法：

• 修改从库的server-id的值，修改为和主库不一样
• 重新启动数据库并在此同步

  3.2 故障二

    故障现象：从库的Slave_IO_Running为NO

    问题分析：主键冲突或者主库删除或更新数据，从库内找不到记录，数据被修改导致

    解决方法：

方法一：

stop slave;
​
set GLOABL SQL_SLAVE_SKIP_COUNTER=1;
​
start slave;

方法二

set global read_only=true;

  3.3 故障三

    故障现象

Error initializing relay log position:I/O error reading the header from the binary log

    问题分析

• 从库的中继日志relay-bin损坏

    解决方法

• 解决修复，重新找到同步的binlog和pos点，然后重新同步即可

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;

---

四、 MySQL优化

  4.1 硬件优化

• CPU：推荐使用S.M.P架构的多路对称CPU

• 内存：4G以上的物理内存

• 磁盘：RAID-0+1磁盘阵列或固态硬盘

  4.2 MySQL配置文件优化

• 调整配置项

  4.3 SQL优化

• 尽量使用索引进行查询

• 优化分页

• GROUP BY优化

  4.4 MySQL架构优化

• 架构选择：主从、主主、一主多从、多主多从