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!
解决方案
(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