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!
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".
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.
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.
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.
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:
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.
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:
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.
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.
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.
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:
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.
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.
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.
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.
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".
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.
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.
Simeon is an experienced System Administrator with over 10 years of experience in the Web Hosting field. He is recognized unanimously as the man behind every technical innovation or improvement that HostArmada introduces. Adaptable and responsible, he solves every technical issue that we or our clients throw at him. Every so often you will find him publishing technical content on our website.