5

I'm trying to restore large SQL files (> 2.5 Gb) into a MySQL database on Windows.

I'm not able to edit these files to add text like SET autocommit=0; at the beginning of the file (which is necessary to improve the import time).

I'm also not able to use source, as this outputs to the screen (which is very slow) and the execution continues even if there are any errors in the file. E.g.:

mysql> CREATE DATABASE IF NOT EXISTS dbname;
mysql> USE dbname;
mysql> SET autocommit=0;
mysql> source file.sql;
mysql> COMMIT;

Is it possible to run arbitrary commands before and after importing an SQL file that apply only to the current session? I've tried both of the following, and neither work on Windows (in both cases the second operation is ignored):

mysql -u username -p -e "SET autocommit=0;" dbname < file.sql

or,

mysql -u username -p < initial_commands.sql < file.sql

If possible I don't want to change the global autocommit setting each time I do this, and then have to remember to change it back (also I'm not sure that this will work without the final COMMIT;).

Perhaps there is a way to use BEGIN ... COMMIT; instead of turning off autocommit?

I'd be happy with any suggestions from people who have to do this kind of thing!

isedwards
  • 2,429
  • 21
  • 29

3 Answers3

10

My answer is late, but it may help future readers.

I have had the same issue. When reading MySQL documentation on MySQL command options, I have found that you can use --init-command parameter.

So your command line will be simply: (-v for verbose and is optional)

mysql --init-command="SET autocommit=0;" -v < sql_file.sql
arshovon
  • 13,270
  • 9
  • 51
  • 69
Mouad
  • 101
  • 1
  • 5
3
(echo set autocommit=0; && type file.sql && echo. && echo commit;) | mysql -u username -p passwd

Just use pipes instead of input redirection, use echo for the commands, and echo. for newlines

hassan
  • 7,812
  • 2
  • 25
  • 36
Vladislav Vaintroub
  • 5,308
  • 25
  • 31
  • Excellent, thank you. It didn't occur to me that you can use pipes and the equivalent of `cat` on windows! – isedwards Feb 15 '17 at 19:11
2

Your goal may be counterproductive. That is, turning the entire load into a single transaction may be slower than committing incrementally.

set autocommit=0 means that all inserts (and other writes) hence forth (until the commit) need to be logged for potential ROLLBACK. At first, the regular logging mechanism can a bunch of writes. But, at some point, a more complex mechanism kicks in. This involves a lot more disk I/O, which is the slowest part of a database.

An alternative is to cut out a significant part of the "commit" process -- the fsync to flush the committed transaction. innodb_flush_log_at_trx_commit controls such. The default is probably =1 for "secure". Many people run with =2 which is less secure, but limits the fsyncs to once a second. The "inseccure" part is that, if you have a power failure, any transactions run in the last second may have been acknowledged to the client with actually being persisted to disk.

Since that setting probably has to be done before you connect, consider a simple, 3-line, .bat script (file):

mysql -e 'SET GLOBAL innodb_flush_log_at_trx_commit = 2'
mysql dbname < file.sql
mysql -e 'SET GLOBAL innodb_flush_log_at_trx_commit = 1'
Rick James
  • 135,179
  • 13
  • 127
  • 222