Disabling binary logging when restoring a compressed MySQL dump

Question

I am busy build a slave of an existing database. I don't want it to build bin logs for the data I import before bringing the slave into the same state as master.

This is mostly to save space on importing 100 G of data.

mysqldump somelargedb | gzip > /somewhere/withspace/dump/somelargedb.sql.gz

Uncompressed this file is in the 100 Gb range. Compressed it is around 2Gb

Answer 1

I found this answer.

https://geert.vanderkelen.org/2009/disabling-binary-logging-when-restoring-a-mysql-dump/

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

A better solution would have been the following. Taken from the comments on the above site. But as the volume of data is rather large I don't want to spend another few hours waiting for the data to be exported. This also does not include compress of the file at dump time.

$ echo "SET SESSION SQL_LOG_BIN=0;" > dumpfile   
$ mysqldump .... >> dumpfile

I have adapted it as follows.

echo "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - /somewhere/withspace/dump/somelargedb.sql.gz | mysql -u root -p somelargedb

Answer 2

It can be done for .gz dumps this way:

(echo "SET SESSION SQL_LOG_BIN=0;"; gzip -dc dump.sql.gz) | mysql

Or if you copy a database right from a remote server:

(echo "SET SESSION SQL_LOG_BIN=0;"; mysqldump --host your_host --verbose --compress my_database) | mysql my_database

It's implied that a user and a password for both mysqldump and mysql are added in the .cnf file (e.g. in ~/.my.cnf)