MySQL 故障排查与生产环境优化（精简实用版）

本文基于 MySQL 8.0 生产环境整理高频故障排查方案、全维度优化配置、SQL 性能调优包含完整可执行代码补充关键知识点简洁易懂、不与原文重复。一、MySQL 基础架构补充知识点MySQL 采用三层架构明确各层职责便于定位故障连接层处理客户端连接、权限验证、线程管理服务层SQL 解析、优化、查询缓存、执行计划生成存储引擎层数据存储与提取InnoDB 为生产首选数据层文件系统存储数据、日志、索引文件核心知识点生产环境必须使用 InnoDB支持事务、行锁、崩溃恢复MyISAM 已不推荐。二、MySQL 单实例故障排查含完整代码1. 无法通过 socket 连接报错ERROR 2002 (HY000): Cant connect to local MySQL server through socket /data/mysql/mysql.sock (2)原因MySQL 未启动、socket 路径错误、防火墙拦截解决bash运行# 启动MySQL systemctl start mysqld # 防火墙开放3306端口 firewall-cmd --add-port3306/tcp --permanent firewall-cmd --reload2. root 密码丢失 / 权限拒绝报错ERROR 1045 (28000): Access denied for user rootlocalhost解决MySQL 8.0 专用bash运行# 1. 编辑配置文件 vi /etc/my.cnf # [mysqld]下添加 skip-grant-tableson # 2. 重启MySQL systemctl restart mysqld # 3. 无密码登录并重置密码 mysql mysql UPDATE mysql.user SET authentication_string WHERE userroot AND Hostlocalhost; mysql FLUSH PRIVILEGES; mysql ALTER USER rootlocalhost IDENTIFIED BY 新密码; # 4. 删除skip-grant-tables重启MySQL systemctl restart mysqld # 5. 创建远程用户并授权 mysql CREATE USER root% IDENTIFIED BY 密码; mysql GRANT ALL ON *.* TO root% WITH GRANT OPTION; mysql FLUSH PRIVILEGES;3. 远程连接极慢原因MySQL 默认开启 DNS 反向解析内网无 DNS 导致超时解决bash运行vi /etc/my.cnf # [mysqld]下添加 skip-name-resolveon systemctl restart mysqld知识点开启后只能用 IP 授权不能用主机名。4. 表损坏无法打开报错Cant open file: xxx.MYI (errno: 145)解决bash运行# 命令行修复MyISAM表 myisamchk -r /var/lib/mysql/库名/表名.MYI # SQL修复InnoDB/MyISAM通用 mysql REPAIR TABLE 表名;知识点修复前必须备份数据避免数据丢失。5. 主机被阻塞报错Host xxx is blocked because of many connection errors解决bash运行# 临时解除阻塞 mysqladmin -uroot -p flush-hosts # 永久配置 vi /etc/my.cnf max_connect_errors1000 systemctl restart mysqld6. 连接数超限报错Too many connections解决bash运行# 临时修改重启失效 mysql SET GLOBAL max_connections10000; # 永久修改 vi /etc/my.cnf max_connections10000 systemctl restart mysqld7. 配置文件权限错误报错World-writable config file /etc/my.cnf is ignored解决bash运行chmod 644 /etc/my.cnf chown mysql:mysql /etc/my.cnf8. InnoDB 数据文件损坏原因服务器宕机、磁盘故障导致 ibd 文件损坏解决bash运行vi /etc/my.cnf # [mysqld]下添加 innodb_force_recovery4 # 启动后备份数据再删除参数重启 systemctl start mysqld mysqldump -uroot -p --all-databases all_backup.sql知识点innodb_force_recovery4为只读模式仅用于备份不可长期使用。三、MySQL 主从复制故障排查含完整代码1. 主从 server-id 冲突现象Slave_IO_RunningNO解决bash运行# 从库修改配置 vi /etc/my.cnf server-id2 # 主从必须不同 systemctl restart mysqld # 重启同步 mysql STOP SLAVE; mysql START SLAVE;2. 从库同步报错主键冲突 / 数据丢失常见错误码1062、1032、1007解决bash运行# 跳过1条错误SQL mysql STOP SLAVE; mysql SET GLOBAL SQL_SLAVE_SKIP_COUNTER1; mysql START SLAVE; # 从库设为只读防止数据篡改 mysql SET GLOBAL read_onlyON;3. 中继日志损坏现象I/O error reading the header from the binary log解决bash运行# 重新指定主库binlog和pos点 mysql STOP SLAVE; mysql CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; mysql START SLAVE;四、MySQL 生产环境优化硬件 配置 SQL一硬件优化补充知识点CPU推荐多核 CPUMySQL 8.0 对多核调度优化更好内存生产≥16GB缓冲池占内存 50%~70%磁盘SSD 优先RAID10读写性能 数据安全禁用 RAID5网络主从复制用内网避免公网延迟二配置文件优化my.cnf 完整版适用32 核 CPU、64GB 内存、SSD 硬盘ini[mysqld] # 基础配置 usermysql port3306 datadir/var/lib/mysql socket/data/mysql/mysql.sock skip-name-resolveon default-storage-engineInnoDB character-set-serverutf8mb4 # 连接与线程 max_connections1000 thread_cache_size100 max_connect_errors1000 # InnoDB核心配置 innodb_buffer_pool_size40G innodb_log_file_size2G innodb_log_files_in_group2 innodb_flush_log_at_trx_commit2 innodb_flush_methodO_DIRECT innodb_io_capacity2000 innodb_thread_concurrency0 innodb_autoinc_lock_mode2 # 临时表与排序 tmp_table_size128M max_heap_table_size128M sort_buffer_size4M join_buffer_size8M # 日志配置 slow_query_logON long_query_time1 log_error/var/log/mysql/error.log binlog_formatROW expire_logs_days7补充知识点innodb_flush_log_at_trx_commit2性能优先金融级业务建议设为 1O_DIRECT绕过系统缓存直接读写磁盘减少双缓冲三SQL 优化含完整测试代码1. 创建测试表与数据sql-- 创建库 CREATE DATABASE test_db; USE test_db; -- 创建用户表 CREATE TABLE user_info ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, age TINYINT NOT NULL, create_time DATETIME DEFAULT CURRENT_TIMESTAMP ); -- 插入10万测试数据 DELIMITER $$ CREATE PROCEDURE insert_data() BEGIN DECLARE i INT DEFAULT 0; WHILE i 100000 DO INSERT INTO user_info(username, phone, age) VALUES (CONCAT(test_,i), CONCAT(1380000,FLOOR(RAND()*10000)), FLOOR(RAND()*50)18); SET i i 1; END WHILE; END$$ DELIMITER ; -- 执行存储过程 CALL insert_data();2. EXPLAIN 分析 SQLsql-- 未加索引全表扫描 EXPLAIN SELECT * FROM user_info WHERE usernametest_1234; -- 添加索引 ALTER TABLE user_info ADD INDEX idx_username(username); -- 加索引后索引查找 EXPLAIN SELECT * FROM user_info WHERE usernametest_1234;补充知识点EXPLAIN 中type优先级const ref range index ALL避免ALL全表扫描优先建立单列 / 复合索引五、优化总结故障优先先排查连接、权限、配置再处理数据损坏主从稳定保证 server-id 唯一、从库只读、定期校验数据配置核心InnoDB 缓冲池、日志、刷新策略决定性能SQL 关键用 EXPLAIN 分析建立合理索引避免慢查询