S h o r t S t o r i e s

// Tales from software development

MySQL restore without logging

leave a comment »

If you’ve got binary logging enabled then restoring a database is likely to increase the size of the binary log by the size of the database being restored and this is probably log data that isn’t wanted or needed.

There are several options to get around this with the two obvious ones being: (1) to configure logging to ignore updates to the database being restored, or; (2) to use a text editor to insert “SET sql_log_bin=0;” at the start of the dump file.

Option (1) is achieved using the MySQL Administrator utility to set the ‘Ignore updates for’ option on the Log Files tab of the Startup Variables view. The first disadvantage is that it won’t take effect until MySQL is restarted which might be a problem in a live environment. Then there’s the issue of whether you really want to turn all logging off for the database in question.

Option (2) is nice and simple but also has a couple of disadvantages. First, it might be problematic to edit a large dump file. Secondly, should you be doing this and compromising the integrity of the dump file ? In the particular scenario that I was dealing with, the dump file was for a live database and was being restored as a copy of this database to be used in a test system. I didn’t really want to be compromising the integrity of the live backup by editing it manually.

There is a solution but it’s not as obvious as it should be. The MySQL.exe utility allows a file containing SQL statements to be processed as input either using STDIN redirection or the -e argument and the source command. The following are equivalent:

mysql.exe db-name <database.dmp
mysql.exe db-name -e"source database.dmp"

The -e argument allows multiple statements to be executed:

mysql.exe db-name -e"statement1;statement2;etc;"

So, you might think that this this would work:

mysql.exe db-name -e"SET sql_log_bin=0;source database.dmp"

Unfortunately, it doesn’t. The MySQL.exe utility doesn’t appear to allow the source command to be mixed with SQL statements in this way in the -e argument. However, it does allow this combination of commands in a source file. So, if you create a text file called, for example, restore.sql containing

SET sql_log_bin=0;
source database.dmp

You can then execute MySQL.exe against it:

mysql.exe db-name -e"restore.sql"

and the restore is performed without logging.


Written by Sea Monkey

August 20, 2012 at 8:00 pm

Posted in Environments

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: