一、MySQL单实例常见故障
1.逻辑架构图
MySQL逻辑架构图
- 客户端和连接服务
- 核心服务功能
- 存储引擎层
- 数据存储层
2.故障一
故障现象
ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/data/mysql/mysql.sock'(2)
问题分析
数据库未启动或者数据库端口被防火墙拦截
解决方法
启动数据库或者防火墙开放数据监听端口
3.故障二
故障现象
ERROR 1045 (28000): Access denied for user 'root'@"ocalhost' (using password:
NO)
问题分析
密码不正确或者没有权限访问
解决办法
修改my.cnf主配置文件,在[mysqld]添加skip——grant-tables
- update更新user表authentication_string字段
- 重新授权
4.故障三
故障现象
在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题
问题分析
DNS解析慢、客户端连接过多
解决方法
- 修改my.cnf主配置(增加skip-name-resolve参数)
- 数据库授权禁止使用主机名
5.故障四
故障现象
Can't open file:'xxx forums.MYl'.(errno: 145)
问题分析
- 服务器非正常关机,数据库所在空间已满,或一些其他未知的原因,对数据库表造成了损坏
- 因拷贝数据库导致文件的属组发生变化
解决方法
- 修复数据表(myisamchk、phpmyadmin)
- 修改文件的属组
6.故障五
故障现象
ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx' is blocked because of manyconnection errors; unblock with 'mysqladmin flush-hosts'
问题分析
超出最大连接错误数量控制
解决方法
- 清楚缓存(flush-hosts关键词)
- 修改mysql配置文件(max_connect_errors=1000)
7.故障六
故障现象
TOO many connections
问题分析
连接数超出MySQL的最大连接限制
解决方法
- 临时MySQL配置文件(max_connection=10000)
- 临时修改参数
set GLOBAL max_connections=10000;
8.故障七
故障现象
Warning:World-writable config file /etc/my.cnf is ignoredERROR!
MySQL is running but PiD file could not be found
问题分析
MySQL的配置文件/etc/my.cnf权限问题
解决方法
chmod 644 /etc/my.cnf
9.故障八
故障现象
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)
- 启动数据库后备份数据文件
-
利用备份文件恢复数据
二、MySQL主从环境常见故障
1.故障一
故障现象
从库里的Slave_IO_Runing为NO
The slave l/0 thread stops because master and slave have equal MySQL serverids: these ids must be different for replication to work (or the --replicate-sameserver-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
问题分析:主键冲突或者主库删除或更新数据,从库内找不到记录,数据被修改导致
解决方法
方法一
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: l/O error reading the header from the binary log
问题分析
从库的中继日志relay-bin损坏
解决方法
手工修复,重新找到同步的binlog和pos点,然后重新同步即可
mysqI> CHANGE MASTER TO MASTER LOG FILE='mysql-bin.xxX',MASTER LOG POS=xxx;
三、MySQL优化
1.硬件优化
CPU:推荐使用S.M.P架构的多路对此CPU
内存:4GB以上的物理内存
存储:使用高速存储设备,如SSD固态硬盘替代传统的HDD机械硬盘,大幅提高I/O性能。
磁盘:RAID-0+1磁盘陈列或固态硬盘
2.MySQL配置文件优化
MySQL配置文件优化
MySQL的配置文件(my.cnf)是用来配置MySQL服务器的参数和选项的。通过对配置文件进行优化,可以提高MySQL服务器的性能和稳定性。以下是一些常见的MySQL配置文件优化建议:
-
内存设置:
- innodb_buffer_pool_size:设置InnoDB存储引擎使用的内存缓冲池大小,建议设置为物理内存的70-80%。
- key_buffer_size:设置MyISAM存储引擎使用的键缓冲区大小,建议设置为物理内存的10%。
- query_cache_size:设置查询缓存的大小,建议根据实际情况进行调整。
-
并发连接设置:
- max_connections:设置允许的最大并发连接数,建议根据实际需求进行调整。
- thread_cache_size:设置线程缓存的大小,建议根据实际情况进行调整。
-
查询优化:
- slow_query_log:开启慢查询日志,记录执行时间超过设定阈值的查询语句,方便进行性能优化。
- log_queries_not_using_indexes:记录未使用索引的查询语句,帮助优化查询性能。
-
日志设置:
- general_log:开启通用查询日志,记录所有查询语句,仅用于调试目的。
- log_error:指定错误日志文件路径,记录MySQL服务器的错误信息。
-
其他优化选项:
- skip-name-resolve:禁用DNS反向解析,加快连接速度。
- skip-locking:禁用旧版的表级锁定,提高并发性能。
3.SQL优化
- 尽量使用索引进行查询
- 优化分页
- GRUOP BY优化
4.MySQL架构优化
MySQL架构优化是指对MySQL数据库的结构和配置进行调整和优化,以提高数据库的性能和可靠性。以下是一些常见的MySQL架构优化方法:
-
数据库优化:合理设计数据库表结构,避免冗余字段和表,使用适当的数据类型和索引,以提高查询效率。
-
查询优化:编写高效的SQL查询语句,避免全表扫描和不必要的连接操作,使用合适的索引来加速查询。
-
硬件优化:选择合适的硬件设备,包括CPU、内存、磁盘等,以满足数据库的性能需求。同时,合理配置硬件参数,如文件系统、内核参数等。
-
缓存优化:使用MySQL的查询缓存功能,将经常查询的结果缓存起来,减少数据库的访问压力。同时,合理设置缓存大小和过期时间,避免缓存过期导致数据不一致。
-
分区和分表:对于大型数据库,可以考虑将数据进行分区或分表存储,以提高查询效率和负载均衡能力。
-
主从复制:通过设置主从复制架构,将读写操作分离到不同的服务器上,提高数据库的并发处理能力和可用性。
-
定期维护:定期进行数据库的备份、优化和清理工作,包括删除不再使用的索引、优化表结构、清理日志文件等。
总结
MySQL故障排除
MySQL无法启动
检查MySQL服务状态及启动脚本。
查看错误日志,了解具体启动失败的原因,如权限问题、配置文件错误、磁盘空间不足等。
确保所有依赖的服务(如网络、存储等)正常运行。
MySQL连接不上
检查数据库服务器是否在线,端口是否开放。
确认账户名、密码、主机名或IP地址无误,以及防火墙设置没有阻止连接请求。
查看最大连接数限制是否已达到。