一、MySQL 基础架构（补充知识点）
MySQL 采用三层架构，明确各层职责，便于定位故障：
- 连接层：处理客户端连接、权限验证、线程管理服务层：SQL 解析、优化、查询缓存、执行计划生成存储引擎层：数据存储与提取，InnoDB 为生产首选数据层：文件系统存储数据、日志、索引文件
核心知识点：生产环境必须使用 InnoDB，支持事务、行锁、崩溃恢复，MyISAM 已不推荐。
二、MySQL 单实例故障排查（含完整代码）
1. 无法通过 socket 连接
报错：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql.sock' (2)原因：MySQL 未启动、socket 路径错误、防火墙拦截解决：
# 启动MySQL systemctl start mysqld # 防火墙开放3306端口 firewall-cmd --add-port=3306/tcp --permanent firewall-cmd --reload
2. root 密码丢失 / 权限拒绝
报错：ERROR 1045 (28000): Access denied for user 'root'@'localhost'解决（MySQL 8.0 专用）：
# 1. 编辑配置文件 vi /etc/my.cnf # [mysqld]下添加 skip-grant-tables=on # 2. 重启MySQL systemctl restart mysqld # 3. 无密码登录并重置密码 mysql mysql> UPDATE mysql.user SET authentication_string='' WHERE user='root' AND Host='localhost'; mysql> FLUSH PRIVILEGES; mysql> ALTER USER 'root'@'localhost' 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 导致超时解决：vi /etc/my.cnf # [mysqld]下添加 skip-name-resolve=on systemctl restart mysqld
知识点：开启后只能用 IP 授权，不能用主机名。
4. 表损坏无法打开
报错：Can't open file: 'xxx.MYI' (errno: 145)解决：
# 命令行修复MyISAM表 myisamchk -r /var/lib/mysql/库名/表名.MYI # SQL修复（InnoDB/MyISAM通用） mysql> REPAIR TABLE 表名;
知识点：修复前必须备份数据，避免数据丢失。
5. 主机被阻塞
报错：Host 'xxx' is blocked because of many connection errors解决：
# 临时解除阻塞 mysqladmin -uroot -p flush-hosts # 永久配置 vi /etc/my.cnf max_connect_errors=1000 systemctl restart mysqld
6. 连接数超限
报错：Too many connections解决：
# 临时修改（重启失效） mysql> SET GLOBAL max_connections=10000; # 永久修改 vi /etc/my.cnf max_connections=10000 systemctl restart mysqld
7. 配置文件权限错误
报错：World-writable config file '/etc/my.cnf' is ignored解决：
chmod 644 /etc/my.cnf chown mysql:mysql /etc/my.cnf
8. InnoDB 数据文件损坏
原因：服务器宕机、磁盘故障导致 ibd 文件损坏解决：vi /etc/my.cnf # [mysqld]下添加 innodb_force_recovery=4 # 启动后备份数据，再删除参数重启 systemctl start mysqld mysqldump -uroot -p --all-databases > all_backup.sql
知识点：innodb_force_recovery=4为只读模式，仅用于备份，不可长期使用。
三、MySQL 主从复制故障排查（含完整代码）
1. 主从 server-id 冲突
现象：Slave_IO_Running=NO解决：
# 从库修改配置 vi /etc/my.cnf server-id=2 # 主从必须不同 systemctl restart mysqld # 重启同步 mysql> STOP SLAVE; mysql> START SLAVE;
2. 从库同步报错（主键冲突 / 数据丢失）
常见错误码：1062、1032、1007解决：# 跳过1条错误SQL mysql> STOP SLAVE; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> START SLAVE; # 从库设为只读（防止数据篡改） mysql> SET GLOBAL read_only=ON;
3. 中继日志损坏
现象：I/O error reading the header from the binary log解决：
# 重新指定主库binlog和pos点 mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; mysql> START SLAVE;
四、MySQL 生产环境优化（硬件 + 配置 + SQL）
（一）硬件优化（补充知识点）
- CPU：推荐多核 CPU，MySQL 8.0 对多核调度优化更好内存：生产≥16GB，缓冲池占内存 50%~70%磁盘：SSD 优先，RAID10（读写性能 + 数据安全），禁用 RAID5网络：主从复制用内网，避免公网延迟
（二）配置文件优化（my.cnf 完整版）
适用：32 核 CPU、64GB 内存、SSD 硬盘
[mysqld] # 基础配置 user=mysql port=3306 datadir=/var/lib/mysql socket=/data/mysql/mysql.sock skip-name-resolve=on default-storage-engine=InnoDB character-set-server=utf8mb4 # 连接与线程 max_connections=1000 thread_cache_size=100 max_connect_errors=1000 # InnoDB核心配置 innodb_buffer_pool_size=40G innodb_log_file_size=2G innodb_log_files_in_group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT innodb_io_capacity=2000 innodb_thread_concurrency=0 innodb_autoinc_lock_mode=2 # 临时表与排序 tmp_table_size=128M max_heap_table_size=128M sort_buffer_size=4M join_buffer_size=8M # 日志配置 slow_query_log=ON long_query_time=1 log_error=/var/log/mysql/error.log binlog_format=ROW expire_logs_days=7
补充知识点：
innodb_flush_log_at_trx_commit=2：性能优先，金融级业务建议设为 1O_DIRECT：绕过系统缓存，直接读写磁盘，减少双缓冲
（三）SQL 优化（含完整测试代码）
1. 创建测试表与数据
-- 创建库 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 分析 SQL
-- 未加索引，全表扫描 EXPLAIN SELECT * FROM user_info WHERE username='test_1234'; -- 添加索引 ALTER TABLE user_info ADD INDEX idx_username(username); -- 加索引后，索引查找 EXPLAIN SELECT * FROM user_info WHERE username='test_1234';
补充知识点：
EXPLAIN 中type优先级：const > ref > range > index > ALL避免ALL（全表扫描），优先建立单列 / 复合索引
五、优化总结
- 故障优先：先排查连接、权限、配置，再处理数据损坏主从稳定：保证 server-id 唯一、从库只读、定期校验数据配置核心：InnoDB 缓冲池、日志、刷新策略决定性能SQL 关键：用 EXPLAIN 分析，建立合理索引，避免慢查询
以上就是MySQL故障排查与生产环境优化指南的详细内容，更多关于MySQL故障排查与环境优化的资料请关注其它相关文章！
