Skip to content

Instantly share code, notes, and snippets.

@abdus
Last active October 3, 2022 19:12
Show Gist options
  • Save abdus/edd2f945ba0572f37deeb73cd322b775 to your computer and use it in GitHub Desktop.
Save abdus/edd2f945ba0572f37deeb73cd322b775 to your computer and use it in GitHub Desktop.
Takes a Back-up of MySQL database
/**
* 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 }),
});
}
});
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment