MySQL 常见故障排查与备份脚本

数据库出问题影响最大。这篇文章整理了 MySQL 最常见的几个故障和对应的排查思路，以及两个能直接用的运维脚本。

一、先掌握排查三件套

# 1. 服务状态
sudo systemctl status mysql

# 2. 错误日志（最重要的排障入口）
sudo tail -n 100 /var/log/mysql/error.log

# 3. 能否连上、基础健康状态
mysqladmin -uroot -p status
mysqladmin -uroot -p ping

二、故障 1：MySQL 连不上

排查顺序：

# 服务有没有起
systemctl status mysql
# 端口是否监听（默认 3306）
ss -tlnp | grep 3306
# 能不能本地连
mysql -uroot -p
# 是不是 socket 路径问题（看 my.cnf）
grep socket /etc/mysql/my.cnf

常见原因： 服务没起、端口被防火墙拦、skip-networking 被打开、socket 路径不一致、密码/账号错误。

三、故障 2：too many connections（连接数满）

# 看当前连接数
mysql -uroot -p -e "SHOW STATUS LIKE 'Threads_connected';"
# 临时调大连接数（改 my.cnf 后要重启才永久生效）
mysql -uroot -p -e "SET GLOBAL max_connections = 500;"

# 永久修改：编辑 /etc/mysql/mysql.conf.d/mysqld.cnf
# [mysqld]
# max_connections = 500

四、故障 3：Access denied（权限报错）

# 查看某个用户有哪些权限、允许哪些主机
mysql -uroot -p -e "SHOW GRANTS FOR 'wpuser'@'localhost';"

# 授权示例
mysql -uroot -p -e "GRANT ALL PRIVILEGES ON wpdb.* TO 'wpuser'@'localhost';"
mysql -uroot -p -e "FLUSH PRIVILEGES;"

常见原因： 用户不存在、主机限制不匹配（如只允许 localhost 却从别处连）、密码错误。

五、故障 4：磁盘满 / binlog 占满空间

# 看磁盘
df -h
# 看 binlog 占了多少
du -sh /var/log/mysql/
# 查看 binlog 保留策略
mysql -uroot -p -e "SHOW VARIABLES LIKE 'expire_logs_days';"

# 临时清理到指定位置
mysql -uroot -p -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"

六、慢查询定位

# 开启慢查询日志（改配置文件重启，或临时开启）
mysql -uroot -p -e "SET GLOBAL slow_query_log = ON;"
mysql -uroot -p -e "SHOW VARIABLES LIKE 'slow_query_log%';"

# 分析某条 SQL 是否走索引
EXPLAIN SELECT * FROM posts WHERE post_title='xxx';
# 重点关注 type（全表扫 ALL / 走索引 range/ref）、rows（扫描行数）

七、脚本 1：MySQL 一键健康检查

#!/bin/bash
set -euo pipefail
MYSQL_PWD='你的密码' mysqladmin -uroot status 2>/dev/null || {
    echo "[ERROR] MySQL 未响应：$(date '+%F %T')" >> /var/log/mysql_check.log
}

注意：生产环境不要明文写密码，可用 ~/.my.cnf（权限 600）或环境变量。

八、脚本 2：数据库自动备份

#!/bin/bash
set -euo pipefail

DB="wpdb"                       # 要备份的库
BACKUP_DIR="/backup/mysql"
KEEP=7                          # 保留 7 天
DATE=$(date +%F)

mkdir -p "$BACKUP_DIR"

# 单库备份（用 ~/.my.cnf 提供密码，避免命令行暴露）
mysqldump --single-transaction --quick "$DB" > "$BACKUP_DIR/${DB}-${DATE}.sql"

# 保留最近 KEEP 天的备份，删除更早的
find "$BACKUP_DIR" -name "*.sql" -mtime +"$KEEP" -exec rm -f {} \;

echo "[$(date '+%F %T')] 备份完成：$BACKUP_DIR/${DB}-${DATE}.sql"

配合 crontab：0 3 * * * /root/scripts/backup_mysql.sh