phpMyAdmin installation/config

Question

I'm a noob to Linux and packages, not to mention websites and databases, however, I've followed this guide and completed all steps successfully and have a working wordpress site that I can access via the web, the tutorial I followed can be found here.

System info: Linux 5.19.0-21-generic, Ubuntu 22.10

I am connected via SSH into my machine and via the terminal I've installed and set up the following:

• apache2
• MariaDB/MySQL (I don't really understand the difference or why both are installed)
• Additional config steps as displayed in the video (chown on a folder, database rights for new wordpress user etc)

Now when attempting to mess around with plugins for word press and troubleshooting, there are things that I want to view in the MySQL database but navigating via query's is difficult as I have no experience. I understand I should be able to use /phpmyadmin to navigate it via a GUI so naturally I followed other guides to install phpmyadmin and configure without knowing if it was already set up or not. Mid way through configuration after choosing the following options:

1. Yes (Configure common database?)
2. SQL application password (I set it's password to a new one I generated?)
3. Password confirm
4. apache2 (Web server to reconfigure automatically)

I'm then slapped with an access error while installing the database:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) . Your options are:

• abort - Causes the operation to fail; you will need to downgrade, reinstall... etc
• retry - Prompts once more with all the configuration questions... etc
• retry (skip questions) - Immediately attempts the operation again, skipping all questions... etc
• ignore - Continues the operation ignoring dbconfig-common errors. This will usually leave this package without a functional database... etc

This is nothing important and so in my attempt to rapid fire the situation I tried all options and tried to look into the MySQL user accounts (I've just been using root but did change it's default password which was blank) but I am still stuck and I am afraid my phpmyadmin configuration is in a terrible state but I am unsure how to start from scratch. Preferably I'd just like to reinstall phpmyadmin without having to redo MySQL/Apache2/Wordpress and hopefully learn what I've done incorrectly so I can take corrective actions manually.

I have also tried to follow, what appears to be, the same issue I am having here but the corrective config lines did not help me at all.

Answer 1

You cannot – and should not – connect to the MySQL root account unless you are configuring the system for the very first time or fixing something that has gone very, very wrong. This is because people have historically used very weak passwords when setting up their databases, which were then very easy to "hack" for fools with nothing better to do. As a result, MySQL changed the way root is accessed about 5 years ago.

Instead, you need to create separate MySQL accounts:

• one for WordPress, which is limited to only the WordPress database
• one for administration, which phpMyAdmin uses to manage the entire MySQL installation

Assuming you have not hosed your MySQL root account, you can follow these steps:

1. Connect to MySQL as root via the Terminal (or SSH):

   sudo mysql
   

   ^{Note: You may be asked for your server password here to use sudo. This is not the same as your MySQL password.}
2. If you have not already done so, create a database for your WordPress installation:

   CREATE DATABASE `wordpress` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
   

   ^{Note: By setting the CHARSET and COLLATE values to the ones specified, you will have no issues with things like emoji, non-English characters, and the like.}
3. If you have not already done so, create a MySQL user account for your WordPress installation:

   CREATE USER 'wp_site'@'localhost' IDENTIFIED BY 'superSecretPassword!123';
   

   ^{Note: Be sure to change superSecretPassword!123 to something less silly.}
4. Grant the WordPress user account permission to do everything with the WordPress database, and only the WordPress database:

   GRANT ALL ON `wordpress`.* TO 'wp_site'@'localhost';
   

5. Create a MySQL administrator account that will be used by phpMyAdmin:

   CREATE USER 'silently'@'localhost' IDENTIFIED BY 'superSecretAdminPassword!123';
   

   ^{Note: Be sure to not reuse passwords, particularly on servers that are connected to the Internet.}
6. Grant full permissions to that administrator account:

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

7. Exit MySQL as root and never use that account again. Any administration should be done via the new admin account you created. Only use root when something is seriously broken.

Welcome to the world of systems management 🙂

---

Additional Notes:

• it's unlikely that you have both MySQL and MariaDB installed, as they generally do not cooperate and will clobber each other. If you did somehow manage to install both (perhaps as a XAMPP + MySQL installation), then rebuild your server from scratch. Also, if you are using XAMPP, then there are going to be lots of little differences between their version of Apache and MariaDB that will make most of the Linux sites you read for support pretty much useless. XAMPP does things their own way, which is not the same way as the rest of the planet.
• in the event the database will not let you sign in anymore, it would be easier to scrub the server and start again. Repairing a MySQL or MariaDB installation an be a right pain if you do not follow all the steps precisely.