NodeJS: Backup MySQL database

Question

I want to backup a MySQL database. Is it possible to do it in Node.js? If not, what is the best way to do so?

Answer 1

simple way without libs:

var exec = require('child_process').exec;
var child = exec(' mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql');

Answer 2

You can use exec method as described by siavolt it is simple but not the "best way" as requested by the question.

The best and efficient way in case if you care about your system memory and CPU and performance or if you have a big database is to use spawn along with the stream, so here is the "best way" in my opinion:

var fs = require('fs');
var spawn = require('child_process').spawn;
var wstream = fs.createWriteStream('dumpfilename.sql');

var mysqldump = spawn('mysqldump', [
    '-u',
    'DB_USER',
    '-p',
    'DB_PASSWORD',
    'DB_NAME',
]);

mysqldump
    .stdout
    .pipe(wstream)
    .on('finish', function () {
        console.log('Completed')
    })
    .on('error', function (err) {
        console.log(err)
    });

If your database doesn't have a password you can remove the '-p', 'DB_PASSWORD', so the code will become:

var fs = require('fs');
var spawn = require('child_process').spawn;
var wstream = fs.createWriteStream('dumpfilename.sql');

var mysqldump = spawn('mysqldump', [
    '-u',
    'DB_USER',
    'DB_NAME',
]);

mysqldump
    .stdout
    .pipe(wstream)
    .on('finish', function () {
        console.log('Completed')
    })
    .on('error', function (err) {
        console.log(err)
    });

Answer 3

simple and powerfull way

npm install mysqldump

Run :

import mysqldump from 'mysqldump';
// or const mysqldump = require('mysqldump')
 
// dump the result straight to a file
mysqldump({
    connection: {
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'my_database',
    },
    dumpToFile: './dump.sql',
});

File Path:

write absolute path example (check into G Disk Drive)

dumpToFile:'G:\dump.sql'

or Relative path

dumpToFile:'.\dump.sql' (root of project directory)

Answer 4

You could backup mySQL Databases utilizing node-cron, I like this because it ensures that your DB backup will always run regardless of the OS that you are hosting your Node App.

npm install node-cron
npm install moment (only for file name formatting)

@app.js (the file that you serve your Node App)

const cron = require('node-cron')
const moment = require('moment')
const fs = require('fs')
const spawn = require('child_process').spawn

// You can adjust the backup frequency as you like, this case will run once a day
cron.schedule('0 0 * * *', () => {
// Use moment.js or any other way to dynamically generate file name
  const fileName = `${process.env.DB_NAME}_${moment().format('YYYY_MM_DD')}.sql`
  const wstream = fs.createWriteStream(`/Path/You/Want/To/Save/${fileName}`)
  console.log('---------------------')
  console.log('Running Database Backup Cron Job')
  const mysqldump = spawn('mysqldump', [ '-u', process.env.DB_USER, `-p${process.env.DB_PASSWORD}`, process.env.DB_NAME ])

  mysqldump
    .stdout
    .pipe(wstream)
    .on('finish', () => {
      console.log('DB Backup Completed!')
    })
    .on('error', (err) => {
      console.log(err)
    })
})

...
...
app.listen()

You could hardcode all your DB_USER & DB_PASSWORD but i find it is best practice to read from a .env file, so it can work in both Development & Production environment

ENVIRONMENT = development
LOCAL_API = http://api.url
GOD_TOKEN = godtokenauthmenow
DB_HOST = 127.0.0.1
DB_NAME = myDatabaseName
DB_USER = myUsername
DB_PASSWORD = myPassword