我要评论一、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：性能优先，金融级业务建议设为 1
- o_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故障排查与环境优化的资料请关注代码网其它相关文章！
发表评论