5

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

5

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
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.