Orion K Blog
MySQL故障排查与问题解决实战指南
Orion K Lv6
  2024-12-18 09:00:00  

MySQL故障排查与问题解决实战指南

概述

MySQL故障排查是数据库管理员必备的核心技能。本文将系统性地介绍MySQL常见故障的诊断方法、排查思路和解决方案，帮助您快速定位和解决各种数据库问题，确保系统稳定运行。

故障排查基础

1. 故障分类和诊断思路

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
-- 故障分类和诊断框架
DELIMITER //
CREATE PROCEDURE TroubleshootingFramework()
BEGIN
    SELECT '=== MySQL故障分类和诊断框架 ===' as section;
    
    -- 创建故障分类表
    CREATE TEMPORARY TABLE fault_categories (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        fault_category VARCHAR(50),
        fault_type VARCHAR(50),
        typical_symptoms TEXT,
        initial_diagnosis TEXT,
        common_causes TEXT,
        urgency_level VARCHAR(20)
    );
    
    -- 插入故障分类数据
    INSERT INTO fault_categories (fault_category, fault_type, typical_symptoms, initial_diagnosis, common_causes, urgency_level) VALUES
    ('连接问题', '无法连接', '客户端连接超时、拒绝连接', '检查服务状态和网络', '服务未启动、端口被占用、防火墙阻断', '高'),
    ('连接问题', '连接数耗尽', '连接被拒绝、Too many connections', '检查连接数配置', '连接池配置不当、连接泄露', '高'),
    ('性能问题', '查询缓慢', '查询响应时间长、超时', '分析慢查询日志', '缺少索引、查询不优化、锁等待', '中'),
    ('性能问题', '系统负载高', 'CPU、内存、IO使用率高', '检查系统资源使用', '并发过高、配置不当、硬件不足', '中'),
    ('数据问题', '数据丢失', '数据查询结果为空、记录缺失', '检查备份和日志', '误删除、硬件故障、复制问题', '高'),
    ('数据问题', '数据不一致', '主从数据不同、查询结果异常', '对比数据和检查复制', '复制延迟、复制错误、并发冲突', '中'),
    ('复制问题', '复制中断', '从库停止复制、复制延迟大', '检查复制状态', '网络问题、权限问题、数据冲突', '中'),
    ('复制问题', '复制错误', '复制线程报错、数据同步失败', '查看错误日志', '数据类型不匹配、约束冲突', '中'),
    ('存储问题', '磁盘空间不足', '写入失败、表空间满', '检查磁盘使用情况', '日志文件过大、数据增长过快', '高'),
    ('存储问题', '表损坏', '查询报错、数据读取异常', '检查表完整性', '异常关机、硬件故障、文件系统错误', '高');
    
    -- 显示故障分类
    SELECT 
        fault_category as '故障类别',
        fault_type as '故障类型',
        typical_symptoms as '典型症状',
        common_causes as '常见原因',
        urgency_level as '紧急程度'
    FROM fault_categories
    ORDER BY 
        CASE urgency_level WHEN '高' THEN 1 WHEN '中' THEN 2 WHEN '低' THEN 3 END,
        fault_category;
    
    -- 诊断流程
    SELECT '=== 故障诊断流程 ===' as subsection;
    
    SELECT 
        '诊断阶段' as diagnosis_phase,
        '主要任务' as main_tasks,
        '关键工具' as key_tools,
        '输出结果' as expected_output
    UNION ALL
    SELECT 
        '1. 问题确认',
        '确认故障现象和影响范围',
        '监控系统、用户反馈',
        '明确的问题描述'
    UNION ALL
    SELECT 
        '2. 信息收集',
        '收集系统状态和日志信息',
        '系统命令、MySQL命令',
        '详细的系统状态'
    UNION ALL
    SELECT 
        '3. 初步分析',
        '基于症状进行初步判断',
        '经验知识、诊断工具',
        '可能的原因列表'
    UNION ALL
    SELECT 
        '4. 深入调查',
        '针对性地深入分析问题',
        '专业工具、详细日志',
        '确定的根本原因'
    UNION ALL
    SELECT 
        '5. 解决方案',
        '制定和实施解决方案',
        '修复工具、配置变更',
        '问题得到解决'
    UNION ALL
    SELECT 
        '6. 验证确认',
        '验证问题是否彻底解决',
        '测试工具、监控验证',
        '系统恢复正常'
    UNION ALL
    SELECT 
        '7. 总结预防',
        '总结经验并制定预防措施',
        '文档记录、流程改进',
        '预防措施和知识积累';
    
END //
DELIMITER ;

-- 故障排查工具箱
DELIMITER //
CREATE PROCEDURE TroubleshootingToolbox()
BEGIN
    SELECT '=== 故障排查工具箱 ===' as section;
    
    -- 1. 系统级诊断工具
    SELECT '=== 系统级诊断工具 ===' as subsection;
    
    SELECT 
        '工具名称' as tool_name,
        '主要功能' as main_function,
        '使用场景' as use_case,
        '关键参数' as key_parameters,
        '输出解读' as output_interpretation
    UNION ALL
    SELECT 
        'top/htop',
        '系统资源监控',
        'CPU和内存使用分析',
        '-p PID 监控特定进程',
        '关注CPU%、MEM%、LOAD'
    UNION ALL
    SELECT 
        'iostat',
        '磁盘IO监控',
        '磁盘性能问题诊断',
        '-x 1 显示扩展统计',
        '关注%util、await、svctm'
    UNION ALL
    SELECT 
        'netstat',
        '网络连接状态',
        '网络连接问题排查',
        '-an 显示所有连接',
        '关注连接数和状态'
    UNION ALL
    SELECT 
        'lsof',
        '文件和端口占用',
        '端口占用和文件锁定',
        '-i :3306 检查端口',
        '确认进程和文件占用'
    UNION ALL
    SELECT 
        'df/du',
        '磁盘空间使用',
        '磁盘空间不足问题',
        '-h 人性化显示',
        '关注使用百分比'
    UNION ALL
    SELECT 
        'ps',
        '进程状态查看',
        '进程运行状态检查',
        'aux 显示详细信息',
        '确认MySQL进程状态';
    
    -- 2. MySQL专用诊断工具
    SELECT '=== MySQL专用诊断工具 ===' as subsection;
    
    SELECT 
        '工具类型' as tool_type,
        '工具名称' as tool_name,
        '主要用途' as main_purpose,
        '使用方法' as usage_method,
        '适用场景' as applicable_scenario
    UNION ALL
    SELECT 
        'MySQL内置',
        'SHOW STATUS',
        '查看服务器状态变量',
        'SHOW GLOBAL STATUS',
        '性能监控和问题诊断'
    UNION ALL
    SELECT 
        'MySQL内置',
        'SHOW PROCESSLIST',
        '查看当前连接和查询',
        'SHOW FULL PROCESSLIST',
        '连接问题和慢查询分析'
    UNION ALL
    SELECT 
        'MySQL内置',
        'EXPLAIN',
        '分析查询执行计划',
        'EXPLAIN SELECT ...',
        '查询性能优化'
    UNION ALL
    SELECT 
        'MySQL内置',
        'SHOW ENGINE INNODB STATUS',
        '查看InnoDB引擎状态',
        '直接执行命令',
        'InnoDB相关问题诊断'
    UNION ALL
    SELECT 
        '第三方工具',
        'mysqladmin',
        'MySQL管理工具',
        'mysqladmin processlist',
        '快速状态检查'
    UNION ALL
    SELECT 
        '第三方工具',
        'pt-query-digest',
        '慢查询日志分析',
        'pt-query-digest slow.log',
        '查询性能分析'
    UNION ALL
    SELECT 
        '第三方工具',
        'mysqltuner',
        'MySQL配置优化建议',
        'perl mysqltuner.pl',
        '配置优化和调优';
    
END //
DELIMITER ;

2. 连接问题排查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
-- 连接问题诊断和解决
DELIMITER //
CREATE PROCEDURE ConnectionTroubleshooting()
BEGIN
    SELECT '=== 连接问题诊断和解决 ===' as section;
    
    -- 1. 连接问题分类
    SELECT '=== 连接问题分类 ===' as subsection;
    
    SELECT 
        '问题类型' as problem_type,
        '错误信息' as error_message,
        '可能原因' as possible_causes,
        '诊断方法' as diagnosis_method,
        '解决方案' as solution
    UNION ALL
    SELECT 
        '服务未启动',
        'Can not connect to MySQL server',
        'MySQL服务未运行',
        'systemctl status mysql',
        'systemctl start mysql'
    UNION ALL
    SELECT 
        '端口不可达',
        'Connection refused',
        '端口被防火墙阻断或未监听',
        'netstat -an | grep 3306',
        '检查防火墙和端口配置'
    UNION ALL
    SELECT 
        '认证失败',
        'Access denied for user',
        '用户名密码错误或权限不足',
        'SELECT user,host FROM mysql.user',
        '检查用户权限和密码'
    UNION ALL
    SELECT 
        '连接数耗尽',
        'Too many connections',
        '达到最大连接数限制',
        'SHOW STATUS LIKE "Threads_connected"',
        '增加max_connections或优化连接池'
    UNION ALL
    SELECT 
        '网络超时',
        'Lost connection during query',
        '网络不稳定或查询时间过长',
        '检查网络和查询性能',
        '优化网络或调整超时参数'
    UNION ALL
    SELECT 
        'DNS解析问题',
        'Host is not allowed to connect',
        'DNS解析失败或主机名配置错误',
        'nslookup hostname',
        '使用IP地址或修复DNS配置';
    
    -- 2. 连接状态检查
    SELECT '=== 连接状态检查 ===' as subsection;
    
    -- 当前连接统计
    SELECT 
        '连接指标' as connection_metric,
        '当前值' as current_value,
        '说明' as description
    UNION ALL
    SELECT 
        '当前连接数',
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected'),
        '当前活跃连接数量'
    UNION ALL
    SELECT 
        '最大连接数',
        @@max_connections,
        '系统允许的最大连接数'
    UNION ALL
    SELECT 
        '连接使用率',
        CONCAT(ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected') * 100.0 / @@max_connections, 2), '%'),
        '连接池使用百分比'
    UNION ALL
    SELECT 
        '历史最大连接数',
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Max_used_connections'),
        '服务启动以来的最大连接数'
    UNION ALL
    SELECT 
        '拒绝连接次数',
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Connection_errors_max_connections'),
        '因连接数超限被拒绝的次数';
    
    -- 3. 连接详细信息
    SELECT '=== 当前连接详细信息 ===' as subsection;
    
    SELECT 
        ID as '连接ID',
        USER as '用户',
        HOST as '来源主机',
        DB as '数据库',
        COMMAND as '命令',
        TIME as '执行时间(秒)',
        STATE as '状态',
        LEFT(INFO, 50) as '查询信息'
    FROM information_schema.PROCESSLIST
    WHERE COMMAND != 'Sleep'
    ORDER BY TIME DESC
    LIMIT 10;
    
    -- 4. 连接问题解决方案
    SELECT '=== 连接问题解决方案 ===' as subsection;
    
    SELECT 
        '解决策略' as solution_strategy,
        '具体措施' as specific_measures,
        '配置参数' as configuration_parameters,
        '注意事项' as precautions
    UNION ALL
    SELECT 
        '增加连接数',
        '调整max_connections参数',
        'SET GLOBAL max_connections = 500',
        '需要考虑内存使用量'
    UNION ALL
    SELECT 
        '优化连接池',
        '合理配置应用连接池',
        '设置合适的池大小和超时',
        '避免连接泄露'
    UNION ALL
    SELECT 
        '清理僵尸连接',
        '定期清理长时间空闲连接',
        'SET GLOBAL wait_timeout = 300',
        '不要设置过小影响正常业务'
    UNION ALL
    SELECT 
        '网络优化',
        '优化网络配置和防火墙',
        '调整TCP参数',
        '确保网络稳定性'
    UNION ALL
    SELECT 
        '权限管理',
        '正确配置用户权限',
        'GRANT SELECT ON db.* TO user@host',
        '遵循最小权限原则';
    
END //
DELIMITER ;

3. 性能问题诊断

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
-- 性能问题诊断和优化
DELIMITER //
CREATE PROCEDURE PerformanceTroubleshooting()
BEGIN
    SELECT '=== 性能问题诊断和优化 ===' as section;
    
    -- 1. 性能问题分类
    SELECT '=== 性能问题分类 ===' as subsection;
    
    SELECT 
        '性能问题类型' as performance_issue_type,
        '主要症状' as main_symptoms,
        '常见原因' as common_causes,
        '诊断重点' as diagnosis_focus,
        '优化方向' as optimization_direction
    UNION ALL
    SELECT 
        '查询响应慢',
        '单个查询执行时间长',
        '缺少索引、查询不优化、数据量大',
        '执行计划分析、索引使用',
        '索引优化、查询重写'
    UNION ALL
    SELECT 
        '并发性能差',
        '高并发时响应时间增加',
        '锁竞争、资源争用、配置不当',
        '锁等待分析、资源使用',
        '锁优化、参数调整'
    UNION ALL
    SELECT 
        '系统负载高',
        'CPU、内存、IO使用率高',
        '硬件不足、配置不合理',
        '系统资源监控',
        '硬件升级、参数优化'
    UNION ALL
    SELECT 
        '吞吐量低',
        '单位时间处理请求数少',
        '瓶颈限制、架构问题',
        '瓶颈识别、架构分析',
        '架构优化、扩展方案'
    UNION ALL
    SELECT 
        '间歇性性能问题',
        '性能时好时坏',
        '缓存失效、垃圾回收、网络抖动',
        '性能趋势分析',
        '稳定性优化';
    
    -- 2. 慢查询分析
    SELECT '=== 慢查询分析 ===' as subsection;
    
    -- 创建慢查询示例数据
    CREATE TEMPORARY TABLE slow_query_examples (
        query_id INT AUTO_INCREMENT PRIMARY KEY,
        query_pattern TEXT,
        avg_execution_time DECIMAL(8,3),
        execution_count INT,
        total_time DECIMAL(10,2),
        rows_examined BIGINT,
        rows_sent INT,
        optimization_suggestion TEXT
    );
    
    INSERT INTO slow_query_examples (query_pattern, avg_execution_time, execution_count, total_time, rows_examined, rows_sent, optimization_suggestion) VALUES
    ('SELECT * FROM orders WHERE customer_id = ? AND order_date BETWEEN ? AND ?', 2.456, 1250, 3070.0, 125000, 850, '在(customer_id, order_date)上创建复合索引'),
    ('SELECT COUNT(*) FROM products WHERE category_id IN (?, ?, ?)', 5.234, 890, 4658.3, 890000, 1, '优化IN查询，考虑使用EXISTS或JOIN'),
    ('UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?', 1.123, 2340, 2627.8, 2340, 0, '确保product_id上有索引，考虑批量更新'),
    ('SELECT u.*, p.profile_data FROM users u LEFT JOIN profiles p ON u.id = p.user_id WHERE u.email LIKE ?', 3.789, 456, 1727.8, 456000, 123, '避免LIKE前缀通配符，考虑全文索引'),
    ('SELECT * FROM logs WHERE created_at > ? ORDER BY created_at DESC LIMIT 100', 0.876, 5670, 4966.9, 567000, 100, '在created_at上创建索引，考虑分区表');
    
    SELECT 
        query_pattern as '查询模式',
        CONCAT(avg_execution_time, 's') as '平均执行时间',
        execution_count as '执行次数',
        CONCAT(total_time, 's') as '总耗时',
        rows_examined as '扫描行数',
        rows_sent as '返回行数',
        optimization_suggestion as '优化建议'
    FROM slow_query_examples
    ORDER BY total_time DESC;
    
    -- 3. 锁等待分析
    SELECT '=== 锁等待分析 ===' as subsection;
    
    SELECT 
        '锁类型' as lock_type,
        '产生原因' as cause,
        '影响' as impact,
        '检查方法' as check_method,
        '解决方案' as solution
    UNION ALL
    SELECT 
        '表级锁',
        'ALTER TABLE、LOCK TABLES等操作',
        '阻塞所有对表的访问',
        'SHOW PROCESSLIST查看Waiting for table metadata lock',
        '避免长时间DDL操作，使用在线DDL'
    UNION ALL
    SELECT 
        '行级锁',
        '事务中的UPDATE、DELETE操作',
        '阻塞对特定行的访问',
        'SELECT * FROM performance_schema.data_locks',
        '缩短事务时间，优化索引'
    UNION ALL
    SELECT 
        '间隙锁',
        'RR隔离级别下的范围查询',
        '阻塞插入操作',
        '分析事务隔离级别和查询模式',
        '调整隔离级别或优化查询'
    UNION ALL
    SELECT 
        '死锁',
        '多个事务相互等待对方释放锁',
        '事务回滚，影响业务',
        'SHOW ENGINE INNODB STATUS查看死锁信息',
        '统一锁顺序，缩短事务时间';
    
    -- 4. 系统资源分析
    SELECT '=== 系统资源分析 ===' as subsection;
    
    SELECT 
        '资源类型' as resource_type,
        '监控指标' as monitoring_metrics,
        '正常范围' as normal_range,
        '异常表现' as abnormal_signs,
        '优化措施' as optimization_measures
    UNION ALL
    SELECT 
        'CPU使用率',
        'CPU利用率、负载平均值',
        '< 80%',
        '持续高CPU使用率',
        '查询优化、索引优化、硬件升级'
    UNION ALL
    SELECT 
        '内存使用',
        '内存使用率、缓冲池命中率',
        '缓冲池命中率 > 99%',
        '频繁的物理IO',
        '增加内存、优化缓冲池配置'
    UNION ALL
    SELECT 
        '磁盘IO',
        'IOPS、IO等待时间',
        'IO等待 < 10%',
        '高IO等待、磁盘队列长',
        '使用SSD、优化查询、分离读写'
    UNION ALL
    SELECT 
        '网络带宽',
        '网络吞吐量、连接数',
        '< 80%带宽使用',
        '网络拥塞、连接超时',
        '网络优化、负载均衡';
    
END //
DELIMITER ;

4. 数据恢复和修复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
-- 数据恢复和修复指南
DELIMITER //
CREATE PROCEDURE DataRecoveryAndRepair()
BEGIN
    SELECT '=== 数据恢复和修复指南 ===' as section;
    
    -- 1. 数据丢失场景分类
    SELECT '=== 数据丢失场景分类 ===' as subsection;
    
    SELECT 
        '丢失场景' as loss_scenario,
        '丢失原因' as loss_cause,
        '影响范围' as impact_scope,
        '恢复难度' as recovery_difficulty,
        '预防措施' as prevention_measures
    UNION ALL
    SELECT 
        '误删除数据',
        '错误的DELETE或DROP操作',
        '部分或全部数据',
        '中等',
        '定期备份、权限控制、操作审计'
    UNION ALL
    SELECT 
        '硬件故障',
        '磁盘损坏、服务器故障',
        '整个数据库',
        '高',
        'RAID配置、硬件监控、异地备份'
    UNION ALL
    SELECT 
        '软件故障',
        'MySQL崩溃、操作系统故障',
        '可能导致数据不一致',
        '中等',
        '定期备份、事务日志、集群部署'
    UNION ALL
    SELECT 
        '人为错误',
        '配置错误、操作失误',
        '配置或数据损坏',
        '低到高',
        '操作规范、权限管理、变更控制'
    UNION ALL
    SELECT 
        '恶意攻击',
        '数据库被攻击或感染',
        '数据被篡改或删除',
        '高',
        '安全加固、访问控制、入侵检测'
    UNION ALL
    SELECT 
        '自然灾害',
        '火灾、地震等不可抗力',
        '整个数据中心',
        '极高',
        '异地备份、灾备中心、云备份';
    
    -- 2. 恢复策略选择
    SELECT '=== 恢复策略选择 ===' as subsection;
    
    SELECT 
        '恢复方法' as recovery_method,
        '适用场景' as applicable_scenario,
        '恢复时间' as recovery_time,
        '数据完整性' as data_integrity,
        '操作复杂度' as operation_complexity
    UNION ALL
    SELECT 
        '完整备份恢复',
        '整个数据库需要恢复',
        '几小时到几天',
        '恢复到备份时间点',
        '简单'
    UNION ALL
    SELECT 
        '增量备份恢复',
        '需要恢复到最近时间点',
        '中等时间',
        '最小数据丢失',
        '中等'
    UNION ALL
    SELECT 
        '二进制日志恢复',
        '基于时间点的精确恢复',
        '较长时间',
        '精确到指定时间',
        '复杂'
    UNION ALL
    SELECT 
        '表级恢复',
        '只需要恢复特定表',
        '较短时间',
        '部分数据恢复',
        '中等'
    UNION ALL
    SELECT 
        '从库提升',
        '主库故障，从库可用',
        '几分钟',
        '可能有少量延迟',
        '简单'
    UNION ALL
    SELECT 
        '第三方工具恢复',
        '复杂的数据恢复需求',
        '不确定',
        '取决于工具能力',
        '复杂';
    
    -- 3. 表修复方法
    SELECT '=== 表修复方法 ===' as subsection;
    
    SELECT 
        '修复方法' as repair_method,
        '适用引擎' as applicable_engine,
        '修复命令' as repair_command,
        '修复效果' as repair_effect,
        '注意事项' as precautions
    UNION ALL
    SELECT 
        'CHECK TABLE',
        'MyISAM, InnoDB',
        'CHECK TABLE table_name',
        '检查表完整性',
        '只检查不修复'
    UNION ALL
    SELECT 
        'REPAIR TABLE',
        'MyISAM, ARCHIVE',
        'REPAIR TABLE table_name',
        '修复表结构和数据',
        'InnoDB不支持'
    UNION ALL
    SELECT 
        'OPTIMIZE TABLE',
        'MyISAM, InnoDB',
        'OPTIMIZE TABLE table_name',
        '优化表结构，回收空间',
        '会锁表，选择维护窗口'
    UNION ALL
    SELECT 
        'ALTER TABLE',
        'InnoDB',
        'ALTER TABLE table_name ENGINE=InnoDB',
        '重建表结构',
        '耗时较长，需要足够空间'
    UNION ALL
    SELECT 
        'mysqldump重建',
        '所有引擎',
        'mysqldump + DROP + CREATE',
        '完全重建表',
        '需要停机，数据量大时耗时长'
    UNION ALL
    SELECT 
        'mysqlcheck',
        '所有引擎',
        'mysqlcheck --repair database',
        '批量检查和修复',
        '可以在线执行';
    
    -- 4. 恢复操作步骤
    SELECT '=== 恢复操作步骤 ===' as subsection;
    
    SELECT 
        '恢复阶段' as recovery_phase,
        '主要任务' as main_tasks,
        '关键操作' as key_operations,
        '验证方法' as verification_method
    UNION ALL
    SELECT 
        '1. 评估损失',
        '确定数据丢失范围和影响',
        '检查错误日志、用户反馈',
        '明确恢复目标和范围'
    UNION ALL
    SELECT 
        '2. 停止服务',
        '防止进一步的数据损坏',
        '停止MySQL服务和应用',
        '确认服务已停止'
    UNION ALL
    SELECT 
        '3. 备份现状',
        '保存当前状态用于分析',
        '备份数据目录和配置文件',
        '确保备份完整'
    UNION ALL
    SELECT 
        '4. 选择恢复方法',
        '根据情况选择最佳恢复策略',
        '评估各种恢复方案',
        '确认恢复方案可行性'
    UNION ALL
    SELECT 
        '5. 执行恢复',
        '按照选定方案执行恢复',
        '恢复数据和配置',
        '监控恢复进度'
    UNION ALL
    SELECT 
        '6. 验证数据',
        '检查恢复后的数据完整性',
        '数据一致性检查、功能测试',
        '确认数据正确性'
    UNION ALL
    SELECT 
        '7. 恢复服务',
        '重新启动服务并监控',
        '启动MySQL和应用服务',
        '确认服务正常运行'
    UNION ALL
    SELECT 
        '8. 总结改进',
        '分析故障原因并改进',
        '更新备份策略、加强监控',
        '预防类似问题再次发生';
    
END //
DELIMITER ;

-- 执行所有故障排查演示
DELIMITER //
CREATE PROCEDURE ExecuteAllTroubleshootingDemos()
BEGIN
    SELECT '=== MySQL故障排查实战演示开始 ===' as demo_start;
    
    -- 1. 故障分类和诊断框架
    CALL TroubleshootingFramework();
    
    -- 2. 故障排查工具箱
    CALL TroubleshootingToolbox();
    
    -- 3. 连接问题排查
    CALL ConnectionTroubleshooting();
    
    -- 4. 性能问题诊断
    CALL PerformanceTroubleshooting();
    
    -- 5. 数据恢复和修复
    CALL DataRecoveryAndRepair();
    
    SELECT '=== 所有演示完成 ===' as demo_end;
    
END //
DELIMITER ;

-- 执行完整演示
CALL ExecuteAllTroubleshootingDemos();

实战故障排查案例

案例1：连接数耗尽问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#!/bin/bash
# 连接数耗尽问题排查脚本

echo "=== 连接数耗尽问题排查 ==="

# 1. 检查当前连接状态
echo "1. 检查当前连接状态"
mysql -e "SHOW STATUS LIKE 'Threads_connected';"
mysql -e "SHOW VARIABLES LIKE 'max_connections';"

# 2. 查看连接详情
echo "2. 查看连接详情"
mysql -e "SHOW PROCESSLIST;" | head -20

# 3. 分析连接来源
echo "3. 分析连接来源"
mysql -e "SELECT SUBSTRING_INDEX(host, ':', 1) as client_ip, COUNT(*) as connection_count 
          FROM information_schema.processlist 
          GROUP BY client_ip 
          ORDER BY connection_count DESC;"

# 4. 检查长时间运行的查询
echo "4. 检查长时间运行的查询"
mysql -e "SELECT id, user, host, db, command, time, state, info 
          FROM information_schema.processlist 
          WHERE time > 300 
          ORDER BY time DESC;"

# 5. 解决方案建议
echo "5. 解决方案建议"
echo "- 增加max_connections参数"
echo "- 优化应用连接池配置"
echo "- 清理长时间空闲连接"
echo "- 检查是否有连接泄露"

案例2：慢查询问题分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#!/bin/bash
# 慢查询问题分析脚本

echo "=== 慢查询问题分析 ==="

# 1. 检查慢查询配置
echo "1. 检查慢查询配置"
mysql -e "SHOW VARIABLES LIKE 'slow_query%';"
mysql -e "SHOW VARIABLES LIKE 'long_query_time';"

# 2. 分析慢查询统计
echo "2. 分析慢查询统计"
mysql -e "SHOW STATUS LIKE 'Slow_queries';"

# 3. 查看当前正在执行的慢查询
echo "3. 查看当前正在执行的慢查询"
mysql -e "SELECT id, user, host, db, time, state, 
          LEFT(info, 100) as query_snippet
          FROM information_schema.processlist 
          WHERE time > 10 AND command = 'Query'
          ORDER BY time DESC;"

# 4. 使用pt-query-digest分析慢查询日志
echo "4. 分析慢查询日志（需要pt-query-digest工具）"
if command -v pt-query-digest &> /dev/null; then
    pt-query-digest /var/log/mysql/slow.log | head -50
else
    echo "pt-query-digest工具未安装，请安装percona-toolkit"
fi

# 5. 优化建议
echo "5. 优化建议"
echo "- 分析执行计划，添加必要索引"
echo "- 重写复杂查询，避免全表扫描"
echo "- 考虑分页查询，避免大结果集"
echo "- 优化WHERE条件，提高选择性"

预防性维护

监控和告警体系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
#!/bin/bash
# MySQL健康检查脚本

MYSQL_USER="monitor"
MYSQL_PASSWORD="monitor_pass"
MYSQL_HOST="localhost"
LOG_FILE="/var/log/mysql_health_check.log"
ALERT_EMAIL="dba@company.com"

# 记录日志函数
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

# 发送告警函数
send_alert() {
    local subject="$1"
    local message="$2"
    echo "$message" | mail -s "$subject" $ALERT_EMAIL
    log_message "ALERT: $subject"
}

# 检查MySQL服务状态
check_mysql_service() {
    if ! systemctl is-active --quiet mysql; then
        send_alert "MySQL服务异常" "MySQL服务未运行"
        return 1
    fi
    log_message "INFO: MySQL服务运行正常"
    return 0
}

# 检查连接数
check_connections() {
    local current_conn=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')
    local max_conn=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2 {print $2}')
    local conn_usage=$(echo "scale=2; $current_conn * 100 / $max_conn" | bc)
    
    if (( $(echo "$conn_usage > 80" | bc -l) )); then
        send_alert "连接数使用率过高" "当前连接使用率: ${conn_usage}% (${current_conn}/${max_conn})"
    fi
    
    log_message "INFO: 连接使用率: ${conn_usage}%"
}

# 检查慢查询
check_slow_queries() {
    local slow_queries=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2 {print $2}')
    
    # 检查是否有新的慢查询（这里简化处理）
    if [ -f /tmp/last_slow_queries ]; then
        local last_slow_queries=$(cat /tmp/last_slow_queries)
        local new_slow_queries=$((slow_queries - last_slow_queries))
        if [ $new_slow_queries -gt 10 ]; then
            send_alert "慢查询增加异常" "新增慢查询: $new_slow_queries 个"
        fi
    fi
    
    echo $slow_queries > /tmp/last_slow_queries
    log_message "INFO: 当前慢查询总数: $slow_queries"
}

# 检查磁盘空间
check_disk_space() {
    local mysql_data_dir=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW VARIABLES LIKE 'datadir';" | awk 'NR==2 {print $2}')
    local disk_usage=$(df -h $mysql_data_dir | awk 'NR==2 {print $5}' | sed 's/%//')
    
    if [ $disk_usage -gt 85 ]; then
        send_alert "磁盘空间不足" "MySQL数据目录磁盘使用率: ${disk_usage}%"
    fi
    
    log_message "INFO: 磁盘使用率: ${disk_usage}%"
}

# 主函数
main() {
    log_message "INFO: 开始MySQL健康检查"
    
    if check_mysql_service; then
        check_connections
        check_slow_queries
        check_disk_space
    fi
    
    log_message "INFO: MySQL健康检查完成"
}

# 执行健康检查
main

最佳实践总结

核心要点

  1. 系统性思维：故障排查需要从系统、网络、应用等多个层面综合分析
  2. 工具熟练使用：掌握各种诊断工具的使用方法和输出解读
  3. 日志分析能力：能够从各种日志中快速定位问题根因
  4. 预防性维护：建立完善的监控和预防机制，防患于未然
  5. 经验积累：建立故障知识库，积累排查经验

排查流程

  1. 快速响应：接到故障报告后快速响应，评估影响范围
  2. 信息收集：系统性地收集相关信息和日志
  3. 问题定位：基于症状和经验快速缩小问题范围
  4. 深入分析：使用专业工具深入分析根本原因
  5. 解决问题：制定和实施解决方案
  6. 验证效果：确认问题彻底解决
  7. 总结改进：总结经验，完善预防措施

预防措施

  1. 监控体系：建立全面的监控和告警机制
  2. 定期检查：定期进行健康检查和性能分析
  3. 备份策略：制定完善的备份和恢复策略
  4. 文档维护：维护详细的系统文档和操作手册
  5. 团队培训：提升团队的故障排查技能

常见误区

  1. 症状治疗：只解决表面问题，不深入分析根本原因
  2. 经验主义：过度依赖经验，不使用工具进行客观分析
  3. 单点思维：只从数据库角度分析，忽视系统整体性
  4. 缺乏预防：只在出现问题时才关注，缺乏预防性维护
  5. 文档缺失：不记录故障处理过程，无法积累经验

通过系统性的故障排查方法和持续的实践积累，可以显著提升MySQL数据库的稳定性和可靠性。关键是要建立科学的排查流程，熟练掌握各种工具，并持续完善监控和预防机制。

记住，故障排查不仅是技术技能，更是一种思维方式。优秀的DBA不仅能快速解决问题，更能从根本上预防问题的发生。

  1. MySQL故障排查与问题解决实战指南
    1. 概述
    2. 故障排查基础
      1. 1. 故障分类和诊断思路
      2. 2. 连接问题排查
      3. 3. 性能问题诊断
      4. 4. 数据恢复和修复
    3. 实战故障排查案例
      1. 案例1：连接数耗尽问题
      2. 案例2：慢查询问题分析
    4. 预防性维护
      1. 监控和告警体系
    5. 最佳实践总结
      1. 核心要点
      2. 排查流程
      3. 预防措施
      4. 常见误区
© 2025    Orion K
本站由 提供部署服务
  1. MySQL故障排查与问题解决实战指南
    1. 概述
    2. 故障排查基础
      1. 1. 故障分类和诊断思路
      2. 2. 连接问题排查
      3. 3. 性能问题诊断
      4. 4. 数据恢复和修复
    3. 实战故障排查案例
      1. 案例1：连接数耗尽问题
      2. 案例2：慢查询问题分析
    4. 预防性维护
      1. 监控和告警体系
    5. 最佳实践总结
      1. 核心要点
      2. 排查流程
      3. 预防措施
      4. 常见误区