In this short article I would explain how you can go about safely restoring a MySQL database to a cPanel server.
There are two ways to go about restoring a MySQL database
A) Via cPanel’s phpMyAdmin
B) Via command line
So lets start with via cPanel’s phpMyAdmin . The important thing to note is the size of the DB you wish to restore. The max size SQL file which you can upload is determined by the Sys Admin. So when you begin the restoration process, you would be able to see what max size is allowed. If your SQL file is bigger than the max file size limit, then in that case you can compress your SQL file in ZIP format. This can bring the upload size to less than max file size limit. So let me put out the steps
Step 1 ) Login to cPanel and open phpMyAdmin
So now once phpMyAdmin is opened, select the DB on which you wish to restore the SQL file . In my case the DB name is lo7_dbr
Now click on Import as highlighted in the previous image. This will present you with various options
W.r.t. uploading the SQL file, you can upload the raw .sql file or zip compressed file. The key thing here is the Max size allowed and disabling the partial import. Both have been highlighted above. If partial import is checked, it may allow broken SQL file to be executed leading to inconsistencies in the tables.
Now when you have chosen the file you wish to upload, simply click Go. Its all done.
But what if your SQL file size even after zipping is greater than the max allowed file size. Then in that case you need to take these steps. Mind you, you would require shell access in this case. If you do not have it, then please ask your hosting company to enable it for you.
A) Upload the zipped sql file via SFTP/FTP to your home folder
B) Now login to command line/shell via SSH or cPanel -> Terminal
C) If the uploaded SQL file in in zip format, unzip it using following command
D) Once the file is unzipped, then simply run the MySQL restoration process with the following command
mysql -u CpanelUsername -p RestorationDBname < filename.sql
Here above, CpanelUsername would be your cPanel login username. In my case it is lo7.
The RestorationDBName is the DB to which SQL file has to be restored to. In my case it is lo7_dbr
Mind you, the above command would ask from your cPanel password in order to continue further.