Databases are commonly used by web applications to easily store and retrieve information. Many automated installations and scripts create databases and users for it. Softaculous, for example, is a leading auto-installer that is bundled with cPanel and allows for the easy installation of more than four hundred apps including the most popular CRM WordPress.
For most users, this takes away the pain from manually installing web applications and creating a database. However, there are certain applications that require the creation of a database. Other times a manual installation is preferred or needed over an automated solution.
An example of the need to create a database when automated scrips can’t be executed due to hosting restrictions. This can happen in the case of InfiniteWP the automated installation fails due to not having cron available.
Most shared hosting uses the cPanel backend. For those with dedicated hosting, cloud hosting or VPS you may need to reach out to your hosts support.
cPanels theme varies depending on the host. Some hosts like Crazy Domains and Hostgator use white-labelled interfaces that differ from the default theme. However, there is usually a way to manage the database.
The easiest way to access cPanel is to add /cPanel to the end of your domain (e.g. creativus-sites.com/cpanel). It will then redirect you to the port that cPanel is hosted on (usually 2083)
Through your host’s interface
Log into your website host and find the service where your website is hosted on. There should be a one-click login for cPanel or a direct link to SQL databases.
If you’ve accessed cPanel directly then choose MySQL databases.
Creating a database
Step 1: Create a name for it.
Creating a database is straightforward. Your cPanel username will automatically be prefixed to the database to avoid any clashes from other databases.
Afterwards, you will need to create and assign a user.
As a safety precaution never share your database name, user or credentials to anyone not authorised to work on your website. Only provide credentials to applications that you trust.
Deleting and renaming a database
Always use caution when deleting or renaming a database. This can easily break your website! Make sure to create a backup of your database before making any changes. The wordpress.org documentation has easy steps on how to backup your database through PHPmyadmin.
To delete a database click delete (2)
To rename a database click rename (3)
A confirmation dialogue will appear after the action is undertaken.
mySQL users provide access to databases for administrators and web applications. By assigning users per an application the credentials can be revoked if compromised.
Adding a user
Choose your username and password, like in the case of the database name your cPanel username will be prefixed to the username. A strong password is needed and should be alphanumerical with special characters. See 1 and choose to create a user.
Deleting a user
Make sure that the user isn’t currently being used by an application. Otherwise, this can break your site. On the other hand, if the user has been compromised delete it ASAP! Find the relevant user under current users and click delete (2).
Assigning a user [IMPORTANT]
A database without a user can not interact with your application! A common mistake is assuming that a user with the same name as the database automatically has access. Choose to add a user to the database (3) and make sure it is the right one.
Checking and removing users assigned to databases
The current databases section is where you can check if a user is assigned to a database. Make sure that a user is assigned to a database before utilising it for your own purposes.
It follows a similar logic to the current user’s section and users can be removed from the database by clicking delete.
Under the privileged user’s section you can see what users are assigned to what databases. Click the username to find out what privileges the user has been assigned to the database.
By clicking all privileges all permissions will be granted. This will allow the user to manipulate the database in any way possible and make most applications work out of the box.
Database security is a whole field in and of itself. There are technical documents from Microsoft that explain the ins and outs of database management but are quite complicated for the average user.
The best approach is to find the minimum privileges required for the application. For example in general, WordPress only needs SELECT, INSERT, UPDATE and DELETE permissions.
However, discretion and testing are always required.
For example, certain plugins need more access. InfiniteWP is an awesome tool that extends WordPress functionality and makes it easy to centrally manage multiple sites. One of its most powerful features is the ability to backup websites in bulk. However, to do so it requires full access. In this case, the benefits of its tools outweigh the risks of granting more access to the database. If you schedule backups, then you’ll always be able to fall back to an earlier version.
A note on InfinteWP
InfiniteWP actually inspired me to write this article. They offer two automated ways of installing their application easily without having to worry about databases. I personally don’t install plugins unless I have to and with the need to manage multiple websites a centralised management solution is essential and definitely worth the install.
Unfortunately, the cPanel installation wouldn’t work out of the box for me and instead of waiting back from my host to enable the cron feature, I decided to through the DIY installation method.
It’s paid version has a plethora of features, the most useful for me is being able to backup website directly to my Gsuite (Google) Drive.
This article is drawn from my own knowledge and experience with no paid sponsorship from any entity. The links to InfiniteWP are affiliate links, only because I think the program is awesome and I hope other admins/businesses owners can also see its value.