How to import a large dump file to a database - MySQL

Today, while working on a project, I had to import a large SQL dump to a database (almost 10 GB). While doing this using the conventional methods, I ran into a number of hiccups causing issues with the import.

So, I decided to find an easier way and stumbled upon this after hours of research and hit and trials. To do this, we will be using the MySQL console.

Opening MySQL Console:

If you are on Linux, open the shell terminal with administrative privileges and run the following command:

mysql -u username -p password

If you are on Windows, open the PowerShell terminal with administrative privileges and run the following command:

$PATH_TO_MYSQL\mysql.exe -u username -p password

For a remote SQL server, just add the host and port number before the username as follows:

mysql -h host_ip --port=host_port -u username -p password

Once you are connected to the MySQL shell, we can continue with the next steps:

Creating a database:

The next step is to create a database, to create and use a database to which the file will be imported follow these commands:

create database database_name;
use database_name;

Now that we have created and used the database to which the file is to be imported, we can now proceed further:

Importing the dump file:

Firstly, we will set a large byte number to the network buffer length, as the default value may throw an error for such a large file. To do this, run the following command:

set global net_buffer_length=1000000;

Now, the next step is to increase the maximum allowed packet size, as it can also throw an error for such a large file. To do this, run the following command:

set global max_allowed_packet=1000000000;

The next step is to disable the foreign key, unique check and auto commit so as there are no unwanted errors or delays in the saving process:

SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

We are now ready to import the file to the database, to do this just copy the entire path of the SQL file from the file explorer and use the following command to initiate the import process:

source path_of_sql_dump_file

E.g. Let us assume the file is at the following location "D:\sql_dump\largedump.sql", then the command would be:

source D:\sql_dump\largedump.sql

Now, we need to wait for the process to finish, depending upon the size of the SQL dump file, this may take some time. Be patient and watch as the command does its magic.

Once the command is done with the import of your file, you need to enable the foreign key, unique check and auto commit for the database, for this run the following commands:

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

Congratulations, your SQL dump has successfully been imported, now you can close the shell and enjoy working on your newly imported database.

I hope you were able to flow the process and import your database. If you have any queries, please let us know in the comment section below.

Thank You