If you need to import a large MySQL database that’s too large to upload reliably using phpMyAdmin, you can do it manually via an SSH session. Here’s a step-by-step guide on how to accomplish this:
Assumptions & Requirements:
- This article assumes you know how to access to your server through SSH.
- Location and filename of your database dump file (usually with a .sql extension). Upload this file to your home directory using FTP.
- Name of the database you want to import into (make sure it already exists).
- A valid database username and password with full permissions for the database.
Importing the Database:
- Open an SSH session to your server.
- Use the following command as an example, replacing the placeholders with your own information:
mysql -u your_username -p your_database_name < your_dumpfile.sql- mysql: This is the command to run MySQL in interactive mode.
- -u your_username: Replace your_username with your MySQL username configured in cPanel.
- -p: This flag tells MySQL that you will enter a password manually.
- your_database_name: Replace with the full name of the database you want to import into.
- <: This is the redirection symbol, indicating that you’re sending what’s on the right to what’s on the left.
- your_dumpfile.sql: This is the MySQL backup/dump file you want to import. If it’s in the current working directory, you don’t need to specify a path. If it’s in a different folder, either navigate to that folder and run the command or include the full path to the dump file.
After entering the command, press Enter. You’ll be prompted to enter the password for the database user. Type it (it won’t be displayed on the screen), and press Enter again to start the import.
Restoring a large database with thousands or hundreds of thousands of rows may take some time, so be patient while it completes.
Common Issues and Solutions:
Import Failure: If the import fails or doesn’t complete due to errors, it’s easiest to delete the database from within cPanel (if you don’t need it) and recreate an empty one. Then, try importing again.
Database Name Instructions: Some MySQL dump files include instructions to CREATE or USE a specific database name. If these instructions exist at the top of the file, either comment them out by adding two hyphens (–) at the start of each line or remove them.
This manual method of importing large databases via SSH is more reliable and efficient for handling large datasets compared to using phpMyAdmin.
