Detailed explanation of how to efficiently import multiple .sql files into MySQL

MySQL has multiple ways to import multiple .sql files (containing sql statements). There are two commonly used commands: mysql and source.

However, the import efficiency of these two commands is very different. Please see the final comparison for details.

(There are also import methods such as sqlimport and LOAD DATA INFILE, but they are mainly used to import .csv or .xml file data, not .sql files)

Suppose we have a large file users.sql. For convenience, we split it into three independent small sql files: user1.sql, user2.sql, and user3.sql.

1. Import mysql command

mysql command to import multiple sql files:

$ for SQL in *.sql; do mysql -uroot -p"123456" mydb < $SQL; done

2. Import source command

The source command requires you to first enter the MySQL command line:

$ mysql -uroot -p"123456"

To import multiple sql files, you need to create an additional file first. The name is arbitrary. Here we take: all.sql, the content is:

source user1.sql
source user2.sql
source user3.sql

Note that each line must start with a source command.

Then execute the file using the source command:

mysql > use mydb;
mysql > source /home/gary/all.sql

3. How to improve the import speed?

For files over 100 MB, the speed is extremely slow if you only import them in this way.

According to MySQL official recommendations, we have several measures that can greatly increase the speed of import, as follows:

For MyISAM, adjust the system parameter: bulk_insert_buffer_size (at least twice the size of a single file)

For InnoDB, adjust the system parameter: innodb_log_buffer_size (at least twice the size of a single file. You can change it back to the default 8M after the import is complete. Note that it is not innodb_buffer_pool_size.)

Except for the primary key, delete other indexes and rebuild the index after the import is complete.

Turn off autocommit: autocommit=0. (Do not use the set global autocommit=1; command to turn it off, otherwise the entire MySQL system will stop automatically committing, and the innodb log buffer will soon be full. Items 5 and 6 are also only valid in the session. Please see below for the correct method.)

Turn off unique index checks: unique_checks=0. (Turning this off will affect the effect of on duplicate key update)

Turn off foreign key checks: foreign_key_checks=0.

The insert value is written in one statement, such as: INSERT INTO yourtable VALUES (1,2), (5,5), ...;

If there is an auto-increment column, set the value of innodb_autoinc_lock_mode to 2.

Among them, items 1-2 and 8 modify the my.cnf file and then restart MySQL:

bulk_insert_buffer_size=2G;
innodb_log_buffer_size=2G;
innodb_autoinc_lock_mode=2;

The commands used in Article 3:

# Delete the index DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

# Add an index ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Items 4-6 are written in .sql, and the batch bash script is as follows:

for SQL in *.sql;
do
  echo $SQL;
  sed -i '1i\SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;' $SQL
  sed -i '$a\COMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;' $SQL
done

After adjusting according to the above steps, the insertion speed will be greatly improved.

4. Comparison of efficiency between mysql and source

When the SQL file is small, source is faster than MySQL.

In the actual test import, 5 sql files with a total size of 25M, the mysql command is 2 seconds faster than the source (my own test, not representative of the general results),

It can be roughly concluded that when importing large sql files, it is recommended to use the mysql command.

The above is the method of efficiently importing multiple .sql files into MySQL. I hope it can help you.