How to import large databases

Normally, to import a database, we can use phpMyAdmin’s import tool. However, there are cases when we need to import large databases, and the import process fails either because of a maximum size limit or because the process times out.

In all cases where phpMyAdmin cannot be used, we can import the database using an alternative solution:

• Use a program such as Sequel Ace, Sequel Pro, or HeidiSQL.
• Use a terminal command for importing.

Let’s see how to do it using both methods.

Using a program to import the database

Several programs allow us to manage databases remotely. For macOS, you can use Sequel Ace or Sequel Pro.

For Windows, you can use HeidiSQL, which is fully compatible with Windows 10 and 11, and can also be used with Windows 7, 8, and on Wine (although in the latter cases you may encounter some errors).

Let’s walk through how to do it using HeidiSQL.

Before proceeding, you need to enable remote connections to the database. To do this, go to cPanel and find the “Remote MySQL” tool.

Here, you can add your computer’s IP address so that you can connect to the database. You can find your IP using our tool.

Enter the IP in the “Host” field and then click on the “Add Host” button.

Now, we can donwload HeidiSQL and install it on our computer, or start it if you’re using the portable version.

After starting HeidiSQL, add a new session by clicking “New.”

You will be prompted for the data to connect to the database:

• Host name: the name or IP of the server.
• User: the username connected to the database.
• Password: the user’s password.
• Port: usually, you can leave the default one.

Once all the fields are filled in, click “Open” to start the connection.

If the data is correct, you will see the contents of the database. At this point, you can import your .sql file by clicking on File > Load SQL File and selecting the .sql file to import.

For large databases, you may see a warning asking whether to run the file directly or load it into the editor. It’s usually preferable to run it directly to avoid saturating memory and causing the program to crash.

At this point, all you have to do is wait for the database import to complete.

Now, let’s see how to import a large MySQL database from the terminal.

Importing large databases from the terminal

As an alternative to the method described above, you can upload the .sql file to the server and then import it using a terminal command.

First, you need to upload the file to the server’s root folder. You can use FileZilla or the cPanel file manager.

With all SupportHost hosting plans, including shared hosting, an SSH connection is available, and the terminal can be accessed from cPanel.

To proceed, you can open the terminal from cPanel or connect via SSH and run the following command:

mysql -u db_user -p db_name < filesql.sql

You need to replace the fields with the correct values:

• db_user: replace with the database username.
• db_name: the name of the database.
• filesql.sql: specify the name and path of the .sql file you want to import.

After running the command, you will be prompted for the database password. Once entered, wait for the import to complete. If there are any issues, you’ll see an error message; otherwise, no warning will appear.

If the file you uploaded is compressed, such as “.sql.gz,” you can extract it directly from the terminal using the gzip command like this:

gzip -d database.sql.gz

Make sure to enter the correct name of the compressed database instead of “database.sql.gz.”