Database backup and recovery solution

Database Backup and Recovery Solution

Database backup and recovery are indispensable core functionalities for any application system. Koa2, as a lightweight Node.js framework, combined with various database drivers and tools, can efficiently implement data security mechanisms.

Backup Strategy Design

Scheduled full backups are the most basic approach. Use Node.js's child_process module to execute system commands for backup operations:

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

// Daily full backup for MySQL
cron.schedule('0 3 * * *', () => {
  const backupCmd = `mysqldump -u root -pPassword DatabaseName > /backups/full_$(date +%Y%m%d).sql`
  exec(backupCmd, (error) => {
    if (error) ctx.app.emit('error', new Error('Backup failed'), ctx)
  })
})

Incremental backups are more suitable for large databases. Implement MySQL incremental backups using binlog:

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

Storage Solution Selection

Cloud storage integration example (Aliyun 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('Upload successful:', result.url)
  } catch (err) {
    console.error('Upload failed:', err)
  }
}

Local storage requires a directory rotation strategy:

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))
      }
    })
}

Recovery Mechanism Implementation

Complete recovery process example:

const { exec } = require('child_process')

app.use(async (ctx) => {
  if (ctx.path === '/restore') {
    const restoreCmd = `mysql -u root -pPassword DatabaseName < /backups/latest.sql`
    exec(restoreCmd, (error, stdout, stderr) => {
      if (error) {
        ctx.status = 500
        ctx.body = { error: stderr.toString() }
      } else {
        ctx.body = { message: 'Recovery successful' }
      }
    })
  }
})

Point-in-time recovery (PITR) requires 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

Automated Testing Verification

Use Jest to write backup verification tests:

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

describe('Backup Integrity Test', () => {
  test('SQL file should contain complete table structure', async () => {
    const result = await checkBackupIntegrity('/backups/test.sql')
    expect(result).toMatch(/CREATE TABLE `users`/i)
  })

  test('Backup file should not be empty', async () => {
    const stats = fs.statSync('/backups/test.sql')
    expect(stats.size).toBeGreaterThan(1024)
  })
})

Monitoring and Alerts

Integrate Prometheus monitoring metrics:

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)
}

Exception alerts via Webhook:

const axios = require('axios')

async function sendAlert(message) {
  await axios.post(process.env.ALERT_WEBHOOK, {
    text: `[Database Alert] ${new Date().toISOString()} ${message}`,
    attachments: [{
      color: 'danger',
      fields: [{ title: 'Error Details', value: message }]
    }]
  })
}

Permissions and Security Controls

Backup file encryption example:

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)
}

Multi-Database Support

MongoDB backup and recovery example:

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 backup command encapsulation:

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' // Custom format
  ]
  
  return spawn('pg_dump', args, { env })
}

Disaster Recovery Drill Plan

Automated drill script:

const { execSync } = require('child_process')

function disasterRecoveryTest() {
  try {
    // 1. Stop primary database
    execSync('systemctl stop mysql')
    
    // 2. Restore from backup
    execSync('mysql -u root -p < /backups/latest.sql')
    
    // 3. Verify data
    const output = execSync('mysql -u root -p -e "SELECT COUNT(*) FROM users"')
    if (!output.includes('1000')) {
      throw new Error('Data verification failed')
    }
    
    return true
  } catch (err) {
    console.error('Disaster recovery drill failed:', err)
    return false
  }
}