How To Install and Secure PhpMyAdmin With Lemp Stack (Ubuntu 20.04)

In this tutorial, we are going to discuss steps to install and secure PhpMyAdmin with LEMP stack on Ubuntu 20.04 server. So, you would be able to access your MySQL or MariaDB databases through a web interface easily.

If you don’t known about PhpMyAdmin, then it’s a free and open source tool that offer users a web interface to manage MySQL or MariaDB servers. This is one of the widely supported tool offered by most of the popular hosting providers to let web administrators to create and manage databases easily.

But, on an Ubuntu server, you will need to install and configure PhpMyAdmin to let it work with MySQL databases and tables easily. So, let’s learn how to install and secure PhpMyAdmin with LEMP Stack/Nginx on Ubuntu 20.04.

Prerequisites

We hope you have met all prerequisites and installed LEMP and MySQL/MariaDB on your Ubuntu server. Now, you need to install and secure phpMyAdmin to connect to MySQL server to access the database through web interface.

Steps to install and secure phpMyAdmin with Nginx on Ubuntu 20.04

Step 1: Install a essential PHP extension

The very first step is to install a PHP extension called php-mbstring which is essential for phpMyAdmin to connect with the database. To do so, run the command below.

sudo apt install -y php-json php-mbstring

Step 2: Install phpMyAdmin

In order to install phpMyAdmin latest version, it’s a good practice to download it from official website. Execute the following command.

wget https://files.phpmyadmin.net/phpMyAdmin/5.0.2/phpMyAdmin-5.0.2-all-languages.tar.gz

Running the command above, you have downloaded the phpMyAdmin package in .tar.gz file. So obviously you need to extract that before installation. You can do this using tar command,

tar -zxvf phpMyAdmin-5.0.2-all-languages.tar.gz

Next is to move the extracted phpMyAdmin file to location where you want to install it. We are moving the phpMyAdmin extracted file to /usr/share/ in the command below.

sudo mv phpMyAdmin-5.0.2-all-languages /usr/share/phpMyAdmin

Step 3: Configuring phpMyAdmin

Since you have moved the phpMyAdmin to your desired location, it needs some configuration edits to work efficiently. Just copy the sample configuration file to working configuration file.

sudo cp -pr /usr/share/phpMyAdmin/config.sample.inc.php /usr/share/phpMyAdmin/config.inc.php

Now you need to edit the configuration file using nano text editor in Ubuntu server through command line.

sudo nano /usr/share/phpMyAdmin/config.inc.php

In the opened configuration file, you need to update a blowfish secret code. You can generate it through https://phpsolved.com/phpmyadmin-blowfish-secret-generator/ and paste the generated code in the file. The configuration line should look like this.

$cfg['blowfish_secret'] = '/rt/rdBpjpi72XfVh0{0Oe9Qr,A1W]E8'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

Once done, you should now un-comment the phpMyAdmin storage settings.

Also Read:   Install Google Play Music Desktop Player On Fedora Workstation

Note: You need to alter the controluser and controlpass section with the username and password through which you want to access phpMyAdmin’s configuration database. We will discuss about how to create it in the next step.

/**
* phpMyAdmin configuration storage settings.
*/

/* User used to manipulate with storage */
$cfg['Servers'][$i]['controlhost'] = 'localhost';
// $cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'user_name';
$cfg['Servers'][$i]['controlpass'] = 'user_pass';

/* Storage database and tables */
$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';

Once you uncomment the mentioned section in configuration file, you need to import create_tables.sql to create tables for phpMyAdmin. You can do this with the following command.

sudo mysql < /usr/share/phpMyAdmin/sql/create_tables.sql -u root -p

Now, login to MySQL/MariaDB

sudo mysql -u root -p

Now you need to create a user user_name and grant permission to phpMyAdmin’s configuration database. You can do these with below mentioned queries in MySQL/MariaDB.

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'user_pass';
GRANT ALL ON phpmyadmin.* TO 'user_name'@'localhost';
FLUSH PRIVILEGES;
exit

Now, it’s required to create a virtual host for phpMyAdmin, probably under /etc/nginx/conf.d directory. Run the command below.

sudo nano /etc/nginx/conf.d/phpMyAdmin.conf

once the server block configuration file is opened before you in text editor, copy the following server block code. Do make sure to change the server_name to your own domain or address where you want to point the server block.

server {
listen 80;
server_name your_domain.com;
root /usr/share/phpMyAdmin;

location / {
index index.php;
}

## Images and static content is treated different
location ~* ^.+.(jpg|jpeg|gif|css|png|js|ico|xml)$ {
access_log off;
expires 30d;
}

location ~ /\.ht {
deny all;
}

location ~ /(libraries|setup/frames|setup/libs) {
deny all;
return 404;
}

location ~ \.php$ {
include /etc/nginx/fastcgi_params;
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME /usr/share/phpMyAdmin$fastcgi_script_name;
}
}

Once the configuration file is edited, you can save and exit the text editor. Next is to create a directory named temp, and change its file access permission to 777, and set the ownership of phpMyAdmin directory, with the commands mentioned.

sudo mkdir /usr/share/phpMyAdmin/temp
sudo chmod 777 /usr/share/phpMyAdmin/temp
sudo chown -R www-data:www-data /usr/share/phpMyAdmin

After this, restart the Nginx and PHP-FPM services.

sudo systemctl restart nginx
sudo systemctl restart php7.4-fpm

Step 4: Creating and Admin user and granting access to manage all databases

Since MariaDB/MySQL root users are allowed to log in through a default Unix shell socket, it’s necessary to create a DB Administrative user with permissions to access all created databases. This can be done with following commands.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'dbadmin'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Note: Do make sure to log in to MySQL/MariaDB account with set root username and password to run above mentioned sql queries.

Step 5: Accessing phpMyAdmin

In order to access phpMyAdmin, just type your_domain.com which you set in the server block above. Just use the Database Admin user name and password you created previously to login to phpMyAdmin.

Also Read:   [How To] Install Steam On Debian 10/11 Desktop

phpmyadmin login page

After s successfully logging in, you can access all created databases and and manage them easily.

That’s all, we hope you have successfully learned to install and secure phpMyAdmin with Nginx on Ubuntu 20.04 to access and manage all databases and tables easily. If you find some errors while following the tutorial above, you can reports us through Suggest Us page.

Nishant

Nishant Verma is a senior web developer who love to share his knowledge about Linux, SysAdmin, and more other web handlers. Currently, he loves to write as content contributor for ServoNode.