NodeJS MySQL 转储

NodeJS MySQL Dump(NodeJS MySQL 转储)

问题描述

我尝试编写一个基本的 cron 脚本来运行和转储"一个 mysql 数据库.出于某种原因，当它成功保存文件"时，它确实创建了文件，但它是空的.如果不是保存文件，而是执行 console.log，它会打印一个空字符串.对我可能做错了什么有任何想法吗?

I've attempted to write a basic cron script to run and 'dump' a mysql database. For some reason, when it 'successfully saves the file', it does create the file, but it is empty. If instead of saving the file, I perform a console.log, it prints an empty string. Any thoughts on what I may be doing wrong?

提前致谢.

var mysql_backup = function(){

    this.backup = '';
    this.mysql = require('mysql'),

    this.init = function(){
        this.connection = this.mysql.createConnection({
            user: 'root',
            password: 'root',
            database: 'test'
        });
    }

    this.query = function(sql, callback) {
        this.connection.query(sql, function (error, results, fields) {
            if (error) {
                throw error;
            }
            if (results.length  > 0) {
                callback(results);
            }
        });
    }

    this.get_tables = function(callback){
        var me = this;
        me.query('SHOW TABLES',
            function(tables) {
                for (var table in tables){
                    me.query(
                        'SHOW CREATE TABLE ' + tables[table].Tables_in_test,
                        function(r){
                            for (var t in r) {
                                me.backup += "DROP TABLE " + r[t].Table + "\n\n";
                                me.backup += r[t]["Create Table"] + "\n\n";
                            }
                        }
                    )
                }
                me.save_backup();
            });
    }

    this.save_backup = function(){
        var fs = require('fs');
        fs.writeFile("./backup_test.txt", this.backup, function(err) {
            if(err) {
                console.log(err);
            } else {
                console.log("The file was saved!");
            }
        });
    }

};

var db = new mysql_backup;
db.init();
db.get_tables();
db.connection.destroy();

推荐答案

所写的代码甚至没有为我保存文件.好像有几个问题.不确定这是实际代码还是复制粘贴中丢失了某些内容.但是，根据您的情况:

The code as written didn't even get to a file saving for me. There seem like a few issues. Not sure if this is the actual code or some things got lost in the copy paste. However, based on what you've got:

一个很大的问题是你永远不会在你的代码中使用 connection.connect() 连接到数据库.

A big one is that you never connect to the database in your code with connection.connect().

连接后要运行的代码应该在 connection.connect() 回调中.例如

The code you want to run once connected should be inside the connection.connect() callback. e.g.

connection.connect(function (err, empty) {
    if (err)
        throw new Error ('Panic');

    // if no error, we are off to the races...
}

然而，即使您快速重构代码以将最后几行包装在 get 连接回调中，您仍然会遇到问题，因为您在进行各种 SQL 调用之前破坏了连接，因此您需要将代码移动到某种最终回调中.

However, even if you quickly refactor your code to wrap your last lines inside of that get connection callback, you'll still have problems, because you are destroying the connection before the various SQL calls are getting made, so you will want to move the code into some sort of final callback.

即使你这样做了，你仍然会有一个空文件，因为你是从你的SHOW TABLES"回调中调用 save_backup 而不是在你通过内部回调实际填充它之后你得到 CREATE TABLE 语句并填充备份属性.

Even after you do that, you'll still have an empty file, because you're calling save_backup from your 'SHOW TABLES' callback rather than after you have actually populated it via the inner callback where you get the CREATE TABLE statement and populate the backup property.

这是对您的代码进行最小程度的重写，可以实现您的意图.需要注意的重要一点是计数器"，它管理何时写入文件和关闭连接.如果是我的话，我会做出其他更改，包括:

This is the minimal rewriting of your code which will do what you are intending. An important thing to note is the "counter" which manages when to write the file and close the connection. I would make other changes if it were mine, including:

• 使用自我"而不是我"
• 使用数字 for 循环而不是 for (... in ...) 语法
• 拥有自己的回调属于 (err, stuff) 的节点约定
• 一个更实质性的变化是，我将重写它以使用 Promise，因为这样做可以让您免于因深度嵌套回调所固有的混乱而感到悲伤.我个人喜欢 Q 库，但这里有几个选项.

希望这有帮助.

var mysql_backup = function(){
    this.backup = '';
    this.mysql = require('mysql');

    this.init = function(){
        this.connection = this.mysql.createConnection({
            user     : 'root',
            password : 'root',
            database : 'test'
        });

    };

    this.query = function(sql, callback) {
        this.connection.query(sql, function (error, results, fields) {
            if (error) {
                throw error;
            }
            if (results.length  > 0) {
                callback(results);
            }
        });
    };

    this.get_tables = function(callback){
        var counter = 0;
        var me = this;
        this.query('SHOW TABLES',
            function(tables) {
                for (table in tables){
                    counter++;
                    me.query(
                        'SHOW CREATE TABLE ' + tables[table].Tables_in_mvc,
                        function(r){
                            for (t in r) {
                                me.backup += "DROP TABLE " + r[t].Table + "\n\n";
                                me.backup += r[t]["Create Table"] + "\n\n";
                            }
                            counter--;
                            if (counter === 0){
                                me.save_backup();
                                me.connection.destroy();

                            }
                        }
                    )
                }
            });
    };

    this.save_backup = function(){
        var fs = require('fs');
        fs.writeFile("./backup_test.txt", this.backup, function(err) {
            if(err) {
                console.log(err);
            } else {
                console.log("The file was saved!");
            }
        });
    }

};

var db = new mysql_backup;
db.init();
db.connection.connect(function (err){
    if (err) console.log(err);
    db.get_tables(function(x){;});

});

更新:如果你很好奇，这里有一个使用 Promise 的被大量评论的实现.请注意，没有解释 Q promise 库函数的注释，它比原始版本短一些，并且还提供了更全面的错误处理.

Update: If you are curious, here is a heavily-commented implementation using promises. Note that without the comments explaining the Q promise library functions, it is somewhat shorter than the original version and also offers more comprehensive error handling.

var MysqlBackup = function(connectionInfo, filename){

    var Q = require('q');
    var self = this;
    this.backup = '';
    // my personal preference is to simply require() inline if I am only
    // going to use something a single time. I am certain some will find
    // this a terrible practice
    this.connection = require('mysql').createConnection(connectionInfo);

    function getTables(){
        //  return a promise from invoking the node-style 'query' method
        //  of self.connection with parameter 'SHOW TABLES'.
        return Q.ninvoke(self.connection,'query', 'SHOW TABLES');
    };

    function doTableEntries(theResults){

        // note that because promises only pass a single parameter around,
        // if the 'denodeify-ed' callback has more than two parameters (the
        // first being the err param), the parameters will be stuffed into
        // an array. In this case, the content of the 'fields' param of the
        // mysql callback is in theResults[1]

        var tables = theResults[0];
        // create an array of promises resulting from another Q.ninvoke()
        // query call, chained to .then(). Note that then() expects a function,
        // so recordEntry() in fact builds and returns a new one-off function
        // for actually recording the entry (see recordEntry() impl. below)

        var tableDefinitionGetters = [];
        for (var i = 0; i < tables.length ; i++){
            //  I noticed in your original code that your Tables_in_[] did not
            //  match your connection details ('mvc' vs 'test'), but the below
            //  should work and is a more generalized solution
            var tableName = tables[i]['Tables_in_'+connectionInfo.database];

            tableDefinitionGetters.push(Q.ninvoke(self.connection, 'query', 'SHOW CREATE TABLE ' + tableName)
                                        .then(recordEntry(tableName)) );
        }

        // now that you have an array of promises, you can use Q.allSettled
        // to return a promise which will be settled (resolved or rejected)
        // when all of the promises in the array are settled. Q.all is similar,
        // but its promise will be rejected (immediately) if any promise in the
        // array is rejected. I tend to use allSettled() in most cases.

        return Q.allSettled(tableDefinitionGetters);
    };

    function recordEntry (tableName){
        return function(createTableQryResult){
            self.backup += "DROP TABLE " + tableName + "\n\n";
            self.backup += createTableQryResult[0][0]["Create Table"] + "\n\n";
        };
    };

    function saveFile(){
        // Q.denodeify return a promise-enabled version of a node-style function
        // the below is probably excessively terse with its immediate invocation
        return (Q.denodeify(require('fs').writeFile))(filename, self.backup);
    }

    // with the above all done, now you can actually make the magic happen,
    // starting with the promise-return Q.ninvoke to connect to the DB
    // note that the successive .then()s will be executed iff (if and only
    // if) the preceding item resolves successfully, .catch() will get
    // executed in the event of any upstream error, and finally() will
    // get executed no matter what.

    Q.ninvoke(this.connection, 'connect')
    .then(getTables)
    .then(doTableEntries)
    .then(saveFile)
    .then( function() {console.log('Success'); } )
    .catch( function(err) {console.log('Something went awry', err); } )
    .finally( function() {self.connection.destroy(); } );
};

var myConnection = {
    host     : '127.0.0.1',
    user     : 'root',
    password : 'root',
    database : 'test'
};

// I have left this as constructor-based calling approach, but the
// constructor just does it all so I just ignore the return value

new MysqlBackup(myConnection,'./backup_test.txt');

这篇关于NodeJS MySQL 转储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！