Managing MySQL or PostgreSQL databases in cpanel

Clicking on the MySQL or PostgreSQL icons on the main cPanel screen will allow you to create or work with those types of databases. The interfaces that cPanel uses for both types of databases are remarkably similar and since most hosts only offer MySQL, we will focus on that (noting where the PostgreSQL interface differs from the one for MySQL).

There are four main sections to the MySQL/PostgreSQL cPanel screen (referred to as the database screen from now on). The first section allows you to create and work with the databases themselves. The section below that allows you to create users that will be able to access one or more databases. Next, if your web host permits, you can set up remote access to your databases. Finally, there is a link to phpMyAdmin or phpPgAdmin, web interfaces for advanced work with your databases.

There are four main sections to the MySQL/PostgreSQL cPanel screen (referred to as the database screen from now on). The first section allows you to create and work with the databases themselves. The section below that allows you to create users that will be able to access one or more databases. Next, if your web host permits, you can set up remote access to your databases. Finally, there is a link to phpMyAdmin or phpPgAdmin, web interfaces for advanced work with your databases.

Creating a Database

Creating databases and users is easy but not the most intuitive process. The first thing you need to do to create a database is actually the last item in the database section. Type the name of the database into the Db: field and then click on the Add Db button. A database will be created and its name will be displayed at the top of the page. The actual name of the database will be slightly different from the one you choose. The database will be created with your cPanel username and an underscore character added to the beginning of the name you chose when you created the database, for example, domain_mydatabase.

Creating a Database User

Creating a database just creates an empty shell, waiting for data. Before you can use the database, you will need to put at least one user into the database. To create a new user, you need to type a username and password into the user section of the database screen and then click Add User. This will create a user with the password you define. Never leave the password field blank. The database username will be your cPanel username followed by an underscore and then the username you chose. For example, domain_jeff

Adding a User to a Database

You aren't done yet. You now have to associate at least one user with a database. Just above the Add Db field is a set of drop-down lists. The first of these allows you to select a user that you have already created and the second allows you to select the database that you want to associate with that user. Click Add User to Db and you are done. You can add a single user to multiple databases if you want to.

Once you add a user to the database you've created, you will notice that the listing for that database gets more detailed. It will show you the database name followed by some links to delete or repair the database and then it will show you every user in that database, along with sample code that shows you how to connect to the database in a PHP, Perl, or JDBC script (JDBC is only listed if your web host offers Java support).

Removing or Repairing a Database

Next to the username is a button marked Delete, but this can be deceiving because deleting a user from a database only removes the user from that database (and any records the user had in that database); it doesn't affect the actual user you created, which can still be active in other databases. Also, there is a Check button that allows you to check that database for problems, and then click Repair if it reports errors. If that doesn't do the trick, contact your web host for support.