HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Disabling binary logging when restoring a compressed MySQL dump

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
loggingdumprestoringmysqlbinarywhencompresseddisabling

Problem

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

Solution

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

Code Snippets

bash $ (echo "SET SESSION SQL_LOG_BIN=0;"; cat dump.sql) > dump_nobinlog.sql
$ echo "SET SESSION SQL_LOG_BIN=0;" > dumpfile   
$ mysqldump .... >> dumpfile
echo "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - /somewhere/withspace/dump/somelargedb.sql.gz | mysql -u root -p somelargedb

Context

StackExchange Database Administrators Q#165617, answer score: 9

Revisions (0)

No revisions yet.