How to import a large database into MySQL
This article will show you how you can import MySQL databases that are too large to upload reliably using the phpMyAdmin tool included in cPanel. Uploading a large database via phpMyAdmin can take a long time, and a dropped connection or other issues can make it very frustrating. In cases like this, it is better to import the database manually using an SSH (Secure Shell) session.
You'll need to connect to your server using SSH to complete this process. We have separate guides on connecting to your server with SSH for PC and Apple Mac.
The rest of this article assumes you have successfully opened an SSH session.
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 krystald_myuser -p krystald_mydata < mydumpfile.sql
mysql : this is the command to run mysql in interactive mode.
-u krystald_myuser : this is the mysql user that you configured in cPanel beforehand.
-p : this tells mysql that you will enter a password manually.
krystald_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.
Not much really, but if the import fails or does not complete due to errors, it's easiest just to delete the database from within cPanel (assuming you don't need it!), and recreate an empty one and try again.
A common problem with importing MySQL files can occur if the dump file includes instructions to CREATE or USE a particular database name. If such instructions exist (and they will be at the top of the file if anywhere), then either comment them out (by inserting two hyphens !! at the start of each line) or remove them.
*
*
You'll need to connect to your server using SSH to complete this process. We have separate guides on connecting to your server with SSH for PC and Apple Mac.
The rest of this article assumes 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 krystald_myuser -p krystald_mydata < mydumpfile.sql
mysql : this is the command to run mysql in interactive mode.
-u krystald_myuser : this is the mysql user that you configured in cPanel beforehand.
-p : this tells mysql that you will enter a password manually.
krystald_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.
What can go wrong?
Not much really, but if the import fails or does not complete due to errors, it's easiest just to delete the database from within cPanel (assuming you don't need it!), and recreate an empty one and try again.
A common problem with importing MySQL files can occur if the dump file includes instructions to CREATE or USE a particular database name. If such instructions exist (and they will be at the top of the file if anywhere), then either comment them out (by inserting two hyphens !! at the start of each line) or remove them.
*
*
Updated on: 24/01/2024
Thank you!