Installing phpMyAdmin and adding password authentication to MySQL on Ubuntu

In this video, we will see how to install and secure phpMyAdmin on Ubuntu 20.04. phpMyAdmin was created to easily interact with MySQL databases using a web-based interface. I will assume that you have configured a non-root user on your Ubuntu machine and installed the LAMP stack.

We will also see how to enable password authentication on MySQL, which supports socket-based authentication by default.

Step 1 - Installing PHPMyAdmin

Let's update our server's package index:

sudo apt update

Run the following command to install phpMyAdmin and related packages:

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

Using this command, we installed the following packages:

• php-mbstring: A module for managing non-ASCII strings with different encodings
• php-zip: An extension that facilitates uploading .zip files to phpMyAdmin
• php-gd: Enables support for GD graphics library
• php-json: Provides support for JSON serialization
• php-curl: Allows PHP to communicate with other servers

While installing PHPMyAdmin, you will see a prompt that will ask you to select the webserver. Press <Spacebar>, <Tab>, and then the enter key to continue the installation.

You will also be asked to continue with dbconfig-common setup. Press enter to confirm.

Finally, choose and confirm a strong password for PHPMyAdmin. If you get a prompt asking for the services which need to be restarted, simply press the "tab" and then "enter" key to continue. Phpmyadmin is now installed on your Ubuntu server.

Step 2 - Configuring Password Access in MySQL

To use MySQL, we need to enable password login in MySQL. Enter the following command to launch the MySQL console:

sudo mysql

Now enter the following query to enable password authentication in MySQL:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'MyStrongPassword1234$';

Please make sure you choose your own password and not the one which I chose ('MyStrongPassword1234$').

You can always check the authentication method used by using the following MySQL query:

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

Exit from the MySQL console by typing exit.

Now you can log in using your MySQL password by entering the following command to the MySQL console:

sudo mysql -u root -p

Step 3 - Configuring a Non-Root User in MySQL

Since it's not a good idea to use root as your login user, we will create another MySQL user named harry to log in to the PHPMyAdmin console:

Execute the following query:

CREATE USER 'harry'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'MyStrongPassword1234$';

This creates a user named harry with the password - 'MyStrongPassword1234$'.

Let's provide this user all the privileges so that we can use it to access the PHPMyAdmin console. Execute the query below:

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

You can now exit the MySQL shell:

exit

Step 4 - Accessing phpMyAdmin

Go to the browser and type http://your_domain_or_IP/phpmyadmin in your URL bar. You will see an option to log into your PHPMyAdmin console. Enter the username (harry in my case) and password you chose for this user.

You will be logged in to PHPMyAdmin.

Enjoy managing your MySQL databases with ease and security. Happy coding!