How to solve the problem that MySQL source import is very slow?

Now there is such a demand. There is a database service on the intranet, and the database on the external network needs to be imported into the intranet database.

The sql file exported from the external network database is 700MB. It is very slow to import it using the source that comes with MySQL, so I used the following method to speed up the import. It is very fast in personal testing.

1. Log in to MySQL

Enter the intranet server, log in to mysql, and enter the password.

mysql -u root -p

2. Create database

Create database according to requirements

create database 数据库名;

3. Set parameters

sql_log_bin Close binary log

autocommit Close Automatic transaction submission

set sql_log_bin=off;
set autocommit=0;

4. Use database

use 数据库名;

5. Open transaction

start transaction;

6. Import sql

source 文件.sql;

7. Manually submit transaction

commit;

8. Change back to configuration

set sql_log_bin=on;
set autocommit=1;

Attached mysql accelerated source import data

# 进入mysql中执行如下
SET GLOBAL foreign_key_checks=0;
SET GLOBAL unique_checks=0;
SET GLOBAL innodb_flush_log_at_trx_commit=0;
SET GLOBAL sync_binlog=0;

-- 你的sql语句1
-- 你的sql语句2
-- 你的sql语句3

SET GLOBAL foreign_key_checks=1;
SET GLOBAL unique_checks=1;
SET GLOBAL innodb_flush_log_at_trx_commit=1;
SET GLOBAL sync_binlog=1;

The above is the detailed content of How to solve the problem that MySQL source import is very slow?. For more information, please follow other related articles on the PHP Chinese website!