How to import a large database into MySQL

This article will guide you on importing MySQL databases that are too large to upload reliably via phpMyAdmin in cPanel. Uploading a large database through phpMyAdmin can be time-consuming and prone to connection drops or other issues, which can be very frustrating. In such cases, it is better to manually import the database using an SSH (Secure Shell) session. How to use SSH

To complete this process, you'll need to connect to your server using SSH. We have separate guides available for connecting via SSH on both PC and Apple Mac.

The following instructions assume you have successfully opened an SSH session.

Importing your database

In order to import your database you will need to have the following information to hand:

• the location and filename of your database dump file (this often has a .sql extension) - you can upload this file into your home directory using FTP.
• the name of the database you want to import into - the database must already exist so create it if you haven't already done so.
• a valid database username (and password) that has full permissions to the database

Here is an example command :

mysql -u hippo1_myuser -p hippo1_mydata < mydumpfile.sql

• mysql : this is the command to run mysql in interactive mode.
• -u hippo1_myuser : this is the mysql user that you configured in cPanel beforehand.
• -p : this tells mysql that you will enter a password manually.
• hippo1_mydata : this is the full name of the database you want to import into.
• < : this is the redirection symbol. Here is it saying take what's on the right and send it to what's on the left!
• mydumpfile.sql : this is the mysql backup/dump file that you want to import (in this example the file is in the current working directory so it does not need to be preceded with a path. If your dump file is located in a different folder - either change to that folder and run the command from there or include the full path to the dump file.)

Just substitute in your MySQL database name, username, and SQL dumpfile name. When you hit Return you will be prompted to enter the password for the database user. Just enter it (it won't echo to the screen) and press Return again to start the import.

Restoring a large database with thousands or hundreds of thousands of rows might take a minute or so to restore, so be patient or go and make a pot of tea - it will finish eventually.