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?
Facebook
LinkedIn
Twitter
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…
@Chuck
Your approach is perfect.