mysql数据库的故障排查与优化

一、mysql单实例的故障排查

（1）故障现象 1

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

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

                    mysql服务没有打开。

解决方法：启动数据库或者防火墙开放数据库监听端口。检查mysql相关服务是否打开了

（2）故障现象 2

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

问题分析：密码不正确或者没有权限访问。

解决方法：
        修改 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> flush privileges;

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

重新授权，命令如下。

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

（3）故障现象 3

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

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

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

（4）故障现象 4

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

问题分析：服务器非正常关机，数据库所在空间已满，或一些其它未知的原因，对数据库表造  成了损坏。
        可能是操作系统下直接将数据库文件拷贝移动，会因为文件的属组问题而产生这个  错误。
解决方法：

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

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

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

        通过 phpMyAdmin 修复， phpMyAdmin 带有修复数据表的功能，进入到某一个表中后，点击“操作”，在下方的“表维护”中点击“修复表”即可。
注意：以上两种修复方式在执行前一定要备份数据库。

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

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

（5）故障现象 5

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，然后重启 MySQL。

（6）故障现象 6

客户端报 Too many connections。

问题分析：连接数超出 Mysql 的最大连接数限制。

解决方法：在 my.cnf 配置文件里面增大连接数，然后重启 MySQL 服务。

max_connections = 10000

临时修改最大连接数，重启后不生效。需要在 my.cnf 里面修改配置文件，下次重启生效。

set GLOBAL max_connections=10000;

（7）故障现象 7

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

问题分析：MySQL 的配置文件/etc/my.cnf 权限不对。

解决方法：chmod 644 /et/my.cnf

（8）故障现象 8

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

问题分析：innodb 数据文件损坏。

解决方法：修改 my.cnf 配置文件，在[mysqld]下添加 innodb_force_recovery=4, 启动数据库后备份数据文件，然后去掉该参数，利用备份文件恢复数据。

二、MySQL 主从故障排查

（1）故障现象 1

从库的 Slave_IO_Running 为 NO
The slave I/O thread stops because master and slave have equal MySQL server ids; these 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 的值，修改为和主库不一样。修改完后重启，再同步即可。

（2）故障现象 2

从库的 Slave_IO_Running 为 NO

问题分析：造成从库线程为 NO 的原因会有很多，主要原因是主键冲突或者主库删除或更新数据，从库找不到记录，数据被修改导致。通常状态码报错有 1007、1032、1062、1452 等。
解决方法一：

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> 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 点，然后重新同步即可。

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

三、mysql优化

3.1 硬件方面

关于cpu：对于 MySQL 应用，推荐使用 S.M.P.架构的多路对称 CPU，4u的服务器来做专门的数据库分析

关于内存：物理内存对于一台使用 MySQL 的 Database Server 来说，服务器内存建议不要小于2GB，推荐使用 4GB 以上的物理内存。不过内存对于现在的服务器而言可以说是一个可以忽略的问题，工作中遇到了高端服务器基本上内存都超过了 32G。

关于磁盘：磁盘寻道能力（磁盘 I/O）。以目前市场上普遍高转速 SAS 硬盘(15000 转/秒)为例， 这种硬盘理论上每秒寻道 15000 次，这是物理特性决定的，没有办法改变。 MySQL 每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以通常认为磁盘 I/O 是制约 MySQL 性能的最大因素之一，通常是使用 RAID-0+1 磁盘阵列，注意不要尝试使用RAID-5，MySQL 在 RAID-5 磁盘阵列上的效率并不高。如果不考虑硬件的投入成本，也可以考虑固态（SSD）硬盘专门作为数据库服务器使用。数据库的读写性能肯定会提高很多。

3.2 配置文件

        默认的 my.cnf 配置文件无法发挥出 MySQL 最高的性能，所以需要根据不同的硬件进行优化，配置文件的优化也是重点。下面是物理内存为 32G 的数据库优化参数，具体从全局、二进制日志、主从、innodb、myisam 几个方面优化。

（1）default-time-zone=+8:00

默认 MySQL 使用的是系统时区，修改为北京时间，也就是所说的东八区。

（2）interactive_timeout = 120

服务器关闭交互式连接前等待活动的秒数。

（3）wait_timeout = 120

服务器关闭非交互连接之前等待活动的秒数。

（4）open_files_limit = 10240

MySQL 服务器打开文件句柄数限制。

（5）group_concat_max_len = 102400

MySQL 默认的拼接最大长度为 1024 个字节，由于 1024 个字节会出现不够用的情况， 根据实际情况进行修改。

（6）user=mysql

使用 mysql 用户运行。

（7）character-set-server=utf8、init_connect='SET NAMES utf8'

设置字符集为 utf8

（8）back_log = 600

在 MySQL 暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的 TCP/IP 连接的监听队列的大小。默认值 50。

（9）max_connections = 5000

MySQL 允许最大的进程连接数，如果经常出现 Too Many Connections 的错误提示， 则需要增大此值。

（10）max_connect_errors = 6000

设置每个主机的连接请求异常中断的最大次数。当超过该次数，MySQL 服务器将禁止
host 的连接请求，直到 MySQL 服务器重启或通过flush hosts 命令清空此host 的相关信息。
        

（11）table_cache = 1024

        数据表调整缓冲区大小。它设置表高速缓存的数目。每个连接进来，都会至少打开一个表缓存。因此，table_cache 的大小与 max_connections 的设置有关。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200×N。这里 N 是应用可以执行查询的一个连接中表的最大数量。
        此外，还需要为临时表和文件保留一些额外的文件描述符。 当 MySQL 访问一个表时， 如果该表在缓存中已经被打开，则可以直接访问缓存。如果还没有被缓存，但是在 MySQL 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区。如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存放，使用表缓存的好处是可以更快速地访问表中的内容。执行 flushtables 会清空缓存的内容。
        一般来说，可以通过 showstatus 命令查看数据库运行峰值时间的状态值 Open_tables 和 Opened_tables，判断是否需要增加 table_cache 的值（其中 open_tables 是当前打开的表的数量，Opened_tables 则是已经打开的表的数量）。若 open_tables 接近 table_cache， 并且 Opened_tables 值在逐步增加， 那就要考虑增加这个值的大小了。还有就是Table_locks_waited 比较高的时候，也需要增加 table_cache。

（12）table_open_cache = 2048

指定表高速缓存的大小。每当MySQL 访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。