Best AI Blog

# How to optimize the long time it takes to import sql files into MySQL?

During my recent development work, I needed to import a 3GB SQL file into a database server. If I imported it through a database tool such as Navicat Premium, it would take a very long time, more than 40 minutes.

The reason is that during the import process, each SQL statement will open transactions and write logs

Later I found that it is very fast to put the file directly on the server and import it directly from the command line. The steps are:

# 1. First put the sql file in the server directory

E:\bakup\db.sql

# 2. Use the MySQL client to log in to the database on the server and enter the database password.

mysql -u root -p

# 3. Switch to the database that needs to be imported (such as test), and turn off automatic submission and binlog.

use test;
set names utf8; #File encoding, if needed
set autocommit=0;
set sql_log_bin=0;

# 4. Start transaction

start transaction;

# 5. Import data

source E:\bakup\db.sql

This step takes a long time, you need to wait for a while

# 6. Wait for the import to complete and submit the transaction

commit;

# 7. Re-open automatic submission and logging

set autocommit=1;
set sql_log_bin=1;

# 8. Conclusion

After optimization, I found that a 3GB SQL file only takes more than 10 minutes on a server with a mechanical hard disk.

←