When dealing with large databases, the standard phpMyAdmin tool in cPanel might not be the most reliable option for uploading. In such cases, it's more efficient to import the database manually using an SSH (Secure Shell) session. This guide will walk you through the process.
Preparing for the Import
Before you begin, you'll need to gather some essential details:
Database Dump File: This is the file containing your database data, often with a .sql extension. You can upload this file into your home directory using FTP.
Database Name: 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.
Database Username and Password: A valid database username (and password) that has full permissions to the database.
Importing Your Database
Once you have successfully opened an SSH session, you can import your database using the following command:
mysql -u yourusername -p yourdatabase < yourdumpfile.sql
Here's what each part of the command means:
- mysql: This is the command to run mysql in interactive mode.
- -u yourusername: This is the MySQL user that you configured in cPanel beforehand.
- -p: This tells MySQL that you will enter a password manually.
- yourdatabase: This is the full name of the database you want to import into.
- <: This is the redirection symbol. It's saying take what's on the right and send it to what's on the left!
- yourdumpfile.sql: This is the MySQL backup/dump file that you want to import.
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. 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.
Troubleshooting
If the import fails or does not complete due to errors, it's easiest 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.
Remember, if you're unsure about something, don't hesitate to reach out to our support team. We're here to help!