Bug #75140 mysql workbench restore database not log inserts
Submitted: 8 Dec 2014 12:57 Modified: 5 Feb 2018 5:28
Reporter: David Hodgson (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench: Administration Severity:S4 (Feature request)
Version:6.3.10 OS:Any
Assigned to: CPU Architecture:Any

[8 Dec 2014 12:57] David Hodgson
Enhance MySQL workbench to have an options to not log the data inserts when restoring a database.

This makes the inserts run faster, and the logging in non-master-slave situations is not helpful - the recovery from a failure would be to re-do the restore rather than try & recover the table from the log!

How to repeat:
Run Data Export then run Data Import with general query logging and bin logging active.

Suggested fix:
The option, if set, should:

SET @save_sql_log_bin = @@session.sql_log_bin ;
SET @save_sql_log_off = @@session.sql_log_off ;
SET @@session.sql_log_bin = @save_sql_log_bin;

at the start of the script (the SET @@session... is so it crashes if the user doesn't have the appropriate authorities), then:

SET @@session.sql_log_bin = 0;
SET @@session.sql_log_off = 1;

before each set of inserts after the table is created, and:

SET @@session.sql_log_off = @save_sql_log_off; 
SET @@session.sql_log_bin = @save_sql_log_bin; 

after the last inserts for the table.
[8 Dec 2014 13:14] David Hodgson
If you wanted to be more fancy, i.e. give an option at restore time rather than backup time, you could also add a flag to the start of the generated SQL which controls switching off the logs, and then if you wanted to restore with logging you could change the flag to False and get all the logs you want.
[5 Feb 2018 5:28] MySQL Verification Team
Hello David,

Thank you for the feature request.