How To… Create MySQL Database and User in cPanel

How To...Published December 20, 2011 at 10:47 am 2 Comments

They are many applications that require you to manually create a MySQL database on your hosting account. Most of these applications will request this database information (such as the hostname, username, and password) even prior to installing correctly. This post will guide you through the steps needed in order to create a MySQL database correctly within a cPanel based environment.

Setting the hostname

If your application is being setup on the same server that your MySQL database is hosted on (this is most likely the case), then you can simply set the hostname of your new MySQL database to “localhost”. If the MySQL database you are planning on using resides on a remote server, then you would set the hostname of your new database to the IP address of that remote MySQL server. It is advised that you use a locally available database, however, as this provides the fastest and least resource intensive connection to said database.

Creating the database

Navigate to the “MySQL Databases” function under the Databases section in cPanel.

Under the ‘Create New Database’ heading, enter the name of your database which identifies which system this database is for. For the purposes of this guide, we will simply call it “newdb”. Once satisfied with the name, click “Create Database”

If the new database (in this case, “newdb”) was created successfully, you will receive the following confirmation notice:

Click on the ‘Back’ button at the bottom of the page to return to the MySQL databases overview section in cPanel. Under the ‘Current Databases’ section you will see the database you just created. Notice the name of the newly created database.

The username for your cPanel account (we will call it johndoe in this case), along with an underscore character, has been added as a prefix to the name of the database. cPanel forces this naming convention so it can differentiate this DB from others on the same server with the same name.

Creating a user

The next step is to create a user for this database. This step is important because your application(s) will utilize this user account to connect to the database you just created. Choose a username and corresponding password and enter it in the fields as shown below (we will simply call this user “newuser”):

You can create a username with the same name as the database if you prefer. Naming the database and user the same makes it easy to keep track and manage the accounts.

Security tip:  It is suggested that you ensure that each application you install on your website has its own MySQL user account, and that the MySQL user account only has access to that one single database.

Assigning permissions to a user account

This is arguably the most important step to creating a fully functional database. Under the “Add User to Database” section of cPanel, select a user name and a corresponding database name.

Notice how cPanel has added cPanel account name as the prefix to the user name. This is done for the same reasons as the database name, it differentiates this username from all others on the same server. Click on the submit button once done.

The next page of this process lists specific privilege types that you can assign to the specific user. This will designate what actions that user can preform on the actual database itself.

Security tip: If the application you are installing specifies what types of permissions it will require, make sure to select only those. If you are unsure which permissions your application will require, you can simply select “All Privileges”.

cPanel will display the following message once the user was added to the database correctly:

That’s it! You have successfully created the necessary database for your new application

If you require further assistance with creating a database, simply let us know by leaving a comment on our Facebook Page, sharing your opinion below, or tweeting us at @VPSLatch