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

一、基础信息1. 实验环境数据库版本MySQL 8.0架构1 台单实例 2 台主从复制环境用途模拟生产故障、验证优化方案2. MySQL 逻辑架构四层连接层处理客户端连接、授权认证、权限校验提供线程池、SSL 安全连接服务层SQL 接口、查询缓存、SQL 解析与优化实现跨存储引擎功能存储过程、函数等存储引擎层负责数据存储与提取可按需选择InnoDB、MyISAM 等数据存储层数据落地文件系统与存储引擎交互二、单实例常见故障 原因 解决方法完整版ERROR 2002无法通过 socket 连接 MySQL原因数据库未启动、socket 路径错误、防火墙拦截端口解决启动 MySQL开放 3306 端口检查 my.cnf socket 配置ERROR 1045Access denied 密码 / 权限错误原因密码错误、用户无权限解决my.cnf [mysqld] 加skip-grant-tableson重启 MySQLMySQL 8.0 清空密码并重置UPDATE mysql.user SET authentication_string WHERE userroot AND Hostlocalhost; FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY 新密码;删除 skip-grant-tables重启远程连接 MySQL 极慢原因MySQL 反向 DNS 解析耗时解决my.cnf 加skip-name-resolve重启表损坏 errno:145原因异常关机、磁盘满、文件属组错误解决命令行myisamchk -r 表文件.MYI工具phpMyAdmin → 操作 → 修复表修复前务必备份Host 被阻塞连接错误次数超限原因max_connect_errors默认 10超限 IP 被拉黑解决临时mysqladmin flush-hosts永久my.cnf 调大max_connect_errors1000Too many connections 连接数超限原因超过max_connections解决临时set GLOBAL max_connections10000;永久my.cnf 设置max_connections10000配置文件权限警告 PID 找不到原因/etc/my.cnf 权限过大 解决chmod 644 /etc/my.cnfInnoDB 日志序列号错误数据文件损坏原因InnoDB 数据文件异常解决my.cnf 加innodb_force_recovery4启动并全量备份数据去掉参数用备份恢复三、主从复制常见故障 原因 解决Slave_IO_Running NOserver-id 冲突原因主从 server-id 相同解决修改从库 server-id重启重新同步Slave_SQL_Running NO数据不一致 / 主键冲突原因1007/1032/1062/1452 等错误主从数据不一致解决stop slave; set GLOBAL SQL_SLAVE_SKIP_COUNTER1; start slave;从库设只读set global read_onlytrue;中继日志损坏I/O error reading binlog header原因relay-bin 日志损坏解决重新指定主库 binlog 文件名与 pos 点重新同步四、生产环境优化硬件 配置 SQL1. 硬件优化CPU多路对称 SMP 架构专用数据库服务器内存≥4GB推荐 32GB 以上磁盘SAS 15000 转 RAID01优先 SSD禁用 RAID5提升 I/O 是 MySQL 性能关键2. MySQL 8.0 配置优化my.cnf 核心参数1核心性能参数参数作用推荐值注意innodb_buffer_pool_sizeInnoDB 数据 / 索引缓存内存 50%~70%64G→40G不超物理内存innodb_log_file_size重做日志大小2G修改需删旧日志重启max_connections最大连接数1000~10000按并发调整thread_cache_size连接线程缓存100减少连接开销tmp_table_size/max_heap_table_size内存临时表128M两者必须相等2查询缓冲区sort_buffer_size4Mjoin_buffer_size8Mquery_cache_typeOFF8.0 已移除3日志与监控slow_query_log ONlong_query_time 1~2 秒binlog_format ROW主从一致性高expire_logs_days 7~14log_error 指定错误日志路径4InnoDB 高级优化innodb_io_capacitySSD2000~4000HDD200~400innodb_flush_method O_DIRECTinnodb_thread_concurrency 0自适应innodb_autoinc_lock_mode 2高并发插入3. SQL 优化核心方法工具EXPLAIN分析执行计划关键字段type避免 ALL全表扫描优先 ref/rangekey是否命中索引rows扫描行数越少越好Extra避免 Using temporary、Using filesort优化步骤用 EXPLAIN 查看全表扫描 SQL给高频查询字段建索引再次 EXPLAIN 验证索引生效五、完整优化总结MySQL 优化是三层联动硬件层大内存 SSD 合理 RAID提供性能底座配置层聚焦 InnoDB 缓冲池、日志、I/O、连接数SQL 层用 EXPLAIN 诊断建索引消除慢查询