patternsqlMinor
Disabling binary logging when restoring a compressed MySQL dump
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.
Uncompressed this file is in the 100 Gb range.
Compressed it is around 2Gb
This is mostly to save space on importing 100 G of data.
mysqldump somelargedb | gzip > /somewhere/withspace/dump/somelargedb.sql.gzUncompressed 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/
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.
I have adapted it as follows.
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.sqlA 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 .... >> dumpfileI have adapted it as follows.
echo "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - /somewhere/withspace/dump/somelargedb.sql.gz | mysql -u root -p somelargedbCode Snippets
bash $ (echo "SET SESSION SQL_LOG_BIN=0;"; cat dump.sql) > dump_nobinlog.sql$ echo "SET SESSION SQL_LOG_BIN=0;" > dumpfile
$ mysqldump .... >> dumpfileecho "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - /somewhere/withspace/dump/somelargedb.sql.gz | mysql -u root -p somelargedbContext
StackExchange Database Administrators Q#165617, answer score: 9
Revisions (0)
No revisions yet.