node-mysql-backup

地址

https://www.npmjs.com/package/node-mysql-dump

https://github.com/webcaetano/mysqldump/issues/30

说明

快速进行Mysql指定表的数据备份，原本是一个老模块，但是效率实在低，在issues的专区看到了一个哥们写的，他没有提交npm估计还是在开发，所以索性提交出来

使用

``
npm install node-mysql-dump --save
`


backup.js
`
//README.md
const mysqlDump = require('./index');

mysqlDump({
host: '192.168.197.128',
user: 'zhaojun',
password: 'zhaojun',
database: 'package_v1',
tables: ['table1', 'table2'], // only these tables
//@todo where: {'players': 'id < 1000'}, // Only test players with id < 1000
//@todo ifNotExist:true, // Create table if not exist
extendedInsert: true, // use one insert for many rows
addDropTable: true,// add "DROP TABLE IF EXISTS" before "CREATE TABLE"
addLocks: true,// add lock before inserting data
disableKeys: true,//adds /!40000 ALTER TABLE table DISABLE KEYS /; before insert
dest: './data.sql' // destination file
}, function (err) {
if (err) throw err;

// data.sql file created;
})

`


`
//index.js
/**
* From Github @https://github.com/webcaetano/mysqldump/issues/30
*/

var mysql = require('mysql');
var extend = require('extend');
var fs = require('fs');
var packageJson = require('./package.json');

/**
*
* @param {object} options Options for dumping
* @param {boolean} [options.extendedInsert=true] Use multiple-row INSERT syntax
* @param {boolean} [options.addDropTable=true] Add DROP TABLE statement before each CREATE TABLE statement
* @param {boolean} [options.addLocks=true] Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
* @param {boolean} [options.disableKeys=true] For each table, surround INSERT statements with statements to disable and enable keys
* @param {string} options.host Database host to connect to (IP address or hostname)
* @param {string} options.user Database user to use when connecting to server
* @param {string} options.password Database password to use when connecting to server
* @param {string} options.database Database to Dump
*
* @param {function} callback
*/
function mysqldump(options, callback) {

function fireCallbackOnce(err) {
if (callbackFired) return;
callbackFired = true;
connection.destroy();
writeStream.end();
if (err && err.code !== 'ENOENT') {
//an error occured lets delete the already created file
fs.unlink(options.dest, callback.bind(null, err));
} else {
return callback(err);
}
}

function streamShemaDump(table) {
connection.query('SHOW CREATE TABLE ??', table, function (err, results) {
if (err) return fireCallbackOnce(err);

writeStream.write('\n\n--\n-- Table structure for table ' + connection.escapeId(table) + '\n--\n\n');
if (options.addDropTable) {
writeStream.write('DROP TABLE IF EXISTS ' + connection.escapeId(table) + ';\n');
}
var shemaDump = results[0]['Create Table'];
writeStream.write(shemaDump + ';\n');
});
}

function streamDataDump(table) {
var fields;
var insertIntoValuesString;
var insertCounter = 0;
connection.query('SELECT * FROM ??', table)
.on('error', fireCallbackOnce)
.on('fields', function (f) {
fields = f;
var columsEscaped = [];
for (var i = 0; i < fields.length; i++) {
columsEscaped[i] = connection.escapeId(fields[i].name);
}
insertIntoValuesString = 'INSERT INTO ' + connection.escapeId(table) + '(' + columsEscaped.join(', ') + ') VALUES';
})
.on('result', function (row) {
var valuesEscaped = [];
for (var i = 0; i < fields.length; i++) {
//@todo escape fancy column types correctly
valuesEscaped.push(connection.escape(row[fields[i].name]));
}
if (insertCounter === 0) {
writeStream.write('\n--\n-- Dumping data for table ' + connection.escapeId(table) + '\n--\n\n');
if (options.addLocks) {
writeStream.write('LOCK TABLES ' + connection.escapeId(table) + ' WRITE;\n');
}
if (options.disableKeys) {
writeStream.write('/!40000 ALTER TABLE ' + connection.escapeId(table) + ' DISABLE KEYS /;\n');
}
}
if (options.extendedInsert) {
if (insertCounter === 0) {
writeStream.write(insertIntoValuesString + '\n(' + valuesEscaped.join(', ') + ')');
} else {
writeStream.write(',\n (' + valuesEscaped.join(', ') + ')');
}
} else {
writeStream.write(insertIntoValuesString + ' (' + valuesEscaped.join(', ') + ');\n');
}
insertCounter++;
})
.on('end', function () {
if (insertCounter > 0) {
if (options.extendedInsert) {
writeStream.write(';\n');
}
if (options.disableKeys) {
writeStream.write('/!40000 ALTER TABLE ' + connection.escapeId(table) + ' ENABLE KEYS /;');
}
if (options.addLocks) {
writeStream.write('\nUNLOCK TABLES;\n');
}
}
});
}

function getTables(callback) {
if (options.tables) return callback(null, options.tables);

connection.query('SHOW TABLES', function (err, results, fields) {
if (err) return callback(err);

var tables = [];
for (var i = 0; i < results.length; i++) {
tables[i] = results[i][fields[0].name];
}
return callback(null, tables);
});
}


var callbackFired = false;
var defaultOptions = {
extendedInsert: true,
addDropTable: true,
addLocks: true,
disableKeys: true,
};
options = extend({}, defaultOptions, options);
callback = callback || function noop() {
};

var connection = mysql.createConnection({
host: options.host,
user: options.user,
password: options.password,
database: options.database,
});
var writeStream = fs.createWriteStream(options.dest);
writeStream.on('error', fireCallbackOnce);


//start writing to the file
writeStream.write('-- ' + packageJson.name + ' ' + packageJson.version + '\n--\n');
writeStream.write('-- Dumped on ' + (new Date()).toUTCString() + '\n--\n');
writeStream.write('-- Host: ' + options.host + ' Database: ' + options.database + '\n-- ------------------------------------------------------\n\n');

getTables(function (err, tables) {
if (err) return fireCallbackOnce(err);

//we can just add all queries at once they are executed in sequence in the order of adding
for (var i = 0; i < tables.length; i++) {
streamShemaDump(tables[i]);
streamDataDump(tables[i]);
}
connection.end(function (err) {
if (err) return fireCallbackOnce(err);

writeStream.on('close', function () {
fireCallbackOnce(null); //when the file is done writing call the success callback
});
writeStream.end();
});
});
}

module.exports = mysqldump;

``