Setup of phpMyAdmin on nginx

Introduction

This tutorial describes how to set up phpMyAdmin on Ubuntu 22.04 with nginx.

The time needed to follow this tutorial is approximately 10 - 20 minutes.

The tutorial uses the example IP 123.123.123.123.
This hostname needs to be replaced by the name of your own server when you perform the steps described in this tutorial.
We will also use the subdomain pma.example.com in this tutorial. Replace it with your own domain.

Requirements

The server must already have the Ubuntu 22.04 image from the (SCP)[https://servercontrolpanel.de/] installed.

Also, you need a domain. A domain used in a webhosting package is sufficient. (You can still use it for your webhosting because we will be using a subdomain.)

In addition, you need to have a user on each of the database servers you want to access with phpMyAdmin.
These users can even be read-only users, but they need to have access to all databases you want to access with phpMyAdmin.

• You can create a new user in the WCP (Plesk) under "Databases" -> "Users" -> "Add Database User".
• After creating the new user, you can set the permissions for the user by clicking the "Edit" button.

Step 1 - Finding out the IP of your server

1. Log in to the SCP (Server Control Panel) under https://www.servercontrolpanel.de
2. Click on the server you would like to use.
3. You are now in the "General" tab and you will see a pane labeled "Network" below on the right.
4. Here you can find the IP of your server (under IPv4).
5. Write it down somewhere.

Step 2 - Configuring DNS

1. Log in to the CCP (Customer Control Panel) under https://www.customercontrolpanel.de
2. In the sidebar on the left, click on "Domains".
3. Find the domain you want to use and click on the "Magnifying glass" symbol next to it.
4. In the new window, click on "DNS".
5. Scroll down until there are empty fields.
6. In the first field, write your subdomain name (I recommend using "pma" for phpMyAdmin).
7. In the dropdown menu, select A.
8. In the last field, write the IP of your server (from Step 1).
9. Click on "Save DNS records" at the bottom of the form.

Step 3 - Preparing the server

First you need to log in to your server via SSH. Then proceed as follows:

1. Enter sudo -s to get root privileges.
2. If asked for your password, enter it and press Enter.
3. Now enter apt update -y && apt upgrade -y && apt remove -y nginx apache2 phpmyadmin lighttpd && apt autoremove -y && apt install -y nginx php8.1 php8.1-fpm phpmyadmin to update the server, remove any existing webservers and old packages and install nginx with PHP and phpMyAdmin.

• If a pink window with the title "Pending kernel upgrade" appears, press Enter once to continue.
• If a pink window with the title "Daemons using outdated libraries" appears, press Enter once to continue.

5. Now you just need to wait until a pink window with the title "Configuring phpmyadmin" is displayed.

Step 4 - Installing PHPMyAdmin

During the installation process, you will be prompted to choose a web server to configure.
Because we are using nginx as a web server, you shouldn’t choose either of these options:

• Press TAB to highlight "Ok>" and then press ENTER to continue the installation process.

You are asked now whether you want dbconfig-common to configure a database for PHPMyAdmin.
We do not want this because we will be accessing the database servers from netcup and not from the server itself:

• Press TAB to highlight "No>" and then press ENTER to continue the installation process.

• If a pink window with the title "Pending kernel upgrade" appears, press Enter once to continue.
• If a pink window with the title "Daemons using outdated libraries" appears, press Enter once to continue.

Step 5 - Host with nginx

First we need to create the password files for HTTP-Basic-Auth: These are not the same as the passwords for the database servers! To create the password files, enter the following commands:

1. Enter sh -c "echo -n 'USERNAME:' >> /etc/nginx/.htpasswd" to create the file. (Replace USERNAME with your desired username.);
2. Enter sh -c "openssl passwd -apr1 >> /etc/nginx/.htpasswd" to create the password. (You will be asked to enter the password twice.);

If you want more than one user, repeat the above steps with different usernames.

In all the following steps you need to replace pma.example.com with the subdomain you set in Step 2!

1. Link PHPMyAdmin to the new directory by entering ln -s /usr/share/phpmyadmin /var/www/pma.example.com
2. Enter nano /etc/nginx/sites-available/pma.example.com to create a new nginx config file.
3. Paste the following code into the file:

server {
        listen 80;
        listen [::]:80;

        server_name pma.example.com;
        root /var/www/pma.example.com;

        index index.php index.html index.htm index.nginx-debian.html;

        location / {
                ## First attempt to serve request as file, then
                ## as directory, then fall back to displaying a 404.
                try_files $uri $uri/ =404;
        }

        ## pass PHP scripts to FastCGI server
        location ~ \.php$ {
                include snippets/fastcgi-php.conf;
                fastcgi_pass unix:/run/php/php8.1-fpm.sock;
        }

        auth_basic "Restricted Content";
        auth_basic_user_file /etc/nginx/.htpasswd;
}

4. Exit the file by first pressing Ctrl + X and then Y and then Enter.
5. Enter ln -s /etc/nginx/sites-available/pma.example.com /etc/nginx/sites-enabled/ to enable the config file.
6. Enter nginx -t to test the config file.
7. After test is successful is displayed, you can reload nginx by entering systemctl reload nginx.

Step 6 - Setting up HTTPS

1. Enter snap install --classic certbot to install certbot.
2. Enter certbot --nginx to start the certbot setup.
3. I recommend using these settings:
   1. Enter your email address.
   2. Yes (Terms of Service).
   3. No (Advertisement).
   4. Press Enter to select the only option (your domain).
   5. This process can take a while, so please be patient.

Step 7 - Setting up the firewall

We need a firewall to protect our server from unwanted connections.

We will use ufw (Uncomplicated Firewall) to set up the firewall, because it is easy to use and configure and is also pre-installed on Ubuntu.

1. Enter ufw allow ssh to allow SSH connections.
2. Enter ufw allow http to allow HTTP connections.
3. Enter ufw allow https to allow HTTPS connections.
4. Enter ufw enable to enable the firewall.
5. Press Y and then Enter to confirm.

Step 8 - Configuring PHPMyAdmin

1. Enter cd /usr/share/phpmyadmin && cp config.sample.inc.php config.inc.php && rm -f /etc/phpmyadmin/config.inc.php && ln -s /usr/share/phpmyadmin/config.inc.php /etc/phpmyadmin/config.inc.php && nano config.inc.php to copy the sample config file to the actual config file and open it.

2. Add the following line to the beginning of the file (Right under $cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */):

$cfg['Server']['ssl_cert'] = '/etc/letsencrypt/live/<YOUR-DOMAIN>/fullchain.pem';
$cfg['Server']['ssl_key'] = '/etc/letsencrypt/live/<YOUR-DOMAIN>/privkey.pem';

3. Remove the following lines:

/**
 * First server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

4. Add the following lines (where you just removed the old ones):

$i++;
$cfg['Servers'][$i]['host'] = '<DB_HOST>';
$cfg['Servers'][$i]['user'] = '<DB_USER-NAME>';
$cfg['Servers'][$i]['password'] = '<DB_USER-PASSWORD>';
$cfg['Servers'][$i]['verbose'] = '<DISPLAYNAME>';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'config';

5. Replace the example values (<DB_HOST>, <DB_USER-NAME>, <DB_USER-PASSWORD>, <DISPLAYNAME>, <YOUR-DOMAIN>) with your actual values (because of netcup restrictions you have to use your hostname when replacing the DB_HOST value and will not be able to use the IPv4 address. You will find the hostname in the CCP.

6. If you need more than one server, you can repeat Step 4 with different values just under the one you just added. (More configuration options can be found in the official documentation under: https://docs.phpmyadmin.net/en/latest/config.html#server-connection-settings)

7. Exit the file by pressing Ctrl + X and then Y and then Enter.

Step 9 - Checking if everything works

1. Enter the domain in your browser to access phpMyAdmin (e.g. https://pma.example.com).

2. You should now see a prompt to enter your username and password (the ones you set in Step 6).

If this works, you should now restart the server shutdown -r now.
After executing this command, you can close the SSH connection and use your browser to access PHPMyAdmin again.

If you ever want to change anything in the config file, you can do so by entering nano /etc/phpmyadmin/config.inc.php.

Conclusion

You have now successfully installed phpMyAdmin and set it up to start on boot.

From now on, your phpMyAdmin instance will be available at your subdomain (e.g. https://pma.example.com).

Thank you for using this tutorial!

License

MIT

Copyright (c) 2023 Konstantin Protzen

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Contributor's Certificate of Origin

By making a contribution to this project, I certify that:

1. The contribution was created in whole or in part by me and I have the right to submit it under the license indicated in the file; or

2. The contribution is based upon previous work that, to the best of my knowledge, is covered under an appropriate license and I have the right under that license to submit that work with modifications, whether created in whole or in part by me, under the same license (unless I am permitted to submit under a different license), as indicated in the file; or

3. The contribution was provided directly to me by some other person who certified (a), (b) or (c) and I have not modified it.

4. I understand and agree that this project and the contribution are public and that a record of the contribution (including all personal information I submit with it, including my sign-off) is maintained indefinitely and may be redistributed consistent with this project or the license(s) involved.