Node_exporter监控MySQL备份

前言

使用node_exporter的 textfile_collector 采集shell脚本输出符合prom 的指标上报给 Prometheus

调整MySQL备份脚本

$ cat /data/backup/mysql/pxb_backup.sh 
#!/bin/bash

# MySQL 连接信息
MYSQL_USER="root"
MYSQL_PASSWORD="Z1xI7zXXXXX"
MYSQL_SOCKET="/tmp/mysql.sock"  # 根据实际情况修改
BACKUP_DIR="/data/backup/mysql/backup"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_NAME="full_backup_$DATE"
METRICS_FILE="/usr/local/node_exporter/textfile_collector/mysql_backup_status.prom" #备份结果输出

# 检查备份目录是否存在，如果不存在则创建
if [ ! -d "$BACKUP_DIR" ]; then
  mkdir -p "$BACKUP_DIR"
fi

# 使用 percona-xtrabackup 进行全库备份
echo "Starting MySQL full backup..."
xtrabackup --backup --user=$MYSQL_USER --password=$MYSQL_PASSWORD --socket=$MYSQL_SOCKET --target-dir=$BACKUP_DIR/$BACKUP_NAME

# 检查备份是否成功
if [ $? -eq 0 ]; then
  echo "MySQL full backup completed successfully."
  BACKUP_STATUS=0  # 0 表示成功
  BACKUP_STATUS_LABEL="success"
else
  echo "MySQL full backup failed!"
  BACKUP_STATUS=1  # 1 表示失败
  BACKUP_STATUS_LABEL="failed"
  exit 1
fi

# 压缩备份文件
echo "Compressing backup..."
tar -czf $BACKUP_DIR/$BACKUP_NAME.tar.gz -C $BACKUP_DIR $BACKUP_NAME

# 记录备份状态到指标文件
echo "mysql_backup_status{backup_type=\"full\", status=\"$BACKUP_STATUS_LABEL\"} $BACKUP_STATUS" > $METRICS_FILE

# 删除未压缩的备份目录
rm -rf $BACKUP_DIR/$BACKUP_NAME

# 保留最近 7 个备份，删除旧的备份
echo "Cleaning up old backups..."
cd $BACKUP_DIR
ls -t | grep full_backup_.*\.tar\.gz | tail -n +8 | xargs rm -f

echo "Backup and cleanup completed."

监控备份文件大小和备份创建时间

$ cat mysql_backup_metrics.sh 
#!/bin/bash

# 定义备份目录
BACKUP_DIR="/data/backup/mysql/backup"

# 定义输出文件
OUTPUT_FILE="/usr/local/node_exporter/textfile_collector/mysql_backup_file.prom"

# 清空之前的指标文件
> $OUTPUT_FILE

# 获取备份文件的大小和修改时间
for file in $(ls $BACKUP_DIR/full_backup_*.tar.gz); do
  size=$(stat -c%s "$file")
  mtime=$(stat -c%Y "$file")
  echo "backup_file_size{file=\"$file\"} $size" >> $OUTPUT_FILE
  echo "backup_file_mtime{file=\"$file\"} $mtime" >> $OUTPUT_FILE
done

调整node_exporter

$ systemctl cat node_exporter.service 
# /usr/lib/systemd/system/node_exporter.service
[Unit]
Description=Node Exporter

[Service]
WorkingDirectory=/usr/local/node_exporter
ExecStart=/usr/local/node_exporter/node_exporter --web.config.file="/usr/local/node_exporter/basic_auth.yml" --collector.textfile.directory="/usr/local/node_exporter/textfile_collector"
[Install]
WantedBy=multi-user.target