How to import a large SQL file into MySQL

Import large SQL dump files into MySQL efficiently using the command-line client with timeout and buffer adjustments.

Prerequisites
Step-by-Step: Import a Large SQL File into MySQL
1. Import the SQL File with the MySQL Client
2. Import a Compressed SQL File
3. Increase Timeout and Buffer Settings for Very Large Imports
4. Monitor Import Progress
Common Issues

Import SQL dump files larger than 100 MB into MySQL or MariaDB using the command-line client, avoiding timeouts and memory limits that affect phpMyAdmin.

Prerequisites

MySQL client installed
The SQL dump file ( .sql or .sql.gz)
Sufficient disk space for the imported data

Step-by-Step: Import a Large SQL File into MySQL

1. Import the SQL File with the MySQL Client

mysql -u root -p myapp < large_dump.sql

The MySQL command-line client has no upload size limit, unlike phpMyAdmin's PHP-based import which is constrained by upload_max_filesize and max_execution_time.

2. Import a Compressed SQL File

gunzip < large_dump.sql.gz | mysql -u root -p myapp

3. Increase Timeout and Buffer Settings for Very Large Imports

For imports exceeding 1 GB, increase the MySQL server's packet and timeout limits:

mysql -u root -p --max_allowed_packet=512M --net_buffer_length=16384 myapp < huge_dump.sql

4. Monitor Import Progress

Use pv (pipe viewer) to show a progress bar:

pv large_dump.sql | mysql -u root -p myapp

Common Issues

"ERROR 2006: MySQL server has gone away"— The SQL file contains a statement exceeding max_allowed_packet. Increase with --max_allowed_packet=512M on the mysql command.

Import is very slow— Disable indexes and foreign key checks temporarily by adding to the top of the SQL file: SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;.