How to Install and Upgrade phpMyAdmin for Apache on Ubuntu 18.04

Introduction

phpMyAdmin is a free software tool written in PHP and was created so that users can interact with MySQL through a web interface. While there are many installation guides available, I have found none that properly indicate that phpMyAdmin from the default Ubuntu repositories is not compatible with PHP 7.2. In this guide, we’ll discuss how to install and upgrade phpMyAdmin for PHP 7.2 so that you can safely use it to manage your databases on an Ubuntu 18.04 system.

Please be aware it is a widely-deployed PHP application which is frequently targeted for attacks, you should never run phpMyAdmin on remote systems over a plain HTTP connection.

Requirements

You will need a server with Ubuntu 18.04 configured with a LAMP (Linux, Apache, MySQL, and PHP) installation, as well as a firewall such as UFW and a non-root user with sudo privileges.

Installing phpMyAdmin

First, we will update your servers package index and install phpMyAdmin from the default Ubuntu repositories.

sudo apt update && sudo apt install phpmyadmin

You will be asked a few questions in order to configure your installation correctly.

  • For the server selection when the prompt appears, apache2 is highlighted, but not selected. hit SPACE to select apache2 and ENTER to confirm and continue.
  • Select Yes when asked whether to use dbconfig-common to set up the database.
  • You will then be asked to choose and confirm a MySQL application password for phpMyAdmin.

The phpMyAdmin Apache configuration file will be saved into the /etc/apache2/conf-enabled/ directory, where it is read automatically.

Afterwards, restart Apache for your changes to be recognized:

sudo systemctl restart apache2

phpMyAdmin is now installed. However, further configurations are needed before you can log in and begin interacting with your MySQL databases.

User Authentication and Privileges

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. phpMyAdmin automatically created a database user called phpmyadmin with the administrative password you set during installation which performs certain underlying processes for the program. It’s recommended that you log in as either your root MySQL user or as the default phpmyadmin MySQL user with the proper privileges to managing databases through the phpMyAdmin interface.

Access as root

To log in to phpMyAdmin as the root MySQL user, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal.

sudo mysql

Check which authentication method each of your MySQL user accounts uses with the following command.

SELECT user,authentication_string,plugin,host FROM mysql.user;

Output:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *8486437DE5F65ADC4A4B001CA591363B64746D4C | mysql_native_password | localhost |
| phpmyadmin       | *5FD2B7524254B7F81B32873B1EA6D681503A5CA9 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)

In the above, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change your password to a strong password.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect.

FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin.

Output:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *8486437DE5F65ADC4A4B001CA591363B64746D4C | mysql_native_password | localhost |
| phpmyadmin       | *5FD2B7524254B7F81B32873B1EA6D681503A5CA9 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)

You can see from this output that the root user will authenticate using a password. You can now log in to the phpMyAdmin interface as your root user with the password you’ve set for it here.

Access as phpmyadmin

To log in to phpMyAdmin with the default MYSQL user phpmyadmin, you will need to grant the required privileges. To do this, open up the MySQL prompt from your terminal.

sudo mysql

Grant the required privileges for user phpmyadmin.

GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect.

FLUSH PRIVILEGES;

You can now log in to the phpMyAdmin interface with the default MYSQL user phpmyadmin with the password you’ve set during the initial package installation.

Manually updating phpMyAdmin

As of writing this, the phpMyAdmin version available in the Ubuntu package manager (4.5.4) or phpMyAdmin PPA (4.6.6) does not fully support php7.2, which leads to the annoying count() warnings. The solution is to update to the latest phpMyAdmin version, which is 4.8.3 at the time of writing.

We will be doing the following:

  • Make a backup copy of current installation.
  • Download and extract the latest version of phpMyAdmin.
  • Copy the old config.inc.php to our new installation.
  • Modify the vendor_config.php file to set the appropriate tmp directory for phpMyAdmin.
  • Add a longer random string to resolve blowfish error messages and allow proper cookie authentication.

To stay as close as possible to Ubuntu’s package installation settings with our manual upgrade, the following changes will need to be made. These terminal commands should get the job done (use sudo if applicable):

mv /usr/share/phpmyadmin /usr/share/phpmyadmin_old
mkdir /usr/share/phpmyadmin
cd /tmp && wget https://files.phpmyadmin.net/phpMyAdmin/4.8.3/phpMyAdmin-4.8.3-all-languages.tar.gz
tar -xf phpMyAdmin-4.8.3-all-languages.tar.gz -C /usr/share/phpmyadmin --strip-components=1

Next we need to move the original config.inc.php from the initial package installation to our installation folder:

cd /etc/phpmyadmin && cp config.inc.php /usr/share/phpmyadmin/

Open the file /usr/share/phpmyadmin/libraries/vendor_config.php. Make the following changes on or around line 38:

//define('TEMP_DIR', './tmp/');
define('TEMP_DIR', '/var/lib/phpmyadmin/tmp/');

Save the file.

Open the file /var/lib/phpmyadmin/blowfish_secret.inc.php. Find the following:

$cfg['blowfish_secret'] = 'Secret_String';

Add additional randomness to the $cfg['blowfish_secret'] string, at least 40 characters. Example:

$cfg['blowfish_secret'] = '7NfSjYezwmwGCfGDuDO7uWn4ESw2sCFCym1RatPjGCfGCym1RatPjGCfG';

Save the file.

You may remove the backup copy of the old phpMyAdmin version as it no longer will be needed if you like.

rm -rfv /usr/share/phpmyadmin_old

You can now access the web interface by visiting your server’s domain name or public IP address followed by /phpmyadmin. Log in to the interface, either as root or with the default phpmyadmin whichever you configured.

https://your_domain_or_IP/phpmyadmin

Conclusion

You should now have phpMyAdmin configured with the latest version ready to use on your Ubuntu 18.04 server. Using this interface, you can easily create databases, users, tables, etc., and perform the usual operations like deleting and modifying structures and data.