Shulou(Shulou.com)05/31 Report--

This article to share with you is about how to import multiple.sql files efficiently in MySQL, Xiaobian think it is very practical, so share it with you to learn, I hope you can gain something after reading this article, not much to say, follow Xiaobian to see it.

1. mysql command import

mysql command import multiple sql file method:

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

< $SQL; done 2、source命令导入 source命令需要首先进入MySQL命令行： $ mysql -uroot -p"123456" 导入多个sql文件需要先创建一个额外的文件，名字随意，这里我们取：all.sql，内容： source user1.sql source user2.sql source user3.sql 注意，这里每行一条，必须以source命令开头。 然后用source命令执行该文件： mysql >

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

3. How to improve the import speed?

For files above M level, if light is imported in this way, the speed is extremely slow.

According to MySQL official recommendations, we have several measures to greatly improve 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, and can be changed back to the default 8M after import, note that it is not innodb_buffer_pool_size).

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

Turn off autocommit: autocommit=0. (Do not use the set global autocommit=1; command to close, otherwise the entire MySQL system will stop autocommit, innodb log buffer will soon be full, 5 and 6 items please only valid in the session, the correct way please look down)

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

Turn off foreign key checking: foreign_key_checks=0.

insert values are written in a statement such as Insert Into Yourtable VALUES (1,2), (5,5),...;

Set 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;

Order used in Article 3:

#DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY#ADD 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 foreign_key_checks = 1;' $SQLdone The above is how to import multiple.sql files efficiently in MySQL. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.