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!

arshovon · Accepted Answer · 2019-04-08 15:01:16Z

12

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

edited Apr 8, 2019 at 15:01

arshovon

13.7k9 gold badges54 silver badges72 bronze badges

answered Aug 14, 2018 at 9:16

Mouad

1211 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hassan · Accepted Answer · 2020-06-15 08:41:34Z

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

edited Jun 15, 2020 at 8:41

hassan

8,3282 gold badges28 silver badges40 bronze badges

answered Feb 15, 2017 at 10:25

Vladislav Vaintroub

5,7581 gold badge27 silver badges38 bronze badges

1 Comment

isedwards Over a year ago

Excellent, thank you. It didn't occur to me that you can use pipes and the equivalent of cat on windows!

Rick James · Accepted Answer · 2017-02-16 21:54:19Z

3

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'

answered Feb 16, 2017 at 21:54

Rick James

144k15 gold badges144 silver badges255 bronze badges

4 Comments

Vladislav Vaintroub Over a year ago

Is innodb_flush_log_at_trx_commit = 0 any worse than 2?

Rick James Over a year ago

@VladislavVaintroub - perhaps. Performance diff is insignificant, so simply use 2.

isedwards Over a year ago

In my simple tests, the autocommit=0 route seems to still be faster, but I'll look into this some more...

Rick James Over a year ago

In theory it will start out faster, but get slower before it finishes. There are too many variables to really predict whether the total time will be slower or not.

Collectives™ on Stack Overflow

Single commit when importing very large SQL files (MySQL)

3 Answers 3

Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related