In the process of migrating your site from your old host, or if you are restoring a very large database backup, you may need to import a MySQL database larger than 50MB. As of right now, PhpMyAdmin in cPanel has a database upload limit of 50MB. That being said, you can use a command line interface such as SSH or the Terminal application in cPanel to import MySQL databases without this limitation.
Uploading Your Database Backup
1. Login to cPanel and click File Manager
2. Click Upload and upload to a folder where you can easily locate it. For this tutorial we will upload the database .SQL file to the public_html folder.
Assigning Username and Password For Database
Create an Empty Database
Navigate to MySQL Databases within cPanel. If you already have a database you are trying to import your database backup into, then you can skip the step of creating an empty database. Enter the name for your database and click Create Database.
Create a Database User
Under the heading that says Add New User, create a database username and password and click Create User. Make sure you remember this information for later.
Add Database User to Database
Under the heading that says Add User to Database, select the Database User and the Database you want to use and then click Add.
On the next screen, in most cases you will want to check ALL PRIVILEGES to give your database user full access to the database. Click Make Changes and the database user will now be added to your database. Click Go Back to be brought back to the area where you can see your MySQL Databases.
You will now see your database and database user listed under Current Databases. At any time if you need to delete or rename your database you can do it here by clicking on the Actions buttons to the right. You can also click the trash can icon under Privileged Users to remove the database user from the database.
If you need to change your database user password at any time, you can scroll to the bottom of the page and click Change Password next to the user. You can also click Rename to rename the user, and Delete to remove the database user entirely.
Importing Database Via Command Line
1. Navigate to the cPanel main menu by clicking the cPanel icon at the top left of the screen.
2. Click the button that says Terminal.
3. Use the command line operation cd to navigate to the directory where you uploaded the .sql file. In this case we upload it to the public_html folder, so we would enter the following command:
4. Use the command line operation ls to verify the .sql file is in your current folder.
5. If everything looks good you can now connect to MySQL and import the database using the MySQL username and password that we have set above. In this case you would enter createreseller_createwp for the database user, and createreseller_createwp for the database name. At the end of this command you would make sure your .sql file matches what you saw using ls. In this case you would enter databasebackup.sql. The full syntax for the command would be mysql -u database_username -p database_name < databasebackup.sql
In this scenario the full command you would enter would be:
mysql -u createreseller_createwp -p createreseller_createwp < databasebackup.sql
6. You will be prompted for the database user password. You would enter the password you created above. It might take a couple moments, but after a couple minutes your database will be fully imported!
If you run into any issues while doing this, feel free to reach out to us at https://support.create.com.