MySQL故障排查与优化
一、MySQL故障排查
1.1 故障现象与解决方法
1.1.1 故障1
1.1.2 故障2
1.1.3 故障3
1.1.4 故障4
1.1.5 故障5
1.1.6 故障6
1.1.7 故障7
1.1.8 故障8
1.1.9 MySQL 主从故障排查
二、MySQL优化
2.1 硬件方面
2.2 查询优化
一、MySQL故障排查
1.1 故障现象与解决方法
1.1.1 故障1
原因：数据库未启动或者数据库端口被防火墙拦截。
解决方法：启动数据库或者防火墙开放数据库监听端口。
1.1.2 故障2
原因：密码不正确或者没有权限访问。
解决方法：
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> flush privileges;
2）再删除刚刚添加的 skip-grant-tables 参数，重启数据库，使用新密码即可登录。
3）重新授权，命令如下：
mysql>grant all on *.* to 'root'@'mysql-server' identified by '123456';1.1.3 故障3
使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题。
原因：DNS解析慢、客户端连接过多。
解决方法：修改 my.cnf 主配置文件，在[mysqld]下添加 skip-name-resolve，重启数据库可以解决。注意在以后授权里面不能再使用主机名授权。
1.1.4 故障4
原因：
1）服务器非正常关机，数据库所在空间已满，或一些其它未知的原因，对数据库表造 成了损坏。
2）可能是操作系统下直接将数据库文件拷贝移动，会因为文件的属组问题而产生这个错误。
解决方法：
使用下面的两种方式修复数据表（第一种方法仅适合独立主机用户）：
1）使用 MySQL 自带的专门用户数据表检查和修复工具 myisamchk。一般情况下只有在命令行下面才能运行 myisamchk 命令。常用的修复命令为：
myisamchk -r 数据文件目录/数据表名.MYI；
通过 phpMyAdmin 修复， phpMyAdmin 带有修复数据表的功能，进入到某一个表中后，点击“操作”，在下方的“表维护”中点击“修复表”即可。
注意：以上两种修复方式在执行前一定要备份数据库。
2）修改文件的属组（仅适合独立主机用户）：
复制数据库文件的过程中没有将数据库文件设置为 MySQL 运行的帐号可读写（一般适用于 Linux 和 FreeBSD 用户）。
1.1.5 故障5
原因：超出最大连接错误数量限制。
解决办法：
1）使用 mysqladmin flush-hosts 命令清除缓存，命令执行方法如下：
mysqladmin -uroot -p -h 192.168.241.48 flush-hosts Enter password:2）修改 mysql 配置文件，在[mysqld]下面添加 max_connect_errors=1000，然后重启 MySQL。
1.1.6 故障6
客户端报 Too many connections。
原因：连接数超出 Mysql 的最大连接数限制。
解决办法：
1）在 my.cnf 配置文件里面增大连接数，然后重启 MySQL 服务。
max_connections = 100002）临时修改最大连接数，重启后不生效。需要在 my.cnf 里面修改配置文件，下次重启生效。
set GLOBAL max_connections=10000;1.1.7 故障7
原因：MySQL 的配置文件/etc/my.cnf 权限不对。
解决办法：chmod 644 /etc/my.cnf
1.1.8 故障8
原因：innodb 数据文件损坏。
解决方法：修改 my.cnf 配置文件，在[mysqld]下添加 innodb_force_recovery=4, 启动数据库后备份数据文件，然后去掉该参数，利用备份文件恢复数据。
1.1.9 MySQL 主从故障排查
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）故障现象
从库的 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）故障现象
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优化
2.1 硬件方面
（1）推荐使用 S.M.P.架构的多路对称 CPU。例如：可以使用两颗Intel Xeon 3.6GHz 的 CPU。现在比较推荐用 4U 的服务器来专门做数据库服务器。
（2）推荐使用 4GB 以上的物理内存。
（3）通常是使用 RAID-0+1 磁盘阵列，注意不要尝试使用RAID-5，MySQL 在 RAID-5 磁盘阵列上的效率并不高。如果不考虑硬件的投入成本，也可以考虑固态（SSD）硬盘专门作为数据库服务器使用。数据库的读写性能肯定会提高很多。
2.2 查询优化
（1）建表时表结构要合理，每个表不宜过大；在任何情况下均应使用最精确的类型。
（2）索引，建立合适的索引。
（3）查询时尽量减少逻辑运算（与运算、或运算、大于小于某值的运算）；
（4）减少不当的查询语句，不要查询应用中不需要的列，比如说 select * from 等操作。
（5）减小事务包的大小；
（6）将多个小的查询适当合并成一个大的查询，减少每次建立/关闭查询时的开销；
（7）将某些过于复杂的查询拆解成多个小查询，和上一条恰好相反。
（8）建立和优化存储过程来代替大量的外部程序交互。
2.3 小结
2.3.1 软优化
（1）查询语句的优化比如:select 结合order by [ascldesc]排序之后的limit查询适当使用子查询，结合连接表达式使用J0IN查询
（2）使用索引在适合创建索引的字段创建索引:经常与其他表进行连接的表，在连接字段上应该建立索引、建在选择性高的字段上、建在小字段上:在不适合创建索引的字段不要盲目创建索引:唯一性太差的字段、更新太频繁地字段、大的文本字段甚至超长字段，不要建立索引。
（3）优化子查询:
1）分析表
2）增加中间表
3）增加冗余字段
4）分析表，检查表，优化表
2.3.2 硬优化
（1）分库分表
（2）读写分离
对系统做分库分表、读写分离:通过缓存集群来承载大部分的读请求。