MySQL的故障排查与生产环境

MySQL 逻辑架构图

MySQL 的运行原理

最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/ 服务器端工具实现的 TCP/IP 通信。主要完成一些连接处理、授权认证、及相关的安全方案 等。在该层上引入了线程池的概念，为通过安全认证接入的客户端提供线程。同样在该层上 可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作 权限。

第二层架构主要完成大多数的核心服务功能，如 SQL 接口、缓存的查询、SQL 的分析 和优化以及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。 在该层上服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询 表的顺序，是否利用索引等，最后生成相应的执行操作。如果是 select 语句，服务器还会 查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系 统的性能。

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，可以根据自己的实际需要进行选取。

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的 交互。

MySQL单实例故障排查

故障现象1

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

问题分析：

以上这种情况一般都是数据库未启动或者数据库端口被防火墙拦截导致。

解决方法：

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

故障现象 2

MySQL源码安装完数据库后不能启动

问题分析

MySQL目录用户不为mysql

解决方法

chown -R MySQL目录存放路径

故障现象 3

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

问题分析：

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

解决方法：

1 修改 my.cnf 主配置文件，在[mysqld]下添加 skip-grant-tables，重启数据库。最后 修改密码命令如下。

mysql>use mysql;

mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

或

mysql> set password = password('123456'); mysql> flush privileges;

再删除刚刚添加的 skip-grant-tables 参数，重启数据库，使用新密码即可登录。

2 重新授权，命令如下。

mysql>grant all on *.* to 'root'@'mysql-server' identified by '123456';

故障现象 4

远程连接时

ERROR 1130 (HY000): Host '远程MySQL数据库IP地址' is not allowed to connect to this MariaDB server

问题分析

远程连接使用的用户没有登录此IP的权限

解决方法

进入数据库给用户授权

grant all on *.* to 'root'@'%' identified by '123456'; flush privileges;

故障现象 5

远程连接时

ERROR 2003 (HY000): Can't connect to MySQL server on '远程MySQL数据库IP地址' (113)

问题分析：

MySQL服务器没有关闭防火墙或防火墙不允许MySQL通过

解决方法

关闭防火墙或设置放行MySQL通过

故障现象 6

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

问题分析：

如果 MySQL 主机查询 DNS 很慢或是有很多客户端主机时会导致连接很慢. 由于开发机器是不能够连接外网的，在进行 MySQL 连接时，DNS 解析是不可能完成的， 从而也就明白了为什么连接那么慢了。

解决方法：

修改 my.cnf 主配置文件，在[mysqld]下添加 skip-name-resolve，重启数据 库可以解决。注意在以后授权里面不能再使用主机名授权。

故障现象 8

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

问题分析：

●服务器非正常关机，数据库所在空间已满，或一些其它未知的原因，对数据库表造 成了损坏。

●可能是操作系统下直接将数据库文件拷贝移动，会因为文件的属组问题而产生这个 错误。

解决方法：

● 可以使用下面的两种方式修复数据表（第一种方法仅适合独立主机用户）：

◆ 使用 MySQL 自带的专门用户数据表检查和修复工具 myisamchk。一般 情况下只有在命令行下面才能运行 myisamchk 命令。常用的修复命令为：

myisamchk -r 数据文件目录/数据表名.MYI；

◆ 通过 phpMyAdmin 修复， phpMyAdmin 带有修复数据表的功能，进入 到某一个表中后，点击“操作”，在下方的“表维护”中点击“修复表”即可。

**注意：**以上两种修复方式在执行前一定要备份数据库

● 修改文件的属组（仅适合独立主机用户）：

◆ 复制数据库文件的过程中没有将数据库文件设置为 MySQL 运行的帐号 可读写（一般适用于 Linux 和 FreeBSD 用户）。

故障现象 8

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

问题分析：

由于 mysql 数据库的参数：max_connect_errors，其默认值是 10。当大量 (max_connect_errors)的主机去连接 MySQL，总连接请求超过了 10 次，新的连接就再也无 法连接上 MySQL 服务。同一 个 ip 在短时间内产生太多中断的数据库连接而导致的阻塞（超过 mysql 数据库 max_connection_errors 的最大值）。

解决方法：

● 使用 mysqladmin flush-hosts 命令清除缓存，命令执行方法如下：

mysqladmin -uroot -p -h 192.168.241.48 flush-hosts Enter password:

●修改 mysql 配置文件，在[mysqld]下面添加 max_connect_errors=1000，然后重 启 MyS