How to Create a MySQL Database, a User and Delete Database in Cpanel

Create MySQL Database and User via cPanel

The MySQL Database Wizard is another useful tool that allows you to create a MySQL database and user via a web interface. If you are about to install a PHP-MySQL application and you do not have a database already created for your script, this tool will guide you through all the steps needed to create such.

To access the MySQL database Wizard, please scroll to the Databases section in your cPanel and click on the MySQL Database Wizard icon.

 

Further reading:

Backup mysql database tutorial

Beginner Guide to cPanel Control Panel

CPanel Account Information Tutorial

CPanel Login Tutorial

CPanel Security

Cannot See My Website Online After Upload, Why?

Character encoding in cPanel file manager

Custom error page is not working

How To Create, Edit, and Delete a File in CPanel Using File Manager

I have a full backup of account through cPanel. How do I restore it?

 

During the first step of your new database creation you will need to input the desired database name. Be advised that all cPanel databases are created with a prefix. This prefix represent your cPanel username and assures that your database name will be unique on the shared hosting environment even if you use a general database name such as test.

For example, if your cPanel user is "myuser" and you input "database" as your database name, the result will be a database called "myuser_database". You should take this under consideration when you configure your application or script to make sure that the correct settings are applied.

When you have the desired database name set, please click the Next Step button. During the next step of the process you will need to input your database username and password. This user will be the only one able to access the database. The username also follows the rule that its name is generated with a prefix, containing your cPanel username.

For example, if your cPanel user is "myuser" and you input "user" as your database user, the newly created database user will be "myuser_user".

To create the user click on the Create User button.

During the next step you should add privileges to your database user. Database user privileges sets the database actions that can be performed by this specific user. If you are about to setup and configure an application we recommend to apply All Privileges to make sure that your script will work properly.

When you are ready, click on the Next Step button on the bottom of the page. This will complete the process and you can now use your database name, user and password to install your application.

 

Further reading:

Backup mysql database tutorial

Beginner Guide to cPanel Control Panel

CPanel Account Information Tutorial

CPanel Login Tutorial

CPanel Security

Cannot See My Website Online After Upload, Why?

Character encoding in cPanel file manager

Custom error page is not working

How To Create, Edit, and Delete a File in CPanel Using File Manager

I have a full backup of account through cPanel. How do I restore it?

How to Reset or Change to a New CPanel Password

 

Manage MySQL Databases and Users

If you would like to delete your MySQL databases or add additional users, modify privileges or change your MySQL user password, the cPanel MySQL Database provides an user-friendly interface to do so.

To access the MySQL Databases tool in cPanel, please refer to the Databases section and click on the MySQL Databases icon.

Via the Add new User section you can create a new MySQL user to which can later add privileges to a specific database or a group of databases.

To add a user to a specific database or change an existing user password you may scroll to the bottom of the page and use the Add User to Database tool or the Current Users section to edit your existing users.

Remember to reconfigure any existing applications if you change your MySQL database user so they have the most recent logins configured. Otherwise, you application will not be able to establish a successful MySQL connection.

 

Further reading:

Backup mysql database tutorial

Beginner Guide to cPanel Control Panel

CPanel Account Information Tutorial

CPanel Login Tutorial

CPanel Security

Cannot See My Website Online After Upload, Why?

Character encoding in cPanel file manager

Custom error page is not working

How To Create, Edit, and Delete a File in CPanel Using File Manager

I have a full backup of account through cPanel. How do I restore it?

How to Set Up and Delete a Cron Job

 

Remote MySQL access

If you would like to have access to your database from a remote location or connect an application that is located on a different server to your hosting account databases you will need to use the Remote MySQL Access feature in your account cPanel.

The Remote MySQL Access tool is located under the Database section of your cPanel.

The Remote MySQL Access tool is pretty straight forward - you will need to simply add the IP address of the remote server or computer. If you would like to access your databases via your local computer, you should input your local IP address. You can find out what is your current local IP address by simply typing "What is my IP" into the Google Search box.

Just copy the IP address and add it to the allowed hosts.

If you would like to allow access from any IP address to your databases, you should input the wildcard sign - "%" - without the quotes. Click on the Add Host button when you have the desired host set in the field.

 

 

 

How to Delete a Database

If you are deleting a script or would like to restart a new database, the first step is to delete the old MySQL database. To do so, please do the following :

    Click MySQL Databases
    Under Current Databases, you will find a list of all your current databases.
    Click "Delete Database" next to the name of the database you would like to delete.

You have now deleted a MySQL Database.

 

Further reading

 Default Home Directory Folders

 Getting Familiar With the cPanel User Environment

 How Do I Create and Delete a Parked Domain

 How do I create and remove an Addon Domain?

 How to Backup My Website in cPanel

 How to Create a SubDomain in Cpanel

 How to Create a Website/Domain Redirect in cPanel

 How to Reset or Change to a New CPanel Password

 How to Set Up and Delete a Cron Job

 How to Setup Google Apps MX, CNAME and SPF records

 

How to repair a broken database in CPanel

You could have a problem of MySQL queries or tables stop working

If you get an error saying, "..........marked as crashed and needs to be repaired," you can easily fix this in cPanel.

Even if you are getting a different kind of error like "supplied argument is not a valid" or "table attribute does not exist", you can also use this test to try and fix the problem.

Please follow the proceedure below to fix a database error.

   Please back up any database on the account before making changes)

 

  1. Log in to your cPanel account.
  2. Under the Database Tools click on phpMyAdmin.
  3. On the left will be a list of databases. Select the one to repair/optimize.
  4. This will load all of the tables in your database into the right side. At the bottom of this list, check the "Check All" checkbox.
  5. Just to the right of the Check All link, select the "Repair" option in the drop-down list.
  6. This will list all the tables, with "OK" listed next to them. When this is done, look up to the top of the page and click on the "Structure" tab.
  7. Once the process has finished the page will revert back to step 4.
  8. Click "Check All" again, and then choose "Optimize" in the drop down menu.
Now the process is done!

Many MySQL errors can be fixed by this process. It is recommended to perform these steps frequently to keep any database in good condition.

If the error is not gone, please contact Todhost via ticket with the details.
 

 

Restore mysql database tutorial

This tutorial will take you through the process of restoring a adatabase you have already backed up using the phpmyadmin tool in cpanel,

To restore (import) a database via phpMyAdmin, first choose the database you'll be importing data into. This can be done from the corresponding menu on the left. Then click the Import tab:

You have the option of importing an .sql file. Choose the file from your local computer. Note that you are given the option to pick the character set of the file from the drop down-menu just below the upload box. If you are not certain about the character set your database is using just leave the default one.

In order to start the restore click on the Go button at the bottom-right. A notification will be displayed upon a successful database import.

 

Is There a Limit to MySQL Database Size?

There is no limit for the size of databases.

MySQL is only limited to the size of your cPanel's disk space.

 

Further reading:

 

 

MySQL User Has No Privileges

 
You can have a situation where your MySQL user has no privileges. Connection to the database is stalled by such situation. You will have to correct this by doing the following:

    Go to your MySQL Databases in cPanel.
    Re-add the user to each database by selecting the user and the database from the drop-down menus.
    Reassign the privileges.
    Click Add.
Now you now how to connect to MySQL databases
 

 

Uploading a Database

There is a simple utility tool in cPanel which can be used to upload a database or restore downloaded database in cPanel. This tool is called phpMyAdmin. It is available under the database section in cPanel.


Follow this process to use the phpMyAdmin tool to upload a database in cPanel

1.    Login to your website cPanel environment. This ill usually require authentication with a username and password.
2.    Once you are successfully logged in, scroll down to the database section and click on the phpMyAdmin icon. This ill lead to a window where you will have all your databases displayed. Depending on the configuration of your cPanel and the version you are running, you will have all databases listed. In latest version of cPanel, you will find a button to click which will display all the databases on the website control panel.
3.    Click on the database you want to populate. From the top menu bar, click on “import”, locate your database file, usually an “sql” file and then you upload the file. Once it is done successfully, you will find the green bar indicating success.


The database you upload could have been downloaded from the web or saved from localhost.
Let’s briefly look at how to download a database from a live server.

1.    Login to the cPanel control panel.
2.    Scroll down to the database section and click phpMyAdmin.
3.    Click on the database you want to download. Scroll down to the bottom and click the check all button. From the options that follow, click “export” to begin download. You need not do anything else to download an “sql” file of your database.
4.     Click “Go” to download your database. The file will save on your local computer. By default, it will save in the downloads folder.

 

Downloading  a database from localhost

To download a database from localhost, let us assume you are using WampServer, you will follow these steps:
1.    Start WampServer on your local machine.
2.    Click on the phpMyAdmin tool
3.    Click on the database you wish to export.
4.    Scroll down to the bottom and check all the database tables.
5.    Click on the export button to begin download.
6.    Click “Go” to get started.

The database will be downloaded to your local machine and can be imported from there to a live server.

 

What if there be an error?

There are cases when you upload a database and all the tables are not uploaded or an error is reported. Normally, there will be an error message which should guide you on how to go about it.

As a  rule, you can try re-uploading your database, if the error continues, try to re-download your database. If the problem is not fixed, check your internet connection. Slow internet can create a problem for file upload.

To delete a database and re-upload, simply check all the tables again, this time, click on “Drop” and click the “Go” button.
Note, terminated databases cannot be recovered so make sure you have a rethink before you terminate. Infact, it is advisable you download to your local machine before you terminate your database.
 

 

How to Copy a Database

Login to cPanel. You will do this by providing the required username and password for authentication.

  • Locate the Database Section with your cPanel control panel.
  • Click on phpMyAdmin. You will now be presented with all the databases listed on your account.
  • Check on the database whose table you want to copy. You may want to copy the entire tables or select the tables you want to create.
  • After checking your desired table, from the dropdown or the top bar menu, click Export. On the “Export” page, click “Go” to copy database to your local computer.

 

How to Import Your Database

You can import your copied database tables into an empty database. Just follow these steps to do that:

  • Login to cPanel
  • From the Database section, click on phpMyAdmin
  • Select the database into which you intend to import tables.
  • From the top menu bar in cPanel, click on IMPORT.
  • Locate the exported file, it should be an sql file, and clic “Go”

Your database will be uploaded and you will have a new set of database table.

 

How to Rename database tables

From the database section, you will find phpMyAdmin. Click phpMyAdmin.

Click on the database table you wish to rename

Now click the table you wish to rename

Then click the Operations button

Enter a new name for the table here

Then click Go

That is all. You now know how to copy a database table and how to recreate your database from exported file.
  • 44 Users Found This Useful
Was this answer helpful?

Related Articles

I have a full backup of account through cPanel. How do I restore it?

It is recommended that you maintain healthy backup of your website and possibly download the...

How To Create, Edit, and Delete a File in CPanel Using File Manager

This tutorial will be useful for you to understand how to create a file, edit a file or delete...

How do I create and remove an Addon Domain?

An add-on domain is a separate domain name that you add to your web hosting subscription with...

How to Set Up and Delete a Cron Job

A cron job allows you to run a certain command at times set by the job. For example, you could...

How Do I Create and Delete a Parked Domain

Parked domains are additional domains which you host on your account. They will display the same...

Powered by WHMCompleteSolution