This article covers cPanel MySQL Database and User Creation.
When installing an application on your website the instructions will often request that you supply the hostname of the MySQL server, database name, username, and password. These are requested so that the application can connect to the database server (which requires the user account), access the database (which requires that the user account have permissions to access the database), and then create tables and data within the database.
This article shows you how you can generate the database, user account, and assign the user account permissions to the database.
MySQL HostName
If you are setting up an application on the same server as the MySQL server (which applies to all WebGo shared and reseller servers), then you simply need to use 'localhost' as the hostname of the MySQL server. Only in situations where the MySQL database is hosted from a remote server should you use an IP address or hostname for the MySQL server. The 'localhost' address will provide the fastest and least resource intensive connection to the MySQL server.
Database Creation
The first step is to access the MySQL Databases area of the cPanel and create the database which your application will use to store data. To do this, click on the 'MySQL Databases' icon which is located under the 'Databases' section of your cPanel.
Under the 'Create New Database' heading, enter the name of your database. Its recommended that you enter a name which identifies which system this database is for. Click on the 'Create Database' button once you are finished entering the name of the new database.
The cPanel system will display the following to confirm that the database was generated without any errors. Click on the 'Back' button at the bottom to return to the MySQL databases area of the cPanel.
Under the 'Current Databases' table you will see your new database listed. Notice that the username for your account, along with an underscore character, has been added as a prefix to the name of the new database. The cPanel system forces your databases to include this prefix so that it may distinguish your database from ones owned by other users on the server which have the same name.
User Creation
The next step is to create the user account which your application will use whenever it makes a connection to the database server.
Under the 'Add New User' heading, enter the username you wish to use to access the database, and then enter the password for this new user twice. Click on the 'Create User' button after you have finished entering the username and password.
If you wish, use the same name for the user as you did for the database. It is best security wise to 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. Naming the database and user the same makes it easy to keep track and manage the accounts.
The cPanel system will display something similar to the following to confirm that the new user account was created successfully.
Permission Assignment
Once you have created a new MySQL database and user account, the last step you need to perform is to assign that user with permissions to the database. Under the 'Add User to Database' heading, select the name of the user account and the database. Notice that the user account you created also begins with your username as a prefix. This is done for the same reasons it is done to the database name by cPanel.
Click on the 'Submit' button once you have chosen the database and username.
On the next page you will be presented with specified privilege types which you may allow the user to perform on the database. If the application you are installing specifies exactly what MySQL permissions are necessary, make sure you select only these permissions, otherwise click on the 'All Privileges' checkbox at the top to select all the permissions. After you are finished, click on the 'Make Changes' button to complete the privilege assignment.
NOTE: These permissions are useful in providing tighter security for your applications when possible. For instance if you are custom-developing an application with a backend administration area, and a front-end website interface which only reads information from the database, it may be wise to create two separate MySQL user accounts. One account could be used by the frontend portion of the system with only 'SELECT' permissions (SELECT is used by MySQL to read data from a database), while the backend portion of the system could have full access. If a hacker were to somehow exploit your frontend system, they would only be able to read information from the database, not delete or update existing records.
The cPanel system will display a message similar to this to confirm that the permissions were successfully added without error.
