WebWork/backend/scripts/backup.js

const fs = require('fs');
const path = require('path');
const db = require('../config/database');

async function backup() {
    console.log('开始备份数据库...');
    const backupDir = path.join(__dirname, '../../database');
    const filename = `backup_${new Date().toISOString().replace(/[:.]/g, '-')}.sql`;
    const filepath = path.join(backupDir, filename);

    if (!fs.existsSync(backupDir)) {
        fs.mkdirSync(backupDir, { recursive: true });
    }

    try {
        const fileStream = fs.createWriteStream(filepath, { flags: 'a' });
        
        // 获取所有表
        const tables = await db.query('SHOW TABLES');
        const tableNames = tables.map(t => Object.values(t)[0]);

        for (const tableName of tableNames) {
            console.log(`正在备份表: ${tableName}`);
            
            // 1. 获取 CREATE TABLE 语句
            const createRows = await db.query(`SHOW CREATE TABLE ${tableName}`);
            const createTableSql = createRows[0]['Create Table'];
            
            fileStream.write(`\n\n-- Table structure for table \`${tableName}\`\n`);
            fileStream.write(`DROP TABLE IF EXISTS \`${tableName}\`;\n`);
            fileStream.write(`${createTableSql};\n`);

            // 2. 获取数据
            const rows = await db.query(`SELECT * FROM ${tableName}`);
            if (rows.length > 0) {
                fileStream.write(`\n-- Dumping data for table \`${tableName}\`\n`);
                fileStream.write(`INSERT INTO \`${tableName}\` VALUES\n`);
                
                const values = rows.map(row => {
                    const rowValues = Object.values(row).map(val => {
                        if (val === null) return 'NULL';
                        if (typeof val === 'number') return val;
                        // 转义单引号和反斜杠
                        return `'${String(val).replace(/\\/g, '\\\\').replace(/'/g, "\\'")}'`;
                    });
                    return `(${rowValues.join(', ')})`;
                });
                
                fileStream.write(values.join(',\n') + ';\n');
            }
        }

        fileStream.end();
        console.log(`备份完成! 文件保存在: ${filepath}`);
        process.exit(0);
    } catch (error) {
        console.error('备份失败:', error);
        process.exit(1);
    }
}

backup();