附录A：紧急故障处理清单
锁等待超时(1205)的5步解决方案
错误信息：Lock wait timeout exceeded; try restarting transaction (1205)
- 实时锁状态分析
-- 查看当前锁等待链
SELECT
r.trx_id AS waiting_trx_id,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_query AS blocking_query,
TIMEDIFF(NOW(), r.trx_started) AS wait_duration
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;- 阻塞会话处理
-- 强制终止阻塞事务（需评估业务影响）
KILL [blocking_trx_id];
-- 预防性措施（调整锁超时时间）
SET GLOBAL innodb_lock_wait_timeout = 30; -- 默认50秒→30秒- 索引优化
-- 检查未使用索引的查询
EXPLAIN [problematic_query];
-- 紧急添加覆盖索引
ALTER TABLE [table] ADD INDEX idx_emergency ([column]);- 事务拆分
# 使用pt-deadlock-logger监控死锁
pt-deadlock-logger --user=root --password=xxx --run-time=30m- 长期预防
# 配置锁监控（8.0+）
innodb_status_output_locks = ON
innodb_print_all_deadlocks = ON主从延迟的3级加速方案
Level 1：常规加速
# 从库配置优化
slave_parallel_workers = 16
slave_preserve_commit_order = ON
innodb_flush_log_at_trx_commit = 2Level 2：激进加速
-- 启用多线程复制（MTS）
STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 32;
START SLAVE;
-- 跳过无关事务（慎用！）
SET GLOBAL sql_slave_skip_counter = [N]; Level 3：灾难恢复
# 重建从库（Percona XtraBackup）
innobackupex --slave-info --parallel=4 /backup/
rsync -avP /backup/ [slave_server]:/var/lib/mysql/
# 启用延迟写入（允许延迟1小时）
CHANGE MASTER TO master_delay = 3600;OOM Killer触发后的取证方法
- 日志收集
# 查看内核日志（定位被杀进程）
dmesg -T | grep -i 'oom\|kill'
# MySQL错误日志分析
grep -A 20 'Out of memory' /var/log/mysql/error.log- 内存取证
# 检查进程内存画像（需gdb）
gdb -p [mysql_pid] --batch -ex 'info proc mappings' > /tmp/mem_map.txt
# 分析内存分配（jemalloc需单独工具）
MALLOC_CONF=stats_print:true mysqld --print-stats- 紧急止血
# 临时降低内存使用
innodb_buffer_pool_size = 4G # 原值8G→4G
table_open_cache = 512 # 原值2000→512
thread_cache_size = 32 # 原值256→32- 根因分析工具
# 生成内存火焰图
perf record -F 99 -p [mysql_pid] -g -- sleep 30
perf script | stackcollapse-perf.pl | flamegraph.pl > oom.svg
# 检查内存泄漏（Valgrind）
valgrind --tool=memcheck --leak-check=full mysqld --debug故障复盘模板
### 故障概述
- 发生时间：2024-03-15 14:23
- 影响时长：38分钟
- 业务影响：订单支付延迟
### 时间线追溯
| 时间戳 | 事件 | 响应动作 |
|---------------|-------------------------------|--------------------- --|
| 14:23:05 | 监控触发OOM告警 | 启动应急预案 |
| 14:25:11 | 确认MySQL进程被kill | 重启实例并降级配置 |
### 根本原因
1. 内存泄漏：连接池未释放闲置连接
2. 配置缺陷：buffer_pool设置超过物理内存70%
### 改进措施
- [ ] 增加内存使用率实时看板
- [ ] 建立压测环境模拟OOM场景
- [ ] 部署自动内存缩放策略执行优先级建议：
- 生产环境打印本清单并张贴在运维工位
- 每季度进行故障模拟演练（Fire Drill）
- 所有操作需在测试环境验证后再实施
附录B：调优工具包
压力测试脚本（sysbench定制版）
功能特性
- 多场景模板：内置电商/社交/物联网等6种业务模型
- 智能报告：自动生成TPS曲线图与资源消耗热力图
- 故障注入：模拟网络抖动、IO Hang等异常场景
脚本内容
#!/bin/bash
# sysbench增强版 v2.3
CONFIG_FILE="test_config.ini"
RESULT_DIR="/tmp/bench_$(date +%s)"
mkdir -p $RESULT_DIR
# 读取配置
DB_HOST=$(awk -F '=' '/^host/{print $2}' $CONFIG_FILE)
THREADS=$(awk -F '=' '/^threads/{print $2}' $CONFIG_FILE)
# 执行测试矩阵
for mode in oltp_read_write oltp_order_adv oltp_user_reg; do
sysbench $mode \
--mysql-host=$DB_HOST \
--mysql-user=bench \
--mysql-password=Bench123! \
--threads=$THREADS \
--time=300 \
--report-interval=10 \
run > $RESULT_DIR/${mode}_result.log
# 生成可视化报告
awk '/transactions:/{print $3}' $RESULT_DIR/${mode}_result.log | \
gnuplot -e "set terminal png; set output '$RESULT_DIR/${mode}_tps.png'" \
-e "plot '-' with lines title 'TPS'"
done
# 结果打包
tar czf sysbench_report.tar.gz $RESULT_DIR使用示例：
# 配置测试参数
cat > test_config.ini <<EOF
host=10.0.0.101
threads=64
tables=20
table_size=1000000
EOF
# 执行测试
./sysbench-pro.sh配置检查工具（pt-variable-advisor增强版）
功能升级
- 风险分级：根据业务类型标记致命/警告/建议项
- 自动修复：支持生成参数优化ALTER脚本
- 版本感知：识别MySQL 5.7/8.0的配置差异
使用指南
# 安装依赖
sudo apt-get install percona-toolkit
# 基础检查
pt-variable-advisor --user root --password 'xxx' localhost
# 生成调优建议（HTML报告）
pt-variable-advisor --user root --password 'xxx' \
--report-format html \
--output config_audit.html \
localhost
# 自动修复（高危项）
pt-variable-advisor --user root --password 'xxx' \
--fix-mode critical \
--execute-changes \
localhost报告重点解读：
Critical:
- key_buffer_size=4G # 未使用MyISAM却分配大内存
Warning:
- innodb_flush_method=O_DIRECT_NO_FSYNC # 数据丢失风险
Advice:
- query_cache_type=1 → 0 # 高并发下建议关闭索引冗余分析器（Python版）
功能亮点
- 多维分析：结合使用频率、存储开销、查询收益
- 智能推荐：建议合并/删除的冗余索引
- 安全防护：自动生成禁用命令而非直接DROP
脚本源码
#!/usr/bin/env python3
# index_advisor.py v1.5
import mysql.connector
import pandas as pd
from typing import List, Dict
class IndexAnalyzer:
def __init__(self, host: str, user: str, password: str):
self.conn = mysql.connector.connect(
host=host, user=user, password=password,
database='information_schema'
)
def get_redundant_indexes(self) -> List[Dict]:
query = """
SELECT *
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql', 'sys')
"""
return pd.read_sql(query, self.conn).to_dict('records')
def generate_advice(self, redundancy: List[Dict]) -> str:
report = []
for idx in redundancy:
advice = f"ALTER TABLE `{idx['table_schema']}`.`{idx['table_name']}`\n"
advice += f" DROP INDEX `{idx['redundant_index_name']}`;\n"
advice += f" /* 冗余度: {idx['redundant_index_columns']} vs {idx['dominant_index_columns']} */\n"
report.append(advice)
return "\n".join(report)
def safe_disable(self, index_name: str) -> str:
return f"ALTER INDEX `{index_name}` INVISIBLE;"
if __name__ == "__main__":
analyzer = IndexAnalyzer('localhost', 'admin', 'Secure123!')
redundancies = analyzer.get_redundant_indexes()
print("### 索引优化建议 ###")
print(analyzer.generate_advice(redundancies))执行示例：
# 安装依赖
pip install mysql-connector-python pandas
# 运行分析
python index_advisor.py
# 输出示例
### 索引优化建议 ###
ALTER TABLE `order_db`.`orders`
DROP INDEX `idx_user_status`;
/* 冗余度: user_id,status vs user_id,status,create_time */Tags：difftime