Single commit when importing very large SQL files (MySQL)

Question

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!

Answer 1

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

Answer 2

(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