#!/bin/bash # yx-nav 数据库备份脚本 # 用法: ./backup.sh [保存天数] set -e RETENTION_DAYS=${1:-30} BACKUP_DIR="/opt/data/backups/yx-nav" TIMESTAMP=$(date +%Y%m%d_%H%M%S) DB_HOST="${DB_HOST:-110.42.189.169}" DB_PORT="${DB_PORT:-9109}" DB_NAME="${DB_NAME:-yx-nav}" DB_USER="${DB_USER:-yx-nav}" DB_PASS="${DB_PASS:-yx-nav}" mkdir -p "$BACKUP_DIR" echo "=== 开始备份 $DB_NAME ===" # 使用 mysqldump 备份(如果可用) if command -v mysqldump &>/dev/null; then mysqldump -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \ --databases "$DB_NAME" --single-transaction --routines --triggers \ | gzip > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.sql.gz" echo "✅ mysqldump 备份完成: ${DB_NAME}_${TIMESTAMP}.sql.gz" else echo "⚠️ mysqldump 不可用,尝试容器内备份..." # 通过 Docker 容器备份 docker exec yx-nav node -e " const mysql = require('mysql2/promise'); const fs = require('fs'); (async()=>{ const c = await mysql.createConnection({host:'$DB_HOST',port:$DB_PORT,user:'$DB_USER',password:'$DB_PASS',database:'$DB_NAME'}); const [tables] = await c.execute('SHOW TABLES'); const out = []; for (const t of tables) { const [rows] = await c.execute('SELECT * FROM \`' + t[0] + '\`'); out.push({table: t[0], rows}); } fs.writeFileSync('/tmp/backup_${TIMESTAMP}.json', JSON.stringify(out, null, 2)); await c.end(); })().catch(e=>{console.error(e);process.exit(1)}); " && docker cp "yx-nav:/tmp/backup_${TIMESTAMP}.json" "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.json" echo "✅ 容器内备份完成: ${DB_NAME}_${TIMESTAMP}.json" fi # 清理过期备份 find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete 2>/dev/null find "$BACKUP_DIR" -name "${DB_NAME}_*.json" -mtime +$RETENTION_DAYS -delete 2>/dev/null echo "=== 备份完成,保留 ${RETENTION_DAYS} 天 ===" echo "备份目录: $BACKUP_DIR" ls -lh "$BACKUP_DIR" 2>/dev/null | tail -5