Monday, 17 Jun 2019
Applications Technology

Manage Remote MySQL Databases via phpMyAdmin on Ubuntu 17.04 / 17.10

phpMyAdmin allows you to manage MySQL or MariaDB databases via a simple web browser. In most environments, phpMyAdmin package is installed on the same server as the database server… so not much configurations are needed there… it should just work.

However, if you want to manage MySQL or MariaDB databases on a remote server via phpMyAdmin web interface, you’ll need to make some changes in its configuration files.

In order to access and manage remote MySQL or MariaDB servers, you must configure the servers to allow remote access over the network. The steps below will show you how.

This brief tutorial is going to install and configure phpMyAdmin on Ubuntu 17.04 / 17.10 to access and manage a remote MySQL or MariaDB database server. When you’re ready, please continue with the steps below:

Step 1: phpMyAdmin & Database on Same Host

Traditionally, phpMyAdmin and the MySQL / MariaDB servers are installed on the same host. This is the standard installation and the most popular.

To install phpMyAdmin on the same host as the database server, run the commands below

sudo apt update
sudo apt install php libapache2-mod-phpmyadmin

During the installation, you should get a prompt to choose which web server to be configured for phpMyAdmin. For this post, we will use Apache2 for the web server.

After the installation, open your browser and go to http://servername/phpmyadmin

Replacing servername with the server’s actual hostname. At the login, page enter root for the username and logon with the password.

This will get you logged-in and let you manage the databases on the server.

Step 2: phpMyAdmin & Database on Different Hosts

Step 1 shows the standard phpMyAdmin installation… however, when the database server you want to manage is remote, you’ll have to configure phpMyAdmin differently.

The configuration files for phpMyAdmin are located in /etc/phpmyadmin. The main configuration file is /etc/phpmyadmin/config.inc.php. This file contains configuration options that apply globally to phpMyAdmin.

To use phpMyAdmin to administer a MySQL database hosted on another server, adjust the following in /etc/phpmyadmin/config.inc.php:

sudo nano /etc/phpmyadmin/config.inc.php

Then change the line that looks like the one below

$cfg['Servers'][$i]['host'] = '$dbserver';

To

$cfg['Servers'][$i]['host'] = '192.168.71.21';

Replace $dbserver with the actual remote database server name or IP address. Also, be sure that the phpMyAdmin host has permissions to access the remote database.

Another important configuration file is /etc/phpmyadmin/apache.conf, this file is symlinked to /etc/apache2/conf-available/phpmyadmin.conf, and, once enabled, is used to configure Apache2 to serve the phpMyAdmin site. The file contains directives for loading PHP, directory permissions, etc. From a terminal type:

sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
sudo a2enconf phpmyadmin.conf
sudo systemctl reload apache2.service

Step 3: Configure MySQL Server to Allow Remote Access

Now that phpMyAdmin is installed on the client computer, connect to the remote server where the MySQL / MariaDB database is installed… then run the commands below to open its default configuration file.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Then change the line below to:

bind-address           =              0.0.0.0

Next run run the commands below to allow the root user to access the server from the client machine.

sudo mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.71.20' IDENTIFIED BY 'root_password_here' WITH GRANT OPTION;

Replace the IP address with the address you’re connecting from. Exit and you’re done.

After editing the file above, save your changes and logon to http://clientPC/phpmyadmin

Replace http://clientPC/phpmyadmin with the client computer IP or hostname.

This should allow you to logon remotely to the server from the client phpMyAdmin web portal.

This is how how to manage remote MySQL / MariaDB servers.

phpmyadmin logon page on ubuntu

Congratulations! You’ve successfully configured phpMyAdmin

ubuntu phpmyadmin

Enjoy!

You may also like the post below:

Install Webmin / Virtualmin on Ubuntu 16.04 / 17.04 / 17.10