How to Import Large Databases into phpMyAdmin or any MySQL Database?


As part of a project today, I had to import a sizable SQL dump into a database (almost 90 GB). I encountered a few snags that prevented the import while doing this using the traditional techniques.

So I made the decision to find a simpler solution, and after hours of research, failures, and experiments, I came across this. We'll make use of the MySQL console to accomplish this.

Opening MySQL Console:

Run the following command in a shell terminal on a Linux computer with administrative rights:

mysql -u username -p password

Run the following command in the PowerShell prompt while logged in as an administrator if you're using Windows:

$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:

In the next step we will be going to create a new database or if you have already created then use that database:

create database database_name;
use database_name;

Set Network Buffer Length

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;

Set Maximum Allowed Packet Size

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;

Disable Foreign Key

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

SET foreign_key_checks = 0;

Import Database SQL file:

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

This will take 5 minutes to 5 hours depending upon the size of the database.

Enable Foreign Key

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;

So, your database is imported you can check in your phpMyAdmin. I hope this will help in your large project.

Post a Comment

Previous Post Next Post