Geert JM Vanderkelen

Disabling binary logging when restoring a MySQL dump

Disabling binary logging when restoring a MySQL dump

There is a cool option for mysqlbinlog for disabling the binary log when doing recovery using binary logs, namely –disable-log-bin. How, one would think it is also avialable for something like mysqldump or even the mysql CLI? Nope. There are various ways for doing this, here is one:

 shell> (echo "SET SESSION SQL_LOG_BIN=0;"; cat dump.sql) > dump_nobinlog.sql

Obviously, bit pain for really big files, and when dumping to multiple files. So what is your favorite way for disabling binary logging when restoring a MySQL dump?

9 thoughts on “Disabling binary logging when restoring a MySQL dump

  1. Rob

    mysql> SET SESSION SQL_LOG_BIN=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> source blah;
    Query OK, 1 row affected (0.00 sec)

  2. arjen

    There's more settings you want to change when loading a dump, like innodb_flush_log_at_trx_commit and such. So what you want is
    cat prefix.sql dumpfile.sql postfix.sql | mysql -u …

    I'll write a blog entry on the complete story.

  3. Chuck

    Similar to yours but no need to create another .sql file.

    shell> (echo "set session sql_log_bin=0;" ; cat dump.sql) | mysql

  4. Laurence

    Ideally, IMHO, a wrapper/alias would be ideal, such as:

    # alias mysqldump='(printf "SET SESSION SQL_LOG_BIN=0;\n" && /usr/local/bin/mysqldump)'

    However, this doesn't seem to work, unfortunately.

    The prepend/append feature would be ideal…

  5. Gerhard

    Rather than do it when playning back the dump, I do it when taking the dump:
    echo “SET SESSION SQL_LOG_BIN=0;” > dumpfile
    mysqldump …. >> dumpfile
    This means I don’t forget to do it when doing a recovery.

  6. Kubilay

    Is this safe? Is a great performance enhancer and space saver, but would want to know its cost? Is this similar to non-recoverable operations in Oracle? Like NOLOGGING? I suppose if we have the dump we can always recover it from the dump, hence recoverable and repeatable operation. Just wanted to know what you guys think. Thanks

    1. Geert Vanderkelen Post author

      @Kubilay: You answered your own question: you have the dump, so the data is recoverable and repeatable. If you disable binary logging, it will not go to slave MySQL servers. That would be the only problem you have to tackle.

Leave a Reply

Your email address will not be published. Required fields are marked *

71 − = 67

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>