How to implement scheduled backup of MySQL in Node

Preface

Something happened some time ago that made me laugh or cry. A project deployed on the Centos server, because it needs Re-upload · Deployment, so I executed the following command:

rm -rf /*

When I pressed Enter, I found lines of code flashing through the terminal, and suddenly I felt that things were not simple. , in desperation, I quickly interrupted the terminal with ctrl c. After the interruption, I started uploading files through fpt, but found that ftp had no response. I panicked now. You won’t destroy the system!

Next I decided to restart the server, but, emmm..., it couldn’t be started! It really destroyed the system! After asking the bosses, I heard that Alibaba Cloud can be restored if snapshot exists, but I have not saved the snapshot! Just GG, it doesn’t matter if the program is gone, but the database is gone.

At this time, I realized that I needed to make a scheduled task to regularly back up the database. Combined with the previously encapsulated Email class, the backed up The database is sent to the mailbox.

Development and Deployment

Because my backend uses nodejs, I will use nodejs to write scheduled tasks here.

Installing dependencies

The node-schedule dependency is needed to execute scheduled tasks, and the child_process dependency is needed to execute the backup command.

npm i node-schedule child_process

Writing code

Create a new BackupDB.ts file in the src/command directory, and introduce dependencies in this file:

import schedule from "node-schedule";
import { spawn } from "child_process";
import fs from "fs";

Define a methodbackupDb, all backup operations are within this method:

export const backupDb = () => {}

Use timestamp in the method to define backupunique File name, and Create stream:

export const backupDb = () => {
  const dumpFileName = `${Math.round(Date.now() / 1000)}.dump.sql`;
  const writeStream = fs.createWriteStream(dumpFileName);
}

Define the backup script in the method:

export const backupDb = () => {
  const dumpFileName = `${Math.round(Date.now() / 1000)}.dump.sql`;
  const writeStream = fs.createWriteStream(dumpFileName);
  
  const dump = spawn("mysqldump",[
    "-u",
    "你的mysql账户名",
    "-p",
    "你的mysql账户密码",
    "所要备份的数据库名"
  ])
}

Next time execute the backup command:

export const backupDb = () => {
  const dumpFileName = `${Math.round(Date.now() / 1000)}.dump.sql`;
  const writeStream = fs.createWriteStream(dumpFileName);
  
  const dump = spawn("mysqldump",[
    "-u",
    "你的mysql账户名",
    "-p",
    "你的mysql账户密码",
    "所要备份的数据库名"
  ])
  schedule.scheduleJob("0 0 1 * * *", function(){
    dump.stdout.pipe(writeStream)
      .on("finish",() => {
        console.log("备份成功")
      })
      .on("error",() => {
        console.log("备份失败")
      })
  })
}

Of course, the hard-coded data here can also be controlled as function parameters. In addition, 0 0 1 * * * means Backup at 1 am every day, For the specific time format, please refer to the figure below, or the official document:

In the callback of a successful backup, call the Email class to send the backup content to EMAIL, if this is not the focus, I won’t write it for now.

Finally in the src/command/index.js file Introduce the backup method and call :

import { backupDb } from "./BackupDB";

backupDb();

pm2 deployment

You need to install it globally first pm2:

npm i pm2 -g

pm2 The deployment command format is: pm2 start [nodejs file] -- name [alias]:

pm2 start ./src/command/index.js --name backupDb

After the deployment is completed, you can view it through the pm2 ls command.

At this point, the database will be backed up at 1:00 a.m. every day and sent to email.

The above is the detailed content of How to implement scheduled backup of MySQL in Node. For more information, please follow other related articles on the PHP Chinese website!