Disabling binary logging when restoring a compressed MySQL dump
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
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
)