Takes a Back-up of MySQL database

database-backup.mjs

/**
 * Back-up Level Database and store it in a S3 bucket in Gunzip format
 * required packages:
 *    1. mysqldump
 *    2. gzip
 *    3. aws-cli
 *
 * set mysql credentials using mysql_config_editor
 *    1. mysql_config_editor set --password
 *    2. mysql_config_editor print --all (to see config option)
 *
 * also set-up aws credentials for s3 bucket
 *
 * NOTE: if you are using mysqldump version 8.0.17 or older, you will need to
 *      add the --column-statistics=0 option to the mysqldump command
 */

import fs from 'fs';
import path from 'path';
import { spawn } from 'child_process';

// format Date in yyyy-mm-dd-hh-mm-ss format and pad with 0
function formatDate(date) {
  const d = new Date(date);
  const year = d.getFullYear();
  const month = `0${d.getMonth() + 1}`.slice(-2);
  const day = `0${d.getDate()}`.slice(-2);
  const hour = `0${d.getHours()}`.slice(-2);
  const minute = `0${d.getMinutes()}`.slice(-2);
  const second = `0${d.getSeconds()}`.slice(-2);
  return `${year}/${month}/${day}/${hour}:${minute}:${second}`;
}

const DUMP_DIR = path.resolve('dumps');
const DATABASES = ['target_database_name'];
const PIPEDREAM_URL = 'https://YOUR_PIPEDREAM_WORKFLOW_URL';
const ERROR_ICON = `https://cdn0.iconfinder.com/data/icons/shift-interfaces/32/Error-512.png`;
const DISCORD_EMBED = [
  {
    author: {
      name: 'DB Backup Script',
      url: 'https://github-url',
      icon_url: 'https://get-image.com/random.png',
    },
    title: 'Database Backup Failed',
    url: 'https://level.game',
    description: 'The database backup script failed to backup the database.',
    color: '16711680',
    fields: [],
    thumbnail: { url: ERROR_ICON },
  },
];

if (!fs.existsSync(DUMP_DIR)) fs.mkdirSync(DUMP_DIR, { recursive: true });
if (!fs.lstatSync(DUMP_DIR).isDirectory()) {
  fs.unlinkSync(DUMP_DIR);
  fs.mkdirSync(DUMP_DIR, { recursive: true });
}

for (let dbName of DATABASES) {
  const fileName = path.resolve(
    DUMP_DIR,
    `${dbName}/${formatDate(new Date())}.sql.gz`
  );

  const backupDir = path.dirname(fileName);

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

  const stream = fs.createWriteStream(fileName, { flags: 'w' });
  const mysqldump = spawn('mysqldump', [
    '-u',
    'DB_USER',
    '-p' + 'DB_PASSWORD',
    '-h',
    'DB_HOST_NAME',
    dbName,
    // `--column-statistics=0`, // only required for mysqldump version 8.0.17 or older
  ]);

  const gzip = spawn('gzip', ['-c']);

  let mysqlErrorData = '';

  mysqldump.stderr.on('data', (chunk) => {
    mysqlErrorData += chunk.toString()?.includes('Warning')
      ? ''
      : chunk.toString();
  });

  mysqldump.stderr.on('close', () => {
    if (mysqlErrorData) {
      DISCORD_EMBED[0].title = `Failed to backup ${dbName}`;
      DISCORD_EMBED[0].description = '```' + mysqlErrorData + '```';
      fetch(`${PIPEDREAM_URL}`, {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ embeds: DISCORD_EMBED }),
      });
    }
  });

  mysqldump.stdout.pipe(gzip.stdin);
  gzip.stdout.pipe(stream);

  stream.on('finish', () => {
    gzip.kill();
    mysqldump.kill();

    const aws = spawn('aws', [
      's3',
      'mv',
      stream.path,
      's3://BUCKET_NAME/' + path.relative(DUMP_DIR, stream.path),
    ]);

    aws.on('close', (code) => {
      if (code !== 0) {
        fetch(`${PIPEDREAM_URL}`, {
          method: 'POST',
          body: JSON.stringify({ embeds: DISCORD_EMBED }),
        });
      }
    });
  });
}