math_cms/database_backups/backup_database.sh

#!/bin/bash

# MySQL 数据库备份脚本
# 创建日期: 2025-12-05
# 使用方法: ./backup_database.sh

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# 获取脚本所在目录
SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
PROJECT_DIR="$(dirname "$SCRIPT_DIR")"

# 备份配置
BACKUP_DIR="$SCRIPT_DIR"
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="backup_math_${TIMESTAMP}.sql"
BACKUP_PATH="$BACKUP_DIR/$BACKUP_FILE"
GZIP_BACKUP_PATH="${BACKUP_PATH}.gz"

# 日志文件
LOG_FILE="$BACKUP_DIR/backup.log"

# 记录日志函数
log() {
    local message=$1
    local color=${2:-$NC}
    echo -e "${color}[$(date '+%Y-%m-%d %H:%M:%S')] ${message}${NC}" | tee -a "$LOG_FILE"
}

# 检查是否在正确的目录
if [ ! -f "$PROJECT_DIR/.env" ]; then
    log "错误: 未找到 .env 文件，请确保在正确的项目目录中执行" $RED
    exit 1
fi

# 开始备份
log "=========================================" $GREEN
log "开始 MySQL 数据库备份" $GREEN
log "=========================================" $GREEN

# 切换到项目目录
cd "$PROJECT_DIR"

# 创建 PHP 备份脚本
PHP_SCRIPT="$BACKUP_DIR/backup_${TIMESTAMP}.php"
cat > "$PHP_SCRIPT" << 'EOF'
<?php
require_once __DIR__ . '/../vendor/autoload.php';
$app = require_once __DIR__ . '/../bootstrap/app.php';
$kernel = $app->make(Illuminate\Contracts\Console\Kernel::class);
$kernel->bootstrap();

use Illuminate\Support\Facades\DB;

try {
    // 创建备份内容
    $tables = DB::select('SHOW TABLES');
    $dump = "-- MySQL Database Backup\n";
    $dump .= "-- Generated on: " . date('Y-m-d H:i:s') . "\n";
    $dump .= "-- Database: math\n";
    $dump .= "-- FilamentAdmin Project Backup\n\n";

    $totalTables = count($tables);
    $currentTable = 0;

    foreach ($tables as $table) {
        $tableName = array_values((array)$table)[0];
        $currentTable++;

        echo "备份表 [$currentTable/$totalTables]: $tableName\n";

        // 获取建表语句
        $createTable = DB::select('SHOW CREATE TABLE ' . $tableName);
        if (isset($createTable[0])) {
            $dump .= "--\n-- Table structure for table `$tableName`\n--\n";
            $dump .= "DROP TABLE IF EXISTS `$tableName`;\n";
            $dump .= $createTable[0]->{'Create Table'} . ";\n\n";
        }

        // 获取表数据
        $dump .= "--\n-- Dumping data for table `$tableName`\n--\n";

        // 分批获取数据以避免内存问题
        $offset = 0;
        $limit = 1000;
        $hasData = false;

        do {
            $data = DB::table($tableName)->offset($offset)->limit($limit)->get();

            foreach ($data as $row) {
                $hasData = true;
                $values = [];
                $columns = [];

                foreach ((array)$row as $key => $value) {
                    if (is_numeric($key)) continue; // 跳过数字索引

                    $columns[] = "`$key`";

                    if ($value === null) {
                        $values[] = 'NULL';
                    } elseif (is_string($value)) {
                        // 处理特殊字符
                        $value = str_replace(["\n", "\r", "\t"], ["\\n", "\\r", "\\t"], $value);
                        $values[] = "'" . addslashes($value) . "'";
                    } elseif (is_bool($value)) {
                        $values[] = $value ? '1' : '0';
                    } elseif (is_float($value) || is_double($value)) {
                        $values[] = sprintf('%F', $value);
                    } else {
                        $values[] = $value;
                    }
                }

                $dump .= "INSERT INTO `$tableName` (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $values) . ");\n";
            }

            $offset += $limit;
        } while ($data->count() == $limit);

        if (!$hasData) {
            $dump .= "-- Table is empty\n";
        }

        $dump .= "\n";
    }

    // 添加备份结束标记
    $dump .= "--\n-- Backup completed successfully\n--\n";

    // 保存备份文件
    $filename = getenv('BACKUP_FILE') ?: 'backup.sql';
    file_put_contents($filename, $dump);

    echo "\n备份完成！\n";
    echo "备份文件: $filename\n";
    echo "文件大小: " . number_format(filesize($filename) / 1024, 2) . " KB\n";

} catch (Exception $e) {
    echo "\n备份失败！\n";
    echo "错误信息: " . $e->getMessage() . "\n";
    exit(1);
}
EOF

# 设置环境变量
export BACKUP_FILE="$BACKUP_PATH"

# 执行备份
log "正在执行数据库备份..." $YELLOW
if php "$PHP_SCRIPT" 2>&1; then
    # 备份成功
    log "数据库备份成功完成！" $GREEN

    # 压缩备份文件
    log "正在压缩备份文件..." $YELLOW
    if gzip -c "$BACKUP_PATH" > "$GZIP_BACKUP_PATH"; then
        log "压缩成功！" $GREEN
        log "压缩文件: $GZIP_BACKUP_PATH"
        log "原始文件: $BACKUP_PATH ($(ls -lh "$BACKUP_PATH" | awk '{print $5}'))"
        log "压缩文件: $(ls -lh "$GZIP_BACKUP_PATH" | awk '{print $5}')"
    else
        log "压缩失败！" $RED
    fi

    # 清理临时PHP文件
    rm -f "$PHP_SCRIPT"

    # 显示备份文件列表
    log "\n最近的备份文件:" $GREEN
    ls -lh "$BACKUP_DIR"/backup_math_*.sql.gz | tail -5 | awk '{print $9 " (" $5 ")"}'

    # 保留最近10个备份文件
    log "正在清理旧备份（保留最近10个）..." $YELLOW
    cd "$BACKUP_DIR"
    ls -t backup_math_*.sql.gz | tail -n +11 | xargs -r rm
    ls -t backup_math_*.sql | tail -n +11 | xargs -r rm

    log "清理完成！" $GREEN
else
    # 备份失败
    log "数据库备份失败！" $RED
    rm -f "$PHP_SCRIPT"
    exit 1
fi

log "=========================================" $GREEN
log "备份脚本执行完毕" $GREEN
log "=========================================" $GREEN

exit 0