Importing MYSQL Database Using SSH Command Line

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!

Steps

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.