Configure MySQL / MariaDB and Install phpMyAdmin

Merhaba,

This guide is part of How to: LEMP Stack, so if you are reading this, you're an advanced user that knows what LEMP Stack is, and want to configure MySQL / MariaDB and install phpMyAdmin.

After we have already installed MariaDB Database Server (you can check in the Step 3 section of this post). We can proceed to configure MariaDB.

Step 1: Create a MySQL Super User Account

Since we only permit root to log in by unix_socket_authentication, we can't use phpMyAdmin to login root and manage db users (because it uses a password), we need to create a super user that is capable to create mysql users and create mysql databases with it.

Login to mysql root first, with:

mysql -u root

and then use this query to create a superuser:

CREATE USER 'superuser'@'localhost' IDENTIFIED BY 'new_password_here';

In our example, we create a user account named superuser with a password new_password_here. Grant all permissions to the new MariaDB user account with:

GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

Verify by log-in with:

mysql -u superuser -p

Enter the password, and here is the output:

root@sql:~# mysql -u superuser -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 95
Server version: 10.6.12-MariaDB-1:10.6.12+maria~ubu2004 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Step 2: Download phpMyAdmin

Download phpmyAdmin latest from their official site with wget:

wget https://files.phpmyadmin.net/phpMyAdmin/5.2.1/phpMyAdmin-5.2.1-all-languages.zip

Extract phpMyAdmin*.zip to /var/www/html:

unzip phpMyAdmin*.zip -d /var/www/html

Rename the phpmyadmin folder for security purposes, and change its ownership to www-data:

mv /var/www/html/phpMyAdmin* /var/www/html/pma2023
chown -R www-data:www-data /var/www/html/pma2023

Step 3: Access phpMyAdmin

Since we already created an NGINX virtual host before (in Step 5 of this post), no need to add additional virtual host config.

Open phpmyadmin with your browser, and login with superuser account

Step 4: Add Normal MySQL user

To start add a new MySQL user for install a software later, click User Accounts and add user account.

Click Add user account to start add mysql user. And fill:

• User name : (the username of new mysql user)
• Host name : (% for any host / remote purposes, localhost for local only)
• Password and re-enter it : (your new password of mysql user)

Check Create database with same name and grant all privileges option if you want to create a database with same name.

Scroll, and click Go to proceed.

You can check on phpmyadmin dashboard of your new mysql database, and mysql user.

Anyway, you can explore phpmyadmin with your superuser. I hope it helps!

Thank you for reading! Güle güle