MySQL教程FG287-MySQL故障诊断实战

内容简介：本文主要介绍MySQL存储过程参数与返回值的相关知识，包括参数类型、使用方法和最佳实践等内容。风哥教程参考MySQL官方文档MySQL Troubleshooting、MySQL Server Administration。

Part01-基础概念与理论知识

1.1 故障诊断的重要性

MySQL故障诊断是数据库运维的核心技能，能够快速定位和解决问题，减少停机时间。 01 更多视频教程www.fgedu.net.cn

1.2 故障诊断方法

系统化的故障诊断方法：

# 故障诊断方法

1. 问题定义
– 问题描述
– 影响范围
– 发生时间
– 复现步骤

2. 信息收集
– 错误日志
– 慢查询日志
– 系统状态
– 性能指标

3. 问题分析
– 根因分析
– 影响评估
– 解决方案

4. 问题解决
– 实施修复
– 验证效果
– 文档记录

5. 预防措施
– 监控告警
– 定期检查
– 容量规划

Part02-生产环境规划与建议

2.1 错误日志

错误日志是诊断MySQL故障的首要工具： 02 学习交流加群风哥微信: itpux-com

# 错误日志使用

1. 查看错误日志位置
mysql> SHOW VARIABLES LIKE ‘log_error’;

2. 查看错误日志
tail -f /var/log/mysql/error.log

输出示例：
2026-04-01T10:00:00.000000Z 0 [ERROR] [MY-010267] [Server] Out of memory
2026-04-01T10:00:01.000000Z 0 [ERROR] [MY-010267] [Server] Too many connections
2026-04-01T10:00:02.000000Z 0 [ERROR] [MY-010267] [Server] Disk is full

3. 搜索特定错误
grep -i “error” /var/log/mysql/error.log

输出示例：
2026-04-01T10:00:00.000000Z 0 [ERROR] [MY-010267] [Server] Out of memory
2026-04-01T10:00:01.000000Z 0 [ERROR] [MY-010267] [Server] Too many connections

4. 分析错误频率
grep -c “ERROR” /var/log/mysql/error.log

输出示例：
10

2.2 慢查询日志

慢查询日志帮助发现性能问题：

# 慢查询日志使用

1. 查看慢查询日志位置
mysql> SHOW VARIABLES LIKE ‘slow_query_log%’;

2. 查看慢查询日志
tail -f /var/log/mysql/mysql-slow.log

输出示例：
# Time: 2026-04-01T10:00:00.000000Z
# User@Host: root[root] @ localhost [] Id: 10
# Query_time: 5.000000 Lock_time: 0.000000 Rows_sent: 1000 Rows_examined: 1000000
SET timestamp=1711948800;
SELECT * FROM users WHERE name LIKE ‘%John%’;

3. 分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

输出示例：
Count: 10 Time=5.00s (50s) Lock=0.00s (0s) Rows=1000.0 (10000), root[root]@localhost
SELECT * FROM users WHERE name LIKE ‘S’

4. 使用pt-query-digest分析
pt-query-digest /var/log/mysql/mysql-slow.log

输出示例：
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============== ===== ====== ===== ==============
# 1 0x1234567890ABCDEF 50.0000 10 5.0000 0.01 SELECT * FROM users WHERE name LIKE ‘S’

2.3 进程列表

查看当前运行的进程： 03 学习交流加群风哥QQ113257174

# 进程列表使用

1. 查看进程列表
mysql> SHOW PROCESSLIST;

2. 查看完整进程列表
mysql> SHOW FULL PROCESSLIST;

3. 终止进程
mysql> KILL 10;

输出示例：
Query OK, 0 rows affected (0.00 sec)

Part03-生产环境项目实施方案

3.1 连接问题

诊断和解决连接问题： 04 风哥提示：

# 连接问题诊断

1. 检查MySQL服务状态
systemctl status mysqld

输出示例：
● mysqld.service – MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2026-04-01 10:00:00 UTC; 1h ago
Process: 1234 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Main PID: 1235 (mysqld)
Tasks: 27 (limit: 4915)
Memory: 500.0M
CGroup: /system.slice/mysqld.service
└─1235 /usr/sbin/mysqld –daemonize –pid-file=/var/run/mysqld/mysqld.pid

2. 检查MySQL端口
netstat -tuln | grep 3306

输出示例：
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

3. 检查防火墙
firewall-cmd –list-ports

输出示例：
3306/tcp

4. 检查连接数
mysql> SHOW STATUS LIKE ‘Threads_connected’;

输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 100 |
+——————-+——-+

5. 检查最大连接数
mysql> SHOW VARIABLES LIKE ‘max_connections’;

输出示例：
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |
+—————–+——-+

6. 解决连接数过多问题
vim /etc/my.cnf

[mysqld]
max_connections = 500

systemctl restart mysqld

3.2 性能问题

诊断和解决性能问题：

# 性能问题诊断

1. 查看系统资源使用
top

输出示例：
top – 10:00:00 up 1 day, 1:00, 2 users, load average: 2.00, 1.50, 1.00
Tasks: 100 total, 2 running, 98 sleeping, 0 stopped, 0 zombie
%Cpu(s): 80.0 us, 10.0 sy, 0.0 ni, 5.0 id, 5.0 wa, 0.0 hi, 0.0 si, 0.0 st
MiB Mem: 8000.0 total, 1000.0 free, 5000.0 used, 2000.0 buff/cache
MiB Swap: 2000.0 total, 1000.0 free, 1000.0 used. 6000.0 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1235 mysql 20 0 1.0g 500m 10m R 80.0 6.2 1:00.00 mysqld

2. 查看磁盘I/O
iostat -x 1

输出示例：
Device rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 10.00 5.00 0.50 0.25 100.00 1.00 10.00 5.00 15.00 8.00 12.00

3. 查看MySQL状态
mysql> SHOW ENGINE INNODB STATUS\G;

输出示例：
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2026-04-01 10:00:00 0x7f1234567890 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
—————–
BACKGROUND THREAD
—————–
srv_master_thread loops: 100 srv_active, 0 srv_idle, srv_throttle, 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s, 0.00 writes/s
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 10
OS WAIT ARRAY INFO: signal count 10
RW-shared spins 0, RW-excl spins 0, RW-sx spins 0
Spin rounds per wait: 0.00 mutex, 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx

4. 查看慢查询
mysql> SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

输出示例：
+———————+——+————+————+————+————+—————-+————+—————-+—————-+————-+
| query | db | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent |
+———————+——+————+————+————+————+—————-+————+—————-+—————-+————-+
| SELECT * FROM users | test | 1 | 10000 | 0 | 0 | 50.00 s | 1.00 s | 5.00 ms | 10.00 s | 100000 |
+———————+——+————+————+————+————+—————-+————+—————-+—————-+————-+

3.3 复制问题

诊断和解决复制问题： 05更多学习教程公众号风哥教程itpux_com

# 复制问题诊断

1. 查看复制状态
mysql> SHOW SLAVE STATUS\G;

输出示例：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 12345
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 12345
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12345
Relay_Log_Space: 12345
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12345678-1234-1234-1234-1234567890ab
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:

2. 检查复制延迟
mysql> SELECT * FROM performance_schema.replication_connection_status\G;

输出示例：
*************************** 1. row ***************************
CHANNEL_NAME:
SOURCE_UUID: 12345678-1234-1234-1234-1234567890ab
THREAD_ID: 10
SOURCE_SERVER: 192.168.1.100
SOURCE_PORT: 3306
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 1000
LAST_HEARTBEAT_TIMESTAMP: 2026-04-01 10:00:00
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

3. 解决复制错误
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

输出示例：
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

Part04-生产案例与实战讲解

4.1 崩溃恢复

MySQL崩溃后的恢复： 06 from mysql视频:www.itpux.com

# 崩溃恢复

1. 检查MySQL状态
systemctl status mysqld

输出示例：
● mysqld.service – MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Mon 2026-04-01 10:00:00 UTC; 1s ago
Process: 1234 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Main PID: 1235 (code=exited, status=0/SUCCESS)

2. 查看错误日志
tail -100 /var/log/mysql/error.log

输出示例：
2026-04-01T10:00:00.000000Z 0 [ERROR] [MY-010267] [Server] Out of memory
2026-04-01T10:00:00.000000Z 0 [ERROR] [MY-010267] [Server] mysqld got exception 0xc0000005

3. 尝试启动MySQL
systemctl start mysqld

输出示例：
Job for mysqld.service failed because the control process exited with error code.
See “systemctl status mysqld.service” and “journalctl -xe” for details.

4. 检查InnoDB恢复
vim /var/lib/mysql/ibdata1

5. 使用InnoDB恢复工具
innodb_space –file=/var/lib/mysql/ibdata1 space-summary

6. 从备份恢复
xtrabackup –copy-back –target-dir=/backup/full –datadir=/var/lib/mysql

7. 调整权限
chown -R mysql:mysql /var/lib/mysql

8. 启动MySQL
systemctl start mysqld

输出示例：
● mysqld.service – MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2026-04-01 10:05:00 UTC; 5s ago
Process: 1234 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Main PID: 1235 (mysqld)
Tasks: 27 (limit: 4915)
Memory: 500.0M
CGroup: /system.slice/mysqld.service
└─1235 /usr/sbin/mysqld –daemonize –pid-file=/var/run/mysqld/mysqld.pid

9. 验证数据
mysql -u root -p -e “SHOW DATABASES;”

4.2 数据损坏

数据损坏的诊断和恢复：

# 数据损坏诊断和恢复

1. 检查表完整性
mysql> CHECK TABLE users;

2. 修复表
mysql> REPAIR TABLE users;

3. 使用myisamchk修复（MyISAM表）
myisamchk –recover /var/lib/mysql/test/users.MYI

输出示例：
MyISAM-table ‘users.MYI’ is usable but should be fixed

4. 使用InnoDB恢复
innodb_force_recovery = 1

vim /etc/my.cnf

[mysqld]
innodb_force_recovery = 1

systemctl restart mysqld

5. 导出数据
mysqldump -u root -p test_db > test_db_backup.sql

6. 重新创建数据库
mysql -u root -p -e “DROP DATABASE test_db; CREATE DATABASE test_db;”

7. 导入数据
mysql -u root -p test_db < test_db_backup.sql

8. 验证数据
mysql -u root -p test_db -e “SELECT COUNT(*) FROM users;”

输出示例：
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+