Databases are a central part of most websites and importing them into cPanel is a common task when migrating sites and performing maintenance.
While intimidating at first, the main advantage of importing databases using the command line compared to phpMyAdmin is seen when importing large or complex SQL files.
This is due to limitations with phpMyAdmin which include:
- Upload Size Limit (64~128MB)
- Web Timeouts
- Substantial Performance Penalty
For smaller databases, importing data via phpMyAdmin is perfectly valid and will achieve similar results.
Rules when working on the databases on a shared hosting server
- Always take a backup of the database
- Never ever not even once run the import as root or a superuser, even if it asks for it!
Malicious or badly written queries can affect or compromise other databases on the server
- Always create new databases, database users and adjust permissions through cPanel, not the database directly
- Do not put MYSQL files in web-directories where they may be accidentally downloaded or visible to the public!
1. Open ‘MYSQL Databases’ and evaluate the databases and users currently on the server.
In this example we want to import into this database existing, however a new database could be created if required
2. Create a new Database user, while you can try to track down the existing MySQL user details it is often best just to create a new temporary user.
Remember to write down this password!
3. Add the new MYSQL user to the database and assign the appropriate permissions. Granting ‘ALL PRIVILEGES’ is often the recommended option if the SQL file is trusted.
You should now see the new user assigned next to the database
4. Upload the SQL database file into a non-web-directory for the user
5. Open Up Terminal via the cPanel GUI (If Enabled), or login via SSH as the cPanel user.
As the cPanel and newly created MYSQL user, import the SQL file using the following syntax.
You will be prompted for the MYSQL users password
mysql -u [MYSQL Username] -p [Datebase Name] < [FileName].sql
The console may freeze for a period of time with large SQL files, this is normal, do not cancel it!
You will not get any feedback or confirmation the import was successful once the command ends, only on error.
6. Cleanup and verification
Once completed, you perform the following tasks
- Delete the newly created MYSQL user
- Delete the .SQL file from the web-server
- Verify the tables in phpMyAdmin.