目录
一：MySQL单实例故障排查
1：故障现象1
[root@Euler05 ~]# mysql -uroot -ppwd123
mysql: [Warning] Using a password on the command line interface can be insecure.问题分析：密码输出到屏幕上不安全
解决方法：登录MySQL后再输入密码
[root@Euler05 ~]# mysql -uroot -p
Enter password: 2：故障现象2
#制造故障
[root@Euler05 ~]# systemctl stop mysqld
[root@Euler05 ~]# mysql -uroot -ppwd123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/data/mysql.sock' (2)问题分析：连接不到本地MySQL服务器，或者本地MySQL服务没有开启
解决方法：启动服务，服务启动后会生成localhost.pid和mysql.sock
[root@Euler05 ~]# systemctl start mysqld
#查看/etc/my.cnf
/usr/local/mysql/data/mysql.sock #套接字文件3：故障现象3
[root@Euler05 ~]# mysql -uroot -ppwd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)问题分析：拒绝本地用户登录；密码不正确，没有访问权限
解决方法：
更改密码：系统管理员：/etc/my.cnf：skip-grant-tables #跳过密码认证，然后更改密码
老版本：update mysql.user set authentication_string=password(‘123456’) where user=’root’ and Host=’localhost’;
新版本：update mysql.user set authentication_string=’’ where user=’root’ and Host=’localhost’;
flush privileges;
alter user 'root'@'localhost' identified with mysql_native_password by ‘123456’;
在将skip-grant-tables注释掉4：故障现象4
问题分析：本地更改密码后，远程无法登录
解决方法：
create user ‘root’@’%’ identified by '1234567';
grant replication slave on *.* to 'root'@'%';
alter user 'root'@'%' identified with mysql_native_password by '1234567';
flush privileges;
show master status;5：故障现象5
问题分析：连接优化
解决方法：
max_connections=2048
max_connect_errors=1000
mysqladmin -uroot -p -h 192.168.10.201 flush-hosts #清理缓存6：故障现象6
Can't open file: 'xxx_forums.MYI'. (errno:145)问题分析：
- 服务器非正常关机，数据库所在空间已满，或一些其他未知的原因，对数据库表造成了损坏。
- 可能是操作系统下直接将数据库文件拷贝移动，会因为文件的属组问题而产生这个错误。
解决方法：常用修复命令为：
myisamchk -r 数据文件目录/数据表明.MYI;二：MySQL主从故障排查
1：故障现象1
问题分析：
主从数据不一致（如从库被直接修改）
主库执行了从库不支持的SQL语句
主从表结构不一致
常见错误代码：1062（主键冲突）、1032（数据不存在）
解决方法：
#查看具体错误
show slave status\G;
#临时跳过错误
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;
#数据修复错误一致性
mysqldump --master-data=2 -h主库IP > all.sql
mysql -h从库IP < all.sql2：故障现象2
问题分析：
从库硬件性能不足
主库大事务（批量操作）
网络带宽不足
从库负载过高
单线程复制瓶颈
解决方法：
#优化方案
#升级MySQL多线程复制
STOP SLAVE;
SET GLOBAL slave_parallel_workers=4;
START SLAVE;3：故障现象3
问题分析：
主从连接故障（网络/权限）
主库二进制日志损坏
主库重启导致binlog位置变化
防火墙拦截
解决方法：
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.00000X',
MASTER_LOG_POS=XXX;
START SLAVE;三：MySQL优化
1：硬件方面
（1）关于CPU
多核处理器：MySQL 5.6+版本支持多核并行查询，建议选择多核高频CPU
绑核策略：使用
taskset绑定MySQL进程到独立CPU核心，避免上下文切换C-State控制：在BIOS中关闭CPU节能模式，保持高性能状态
（2）关于内存
- InnoDB缓冲池：设置为物理内存的70%-80%
innodb_buffer_pool_size = 64G # 示例：64GB服务器建议配置- 会话内存：根据连接数调整排序/临时表内存
sort_buffer_size = 4M # 避免过大
read_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M（3）关于磁盘
存储类型：优先使用PCIe NVMe SSD，避免SATA/SAS机械盘
RAID配置：建议RAID 10（兼顾性能与冗余）
文件系统：使用XFS或ext4，禁用atime更新
mount -o noatime,nodiratime /dev/sdb /dataI/O调度器：SSD建议使用
noop或deadline
echo deadline > /sys/block/sda/queue/scheduler2：MySQL配置文件
（1）核心性能优化项
[mysqld]
max_connections = 2000 # 根据业务需求调整
thread_cache_size = 100 # 线程缓存
table_open_cache = 4096 # 表缓存
innodb_flush_method = O_DIRECT # 直接写入磁盘，绕过OS缓存
innodb_file_per_table = ON # 独立表空间
skip_name_resolve = ON # 禁用DNS反向解析（2）查询优化项
long_query_time = 1 # 慢查询阈值（秒）
slow_query_log = 1 # 开启慢查询日志
log_queries_not_using_indexes = 1# 记录无索引查询
query_cache_type = 0 # 8.0+已移除查询缓存，建议关闭
optimizer_switch = 'index_merge=off' # 关闭索引合并优化（按需）（3）日志与监控
log_bin = /data/mysql-bin # 必须开启二进制日志
expire_logs_days = 7 # 自动清理旧日志
server-id = 1 # 集群唯一标识
performance_schema = ON # 性能监控
innodb_status_output = ON # InnoDB状态输出（4）InnoDB高级优化
innodb_log_file_size = 4G # 日志文件大小（建议总大小为缓冲池25%）
innodb_log_buffer_size = 256M # 日志缓冲区
innodb_flush_log_at_trx_commit = 2 # 1=安全模式，2=性能模式
innodb_io_capacity = 20000 # SSD建议20000+
innodb_purge_threads = 4 # 清理线程数
innodb_lock_wait_timeout = 30 # 锁等待超时（秒）表锁定的2种方式，以及它们之间的区别：
Innodb
- 支持事务
- MySQL默认的存储引擎
- 支持外键
- 清空表的操作是重建
Myisam
- 不支持
- 读/写
- 不支持外键
- 清空表的操作是一行一行的删
（5）示例配置片段（my.cnf）
[mysqld]
#核心位置
innodb_buffer_pool_size = 40G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
max_connections = 1000
thread_cache_size = 100
#查询优化
tmp_table_size = 128M
max_heap_table_size = 128M
sort_buffer_size = 4M
join_buffer_size = 8M
#日志与监控
slow_query_log = ON
slow_query_time = 1
log_error = /var/log/mysql/error.log
binlog_format = ROW
expire_logs_days = 7
#InnoDB高级
innodb_io_capacity = 2000
innodb_flush_method = 0_DIRECT
innodb_thread_concurrency = 0
innodb_authinc_lock_mode = 23：SQL方面
（1）创建测试表并插入数据
#创建测试库
Create database test;
#创建用户表
Use test;
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
#插入 10 万条测试数据（使用存储过程生成）
DELIMITER $$
CREATE PROCEDURE insert_users()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 100000 DO
INSERT INTO users (name, email, age)
VALUES (CONCAT('user', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL insert_users();（2）使用EXPLAIN进行SQL优化的步骤及实验验证
实验目标：优化以下典型查询的性能
SELECT * FROM users
WHERE age BETWEEN 20 AND 30
ORDER BY created_at DESC
LIMIT 100;
#执行EXPLAIN：
EXPLAIN
SELECT * FROM users
WHERE age BETWEEN 20 AND 30
ORDER BY created_at DESC
LIMIT 100;
#输出结果：
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 99723 | 11.11 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+（3）优化步骤
优化方案：创建覆盖查询条件的复合索引
ALTER TABLE users
ADD INDEX idx_age_created_at (age, created_at);（4）优化后查询及EXPLAIN分析
#执行优化后的EXPLAIN：
EXPLAIN
SELECT * FROM users
WHERE age BETWEEN 20 AND 30
ORDER BY created_at DESC
LIMIT 100;
#输出结果：
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | users | NULL | range | idx_age_created_at | idx_age_created_at | 4 | NULL | 9972 | 100.00 | Using index condition; Backward index scan |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+---------------------------------------+