How to Install and Secure phpMyAdmin on Ubuntu 20.04

Managing MySQL databases from the command line can be challenging. phpMyAdmin offers a user friendly, web-based interface that simplifies database management tasks. This guide will walk you through installing and securing phpMyAdmin on Ubuntu 20.04.

Prerequisites

Before you begin, ensure you have the following:

• An Ubuntu 20.04 server (cloud VPS or physical).

• A non-root user with sudo privileges configured.

• A fully installed LAMP stack: Apache, MySQL/MariaDB, PHP.

• Firewall (ufw) configured to allow HTTP (80) and HTTPS (443).

• (Optional) A domain name pointing to your server's IP address.

Step-by-Step Guide to a Settings

Step 1: Install phpMyAdmin

You can install phpMyAdmin from the default Ubuntu repositories using APT.

First, update your server's package index as your non-root sudo user:

 
sudo apt update

                                            

Next, install thephpmyadmin package. The official documentation also recommends installing a few PHP extensions to enable certain functionalities and improve performance.

If you followed the prerequisite LAMP stack tutorial, several of these modules might already be installed. However, it's recommended to install these additional packages:

• php-mbstring: For managing non-ASCII strings and converting encodings.

• Aphp-zip: 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 kinds of servers using various protocols.

 
sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl
    

Here's how to respond to the prompts:

• For the server selection, choose apache2. Warning: When the prompt appears, "apache2" is highlighted but not selected. You must press SPACE to select Apache, then TAB, and finally ENTER to proceed.

• 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.

Important Note on MySQL's Validate Password Component:

Sometimes, you might encounter an issue where the installation fails due to MySQL's Validate Password component. If this happens, you'll see a repeated prompt for the MySQL application password.
To resolve this, select the abort option to stop the installation. Then, open your MySQL prompt:

 
sudo  mysql
    

Or, if you enabled password authentication for the root MySQL user:

 
mysql  -u root -p
    

Enter your password when prompted.

From the MySQL prompt, run the following command to disable the Validate Password component. This won't uninstall it, but it will prevent the component from being loaded on your MySQL server:

 
 UNINSTALL COMPONENT "file://component_validate_password";
    

Close the MySQL client:

 
 exit
    

Now, try installing the phpmyadmin package again, and it should proceed as expected:

 
 sudo apt install phpmyadmin
    

Once phpMyAdmin is installed, you can re-enable the Validate Password component by opening the MySQL prompt again sudo mysql or mysql -u root -p and running:

 
INSTALL COMPONENT "file://component_validate_password";
    

The installation process automatically adds the phpMyAdmin Apache configuration file to the /etc/apache2/conf-enabled/ directory. To complete the Apache and PHP configuration for phpMyAdmin, you need to explicitly enable the mbstring PHP extension:

 
sudo phpenmod mbstring
    

Afterward, restart Apache for the changes to take effect:

 
sudo  systemctl restart apache2
    

phpMyAdmin is now installed and configured with Apache. Before you can log in, ensure your MySQL users have the necessary privileges.

PHP Version Compatibility with MySQL 8.0

If you're using phpMyAdmin with MySQL 8.0 , it's crucial to understand authentication method compatibility with different PHP versions. Authentication Methods:

MySQL 8.0 uses caching_sha2_password as its default authentication plugin, which is more secure than the older mysql_native_password. However, this can cause compatibility issues with older PHP versions:

• PHP 7.4 and later: Fully compatible with caching_sha2_password.

• PHP 7.2 and 7.3: May have issues with caching_sha2_password.

• PHP 7.1 and earlier: Not compatible with caching_sha2_password.

Checking Your PHP Version:

You can check your PHP version by running:

 
php -v
    

Handling Compatibility Issues:

If you're using PHP 7.2 or 7.3 and encounter authentication issues, you have two options:

Upgrade PHP (Recommended):

 
 sudo apt update
 sudo apt install php7.4 php7.4-mysql

                                            

Switch to mysql_native_password (Alternative): If upgrading PHP isn't possible, you can modify the MySQL user to use the older authentication method:

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

    

Warning: Using mysql_native_password is less secure than caching_sha2_password. It's highly recommended to upgrade PHP instead of switching authentication methods.

Verifying Authentication Method:
To check which authentication method your MySQL user is using:

 
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

    

This command helps ensure your setup uses the appropriate authentication method for your PHP version.

Step 2: Install phpMyAdmin

When phpMyAdmin is installed, it automatically creates a database user named phpmyadmin for its internal processes. Instead of logging in as this user with the administrative password set during installation, it's recommended to log in as either your MySQL root user or a dedicated user for managing databases through phpMyAdmin.

Configuring Password Access for the MySQL Root Account

On Ubuntu systems running MySQL 5.7 (and later), the root MySQL user defaults to authenticating with the auth_socket plugin, rather than a password. While this enhances security and usability in many cases, it complicates allowing external programs like phpMyAdmin to access the user.

To log in to phpMyAdmin as your root MySQL user, you need to switch its authentication method from auth_socket to one that uses a password, if you haven't already. To do this, open the MySQL prompt:

 
sudo mysql
    

Next, check which authentication method each of your MySQL user accounts uses:

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

    

You'll see output similar to this:

In this example, the root user authenticates 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 'password' to a strong password of your choosing:

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

    

Note: The ALTER USER statement above sets the root MySQL user to authenticate with the caching_sha2_password plugin. This is MySQL's preferred authentication plugin as it offers more secure password encryption than mysql_native_password. However, some versions of PHP don't work reliably with caching_sha2_password. PHP has reported this issue was fixed as of PHP 7.4, but if you encounter an error when trying to log in to phpMyAdmin later, you may want to set root to authenticate with mysql_native_password instead:

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

    

Then, check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

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

    

You should see output similar to this, indicating the root user now uses password authentication:

You can now log in to the phpMyAdmin interface as your root user with the password you've set.

Configuring Password Access for a Dedicated MySQL User

Alternatively, it may be better suited to your workflow to connect to phpMyAdmin with a dedicated user. You can create a user that connects either locally or remotely. (For details on how to allow remote connection access to MySQL, you'll need to configure MySQL to accept connections from other IP addresses).
To start mysql locally, open the MySQL shell:

 
sudo mysql

    

If you have password authentication enabled for your root user, as described in the previous section, you will need to run the following command and enter your password when prompted:

 
mysql -u root -p

    

From there, create a new user and give it a strong password:

 
CREATE USER 'sammy'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

    

Note: Again, depending on your installed PHP version, you may want to set your new user to authenticate with mysql_native_password instead of caching_sha2_password:

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

    

Then, grant your new user appropriate privileges. For example, you could grant the user privileges to all tables within the database, as well as the power to add, change, and remove user privileges, with this command:

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

    

Following that, exit the MySQL shell:

 
exit

    

You can now access the web interface by visiting your server's domain name or public IP address followed by /phpmyadmin:

 
https://your_domain_or_IP/phpmyadmin

    

Log in to the interface, either as root or with the new username and password you just configured. You'll see the user interface once you log in.
Now that you're able to connect and interact with phpMyAdmin, the next crucial step is to harden your system's security to protect it from attackers.

Step 3:Secure Your phpMyAdmin Instance

Due to its widespread use, phpMyAdmin is a popular target for attackers. You should take extra care to prevent unauthorized access. One effective method is to place a gateway in front of the entire application using Apache's built-in .htaccess authentication and authorization functionalities.
To do this, you must first enable the use of .htaccess file overrides by editing your phpMyAdmin installation's Apache configuration file.
Use your preferred text editor to edit the phpmyadmin.conf file located in your Apache configuration directory. Here, we'll use nano:

 
sudo nano /etc/apache2/conf-available/phpmyadmin.conf

    

Add an AllowOverride All directive within the section of the configuration file, like this:

 

/etc/apache2/conf-available/phpmyadmin.conf

    Options SymLinksIfOwnerMatch
    DirectoryIndex index.php
       AllowOverride All


                                        

After adding this line, save and close the file. If you used nano, press CTRL + X, then Y, and then ENTER.
To implement the changes you made, restart Apache:

 
sudo systemctl restart apache2

    

Now that you've enabled the use of .htaccess files for your application, you need to create one to actually implement some security.
For this to be successful, the file must be created within the application directory. Create the necessary file and open it in your text editor with root privileges:

 
sudo nano /usr/share/phpmyadmin/.htaccess

    

Within this file, enter the following information:

 

/usr/share/phpmyadmin/.htaccess
AuthType Basic
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user



                                        

Here's what each of these lines means:

• AuthType Basic: This line specifies the authentication type you are implementing, which uses a password file.

• AuthName: This sets the message for the authentication dialog box. Keep this generic to avoid giving unauthorized users information about what is being protected.

• AuthUserFile: This sets the location of the password file that will be used for authentication. This file should be outside of the directories being served by the web server. We will create this file shortly.

• Require valid-user: This specifies that only authenticated users should be given access to this resource, effectively stopping unauthorized users.

When you are finished, save and close the file.

The location you selected for your password file was /etc/phpmyadmin/.htpasswd. You can now create this file and add an initial user with the htpasswd utility:

 
sudo htpasswd -c /etc/phpmyadmin/.htpasswd username

    

You will be prompted to select and confirm a password for the user you are creating. Afterward, the file is created with the hashed password you entered.

If you want to add an additional user, you need to do so without the -c flag, like this:

 
sudo htpasswd /etc/phpmyadmin/.htpasswd additionaluser

    

Then, restart Apache to put .htaccess authentication into effect:

 
sudo systemctl restart apache2

    

Now, when you access your phpMyAdmin subdirectory, you will be prompted for the additional account name and password that you just configured:

 
https://domain_name_or_IP/phpmyadmin

    

After entering the Apache authentication, you'll be taken to the regular phpMyAdmin authentication page to enter your MySQL credentials. By adding an extra set of non-MySQL credentials, you're providing your database with an additional layer of security, which is desirable since phpMyAdmin has been vulnerable to security threats in the past.

Step 4: Enable HTTPS with Let's Encrypt

Enabling SSL encryption for phpMyAdmin using a free Let's Encrypt certificate is essential to protect sensitive data from potential attackers. This robust security measure guarantees that all data transmitted between your browser and the server is encrypted.

4.1 Install Certbot

Start by installing Certbot and its Apache plugin:

 
sudo apt install certbot python3-certbot-apache


                                            

4.2 Generate and Install the Certificate

Use the following command to obtain a certificate and automatically configure Apache:

 
sudo certbot --apache


    

The process is straightforward and user-friendly. Follow the prompts to select your domain name and allow HTTPS redirection. After a successful run, Certbot will handle HTTPS traffic for your server, including /phpmyadmin.

4.3 Enable Auto-Renewal

Let's Encrypt certificates expire every 90 days. Certbot sets up a systemd timer to renew them automatically:

 
Sudo systemctl status certbot.timer

                                            

Now you can access phpMyAdmin securely at:

 
https://your_domain/phpmyadmin

                                            

Use this setup to enable phpMyAdmin SSL and prevent credential leaks.

Step 5: Access phpMyAdmin via SSH Tunnel (Optional but Recommended)

If you're using phpMyAdmin on a server without a public domain name or don't want to expose it over the internet, you can tunnel access through SSH.

5.1 Open SSH Tunnel from Your Local Machine

On your local Machine Run:

 
ssh -L 8888:localhost:80 username@your_server_ip


    

Replace username and your_server_ip with your actual SSH credentials.

This command forwards your local port 8888 to the server's port 80, where Apache serves phpMyAdmin.

5.2 Access phpMyAdmin via Browser

Once the tunnel is active, open your browser and go to:

http://localhost:8888/phpmyadmin
    

You now have secure, local-only access to phpMyAdmin�ideal for development or staging environments. This is one of the best ways to secure phpMyAdmin on Ubuntu 20.04 in restricted-access scenarios.

Step 6: Fix Common phpMyAdmin Errors

6.1 phpMyAdmin 404 Not Found

Cause: Apache didn't properly link the config.
Fix:

sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-enabled/phpmyadmin.conf
sudo systemctl reload apache2
    

6.2 Access Denied (MySQL)

Cause: Wrong credentials or insufficient MySQL privileges.
Fix: Log into MySQL and verify the user has the necessary access:

SELECT user, host FROM mysql.user;
    

6.3 CSRF Token Mismatch

Cause: Session expired or cookies are corrupted.
Fix: Clear your browser cookies or restart your browser. You can also increase the session lifetime in php.ini:

session.gc_maxlifetime = 1440
    

Restart Apache:

sudo systemctl restart apache2
    

6.4 Forbidden Error (403)

Cause: .htaccess permissions or missing AllowOverride All.
Fix:

Ensure .htaccess is readable:

sudo chown www-data:www-data /usr/share/phpmyadmin/.htaccess
    

Check Apache config:

<Directory /usr/share/phpmyadmin>
    AllowOverride All
</Directory>
    

Step 7: Extra Layer: IP Whitelisting (Optional)

To limit access even further, restrict phpMyAdmin to specific IP addresses.
Open your Apache config:

sudo nano /etc/apache2/conf-available/phpmyadmin.conf
    

Inside the <Directory /usr/share/phpmyadmin> block, add:

<RequireAll>
    Require ip 203.0.113.4
    Require valid-user
</RequireAll>
    

Replace 203.0.113.4 with your actual static IP. Then reload Apache:

sudo systemctl reload apache2
    

This is a must-do if you're running phpMyAdmin on production. It adds another layer of access control.

FAQ

1. How do I install phpMyAdmin on Ubuntu?

To install phpMyAdmin on an Ubuntu server, start by updating your package index:

sudo apt update
    

Then install the required packages:

sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl
    

During installation, select apache2 when prompted and confirm the use of dbconfig-common to auto-configure the database. You'll be asked to set a MySQL application password�use a strong one.
Once installed, enable mbstring:

sudo phpenmod mbstring
sudo systemctl restart apache2
    

phpMyAdmin will be available at http://your_domain/phpmyadmin. After installation, you should secure the interface with SSL and access controls to prevent unauthorized access.

2. How do I log into phpMyAdmin using the MySQL root user?

By default, MySQL on Ubuntu uses the auth_socket plugin for the root user, which doesn't allow web-based login. To enable login via phpMyAdmin, you need to switch to password authentication.
Log into MySQL:

sudo mysql
    

Then run:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_password';
    

Now, navigate to http://your_domain/phpmyadmin, enter root as the username and the password you set. You'll be able to log in.
However, for production, it's not best practice to use the root user for daily tasks. Instead, create a separate user with limited privileges for accessing phpMyAdmin and keep root access disabled unless necessary.

3. How do I secure phpMyAdmin from external access?

Securing phpMyAdmin involves multiple layers of protection. First, enable HTTPS using Let's Encrypt to encrypt traffic:

sudo apt install certbot python3-certbot-apache
sudo certbot --apache
    

Next, enable .htaccess authentication by adding these lines to /usr/share/phpmyadmin/.htaccess:

AuthType Basic
AuthName "Restricted Area"
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user
    

Use the htpasswd utility to generate credentials:

sudo htpasswd -c /etc/phpmyadmin/.htpasswd your_username
    

Optionally, restrict access by IP in Apache's configuration (/etc/apache2/conf-available/phpmyadmin.conf) by adding:

<Directory /usr/share/phpmyadmin>
    Order Deny,Allow
    Deny from all
    Allow from your.ip.address.here
</Directory>
    

Finally, restart Apache:

sudo systemctl restart apache2
    

These steps dramatically improve security against unauthorized access.

4. How can I access phpMyAdmin without exposing it publicly?

For private, secure access, use SSH tunneling. This is especially useful when working from a trusted device or local development environment.

ssh -L 8888:localhost:80 username@your_server_ip
    

This command creates a secure tunnel from your local port 8888 to the remote server's port 80. You can now access phpMyAdmin at:

http://localhost:8888/phpmyadmin
    

This method avoids exposing phpMyAdmin over the internet. Only users with SSH access to the server can reach the interface.
To improve this setup, consider using SSH keys instead of passwords and enabling two-factor authentication on your local SSH client.

5. Why do I see a "403 Forbidden" error in phpMyAdmin?

A 403 error typically means that the server is denying access to phpMyAdmin due to a misconfiguration. Start by checking if Apache is set to allow overrides in /etc/apache2/conf-available/phpmyadmin.conf:

<Directory /usr/share/phpmyadmin>
    AllowOverride All
</Directory>
    

Make sure .htaccess exists and is readable by the web server. Check file ownership and permissions:

sudo chown -R www-data:www-data /usr/share/phpmyadmin
    

Restart Apache to apply changes:

sudo systemctl restart apache2
    

If you've restricted by IP or via a .htaccess file, ensure your client IP is allowed. Finally, check Apache error logs (/var/log/apache2/error.log) to identify the specific reason for denial.

6. What causes a CSRF token mismatch error?

CSRF token mismatches happen when your session token is no longer valid. Common reasons include session timeout, expired login cookies, or corrupted sessions.

To fix this:

• Clear your browser cookies and cache.
• Log in again to generate a new session token.
• Avoid using multiple tabs/windows for phpMyAdmin sessions.

To increase session lifetime, edit php.ini:

session.gc_maxlifetime = 1440
    

Then restart Apache:

sudo systemctl restart apache2
    

This error can also appear if you restore a browser session from history or switch networks (causing IP mismatch). Always ensure phpMyAdmin is accessed freshly with a valid login.

7. How can I change the default login URL for phpMyAdmin?

Changing the URL helps obscure phpMyAdmin from bots and common scanners. Move the directory:

sudo mv /usr/share/phpmyadmin /usr/share/dbadmin
    

Then update Apache configuration (/etc/apache2/conf-available/phpmyadmin.conf):

Alias /dbadmin /usr/share/dbadmin
    

Save the config, then reload Apache:

sudo systemctl reload apache2
    

Now, phpMyAdmin is accessible at http://your_domain/dbadmin.
Make sure any hardcoded paths in the config or scripts are updated too. This method isn't foolproof, but it adds another layer of security through obscurity.

8. How do I remove phpMyAdmin completely?

If you want to remove phpMyAdmin and all related files, run:

sudo apt purge phpmyadmin
sudo rm -rf /usr/share/phpmyadmin
sudo rm -rf /etc/phpmyadmin
    

Check if any database named phpmyadmin exists in MySQL:

DROP DATABASE phpmyadmin;
    

Removing it ensures there are no leftover credentials or sensitive information. You may also want to delete Apache config files:

sudo rm /etc/apache2/conf-available/phpmyadmin.conf
sudo systemctl reload apache2
    

This ensures a clean uninstall and frees up system resources.

9. Can I use phpMyAdmin with Nginx instead of Apache?

Yes, phpMyAdmin works with Nginx, but setup differs since Nginx doesn't support .htaccess or built-in PHP. You'll need PHP-FPM and custom rules.
Ensure PHP-FPM is installed:

sudo apt install php-fpm
    

Add to your Nginx config (typically in your site's server block):

location /phpmyadmin {
    root /usr/share/;
    index index.php;
    location ~ ^/phpmyadmin/(.+\.php)$ {
        try_files $uri =404;
        fastcgi_pass unix:/run/php/php7.4-fpm.sock; # Adjust PHP-FPM socket if needed
        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    }
}
    

Secure with HTTPS using Certbot and consider adding basic auth.

10. Where is phpMyAdmin installed on Ubuntu?

The default installation path is:
/usr/share/phpmyadmin

Apache configuration is usually stored in:
/etc/apache2/conf-available/phpmyadmin.conf

To confirm it's enabled:

ls /etc/apache2/conf-enabled | grep phpmyadmin
    

You can also check Apache's active configuration by running:

apache2ctl -S
    

This will show you virtual host and alias mappings. If you modified the URL path or changed the default folder, remember to update any dependent scripts or users with the new location.

Conclusion

You should now have phpMyAdmin successfully installed, configured, and secured on your Ubuntu server. This web-based interface offers a user-friendly and powerful alternative to managing MySQL or MariaDB databases via the command line. With phpMyAdmin, you can easily create and manage databases, run SQL queries, import/export data, configure user privileges, and handle everyday administrative tasks.
In this guide, you've also taken key steps to harden your setup:

• Enabled HTTPS encryption to protect data in transit.
• Added .htaccess authentication to block unauthorized users.
• Optionally tunneled access via SSH to avoid exposing the interface publicly.
• Addressed common issues like authentication errors, CSRF tokens, and 403 restrictions.