Installing phpMyAdmin and adding password authentication to MySQL on Ubuntu

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 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 its not a good idea to use root as your login user, we will create another MySQL user named harry to login 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!