How to Install and Secure the latest phpMyAdmin version on Debian 12

This tutorial exists for these OS versions

Debian 12 (Bookworm)
Debian 11 (Bullseye)

phpMyAdmin is a free, web-based administration tool used to manage MySQL and MariaDB databases, widely utilized in Debian Linux systems. It provides a user-friendly interface for interacting with databases, allowing users to execute SQL queries, manage database tables, import and export data, and configure various database settings without needing to use the command line. On Debian Linux, phpMyAdmin is often installed alongside a LAMP (Linux, Apache, MySQL/MariaDB, PHP) stack, making it easier for administrators and developers to handle database operations in a more visual and organized way.

In this tutorial, I will show you how to install and secure phpMyAdmin database administration tool on Debian 12.

Prerequisites

A server running Debian 12.
A root password is configured on the server.

Getting Started

Before starting, updating your system packages to the latest version is a good idea. You can update all packages using the following command:

apt update -y

Once your system is updated, you can proceed to the next step.

Install LAMP Server

phpMyAdmin is a PHP-based application that runs on a web server. So, you will need to install the LAMP server on your server. You can install it using the following command:

apt install apache2 mariadb-server libapache2-mod-php php-cli php-mysql php-zip php-curl php-xml php-mbstring php-zip php-gd unzip -y

Once all the packages are installed, you can proceed to the next step.

Install and Configure phpMyAdmin

First, download the latest version of phpMyAdmin from their official website using the following command:

wget https://files.phpmyadmin.net/phpMyAdmin/5.2.1/phpMyAdmin-5.2.1-all-languages.zip

Once the download is completed, unzip the downloaded file with the following command:

unzip phpMyAdmin-5.2.1-all-languages.zip

Next, move the extracted directory to the /usr/share with the following command:

mv phpMyAdmin-5.2.1-all-languages /usr/share/phpmyadmin

Next, create the required directory with the following command:

mkdir -p /var/lib/phpmyadmin/tmp

Next, set proper ownership to the phpMyAdmin directory:

chown -R www-data:www-data /var/lib/phpmyadmin

Next, copy the phpMyAdmin sample configuration file:

cp /usr/share/phpmyadmin/config.sample.inc.php /usr/share/phpmyadmin/config.inc.php

Next, install the pwgen and generate a secrete key with the following command:

apt-get install pwgen -y
pwgen -s 32 1

Output:

pau9t1SG6lmaeCFxKqeeaY5N4erIa25K

Next, edit the config.inc.php file and configure it:

nano /usr/share/phpmyadmin/config.inc.php

Define your secrete key and uncomment the following lines:

$cfg['blowfish_secret'] = 'pau9t1SG6lmaeCFxKqeeaY5N4erIa25K'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'password';

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

$cfg['TempDir'] = '/var/lib/phpmyadmin/tmp';

Save and close the file when you are finished.

Create phpMyAdmin Admin User

Creating a separate user to manage the database through phpMyAdmin is always recommended.

First, import the phpMyAdmin tables to the MariaDB database using the following command:

mysql < /usr/share/phpmyadmin/sql/create_tables.sql

Next, connect to the MariaDB shell with the following command:

mysql

Once you are connected, grant all necessary privileges to the phpmyadmin database with the following command:

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost' IDENTIFIED BY 'password';

Next, create an admin user with the following command:

MariaDB [(none)]> CREATE USER myadmin;

Next, grant all the privileges to the admin user with the following command:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'myadmin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Next, flush the privileges and exit from the MariaDB shell using the following command:

MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;

Once you are finished, you can proceed to the next step.

Configure Apache for phpMyAdmin

Next, you must create an Apache virtual host configuration file for phpMyAdmin. You can create it using the following command:

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

Add the following lines:

Alias /phpmyadmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin>
    Options SymLinksIfOwnerMatch
    DirectoryIndex index.php

    <IfModule mod_php5.c>
        <IfModule mod_mime.c>
            AddType application/x-httpd-php .php
        </IfModule>
        <FilesMatch ".+\.php$">
            SetHandler application/x-httpd-php
        </FilesMatch>

        php_value include_path .
        php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
        php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/php/php-php-gettext/:/usr/share/javascript/:/usr/share/php/tcpdf/:/usr/share/doc/phpmyadmin/:/usr/share/php/phpseclib/
        php_admin_value mbstring.func_overload 0
    </IfModule>
    <IfModule mod_php.c>
        <IfModule mod_mime.c>
            AddType application/x-httpd-php .php
        </IfModule>
        <FilesMatch ".+\.php$">
            SetHandler application/x-httpd-php
        </FilesMatch>

        php_value include_path .
        php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
        php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/php/php-php-gettext/:/usr/share/javascript/:/usr/share/php/tcpdf/:/usr/share/doc/phpmyadmin/:/usr/share/php/phpseclib/
        php_admin_value mbstring.func_overload 0
    </IfModule>

</Directory>

# Authorize for setup
<Directory /usr/share/phpmyadmin/setup>
    <IfModule mod_authz_core.c>
        <IfModule mod_authn_file.c>
            AuthType Basic
            AuthName "phpMyAdmin Setup"
            AuthUserFile /etc/phpmyadmin/htpasswd.setup
        </IfModule>
        Require valid-user
    </IfModule>
</Directory>

# Disallow web access to directories that don't need it
<Directory /usr/share/phpmyadmin/templates>
    Require all denied
</Directory>
<Directory /usr/share/phpmyadmin/libraries>
    Require all denied
</Directory>
<Directory /usr/share/phpmyadmin/setup/lib>
    Require all denied
</Directory>

Save and close the file when you are finished, then enable the phpMyAdmin configuration file with the following command:

a2enconf phpmyadmin.conf

Next, reload the Apache service to apply the changes:

systemctl reload apache2

You can check the status of the Apache service using the following command:

systemctl status apache2

You should see the following output:

? apache2.service - The Apache HTTP Server
     Loaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2024-08-25 11:28:21 UTC; 5s ago
       Docs: https://httpd.apache.org/docs/2.4/
    Process: 23021 ExecStart=/usr/sbin/apachectl start (code=exited, status=0/SUCCESS)
   Main PID: 23031 (apache2)
      Tasks: 6 (limit: 2341)
     Memory: 14.6M
        CPU: 89ms
     CGroup: /system.slice/apache2.service
             ??45031 /usr/sbin/apache2 -k start
             ??45032 /usr/sbin/apache2 -k start
             ??45033 /usr/sbin/apache2 -k start
             ??45034 /usr/sbin/apache2 -k start
             ??45035 /usr/sbin/apache2 -k start
             ??45036 /usr/sbin/apache2 -k start

Sun 25 11:28:21 debian systemd[1]: Starting The Apache HTTP Server...

Access phpMyAdmin

At this point, phpMyAdmin is installed and configured. Now, open your web browser and access the phpMyAdmin using the URL http://your-server-ip/phpmyadmin. You should see the phpMyAdmin login page:

Provide your admin username, password and click on the Go button. You should see the phpMyAdmin dashboard on the following page:

Secure phpMyAdmin

It is a good idea to secure the phpMyAdmin with two-factor authentication. You can do this using the .htaccess authentication and authorization functionalities.

First, edit the phpMyAdmin configuration file:

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

Add the "AllowOverride All" line in the following server block:

<Directory /usr/share/phpmyadmin>
    Options FollowSymLinks
    DirectoryIndex index.php
    AllowOverride All

    <IfModule mod_php5.c>

Save and close the file when you are finished then restart the Apache service:

systemctl restart apache2

Next, create an .htaccess file and define the Apache authentication type:

nano /usr/share/phpmyadmin/.htaccess

Add the following lines:

AuthType Basic
AuthName "Restricted Files"
AuthUserFile /usr/share/phpmyadmin/.htpasswd
Require valid-user

Save and close the file then create a user with the following command:

htpasswd -c /usr/share/phpmyadmin/.htpasswd secureuser

You will be asked to set a password as shown below:

New password: 
Re-type new password: 
Adding password for user secureuser

Verify phpMyAdmin

At this point, phpMyAdmin is secured with additional authentication. To verify it, open your web browser and access the phpMyAdmin using the URL http://your-server-ip/phpmyadmin. You will be asked for the additional username and password as shown below:

After providing your username and password, you will be redirected to the regular phpMyAdmin login page.

Conclusion

Congratulations! You have successfully installed and secured phpMyAdmin on Debian 12. You can now explore phpMyAdmin and manage your database from the web browser.

Log in or Sign up