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:

$ (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?


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)
I remember filing a feature request that would also help with this: http://bugs.mysql.com/bug.php?id=39233
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.
Similar to yours but no need to create another .sql file. shell> (echo “set session sql_log_bin=0;” ; cat dump.sql) | mysql
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…
Seun Osewa
@Chuck Your approach is perfect.”
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.
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
Geert Vanderkelen
@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.’