How to import 10 gb sql file in mysql quickly?

How to import 10 gb sql file in mysql quickly?

October 20, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How to import 10 gb sql file in mysql quickly?

Importing Large SQL Files into MySQL Quickly

As a seasoned developer, I understand the frustration of dealing with large SQL files that take an eternity to import using standard command-line tools. In this post, we’ll explore some practical methods to efficiently import your 10GB SQL file into MySQL without spending hours waiting for the process to complete.

The Default Approach: Command-Line Import

Currently, you’re using the following command to import your SQL file:

mysql -u username -p database_name < file.sql

While this method works for smaller files, it’s not optimized for large datasets. The main issues are:

  1. Single Connection: This approach uses a single MySQL connection, which limits parallel processing.
  2. Resource Intensive: Loading the entire SQL file into memory can be resource-intensive, especially for large files.

Best Practices for Large-Scale Imports

To optimize your import process, consider these best practices:

1. Use the mysqlimport Utility

MySQL provides a utility called mysqlimport, which is specifically designed to handle large data imports. It supports parallel loading and can significantly speed up the process.

Here’s how you can use it:

mysqlimport --fields-terminated-by="\t" --lines-terminated-by="\n" -u username -p database_name file.sql

In this example, we’re using tab-separated fields (--fields-terminated-by="\t") and newline-terminated lines (--lines-terminated-by="\n"). You may need to adjust these settings depending on your SQL file format.

2. Partitioning Your Data

If the structure of your data allows it, you can partition your tables into smaller chunks before importing. This reduces the size of individual transactions and enables parallel processing during the import.

For example, if you have a table called orders, you could create partitions based on the order_date field:

ALTER TABLE orders PARTITION BY RANGE (ORDER_DATE)
(
  PARTITION p0 VALUES LESS THAN ('2021-01-01'),
  PARTITION p1 VALUES LESS THAN ('2021-07-01'),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);

After creating partitions, you can import the data into each partition separately using mysqlimport or even with multiple threads.

3. Optimizing Memory Usage

If your server has sufficient RAM, you can optimize memory usage to speed up imports. You can adjust several configuration settings:

  • innodb_buffer_pool_size: Allocate a larger buffer pool for InnoDB to cache more data.
  • sort_buffer_size and join_buffer_size: Increase these values if your queries involve sorting or joining large datasets.

Remember, while increasing memory usage can improve performance, it’s crucial not to exceed the available RAM to prevent swapping, which would degrade performance.

Conclusion

When dealing with large SQL files like yours (10GB), the standard command-line import method often falls short. To maximize efficiency:

  • Use mysqlimport for its parallel loading capabilities.
  • Partition your data if possible to enable concurrent imports.
  • Optimize memory usage based on available resources.

Remember, performance tuning is an iterative process. Monitor your server’s resource utilization during the import and adjust settings as needed.

For more in-depth insights into MySQL optimization techniques, I recommend checking out our comprehensive guide at Laravel Company.

With these best practices applied, you should be able to import your 10GB SQL file much faster and with reduced stress. Happy importing!

Database Performance Import/Export Errors

Our network