Among all the users who need the features of a database management system such as MySQL/MariaDB, some may feel the need to interact with the system in a way other than from the command line in SQL.
phpMyAdmin was created precisely to allow users to work in SQL through a web interface. In this guide, we will discuss how to install and secure phpMyAdmin so that you can use it safely to manage your databases on an Ubuntu or Debian system.
Prerequisites
To follow this guide, you will need:
- An Ubuntu server. Preferably a non-root user with administrative privileges and a configured firewall. To set this up, follow our initial server setup guide for Ubuntu.
- A LAMP stack (Linux, Apache, MySQL and PHP) installed on your Ubuntu server. If this is not done yet, you can follow this guide on installing a LAMP stack on Ubuntu 22.04.
In addition, there are important security considerations when using software such as phpMyAdmin, because it:
- Communicates directly with your MySQL installation
- Handles authentication using MySQL credentials
- Runs and returns the results of arbitrary SQL queries
For these reasons, and because it is a widely deployed PHP application that is frequently targeted by attacks, you should never run phpMyAdmin on remote systems over a plain, unsecured HTTP connection.
If you do not already have a domain set up with an SSL/TLS certificate, you can follow this guide on securing Apache with Let’s Encrypt on Ubuntu 22.04. This will require registering a domain name, creating DNS records for your server and configuring an Apache virtual host.
Step 1 — Installing phpMyAdmin
You can use APT (the Linux package manager) to install phpMyAdmin from the official Ubuntu repositories.
As a non-root sudo user, update your server's package index if you have not done so recently:
Next, you can install the phpmyadmin package. Along with this package, the official documentation also recommends installing a few PHP extensions on your server to enable certain features and improve performance.
If you followed the LAMP stack tutorial listed in the prerequisites, several of these modules will already have been installed with the php package. However, it is recommended to install these packages as well:
- php-mbstring: A module for handling non-ASCII strings and converting strings between different encodings
- php-zip: This extension supports uploading .zip files to phpMyAdmin
- php-gd: Enables support for the GD graphics library
- php-json: Provides PHP with support for JSON serialization
- php-curl: Allows PHP to interact with different types of servers using different protocols
Run the following command to install these packages on your system. Note, however, that the installation process asks you to make a few choices in order to configure phpMyAdmin correctly. We will go over these options shortly:
Here are the options you should choose when they are offered, in order to configure your installation correctly:
- For the server selection, choose apache2
Select Yes when you are asked whether you want to use dbconfig-common to configure the database.
You will then be asked to choose and confirm a MySQL application password for phpMyAdmin.
The installation process adds the phpMyAdmin Apache configuration file into the /etc/apache2/conf-enabled/ directory, where it is read automatically. To finish configuring Apache and PHP to work with phpMyAdmin, the only remaining task in this section of the tutorial is to explicitly enable the mbstring PHP extension, which you can do by typing:
Then restart Apache so that your changes take effect:
phpMyAdmin is now installed and configured to work with Apache. However, before you can log in and start interacting with your MySQL databases, you will need to make sure that your MySQL users have the privileges required to work with the program.
Step 2 — Authentication and privileges
When you installed phpMyAdmin on your server, it automatically created a database user called phpmyadmin, which runs certain underlying processes for the program. Rather than logging in as this user with the administrator password you set during installation, it is recommended to log in as a user dedicated to managing databases through the phpMyAdmin interface.
Setting up access for a dedicated MySQL user
Alternatively, some may find that it suits their workflow, as well as their security, better to log in to phpMyAdmin with a dedicated user.
To do this, open the MySQL shell again:
If you enabled password authentication for your root user, as described in the previous section, you will need to run the following command and enter your password when prompted in order to log in:
From there, create a new user and give it a strong password:
Note: Again, depending on the version of PHP you have installed, you may want to set your new user to authenticate with mysql_native_password instead of caching_sha2_password.
Next, grant your new user the appropriate privileges. For example, you could grant the user privileges on all the tables in the database, as well as the power to add, modify and delete user privileges, with this command:
After that, exit the MySQL shell:
You can now reach 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 new username and password you have just set up.
Once logged in, you will be taken to the phpMyAdmin user interface.
Step 3 - Securing web access
Because it is so widely used, phpMyAdmin is often targeted by attackers. It is therefore essential to strengthen its security to prevent any unauthorized access. One method is to use the .htaccess authentication and authorization features built into Apache.
To do this, you first need to enable .htaccess file overrides by editing the phpMyAdmin Apache configuration file.
Open the phpmyadmin.conf file located in your Apache configuration directory with your preferred text editor. Here we are using nano:
Add the AllowOverride All directive inside the <Directory /usr/share/phpmyadmin> section, as shown below:
Save and close the file once the line has been added. If you used nano, press CTRL + X, then Y and ENTER.
To apply the changes, restart Apache:
It is now time to create a .htaccess file to strengthen security.
In the application directory, create and open this file with root privileges:
Add the following information to it:
These directives mean, respectively:
- The type of authentication to use.
- The message displayed in the authentication dialog box.
- The location of the file containing the passwords.
- Only authenticated users can access the resource.
Once done, save and close the file.
Now create the .htpasswd file, adding an initial user to it:
If you want to add another user, do it without the -c flag:
Restart Apache to enable authentication through .htaccess:
From now on, when you access your phpMyAdmin subdirectory, an additional authentication will be requested. The connection will still be made from:
After providing the Apache authentication, you will be taken to the usual phpMyAdmin authentication page to enter your MySQL credentials.
This additional layer of security is essential, because phpMyAdmin has been exposed to vulnerabilities in the past.
Conclusion
Your phpMyAdmin should now be configured and ready to use on your Ubuntu 22.04 server. Through this interface, you can manage your databases, users and tables, as well as perform common operations.