Working with the MariaDB or Postgres command-line shell is sufficient for performing basic database administration tasks, such as user permission settings or creating simple databases as we have shown you in this chapter division. The more complex your schemas and relationships between tables get and the more your data grows, the more you should consider using some graphical database user interfaces for better control and work performance. This is also true for novice database administrators as such tools provide you with syntax highlighting and validation and some tools even have graphical representations of your databases (for example, showing Entity Relationship Models). In this process, we will show you how to install two of the most popular graphical open-source database management software for MariaDB and PostgreSQL on the market, namely phpMyadmin and phpPgAdmin, which are web-based browser applications written in PHP.
To Start With: What Do You Need?
To complete this process, you will require a working installation of the CentOS 7 operating system with root privileges, a console-based text editor of your choice, and a connection to the Internet in order to facilitate the download of additional packages. It is expected that your MariaDB or PostgreSQL server is already running using the processes found in this chapter division. Also, you will need a running Apache web server with PHP installed, which must be accessible from all the computers in your private network to deploy these applications. In addition, you need to have enabled the EPEL repositories for installing the correct software packages (refer to process Using a third-party repository, Managing Packages with YUM). Finally, you will need one computer in your network with a graphical window manager and a modern web browser to access these web applications
The Process
In this process, we will first show you how to install and configure phpMyAdmin for remote access and afterwards how to do the same for phpPgAdmin.
Installing and configuring phpMyAdmin in CentOS
To install and configure phpMyAdmin, perform the following steps:
- Type in the following command to install the required package:
yum install phpMyAdmin - Now create a copy of the main phpMyadmin configuration file:
cp /etc/httpd/conf.d/phpMyAdmin.conf
/etc/httpd/conf.d/phpMyAdmin.conf.BAK - Next, open the main phpMyAdmin.conf configuration file and add the line Require ip XXX.XXX.XXX.XXX/XX with your defined subnet’s network address you want to grant access to the web application—for example, Require ip 192.168.1.0/24 below the line Require ip 127.0.0.1. You have to do this twice in the file or you can use sed to do this automatically, as shown here. On the command-line define the environment variable NET= accordingly to fit it to your own subnet’s network address.
NET="192.168.1.0/24" - Then type the following line to apply your changes to the configuration file:
sed -i "s,\(Require ip 127.0.0.1\),\1\nRequire ip $NET,g"
/etc/httpd/conf.d/phpMyAdmin.conf - Afterwards, reload your Apache server and now you should be able to browse to the phpMyAdmin website from any other computer in your subnet using the server’s IP running the web application, for example 192.168.1.12 (log in with your MariaDB administrator user called root or any other database user):
http://192.168.1.12/phpMyAdmin
Installing and configuring phpPgAdmin in CentOS
Following are the steps to install and configure phpPgAdmin:
- Type in the following command to install the required package:
yum install phpPgAdmin - Before editing the phpPgAdmin main configuration, make a backup of it first:
cp /etc/httpd/conf.d/phpPgAdmin.conf /etc/httpd/conf.d/phpPgAdmin.conf.BAK - Allowing remote access to phpPgAdmin is very similar to phpMyAdmin. Here you can also add a Require ip XXX.XXX.XXX.XXX/XX line with your defined subnet’s network address below the line Require local in the phpPgAdmin.conf file, or use the sed utility to do this automatically for you:
NET="192.168.1.0/24"
sed -i "s,\(Require local\),\1\nRequire ip $NET,g"
/etc/httpd/conf.d/phpPgAdmin.conf - Restart Apache and browse to the phpPgAdmin main page:
http://192.168.1.12/phpPgAdmin
How Does It Work?
In this fairly simple process, we have shown you how to install two of the most popular graphical administration tools for MariaDB and Postgres, running as web applications in your browser (and written in PHP) on the same server where your database service is running, and enabled remote access to them.
So what did we learn from this experience?
Installing phpMyAdmin for administering MariaDB databases and phpPgAdmin for Postgres databases was as easy as installing the corresponding rpm packages using the yum package manager. As both the tools are not to be found in the official CentOS 7 repositories, you need to enable the third-party repository EPEL before you can access and install these packages. By default, when installing both the web applications, access is denied to any connection not being made from the server itself (local only). Since we want to have access to it from different computers in our network, having installed a web browser you need to allow remote connections first. For both the web applications, this can be achieved using the Apache Require ip directive which is part of the Apache mod_authz_core module. In both the configuration files for phpMyAdmin and phpPgAdmin, we defined a whole subnet, such as 192.168.1.0/24, to allow connecting to the server, but you can also use a single IP address here which you want to allow access to. The sed commands inserted these important Require lines into the configuration file, but as said earlier you can also do this manually if you like by editing these files with your text editor of choice. After reloading the Apache configuration, you were then able to browse to the web pages using the two URLs shown in the process. On the start page of both the web sites, you can use any database user to log in without the need to enable remote privileges for them; any user with local permissions is sufficient.
In summary, we can say that we only showed you the basic configuration of both administration tools. There is always more to learn; for example, you should consider securing both PHP websites with SSL encryption or configuring your instances to connect to different database servers. Also, if you prefer desktop software for managing your databases, have a look at the open-source MySQL Workbench Community Edition, which can be downloaded from the official MySQL website for all major operating systems (Windows, OS X, Linux).
