How to manage your MySQL databases in cPanel?

Introduction #

Today, nearly every website available on the World Wide Web is utilizing some sort of structured storage system, often called a database and used to dynamically store and retrieve information. The most used database system, at this point, is called MySQL. This is an open-source relational database management system, used for managing SQL types of databases. It is supported and installed by default on all our Services and every client can take advantage of it absolutely for free. In the following lines of this tutorial, we will review the complete process of managing your MySQL databases using the cPanel control panel that we provide for free with every Web Hosting package that we offer!

Accessing the MySQL Databases feature in cPanel #

The very first thing you will need to do when talking for any of the features provided in cPanel is to actually login to the control panel. If you are not familiar with how that should happen, please check our other tutorial on the matter – “How to access cPanel“.

Great! Now that you see the main page of the cPanel service, it is time to locate the feature responsible for the management of your MySQL databases. It is located in the “Databases” section of features and it is called “MySQL Databases“.

Accessing the MySQL Feature of cPanel

This action will redirect you to a brand new page designated for the complete management of MySQL Databases, MySQL Database Users, User Privileges and other useful databases related actions which we will discuss in detail. 

Create a New Database #

The first thing you will see in the newly shown page should be a brief explanation of the “MySQL Databases” feature and the very first aspect of managing your MySQL Databases – creating a new database. Although we already explained in another tutorial How to Create MySQL Database in cPanel, this is a completely different approach since it will only create a database without associating a user with it. Now, please go ahead and fill the Database name into the provided input field. Once ready, please click on the “Create Database” button.

Fill the database name and create a new database

If there are no errors with the creation process, cPanel will provide you with a message indicating the successful creation of your brand new database. Please click the “Go back” link in order to return to the MySQL Databases page. 

Your brand new database will be then listed in the “Current Databases” section which we will review in a moment. 

Modify Databases #

The next section right after the “Create New Database” one is the “Modify Databases” section. Although the name suggests that there will be some modifications applied to your MySQL databases this is more like a maintenance section providing two actions that will improve the performance of your MySQL databases. Let’s review the available actions:

  •  Check Database – By selecting a database from the list of databases available as a dropdown for this action, you will be able to initiate a procedure that will check the selected database for errors. Once you select the correct database, please click on the “Check Database” button so the procedure can be initiated. When the checking is completed, cPanel will present a complete list of all the checked tables in your database and will indicate the state of each table right next to it in the list.
  • Repair Database – Consequence from the “Check Database” action, this action will allow you to initiate a repair procedure for a selected database from the provided drop-down list. When the database you would like to repair is selected, please click on the “Repair Database” button so the procedure can be initiated. Once completed cPanel will provide you with an appropriate message indicating the result of the performed repair procedure.
Check and repair MySQL databases

It is useful if you can turn these Check and Repair actions into a routine, performed on a regular basis since it will ensure that your databases are in their best conditions. Having healthy and well-performing databases translates into more happy website visitors.

Current Databases #

In this section, you can find a complete list of all the databases associated with your web hosting account. The information is conveniently structured in a table view consisting of 4 columns:

  • Database Name – Please notice that the complete name of your database is not only the one you have entered when creating the database but it is also prefixed with the cPanel username. 
  • Size – The total size of your database. Please note that this size might vary from the size which you might see in phpMyAdmin.
  • Privileged Users – The specific database user having privileges to operate with the current database.
  • Available Actions with the database 
Current MySQL Databases

As you might have already noticed, the only actions you can perform fro this section with a database are to “Rename” the database and to “Delete” it. Although we see no reason in explaining these actions, since they are pretty much self-explanatory it is worth mentioning that deleting a database will mean that all the data stored in the same will be deleted. As for restoration options, please check with our Technical Support Team.

Add new MySQL User #

The rest of the sections on this page are mainly focused around the MySQL Users. 

So why do you need a MySQL user when communicating with your database. The simple answer is for security. If a database management system like MySQL had no authentication feature, then everyone could request and insert information into your databases. This is not something you would want. Therefore, to be able to communicate with each individual database you will need to create a new database user and to give that user some privileges on the database.

Let’s start by creating a new user. To do that, please scroll down to the “Add New User” section and fill out the “Username” and “Password” fields. Once done, please click on the “Create User” button located at the bottom of the section. 

Create new MySQL User

If the user creation process is successful cPanel will redirect you to a new page displaying an appropriate status message. Now, please go back to the “MySQL Databases” main page by simply clicking on the “Go Back” button. 

As you might have already noticed, following the same naming convention, cPanel prefixes the MySQL Username again with your cPanel username. Please note that this is cPanel internal naming convention and it cannot be avoided nor changed. When using the database name and database username into your scripts, please use the complete name including the prefixed cPanel username. 

Add User to Database #

The next section available for your Database Users is called “Add User To Database“. Here you will be able to take a single, but very important action – associating a user with a database. To do that you will have to follow 3 basic steps:

  1. Choose a Database User from the “User” drop-down list.
  2. Choose a Database Name from the “Database” drop-down list.
  3. Click the “Add” button.
Add user to a database

cPanel will understand the action you took and will redirect you to a brand new page. Remember the database privileges we have mentioned earlier, well here is the place where you should set all those. 

At the top of the page, you will see the MySQL User and the MySQL Database you are associating. Right after these, you will see all the available privileges conveniently structured in a table view. Please select the privileges your user should have when operating with your database. If you are not certain which privileges should be chosen, simply click the “ALL PRIVILEGES” link at the top of the table so all the privileges can be granted to your MySQL user. 

Assign privileges for mysql users

Finally, please click on the “Make Changes” button in order for the privileges configuration you have set to be preserved and the process of associating a user with a database to be completed. Now, please go back to the “MySQL Databases” main page by simply clicking on the “Go Back” button. 

Current Users #

The final section from the “MySQL Databases” feature is called “Current Users“. Here you will find a complete list of all the MySQL users that you have created in your cPanel account either following this guide or our other tutorial How to create MySQL Database via cPanel. The users will be contained in a two column table where the first column is the actual Username and the second column being the available actions you can take for a concrete username. 

Current MySQL Users table

Let’s start by changing the password for a username. To do that, please find the username you would like to change the password for and simply click the “Change Password” link from the “Actions” column with features. 

Changing MySQL User Password

This action will redirect you to a new page where you will simply need to fill out the new password and then click on the “Change Password” button. Once done, you should go back to the “MySQL Databases” page and scroll down to review the next action which is called “Rename“. 

Renaming MySQL User

This feature will allow you to rename a selected user. Please bear in mind, that when you rename a user you should ensure that you will change the username in any script you have used the username previously. Avoiding that change might cause your website or scripts to produce an error related to the database connectivity. 

Finally to delete a MySQL User, simply use the “Delete” link located in the “Actions” column.

Deleting MySQL Users

You will be redirected to a new page where you will be asked to confirm the user’s deletion. Please bear in mind that there is no option to restore deleted MySQL users apart from simply recreating the user. If you wonder what the previous password for the user was, we strongly suggest checking for any scripts utilizing that username for MySQL Database authentication since most probably there you will be able to find the old password. 

The process of managing your MySQL Databases in cPanel is pretty straight forward and we hope that this tutorial will help you to better understand it. Still, if you are feeling lost or do need some assistance in managing your cPanel Databases – our Technical Support team is 24/7/365 at your disposal.

Powered by BetterDocs