Manage MySQL databases with phpMyAdmin

The command-line interface of a database management system like MySQL is not particularly intuitive and user friendly, presenting a barrier for my use of it. PhpMyAdmin (tutorial1tutorial2)was created to solve this problem. It is important, however to be aware of security considerations, because of its direct access to all of your databases.

Prerequisites

As a widely-deployed PHP application phpMyAdmin is frequently targeted for attack. For this reason, you should start with a SSL-secured website to connect to phpMyAdmin, as a minimum security measure.

Make sure to include php.

Topics on this page

As part of the basic server setup the phpMyAdmin package was installed in /usr/share/phpmyadmin. You can access the application from https://webdomain/phpmyadmin by creating a symbolic link to the root directory as defined in your NGINX server-block.

$ sudo ln -s /usr/share/phpmyadmin /home/webdomain/public_html

You can now login with any valid mySQL user and password. The first time, you can login with root (tutorial). After creating additional users you can login with those, getting acces according to the privileges of the particular user.

Secure phpMyAdmin

It is wise to take som extra security measures to reduce the risk of unwanted vistors. One simple precaution is to change the name of the symbolic link, that refers to phpMyAdmin. Go to the root directory where the phpmyadmin symbolic link lives and simply rename the link.

$ sudo mv phpmyadmin nothingtosee

You need now login at https://webdomain/nothingtosee and are at least less visible for brute force bot-attacks that seaech for phpmyadmin access.

If needed, an additional layer of authentication can be added as described in tutorial1 or tutorial2.

Manage MySQL Databases

The phpMyAdmin tool is a very convenient way to manage all your databases on the server. When you enter as root, or better as a superuser with full access to the mySQL database system, you can manage all your users and databases form a single point of access. As you can manage your websites with ssl-acces, for example with Smartty, you can manage your databases with phpMyAdmin.

MySQL server has a layered system of authorizations. The system database hosts a number of administrative tables. Depending on the scope levels, mysql users and privileges are registered in one of five administrative tables (see: MySQL tutorial for further details):

  • “mysql.user” – Stores privileges granted at the global level, which will apply to all databases on the server.
  • “mysql.db” – Stores privileges granted at the database level, which will apply to all tables in the specified database.
  • “mysql.table_priv” – Stores privileges granted at the table level, which will apply to all columns in the specified table.
  • “mysql.columns_priv” – Stores privileges granted at the column level, which will apply only to the specified column.
  • “mysql.procs_priv” – Stores privileges granted at the routine level, which will apply only to the specified stored function procedure.

With phpMyAdmin, however, you never need to access the tables, but can simply manage user access and privileges trough the webinterface, which is fairly self explanatory.