Linux中MySQL数据库故障系统化诊断的脚本分享

作者：袖梨 2026-07-16

前言

MySQL 意外中断后的系统化诊断脚本融合四大哲学元原则

1. 本体论 - 区分现象与本质不盲从表面状态

2. 整体论 - 建立多维度时间关联定位涌现的因果链

3. 辩证法 - 为恢复方案提供明确的权衡维度

4. 无为/奥卡姆 - 引导配置精简与自愈约束

核心增强特性

1. 每段代码【单段可复制终端执行】分段粘贴即可独立采集信息

2. 所有PID/日志路径/数据目录红色高亮统一存入可复制清单文件

3. 同时兼容宿主机systemd MySQL & Docker容器MySQL

4. 自动生成预填充路径/PID的一键修复命令复制直接运行

使用: bash mysql_crash_diag.sh [输出目录]

(建议以 root / sudo 执行完整采集内核、服务、权限信息)

脚本代码

#!/bin/bash#===============================================================================# 脚本名称: mysql_crash_diag.sh# #===============================================================================set -o pipefail# -----------------------------【单段可复制终端执行】全局常量、颜色、工具函数 -----------------------------# 入参与时间戳OUT_DIR="${1:-/tmp/mysql_diag_$(date +%Y%m%d_%H%M%S)}"TIMESTAMP=$(date '+%Y-%m-%d %H:%M:%S')mkdir -p "$OUT_DIR"# 统一存放所有可复制关键路径、PID、故障标记KEY_COPY_FILE="${OUT_DIR}/key_copy_info.txt"> "$KEY_COPY_FILE"echo "===== 全部可复制关键信息汇总PID/日志/配置/数据目录 =====" > "$KEY_COPY_FILE"# 颜色定义RED='33[0;31m'GREEN='33[0;32m'YELLOW='33[1;33m'WHITE='33[1;37m'CYAN='33[0;36m'NC='33[0m'# 日志打印函数info() { echo -e "${GREEN}[INFO]${NC} $*"; echo "[正常] $*" >> "$KEY_COPY_FILE"; }warn() { echo -e "${YELLOW}[WARN]${NC} $*"; echo "[警告] $*" >> "$KEY_COPY_FILE"; }err()  { echo -e "${RED}[ERROR]${NC} $*"; echo "[严重异常] $*" >> "$KEY_COPY_FILE"; }section() { echo -e "n${YELLOW}========== $1 ==========${NC}"; echo -e "n【诊断模块】$1" >> "$KEY_COPY_FILE"; }# 核心打印高亮可复制变量自动写入清单print_copy() {    local label="$1"    local val="$2"    echo -e "${WHITE}【可复制 $label 】: ${RED}$val${NC}"    echo "$label = $val" >> "$KEY_COPY_FILE"}# 输出可直接执行的完整命令print_cmd() {    echo -e "${CYAN}[一键执行命令]${NC} $1"    echo "执行命令$1" >> "$KEY_COPY_FILE"}# MySQL 全局路径池MYSQL_LOG_PATHS=( "/var/log/mysqld.log" "/var/log/mysql/error.log" "/var/lib/mysql/*.err" )MYSQL_CNF_PATHS=( "/etc/my.cnf" "/etc/mysql/my.cnf" "/usr/local/mysql/my.cnf" )MYSQL_DATA_PATHS=( "/var/lib/mysql" "/usr/local/mysql/data" )# 全局缓存变量全程复用所有命令自动带入MYSQL_PID=""FOUND_ERR_LOG=""SELECTED_CNF=""DATA_DIR=""CRASH_TIMESTAMP=""IS_DOCKER_MYSQL=0DOCKER_MYSQL_CID=""# ----------------------------------------------------------------------------------------------------------# -----------------------------【单段可复制终端执行】0. 部署模式自动识别宿主机 / Docker MySQL -----------------------------section "0. 识别MySQL部署模式宿主机systemd / Docker容器"# 先判断宿主机本地MySQL进程MYSQL_PID=$(pgrep -x mysqld | head -1 || pgrep -x mariadbd | head -1)if [[ -n "$MYSQL_PID" ]]; then    info "检测到宿主机本地MySQL进程PID已缓存"    print_copy "MySQL主进程PID" "$MYSQL_PID"    # 自动解析数据目录    DATA_DIR=$(ps -ef | grep mysqld | grep -oP '--datadir=KS+' | head -1)    [[ -z "$DATA_DIR" ]] && DATA_DIR="/var/lib/mysql"    print_copy "MySQL数据目录" "$DATA_DIR"else    # 无本地进程检查Docker内MySQL容器    DOCKER_MYSQL_CID=$(docker ps -a --filter "name=mysql" --filter "name=mariadb" -q | head -1 2>/dev/null || true)    if [[ -n "$DOCKER_MYSQL_CID" ]]; then        IS_DOCKER_MYSQL=1        warn "未检测到宿主机MySQL识别到Docker MySQL容器"        print_copy "Docker MySQL容器CID" "$DOCKER_MYSQL_CID"        print_cmd "docker logs $DOCKER_MYSQL_CID --tail 300"        print_cmd "docker exec -it $DOCKER_MYSQL_CID cat $(mysql -sse "SELECT @@log_error;")"    else        warn "未找到宿主机MySQL进程、无MySQL容器请手动确认部署方式"        print_cmd "ps aux | grep -E mysqld|mariadb"        print_cmd "docker ps -a | grep -E mysql|mariadb"    fifiecho "[$TIMESTAMP] 诊断报告生成中全部数据存放目录: $OUT_DIR" | tee "$OUT_DIR/report.log"print_copy "诊断文件根目录" "$OUT_DIR"print_cmd "cat $KEY_COPY_FILE # 查看所有可复制PID、路径汇总"# ==========================================================================================================# 步骤1现象取证与时间锚定本体论 & 整体论【单段可复制终端执行】# ==========================================================================================================section "1. 现象取证保护现场快照内核/服务/进程"# 1.1 内核环形缓冲OOM、硬件IO崩溃底层根源dmesg > "$OUT_DIR/dmesg_full.log" 2>/dev/null || echo "无法保存dmesg手工执行: dmesg > dmesg_manual.log" | tee -a "$OUT_DIR/report.log"# 单独提取OOM与磁盘错误单独存档grep -i "oom-killer|killed process|i/o error|ro filesystem" "$OUT_DIR/dmesg_full.log" > "$OUT_DIR/dmesg_oom_io_err.log"info "内核OOM/IO错误快照保存: $OUT_DIR/dmesg_oom_io_err.log"# 1.2 systemd服务状态快照宿主机模式if [[ $IS_DOCKER_MYSQL -eq 0 ]]; then    if command -v systemctl &>/dev/null; then        systemctl status mysqld 2>/dev/null > "$OUT_DIR/mysqld_systemd.log" || systemctl status mariadb > "$OUT_DIR/mysqld_systemd.log" 2>/dev/null        journalctl -u mysqld --since "10 min ago" --no-pager > "$OUT_DIR/mysqld_journal.log" 2>/dev/null    else        service mysql status > "$OUT_DIR/mysqld_service.log" 2>/dev/null    fielse    # Docker模式抓取容器启停日志    docker logs "$DOCKER_MYSQL_CID" --tail 500 > "$OUT_DIR/docker_mysql_container_log.log" 2>/dev/nullfi# 1.3 全量进程快照ps aux > "$OUT_DIR/all_process_snapshot.log"ps aux | grep -v grep | grep -E mysqld|mariadb > "$OUT_DIR/mysql_process.log"info "MySQL进程快照已保存"# 1.4 端口占用检查常见启动失败原因ss -tlnp | grep 3306 > "$OUT_DIR/port_3306_occupy.log"if [[ -s "$OUT_DIR/port_3306_occupy.log" ]]; then    warn "检测到3306端口占用查看文件确认占用进程"    cat "$OUT_DIR/port_3306_occupy.log"fi# 1.5 自动提取崩溃时间戳因果链锚点CRASH_TIMESTAMP=""# 从systemd日志提取停机时间if [[ -f "$OUT_DIR/mysqld_systemd.log" ]]; then    CRASH_TIMESTAMP=$(grep -i "Active: inactive|stop|failed" "$OUT_DIR/mysqld_systemd.log" | head -1 | grep -oP 'd{4}-d{2}-d{2} d{2}:d{2}:d{2}')fi# 内核OOM时间兜底if [[ -z "$CRASH_TIMESTAMP" && -s "$OUT_DIR/dmesg_oom_io_err.log" ]]; then    CRASH_TIMESTAMP=$(grep -i "mysql|mariadb" "$OUT_DIR/dmesg_oom_io_err.log" | tail -1 | grep -oP '[s*d+.d+]' | head -1)fiprint_copy "推断崩溃关键时间戳" "${CRASH_TIMESTAMP:-未识别请人工核对日志时间}"echo "推断的关键异常时间点: ${CRASH_TIMESTAMP:-未自动识别请手工检查日志时间戳}" | tee -a "$OUT_DIR/report.log"# 手工检验可复制命令print_cmd "ls -lt /var/log/mysqld.log /var/log/messages"print_cmd "journalctl -u mysqld --since "$CRASH_TIMESTAMP" --no-pager"# ==========================================================================================================# 步骤2直达本质——错误日志本体分析【单段可复制终端执行】# ==========================================================================================================section "2. 扫描MySQL错误日志提取底层崩溃根源"# 自动检索真实错误日志路径for LOG in "${MYSQL_LOG_PATHS[@]}"; do    for F in $LOG; do        if [ -f "$F" ]; then            FOUND_ERR_LOG="$F"            break 2        fi    donedone# Docker模式特殊读取容器内日志if [[ $IS_DOCKER_MYSQL -eq 1 && -z "$FOUND_ERR_LOG" ]]; then    warn "宿主机未找到日志从Docker容器内抓取错误日志"    docker exec "$DOCKER_MYSQL_CID" bash -c "cat $(mysql -sse "SELECT @@log_error;")" > "$OUT_DIR/mysqld_error.log" 2>/dev/null    FOUND_ERR_LOG="$OUT_DIR/mysqld_error.log"fiif [ -z "$FOUND_ERR_LOG" ]; then    err "未找到MySQL错误日志请手动指定日志路径"    print_cmd "find / -name "*.err" 2>/dev/null"else    cp "$FOUND_ERR_LOG" "$OUT_DIR/mysqld_error.log"    print_copy "MySQL错误日志完整路径" "$FOUND_ERR_LOG"    echo "错误日志源文件: $FOUND_ERR_LOG" | tee -a "$OUT_DIR/report.log"    # 提取致命错误、崩溃标记    echo "--- 最近致命错误、崩溃警告 ---" >> "$OUT_DIR/report.log"    grep -iE "error|fatal|assert|corrupt|tablespace|disk full|oom|signal 11|signal 9|permission denied" "$OUT_DIR/mysqld_error.log" | tail -30 | tee -a "$OUT_DIR/report.log"fi# 本体论分层分析注释保留原有哲学逻辑# 1. Disk is full → 磁盘耗尽优先清理binlog# 2. InnoDB tablespace corrupted → 存储介质损坏逐级innodb_force_recovery导出数据# 3. OOM / out of memory → 进程被系统强制杀死非程序主动退出# 4. Too many open files → ulimit与my.cnf文件句柄配置不匹配# 5. Signal 11(SIGSEGV) / Signal6(ABRT) → 内存越界/数据库内部断言崩溃# ==========================================================================================================# 步骤3整体论关联——多维时间点交叉验证OOM/IO突变【单段可复制终端执行】# ==========================================================================================================section "3. 整体论交叉校验OOM内存耗尽 / 磁盘IO硬件故障"# 提取内核OOM、IO报错DMESG_OOM=$(cat "$OUT_DIR/dmesg_oom_io_err.log" | grep -i "out of memory|oom-killer" | tail -5)DMESG_IO=$(cat "$OUT_DIR/dmesg_oom_io_err.log" | grep -i "i/o error|read-only|filesystem error|blk dev" | tail -5)# OOM内存耗尽链路校验if [ -n "$DMESG_OOM" ]; then    warn "【辩证矛盾点】检测到OOM Killer杀死进程优先核对内存参数而非单纯加内存"    echo "$DMESG_OOM" | tee -a "$OUT_DIR/report.log"    echo "--- 当前宿主机内存/swap快照 ---" >> "$OUT_DIR/report.log"    free -h >> "$OUT_DIR/report.log"    # 历史内存回溯sysstat sar    if command -v sar &>/dev/null; then        echo "--- 近1小时内存历史曲线 ---" >> "$OUT_DIR/report.log"        sar -r -f /var/log/sa/sa$(date +%d) 2>/dev/null | grep -E "^[0-9]" | tail -12 >> "$OUT_DIR/report.log" || echo "当日sar数据不存在" >> "$OUT_DIR/report.log"    else        warn "未安装sysstat无历史内存数据手工命令sar -r 1 10"    fi    print_cmd "grep innodb_buffer_pool_size $SELECTED_CNF"fi# IO/只读文件系统链路校验if [ -n "$DMESG_IO" ]; then    warn "【辩证矛盾点】磁盘IO异常/文件系统只读会直接导致MySQL崩溃、写失败"    echo "$DMESG_IO" | tee -a "$OUT_DIR/report.log"    echo "--- 当前挂载列表检查ro只读标记 ---" >> "$OUT_DIR/report.log"    mount | grep -E "$DATA_DIR|/ " >> "$OUT_DIR/report.log"    print_cmd "mount -o remount,rw $DATA_DIR挂载盘"    print_cmd "fsck -n /dev/磁盘设备 # 停机后磁盘校验"fi# 整体论核心逻辑对齐dmesg、错误日志、sar三者时间戳识别连锁故障# 示例因果链内存耗尽OOM kill mysqld → 大量事务回滚 → binlog持续膨胀 → 磁盘100%占满# 单一日志无法识别完整因果必须多维度时间交叉比对# ==========================================================================================================# 步骤4奥卡姆剃刀资源限制检查磁盘、inode、文件句柄、binlog【单段可复制终端执行】# ==========================================================================================================section "4. 资源硬限制排查磁盘空间、inode、文件句柄、binlog堆积"echo "--- 全局磁盘使用率系统盘+MySQL数据盘+临时目录 ---" >> "$OUT_DIR/report.log"df -h / "$DATA_DIR" /tmp 2>/dev/null | tee -a "$OUT_DIR/report.log"echo "--- inode耗尽检查小文件过多隐形崩溃 ---" >> "$OUT_DIR/report.log"df -i / "$DATA_DIR" /tmp 2>/dev/null | tee -a "$OUT_DIR/report.log"# binlog堆积检测磁盘占满头号诱因if [ -d "$DATA_DIR" ]; then    BINLOG_SIZE=$(du -sh "$DATA_DIR/mysql-bin."* 2>/dev/null | head -1)    [ -n "$BINLOG_SIZE" ] && print_copy "Binlog总占用大小" "$BINLOG_SIZE" && echo "Binlog文件占用: $BINLOG_SIZE" | tee -a "$OUT_DIR/report.log"fi# 错误日志膨胀检查if [ -f "$FOUND_ERR_LOG" ]; then    ERR_LOG_SIZE=$(du -sh "$FOUND_ERR_LOG" 2>/dev/null | awk '{print $1}')    print_copy "错误日志文件大小" "$ERR_LOG_SIZE"    echo "错误日志大小: $ERR_LOG_SIZE (GB级日志需配置logrotate自动切割)" | tee -a "$OUT_DIR/report.log"fi# 文件句柄限制too many open files崩溃根源echo "--- MySQL进程文件句柄硬限制 ---" >> "$OUT_DIR/report.log"if [ -n "$MYSQL_PID" ]; then    cat "/proc/$MYSQL_PID/limits" | grep "open files" | tee -a "$OUT_DIR/report.log"    print_cmd "lsof -p $MYSQL_PID | wc -l # 统计当前打开文件总数"else    warn "无运行MySQL进程无法读取进程级fd限制"fiecho "系统全局open file限制: $(ulimit -n)" >> "$OUT_DIR/report.log"print_cmd "grep open_files_limit $SELECTED_CNF"# 奥卡姆剃刀无为优化思路# 1. 配置binlog_expire_logs_seconds自动清理binlog避免人工频繁清理# 2. logrotate自动切割错误日志防止日志无限膨胀占满磁盘# 3. 统一调高系统与MySQL两层open_files_limit消除句柄瓶颈# ==========================================================================================================# 步骤5配置参数本体审查内存、InnoDB核心参数校验【单段可复制终端执行】# ==========================================================================================================section "5. MySQL核心配置文件审查高危参数风险校验"# 自动检索配置文件for CNF in "${MYSQL_CNF_PATHS[@]}"; do    if [ -f "$CNF" ]; then        SELECTED_CNF="$CNF"        cp "$CNF" "$OUT_DIR/my.cnf_backup"        break    fidoneif [ -f "$SELECTED_CNF" ]; then    print_copy "MySQL配置文件路径" "$SELECTED_CNF"    echo "从 $SELECTED_CNF 提取风险核心参数:" | tee -a "$OUT_DIR/report.log"    grep -E "innodb_buffer_pool_size|innodb_log_file_size|innodb_flush_log_at_trx_commit|max_connections|open_files_limit|binlog_expire|expire_logs_days|core-file" "$SELECTED_CNF" | tee -a "$OUT_DIR/report.log"    # 本体论风险校验buffer_pool超过物理内存70%极易触发OOM    TOTAL_MEM=$(free -b | awk '/Mem:/ {print $2}')    BUFF_POOL_RAW=$(grep innodb_buffer_pool_size "$SELECTED_CNF" | grep -oP 'd+' | head -1)    if [ -n "$TOTAL_MEM" ] && [ -n "$BUFF_POOL_RAW" ]; then        if [ "$BUFF_POOL_RAW" -gt $((TOTAL_MEM * 70 / 100)) ]; then            err "高危风险innodb_buffer_pool_size 超过物理内存70%OOM崩溃概率极高建议下调" | tee -a "$OUT_DIR/report.log"        fi    fielse    warn "未找到my.cnf配置文件请手动查找配置"    print_cmd "find /etc /usr/local -name my.cnf 2>/dev/null"fi# 补充SELinux权限校验容器/宿主机通用权限崩溃根源section "5.1 SELinux权限审计permission denied崩溃排查"ausearch -m avc -ts recent | grep -E mysql|mariadb > "$OUT_DIR/selinux_avc.log" 2>/dev/nullif [[ -s "$OUT_DIR/selinux_avc.log" ]]; then    warn "检测到SELinux拦截MySQL文件读写会导致启动失败/写入崩溃"    cat "$OUT_DIR/selinux_avc.log"    print_cmd "chcon -R -t mysqld_db_t $DATA_DIR"    print_cmd "setsebool -P mysqld_disable_trans 1"fi# ==========================================================================================================# 步骤6辩证法恢复方案、无为长期预防、一键可复制修复命令【单段可复制终端执行】# ==========================================================================================================section "6. 辩证恢复决策清单 + 长期无为预防方案"echo "===== 故障根因匹配恢复操作全部命令预填充路径/PID直接复制运行 =====" | tee -a "$OUT_DIR/report.log"# 分场景自动匹配修复命令if grep -qi "out of memory|oom-killer" "$OUT_DIR/dmesg_oom_io_err.log"; then    echo -e "n【场景1OOM内存耗尽被系统杀死】" | tee -a "$OUT_DIR/report.log"    print_cmd "sed -i 's/innodb_buffer_pool_size=.*/innodb_buffer_pool_size=4G/' $SELECTED_CNF # 根据物理内存调整"    print_cmd "echo 'vm.overcommit_memory=1' >> /etc/sysctl.conf && sysctl -p"    print_cmd "systemctl restart mysqld"    echo "短期缓解降低buffer_pool长期无为方案cgroups限制MySQL内存、增加物理内存/swap" | tee -a "$OUT_DIR/report.log"elif grep -qi "disk full" "$FOUND_ERR_LOG" 2>/dev/null; then    echo -e "n【场景2磁盘空间耗尽】" | tee -a "$OUT_DIR/report.log"    print_cmd "mysql -uroot -p -e "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY);""    print_cmd "du -sh $DATA_DIR/* | sort -rh | head -10 # 定位大文件"    echo "应急清理binlog长期预防配置binlog自动过期、磁盘使用率监控告警" | tee -a "$OUT_DIR/report.log"elif grep -qi "corrupt|tablespace|assertion failed" "$OUT_DIR/mysqld_error.log"; then    echo -e "n【场景3InnoDB表空间/数据文件损坏】" | tee -a "$OUT_DIR/report.log"    print_cmd "sed -i 's/#innodb_force_recovery=.*/innodb_force_recovery=1/' $SELECTED_CNF"    print_cmd "systemctl start mysqld && mysqldump -u root -p --all-databases > full_backup.sql"    echo "辩证权衡RPO优先→逐级恢复导出数据RTO优先→直接使用备份重建实例" | tee -a "$OUT_DIR/report.log"elif grep -qi "i/o error|read-only file system" "$OUT_DIR/dmesg_oom_io_err.log"; then    echo -e "n【场景4磁盘硬件IO故障/文件系统只读】" | tee -a "$OUT_DIR/report.log"    print_cmd "mount -o remount,rw $DATA_DIR挂载分区"    print_cmd "umount /data && fsck /dev/sdXX # 停机校验磁盘"else    echo -e "n【无典型底层故障标记执行全链路补充排查】" | tee -a "$OUT_DIR/report.log"    print_cmd "ls -la $DATA_DIR # 核对目录属主mysql:mysql"    print_cmd "ss -tlnp | grep 3306 # 检查端口占用"    print_cmd "tail -n 100 $FOUND_ERR_LOG # 人工精读完整错误日志"fi# 整体论全链路扩展排查清单echo -e "n===== 全链路补充手工排查上下游关联 =====" | tee -a "$OUT_DIR/report.log"echo "1. 内核硬件层dmesg -T 查看硬盘、内存硬件报错"echo "2. 系统守护层journalctl -u mysqld 完整启停日志"echo "3. 存储层检查RAID/云盘磁盘告警、磁盘读写延迟"echo "4. 业务层批量写入、大事务、无索引慢SQL触发资源暴涨"echo "5. 集群层主从SHOW SLAVE STATUS 复制线程崩溃、延迟堆积"echo "6. 备份层核对定时备份有效性损坏场景用于数据恢复"# 最终汇总输出echo -e "n${GREEN}==========================================${NC}"info "MySQL系统化诊断全部采集完成"print_copy "完整诊断存档目录" "$OUT_DIR"print_copy "一键查看所有PID/路径清单命令" "cat $KEY_COPY_FILE"warn "故障处理优先级建议磁盘满 > OOM内存耗尽 > 数据文件损坏 > IO硬件故障 > 权限/配置错误"echo -e "${GREEN}==========================================${NC}"

知识扩展

MySQL 故障诊断脚本collect_mysql_diag.sh

这是一个用于 Linux 下 MySQL 数据库故障系统化诊断的 Shell 脚本。它能在不干扰业务的前提下快速收集关键诊断信息帮助 DBA 或系统管理员定位问题。

#!/bin/bash# ============================================================================# MySQL 故障系统化诊断脚本# 用途收集 MySQL 运行状态、配置、日志、系统资源等关键信息用于故障排查# 适用Linux 系统需有 MySQL 客户端访问权限或本地 socket# 作者DBA Team# 版本2026.07# ============================================================================# 设置输出目录DIAG_DIR="/tmp/mysql_diag_$(date +%Y%m%d_%H%M%S)"mkdir -p "$DIAG_DIR"# 定义 MySQL 连接参数建议使用 .my.cnf 或环境变量避免密码明文# 若未设置默认使用 unix socket 和当前系统用户MYSQL_CMD="mysql -S /var/lib/mysql/mysql.sock -e"# 若需远程连接请修改为# MYSQL_CMD="mysql -h 127.0.0.1 -P 3306 -u diag_user -pdiag_pass -e"# 定义日志文件DIAG_LOG="$DIAG_DIR/diagnostic.log"exec > >(tee -a "$DIAG_LOG") 2>&1echo "============================================================"echo "MySQL 诊断信息收集开始于: $(date)"echo "诊断输出目录: $DIAG_DIR"echo "============================================================"# 1. 系统基本信息echo ">>> 1. 系统基本信息"uname -acat /etc/os-release 2>/dev/null || cat /etc/redhat-release 2>/dev/nulluptimefree -hdf -hecho ""# 2. MySQL 版本及运行状态echo ">>> 2. MySQL 版本及运行状态"$MYSQL_CMD "SELECT VERSION();"$MYSQL_CMD "SHOW STATUS LIKE 'uptime';"$MYSQL_CMD "SHOW VARIABLES LIKE 'port';"$MYSQL_CMD "SHOW VARIABLES LIKE 'datadir';"echo ""# 3. MySQL 配置文件过滤注释和空行echo ">>> 3. MySQL 配置文件内容"if [ -f /etc/my.cnf ]; then    grep -v '^#' /etc/my.cnf | grep -v '^$' > "$DIAG_DIR/my.cnf"    cat "$DIAG_DIR/my.cnf"elif [ -f /etc/mysql/my.cnf ]; then    grep -v '^#' /etc/mysql/my.cnf | grep -v '^$' > "$DIAG_DIR/my.cnf"    cat "$DIAG_DIR/my.cnf"else    echo "未找到 my.cnf"fiecho ""# 4. 全局状态变量关键指标echo ">>> 4. 关键状态变量"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Threads_connected';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Threads_running';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Max_used_connections';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Slow_queries';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Questions';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Com_select';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Com_insert';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Com_update';"$MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Com_delete';"echo ""# 5. 当前进程列表过滤用户和状态echo ">>> 5. 当前进程列表前50个"$MYSQL_CMD "SHOW PROCESSLIST;" | head -n 50 > "$DIAG_DIR/processlist.txt"cat "$DIAG_DIR/processlist.txt"echo ""# 6. InnoDB 引擎状态关键信息echo ">>> 6. InnoDB 引擎状态部分"$MYSQL_CMD "SHOW ENGINE INNODB STATUSG" > "$DIAG_DIR/innodb_status.txt"# 提取关键部分锁等待、事务、I/Ogrep -A 10 "LATEST DETECTED DEADLOCK" "$DIAG_DIR/innodb_status.txt"grep -A 20 "TRANSACTIONS" "$DIAG_DIR/innodb_status.txt" | head -n 30grep -A 10 "FILE I/O" "$DIAG_DIR/innodb_status.txt"echo ""# 7. 复制状态如果启用echo ">>> 7. 复制状态"$MYSQL_CMD "SHOW SLAVE STATUSG" > "$DIAG_DIR/slave_status.txt"if [ -s "$DIAG_DIR/slave_status.txt" ]; then    grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Err" "$DIAG_DIR/slave_status.txt"else    echo "未启用复制或无法获取复制状态"fiecho ""# 8. 慢查询日志最近50行echo ">>> 8. 慢查询日志最近50行"SLOW_LOG=$(mysql -N -e "SHOW VARIABLES LIKE 'slow_query_log_file';" | awk '{print $2}')if [ -f "$SLOW_LOG" ]; then    tail -n 50 "$SLOW_LOG" > "$DIAG_DIR/slow_query.log"    cat "$DIAG_DIR/slow_query.log"else    echo "未找到慢查询日志文件请确认 slow_query_log 已开启"fiecho ""# 9. 错误日志最近100行echo ">>> 9. MySQL 错误日志最近100行"ERROR_LOG=$(mysql -N -e "SHOW VARIABLES LIKE 'log_error';" | awk '{print $2}')if [ -f "$ERROR_LOG" ]; then    tail -n 100 "$ERROR_LOG" > "$DIAG_DIR/error.log"    cat "$DIAG_DIR/error.log"else    echo "未找到错误日志文件"fiecho ""# 10. 系统资源使用情况MySQL 相关echo ">>> 10. 系统资源使用"ps aux | grep -E 'mysqld|mysql' > "$DIAG_DIR/mysql_processes.txt"cat "$DIAG_DIR/mysql_processes.txt"echo ""iostat -x 1 3 > "$DIAG_DIR/iostat.txt" 2>/dev/nullcat "$DIAG_DIR/iostat.txt"echo ""# 11. 磁盘 I/O 和文件系统信息echo ">>> 11. 数据目录磁盘使用"datadir=$(mysql -N -e "SHOW VARIABLES LIKE 'datadir';" | awk '{print $2}')if [ -d "$datadir" ]; then    du -sh "$datadir" 2>/dev/null    ls -la "$datadir" | head -n 20fiecho ""# 12. 网络连接状态MySQL 端口echo ">>> 12. MySQL 端口连接数"ss -tunap | grep -c 3306ss -tunap | grep 3306 | head -n 20echo ""# 13. 最后打包所有收集的文件echo "============================================================"echo "诊断信息收集完成输出目录: $DIAG_DIR"tar -czf "$DIAG_DIR.tar.gz" -C /tmp "$(basename "$DIAG_DIR")"echo "打包文件: $DIAG_DIR.tar.gz"echo "请将打包文件发送给 DBA 进行分析。"echo "============================================================"# 退出前提示echo "注意部分信息可能包含敏感数据请妥善保管。"exit 0

使用方法

将脚本保存为 collect_mysql_diag.sh并赋予执行权限