1. Home
  2. Databases
  3. How to Manage MySQL and PostgreSQL Databases

How to Manage MySQL and PostgreSQL Databases

The features described in this article will be deployed very soon. In the meantime, please refer to the article How to manage databases with Maria DB.

Introduction

The MG panel provides direct access to interfaces for MySQL and PostgreSQL website database management. You can create and delete databases, add MySQL users with privileges, and much more.

In what follows, we will confine ourselves to information on using the MySQL and PostgreSQL interfaces for beginner users.

MySQL database users may also refer to the article How to Manage Databases with PhpMyAdmin.

If you are working with a MySQL database or a PostgreSQL database (usable with phpPgAdmin), the hostname to specify is 127.0.0.1.

Note : The Glossary contains explanations on multiple topics and can be consulted to clarify certain terms.

Prerequisites

Access to https://mg.n0c.com/en/.

Concepts to be Known

Details on MySQL Databases Users Privileges

Throughout this article, some English terms related to MySQL databases users privileges will come up often. We invite you to refer to the table below to understand their meaning.

Privilege management does not work in the same way for PostgreSQL databases. In the case of MySQL, specific privileges can be granted via the interface, which is not the case with PostgreSQL. This is why we only offer the possibility of associating a user with the PostgreSQL database.

PrivilegeDefinition
ALTERModify properties (ALTER AGGREGATE, ALTER CONVERSION, etc.).
ALTER ROUTINEModify the definition of a routine, that can be an aggregate function, a normal function or a procedure. Routines are SQL objects that allow defining a logic to apply to data.
CREATEDefine a table (CREATE TABLE), a new database (CREATE DATABASE), etc.
CREATE ROUTINEGroup stocked functions and procedures. 
CREATE TEMPORARY TABLESCreate a temporary table. A temporary table exists only in the session that created it. 
CREATE VIEWDefine a view. A view is a virtual table; in other words, it is a table whose data are not stored in the database, and containing informations from many tables.
DELETEDelete lines in a table. By using commands associated to WHERE, it is possible to select the lines that will be deleted.
DROPPermanently delete a table from a database (DROP TABLE), a role (DROP ROLE), etc.
EVENTAllow to obtain data to monitor or solve problems in SQL.
EXECUTEExecute a prepared instruction.
INDEXPerform commands associated to indexes such as CREATE INDEX. With an index placed on one or many columns, a database system can first search data on the index and, if what is searched is found, it knows more rapidly where are the records.
INSERTInsert data in a table.
LOCK TABLESLock a table.
REFERENCESThe reference permission on a table allows to create a foreign key that refers to this table; this way, administrators can perform required modifications without having to take into account the needs of the persons to whom the table has been referenced.
SELECTReturn the records in a result table. This command can select one or many columns in a table.
SHOW VIEWDisplay the value of a executions parameter.
TRIGGERExecute a set of SQL instructions right after an event.
UPDATEPerform modifications on existing lines. Often, this command is used with WHERE to specify on which lines the modification is applicable.
Privileges

Icons for Managing Passwords

When you have to create or change passwords, there are some icons that will be very useful. The table below explains their meaning.

IconDescription
1Hide (or display) password.
2Password complexity (a red icon shows that the password is not secure enough).
3Generate a password.
4Copy the password.
Icons for managing passwords

Access to MySQL and PostgreSQL Interfaces

To open MySQL, choose Databases -> MySQL in the contextual menu to the left:

MySQL interface in Panel MG

To open PostgreSQL, choose Databases -> PostgreSQL in the contextual menu:

PostgreSQL interface in Panel MG

These interfaces are very similar. In both cases, you will notice that CURRENT DATABASES are displayed at the top of the window and CURRENT USERS are listed at the bottom.

Database Creation

Create a MySQL Database with a New User

  1. Access to MySQL databases interface and click on the Create button:
  1. Enter the name of the DATABASE:
  1. It is recommended to create a database user at this point. Check Create Database User checkbox.
  2. Provide the USERNAME:
  1. Provide the PASSWORD.
  2. Set the user’s privileges by checking or unchecking the appropriate boxes.
  3. Click on the CREATE button.
  4. Confirmation messages are displayed and the database is added to the list of CURRENT DATABASES.

Create a PostgreSQL Database with a New User

  1. Access to PostgreSQL databases interface and click on the Create button:
  1. Enter the name of the DATABASE:
  1. It is recommended to create a database user at this point. Check Create Database User checkbox.
  2. Provide the USERNAME.
  3. Provide the PASSWORD.
  4. Click on the CREATE button.

Create a Database User

Access the MySQL or PostgreSQL database interface.

  1. In the CURRENT USERS section, click on the Create button:
  1. Fill in the USERNAME field:
  1. Provide a PASSWORD.
  2. Choose the ASSOCIATED DATABASE from the drop-down list.
  3. For MySQL databases only, set user privileges in the appropriate boxes:
  1. Click on the CREATE button.
  2. Confirmation messages are displayed and the user is added to the list of CURRENT USERS.

Modification of a Database User’s Username or Password

Access the MySQL or PostgreSQL database interface and locate the user whose name or password you wish to change in the CURRENT USERS section.

  1. Click on the Rename or change password icon :
  1. Enter the new USERNAME and/or PASSWORD in the appropriate field:
  1. Click on the SAVE button.
  2. Confirmation messages are displayed.

Adding a User to a Database

Typically, several users will need to connect to the database. You need to add them one by one.

Access the MySQL or PostgreSQL database interface and locate the database to which users are to be added in the CURRENT DATABASES section.

  1. Click on the Add another user icon, as in this example for a MySQL database:

If it is a PostgreSQL database, the interface will look like this:

  1. Select the USER name from the drop-down list:
  1. Click on the ADD button.
  2. A confirmation message is displayed.
  3. The user is displayed alongside the corresponding database.

Modifying MySQL Database User Privileges

MySQL database user privileges can be modified at any time.

Access the MySQL database interface and locate the database for which privilege changes are to be made.

  1. Click on the Modify user privileges icon:
  1. Check or uncheck the appropriate boxes:
  1. Click on the SAVE button.
  2. A confirmation message is displayed.

Deleting a User

It is advisable to delete users who no longer use your databases.

Access the MySQL or PostgreSQL database interface and locate the user to be deleted in the CURRENT USERS section.

  1. Click on the Delete icon:
  1. Approve the deletion at the command prompt.
  2. A confirmation message is displayed.

Remove User Access to a Database

It is essential to keep your database users’ access rights up to date. It is both pointless and risky to neglect to remove rights from a user you do not expect to work with.

Access the MySQL or PostgreSQL database interface and locate the database and user whose privileges are to be withdrawn.

  1. Click on the Delete icon, as in this example for a MySQL database:

If it is a PostgreSQL database, the interface will look like this:

  1. Approve the deletion at the command prompt.
  2. A confirmation message is displayed.

Deleting a Database

It is advisable to delete your databases if they are no longer in use.

Access the MySQL or PostgreSQL database interface and locate the database in the CURRENT DATABASES section.

Click on the Delete icon, as in this example for a MySQL database:

If it is a PostgreSQL database, the interface will look like this:

References

How to Manage Databases with PhpMyAdmin.

How to Import/Export Database in Command Line (User Mode).

Permissions (Database Engine).

Updated on October 30, 2024

Related Articles