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 errconst 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 errcallback()})} else {callback()}}let queryIndex = 0const 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 errconsole.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 errconsole.log(`表 ${tableName} 删除成功`)})})connection.end()})
}