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. 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 highlightning and validation and some tools even have graphical representations of your databases (for example, showing Entity Relationship Models). In this recipe, 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 complete this recipe, 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 recipes found in this chapter. 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 (refer to Chapter 12, Providing Web Services for instructions). In addition, you need to have enabled the EPEL repositories for installing the correct software packages (refer to recipe Using a third-party repository in Chapter 4, 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.
In this recipe, we will first show you how to install and configure phpMyAdmin
for remote access and afterwards how to do the same for phpPgAdmin
.
To install and configure phpMyAdmin, perform the following steps:
yum install phpMyAdmin
phpMyadmin
configuration file:cp /etc/httpd/conf.d/phpMyAdmin.conf /etc/httpd/conf.d/phpMyAdmin.conf.BAK
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"
sed -i "s,(Require ip 127.0.0.1),1
Require ip $NET,g" /etc/httpd/conf.d/phpMyAdmin.conf
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
Following are the steps to install and configure phpPgAdmin:
yum install phpPgAdmin
phpPgAdmin
main configuration, make a backup of it first:cp /etc/httpd/conf.d/phpPgAdmin.conf /etc/httpd/conf.d/phpPgAdmin.conf.BAK
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 Require ip $NET,g" /etc/httpd/conf.d/phpPgAdmin.conf
phpPgAdmin
main page:http://192.168.1.12/phpPgAdmin
In this fairly simple recipe, 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 recipe. 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).