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?

Comments

Rob

Fri Jul 17 2009, 16:18

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)

Morgan

Fri Jul 17 2009, 16:26

I remember filing a feature request that would also help with this: http://bugs.mysql.com/bug.php?id=39233

arjen

Fri Jul 17 2009, 22:50

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.

Chuck

Thu Aug 13 2009, 16:02

Similar to yours but no need to create another .sql file. shell> (echo “set session sql_log_bin=0;” ; cat dump.sql) | mysql

Laurence

Fri Jul 23 2010, 11:35

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

Sat May 7 2011, 00:38

@Chuck Your approach is perfect.”

Gerhard

Thu Oct 11 2012, 16:30

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.

Kubilay

Tue Mar 25 2014, 09:32

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

Tue Mar 25 2014, 10:29

@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.’