Friday, 17 July 2009
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:
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?
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?
Subscribe to:
Post Comments (Atom)
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
Post a Comment