Re: Localhost/phpMyAdmin and l MySQL Installation

[Login 2023]

hello

[Emrah Mutlu]

 
Although many users need the functionality of a database management system like MySQL, managing MySQL from the command prompt alone can be inconvenient.

phpMyAdmin is open source software created so that users can interact with MySQL through a web interface Also it works on localhosts. In this guide, we will discuss how to install and secure phpMyAdmin to manage your databases on an Ubuntu 18.04 system.

Prerequisites
Ubuntu 18.04/19.10 server (DigitalOcean and Vultr are my recommendation)
A configured ufw firewall
An authorized sudo or root user with SSH access
We will also assume that you have completed the installation of LAMP (Linux, Apache, MySQL and PHP) on your Ubuntu 18.04 server. If you haven't completed this yet, you can follow the tutorial Installing Linux, Apache, MySQL, PHP (LAMP) 8 on Localhost Ubuntu 18.04.

Finally, there are important considerations when using software like phpMyAdmin, because phpMyAdmin:

Communicates directly with your MySQL installation,
Handles authentications using your MySQL credentials,
Executes and returns results for arbitrary SQL queries
For these reasons, and because it is a widely used PHP application that is often hacked, you should not run phpMyAdmin over a straight HTTP connection on remote systems. If you do not have an existing domain name configured with an SSL / TLS certificate, you can follow the tutorial How to Install SSL Certificate with Let's Encrypt on Apache on Ubuntu 18.04.

After completing these steps, you are ready to use this guide.

Step 1 - Installing phpMyAdmin
To begin, we will install phpMyAdmin from the default Ubuntu repositories.

sudo apt update && sudo apt install phpmyadmin -y
This will ask you a few questions to properly configure your setup.

In the server selection screen, select the apache2 SPACE key and then the TAB key,
Select Yes when asked whether to use dbconfig-common to set up the database,
You will then be prompted to choose and confirm a MySQL application password for phpMyAdmin
Now restart Apache;

sudo systemctl restart apache2
phpMyAdmin is now installed and configured on localhost/phpmyadmin. However, before you log in and begin interacting with your MySQL databases, you need to ensure that your MySQL users have the necessary privileges to interact with the program.

Step 2 - Setting User Authentication and Privileges
When you install phpMyAdmin on your server, a database user named phpmyadmin is automatically created, which performs the underlying operations for the software. Instead of logging in as this user with the administrator password you set during installation, it is recommended to log in as the root MySQL user or as a user dedicated to managing databases only through the phpMyAdmin interface.

Configuring Password Access for MySQL Root Account
On Ubuntu systems running MySQL 5.7 (and later), the root MySQL user is set to authenticate using the auth_socket plugin by default, rather than a password. This provides greater security and usability in many cases, but can complicate things when you need to allow external software such as localhost/phpMyAdmin to access the user.

We have to change the authentication method auth_socket > mysql_native_password for root MySQL user in phpMyAdmin session. If you've followed our LAMP setup tutorial, you've already done this, if you haven't already. To do this, open the MySQL prompt from your terminal:

sudo mysql
Next, check which authentication method each of your MySQL user accounts is using with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;
+-------------------+-------------------------------- -------------+-----------------------+----------+
| user | authentication_string | plugins | host |
+-------------------+-------------------------------- -------------+-----------------------+----------+
| root | *91EA82EFAD0677E20FDAEC7F11E15244530996F6 | auth_socket | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *70B3E55DA437B329F2F1A90C66719B666CBF4B9E | mysql_native_password | localhost |
| phpmyadmin | *91EA82EFAD0677E20FDAEC7F11E15244530996F6 | mysql_native_password | localhost |
+-------------------+-------------------------------- -------------+-----------------------+----------+
5 rows in set (0.00 sec)
In this example you can see that the root user is actually authenticating using the auth_socket plugin. Run the following ALTER USER command to configure the root account to authenticate with a password.

Make sure to replace 2020*- with a strong password of your choice (One uppercase letter, one lowercase letter, one number and special symbols):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Your Password2020*-';
Then with FLUSH PRIVILEGES you can tell your server to reload the database tables and put your new changes into effect:

FLUSH PRIVILEGES;
Again, check the authentication methods used by each user to verify that the root user is no longer authenticating using the auth_socket plugin:

SELECT user,authentication_string,plugin,host FROM mysql.user;
+-------------------+-------------------------------- -------------+-----------------------+----------+
| user | authentication_string | plugins | host |
+-------------------+-------------------------------- -------------+-----------------------+----------+
| root | *91EA82EFAD0677E20FDAEC7F11E15244530996F6 | mysql_native_password | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *70B3E55DA437B329F2F1A90C66719B666CBF4B9E | mysql_native_password | localhost |
| phpmyadmin | *91EA82EFAD0677E20FDAEC7F11E15244530996F6 | mysql_native_password | localhost |
+-------------------+-------------------------------- -------------+-----------------------+----------+
5 rows in set (0.00 sec)
In this sample output you can see that the root MySQL user is now authenticated using a password. After confirming this on your own server as well, you can exit the MySQL shell.

exit
Configuring Password Access for a Custom MySQL User
Alternatively, some of you may find it better to just connect with a dedicated user to phpMyAdmin. To do this, open the MySQL command prompt once again:

sudo mysql
Note: If you have enabled password authentication as described in the previous section, you must use a different command to access the MySQL command prompt.

mysql -u root -p
Now create a new user;

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'Your Password2020*-';
Next, grant your new user the appropriate privileges. For example, for the new user, you can authorize all tables in the database to add, modify, and remove user privileges with this command:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;
exit
You can now access the web interface by visiting your server's domain or public IP address and then /phpmyadmin :