数据库备份与恢复方案

数据库备份与恢复方案

数据库备份与恢复是任何应用系统不可或缺的核心功能。Koa2作为Node.js的轻量级框架，结合各类数据库驱动和工具，能够高效实现数据安全保障机制。

备份策略设计

定时全量备份是最基础的方式。使用Node.js的child_process模块调用系统命令执行备份操作：

const { exec } = require('child_process')
const cron = require('node-cron')

// MySQL每日全量备份
cron.schedule('0 3 * * *', () => {
  const backupCmd = `mysqldump -u root -p密码 数据库名 > /backups/full_$(date +%Y%m%d).sql`
  exec(backupCmd, (error) => {
    if (error) ctx.app.emit('error', new Error('备份失败'), ctx)
  })
})

增量备份更适合大型数据库。通过binlog实现MySQL增量备份：

mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
  --stop-datetime="2023-01-02 00:00:00" \
  /var/lib/mysql/mysql-bin.000123 > /backups/incremental_20230102.sql

存储方案选择

云存储集成示例（阿里云OSS）：

const OSS = require('ali-oss')
const fs = require('fs')

const client = new OSS({
  region: 'oss-cn-hangzhou',
  accessKeyId: 'yourKeyId',
  accessKeySecret: 'yourKeySecret',
  bucket: 'yourBucket'
})

async function uploadBackup(filePath) {
  try {
    const stream = fs.createReadStream(filePath)
    const result = await client.putStream(`backups/${Date.now()}.sql`, stream)
    console.log('上传成功:', result.url)
  } catch (err) {
    console.error('上传失败:', err)
  }
}

本地存储需考虑目录轮转策略：

const fs = require('fs-extra')
const path = require('path')

function rotateBackups(dir, maxFiles = 30) {
  fs.readdir(dir)
    .then(files => {
      if (files.length > maxFiles) {
        const oldest = files
          .map(f => ({ name: f, time: fs.statSync(path.join(dir, f)).mtime }))
          .sort((a, b) => a.time - b.time)[0]
        fs.unlink(path.join(dir, oldest.name))
      }
    })
}

恢复机制实现

完整恢复流程示例：

const { exec } = require('child_process')

app.use(async (ctx) => {
  if (ctx.path === '/restore') {
    const restoreCmd = `mysql -u root -p密码 数据库名 < /backups/latest.sql`
    exec(restoreCmd, (error, stdout, stderr) => {
      if (error) {
        ctx.status = 500
        ctx.body = { error: stderr.toString() }
      } else {
        ctx.body = { message: '恢复成功' }
      }
    })
  }
})

点时间恢复(PITR)需要结合binlog：

mysqlbinlog --start-datetime="2023-01-01 14:00:00" \
  --stop-datetime="2023-01-01 15:00:00" \
  /var/lib/mysql/mysql-bin.000123 | mysql -u root -p

自动化测试验证

使用Jest编写备份验证测试：

const { checkBackupIntegrity } = require('../utils/backup')

describe('备份完整性测试', () => {
  test('SQL文件应包含完整表结构', async () => {
    const result = await checkBackupIntegrity('/backups/test.sql')
    expect(result).toMatch(/CREATE TABLE `users`/i)
  })

  test('备份文件不应为空', async () => {
    const stats = fs.statSync('/backups/test.sql')
    expect(stats.size).toBeGreaterThan(1024)
  })
})

监控与报警

集成Prometheus监控指标：

const client = require('prom-client')

const backupGauge = new client.Gauge({
  name: 'db_backup_size_bytes',
  help: 'Database backup size in bytes',
  labelNames: ['type']
})

function updateMetrics(filePath) {
  const stats = fs.statSync(filePath)
  backupGauge.set({ type: 'full' }, stats.size)
}

异常报警通过Webhook实现：

const axios = require('axios')

async function sendAlert(message) {
  await axios.post(process.env.ALERT_WEBHOOK, {
    text: `[数据库告警] ${new Date().toISOString()} ${message}`,
    attachments: [{
      color: 'danger',
      fields: [{ title: '错误详情', value: message }]
    }]
  })
}

权限与安全控制

备份文件加密示例：

const crypto = require('crypto')
const zlib = require('zlib')

function encryptBackup(inputPath, outputPath) {
  const cipher = crypto.createCipheriv(
    'aes-256-cbc', 
    Buffer.from(process.env.ENCRYPT_KEY),
    Buffer.from(process.env.ENCRYPT_IV)
  )
  
  const input = fs.createReadStream(inputPath)
  const output = fs.createWriteStream(outputPath)
  const gzip = zlib.createGzip()
  
  input
    .pipe(gzip)
    .pipe(cipher)
    .pipe(output)
}

多数据库支持

MongoDB备份恢复示例：

const { MongoClient } = require('mongodb')

async function mongodump(uri, outputDir) {
  const client = new MongoClient(uri)
  await client.connect()
  await client.db().admin().command({
    mongodump: 1,
    out: outputDir
  })
  await client.close()
}

PostgreSQL备份命令封装：

function pgBackup(config) {
  const env = {
    ...process.env,
    PGPASSWORD: config.password
  }
  
  const args = [
    '-h', config.host,
    '-U', config.user,
    '-d', config.database,
    '-f', config.outputFile,
    '-Fc' // 自定义格式
  ]
  
  return spawn('pg_dump', args, { env })
}

灾备演练方案

编写自动化演练脚本：

const { execSync } = require('child_process')

function disasterRecoveryTest() {
  try {
    // 1. 停止主数据库
    execSync('systemctl stop mysql')
    
    // 2. 从备份恢复
    execSync('mysql -u root -p < /backups/latest.sql')
    
    // 3. 验证数据
    const output = execSync('mysql -u root -p -e "SELECT COUNT(*) FROM users"')
    if (!output.includes('1000')) {
      throw new Error('数据验证失败')
    }
    
    return true
  } catch (err) {
    console.error('灾备演练失败:', err)
    return false
  }
}