nodejs实现数据库备份、清空表、删除表

备份数据库

使用 mysqldump 库，实现数据备份

var path = require('path')
var fs = require('fs')
const mysqldump = require('mysqldump')

const backupsMysql = () => {
  const backupFolder = path.join(__dirname, 'backup')
  if (!fs.existsSync(backupFolder)) {
    fs.mkdirSync(backupFolder)
  }
  const backup_name = new Date().getTime() + '.sql'
  const backupPath = path.join(backupFolder, backup_name)

  const config = {
    connection: {
      host: 'localhost',
      user: 'root',
      password: '123456',
      database: 'you_mysql'
    },
    dumpToFile: backupPath // 设置备份文件路径
  }

  mysqldump(config)
    .then((output) => {
      fs.writeFileSync(config.dumpToFile, output.dump)
      console.log('备份成功')
    })
    .catch((error) => {
      console.error(error) // 会有个报错，但不影响
    })
}

恢复数据 

在控制台输入命令，回车输入密码再回车即可

-D 后面 test_mysql 是数据库名

mysql -uroot -p -Dtest_mysql < F:\backup\backup.sql

定时备份方法

每分钟执行一次；

const cron = require('cron');
const path = require('path')
const fs = require('fs');
const mysqldump = require('mysqldump');

const backupFolder = path.join(__dirname, 'backup')
if (!fs.existsSync(backupFolder)) {
  fs.mkdirSync(backupFolder)
}
const backup_name = new Date().getTime() + '.sql'
const backupPath = path.join(backupFolder, backup_name)

const config = {
  connection: {
      host: 'localhost',
      user: 'root',
      password: '123456',
      database: 'you_mysql'
  },
  dumpToFile: backupPath // 设置备份文件路径
}

const job = new cron.CronJob('0 * * * * *', () => {
    mysqldump(config)
      .then((output) => {
        fs.writeFile(config.dumpToFile, output.dump, (err) => {
            if (err) {
                console.error('Error writing dump to file:', err);
            } else {
                console.log('Database backup successfully saved to file');
            }
        });
      })
      .catch((error) => {
        console.error('Error dumping database:', error);
      });
});

// 手动启动定时任务
job.start();

// 在应用程序关闭时停止定时任务
process.on('exit', () => {
    job.stop();
});

如要修改执行时间，将cron表达式修改为  '0 */5 * * * *' 表示每五分钟执行一次 

清空表数据

使用 mysql 库

const mysql = require('mysql')

const clearMysqlAllTable = () => {
  // 配置数据库连接
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'you_mysql'
  })

  // 连接数据库
  connection.connect()

  // 获取所有表名称的SQL查询
  const getAllTableNamesQuery = `
  SELECT table_name 
  FROM information_schema.tables 
  WHERE table_schema = 'lr_web' 
  AND table_type = 'BASE TABLE';
`

  // 执行查询并清空每个表
  connection.query(getAllTableNamesQuery, (err, results) => {
    if (err) throw err

    const truncateTableQueries = results.map(({ table_name }) => `TRUNCATE TABLE ${table_name}`)

    // 如果你想使用DELETE FROM而不是TRUNCATE TABLE, 可以像这样生成查询:
    // const deleteFromQueries = results.map(({ table_name }) => `DELETE FROM ${table_name}`)

    // 串行执行清空表的查询
    const executeTruncate = (query, callback) => {
      if (query) {
        connection.query(query, (err) => {
          if (err) throw err
          callback()
        })
      } else {
        callback()
      }
    }

    let queryIndex = 0
    const executeNextQuery = () => {
      if (queryIndex < truncateTableQueries.length) {
        executeTruncate(truncateTableQueries[queryIndex++], executeNextQuery)
      } else {
        console.log('操作成功')
        connection.end() // 结束数据库连接
      }
    }

    executeNextQuery() // 开始清空表
  })

  connection.on('end', () => {
    console.log('数据库连接结束')
  })
}

删除所有表

使用 mysql 库

const mysql = require('mysql')

const deleteMysqlAllTable = () => {
  // 配置数据库连接
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'you_mysql'
  })

  connection.connect()

  // 执行SQL来获取所有表名
  connection.query('SHOW TABLES', (err, results) => {
    if (err) throw err
    console.log(results)

    // 遍历所有表名，逐个执行删除表的SQL语句
    results.forEach((result) => {
      const tableName = result['Tables_in_' + connection.config.database]
      const dropTableQuery = `DROP TABLE IF EXISTS ${tableName};`

      connection.query(dropTableQuery, (err, result) => {
        if (err) throw err
        console.log(`表 ${tableName} 删除成功`)
      })
    })

    connection.end()
  })
}