MySQL "connection access denied for 'root'@'localhost'" Solution

During my 'Database Project and Management' class at the university, whilst trying to use my personal laptop to do the class exercise, I had a tricky issue with my MySQL Workbench, and it caused a delay in delivering my response for the activity on time since I had to switch from using my laptop to the lab's computer or else I wouldn't have been able to do it.

It took me about 3 days this week after that class to solve it with patience and attention. Honestly, it could've been faster if I had just mindlessly used ChatGPT to guide me through the process, but I wanted to really understand what was happening and how to solve it. There's nothing wrong with using ChatGPT for that, though. It's just that I'm learning about databases, and if I didn't have that goal in mind, I probably would've solved it quickly but without gaining any knowledge.

Here’s a breakdown of what happened and how I fixed it.

Regarding the MySQL issue: The problem seemed simple at first: every time I tried to connect as the root user through MySQL Workbench, the query tab would open for just a second and then immediately close. No error messages, no explanation — just frustration-inducing silence. Adding to the confusion was the fact that I hadn’t been prompted to set a root password during the MariaDB installation at any time, and I didn't notice that from the get-go.

This lack of a root password ended up being a bummer. It didn't occur to me that it was odd MySQL Workbench wasn't asking for my password when I tried to connect.

I didn’t remember explicitly installing MariaDB before using MySQL Workbench, which started to raise some red flags in my mind, because I recalled one important piece of information my teacher mentioned in class: MySQL Workbench is only a management tool. After searching on the internet, I found that it doesn't come with MariaDB nor MySQL when you install it. You need a database server, like MariaDB or MySQL, for MySQL Workbench to manage. But since I hadn’t thought about this earlier, I was focused solely on getting MySQL Workbench to work.

With further digging, I noticed that, during the installation of MariaDB, you'd be asked to create a password for the root user, as obvious as it may sound.

as obvious as it may sound.

I say that because virtually no IT student (it's a beginner-wise issue) would realize that because when I installed MySQL Workbench, MariaDB was probably installed as a dependence and either skipped prompting me to set a root password or used the mysql_native_password plugin, which I discovered in this Stack Overflow thread . There's also this one addressing a similar problem.

Now that I knew I was dealing with a missing root password, the first thing I needed to do was bypass the normal authentication process. I found that I could start MariaDB in safe mode, which would allow me to log in without needing a password. This would at least give me access to the database so I could run the commands I needed. I started MariaDB in safe mode using:

sudo mariadbd-safe --skip-networking &        

This command starts MariaDB safely, but also disables networking connections, so the database can only be accessed locally. By doing this, I could work on fixing the issue. The '&' at the end runs the command in the background, so I could continue using the terminal for other tasks if needed. With safe mode enabled, I could log into MariaDB without a password

sudo mariadb        

Now I was in the database console, logged in as the root user. This allowed me to dig deeper into the root authentication issue. The first thing I checked was how the root user was set up and what authentication plugin MariaDB was using for root. I found this approach here and ran the following query:

SELECT user, host, plugin FROM mysql.user WHERE user = 'root';        

This showed that the root user was configured to use the mysql_native_password plugin, which expects a password-based login. The issue was that I hadn’t set a password for root during installation, which explained why I couldn’t log in. Matter of fact, the password wasn't even being requested. Even though the plugin was correct, the lack of a password was causing MySQL Workbench to just crash because I got a denied connection in the underlying process.

I needed to set a root password. Thankfully, this part was straightforward and well explained in that stackoverflow thread. I ran:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'here I put my new password';        

Setting a password is one thing, but it’s also important to ensure that MariaDB recognizes the change right away. To do that, I had to flush privileges so that the new settings would take effect

FLUSH PRIVILEGES;         

This step makes sure that any changes to users or privileges are applied immediately. Without flushing, I might have still encountered issues because MariaDB would be using the old configuration until I restarted it or manually flushed the privileges.

Now that the password was set, I needed to restart MariaDB in normal mode, re-enabling networking and restoring the normal authentication flow. To do this, I first stopped the current MariaDB process that was running in safe mode:

Then, I started MariaDB again, this time without the "--skip-networking" flag, allowing it to handle both local and remote connections:

sudo systemctl start mariadb        

At this point, everything should have been working. I opened MySQL Workbench and tried connecting with the new root password to test . To double-check everything from the terminal, I also ran:

mysql -h 127.0.0.1 -u root -p        

After entering the password, I was able to log in successfully both through MySQL Workbench and the terminal. The issue was finally resolved.

Observation:

During this process of running MariaDB, I accidentally made another mistake.

When I tried starting MariaDB to use the MariaDB console again to run those commands, I got an error:

Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.        

I searched the internet and discovered this thread and also this other thread adressing a similar issue (not quite like mine but close enough so I could understand).

While reading, it immediately made me remember that, when I was running the MariaDB console in the previous session, I wrongly exited it using Ctrl + Z instead of typing in the query "EXIT".

This suspended the MariaDB process instead of stopping it, which caused further issues when I tried restarting MariaDB.

This specific problem is due to when MariaDB is suspended, it still holds on to resources like these:

• If sockets or ports are still open from the suspended process, MariaDB refuses to start because it assumes another instance is already using those resources
• If locks on database files or active connections are still held by the suspended process, MariaDB can't access its own files and refuses to start.

So, in my case, I knew I had to either kill the background process or bring it to foreground again and keep using it. I'm not sure if the solutions the other users mentioned in the forums would've worked for me, but in my case I had that suspended job to solve.

For anyone who encounters this, you can bring the suspended process back to the foreground using: fg %1 (In this case that was the only process in the background and that is why it was '%1', but it could've been another)

Once the process is in the foreground, you can then properly terminate it by running the 'exit' query, or Ctrl + C if it's another terminal application. It’s a small detail, but an important one when working with services like MariaDB on Linux.

Unrelated fun fact:

I realize now that it was a habit I acquired by using nano (incorrectly, obviously) ever since my first programming classes in college, where I edited C files and compiled them using gcc in the terminal, just as the teacher did. And I stuck with the wrong habit of """exiting""" nano with Ctrl + T and then Ctrl + Z after saving the changes. What was really happening was that I was stacking suspended nano processes.

Later on, when installing arch linux for the first time, I also used nano to edit some config files during the process. I edited a total of 1 file (I reckon it was a wheel user group list) before I couldn't return to those files or open another because it returned a message saying nano was already running. An outcome of this marvelous, senior-like practice of mine, lol. So I had to search on the internet (using my phone since the computer wasn't usable) how to kill that process in the background to continue the installation. It doesn't take a mastermind to know I've been exiting nano using Ctrl X ever since. But i still had that muscle memory of using Ctrl + Z for terminal-based applications and it caused me other problems later with MariaDB console which is kind of funny. Learned the hard way though.

What I learned from this Headaching journey:

1. How MySQL and MariaDB manage root authentication: I now understand how both database systems use authentication plugins like mysql_native_password and how the lack of a root password can cause login issues.
2. Importance of setting good root passwords: During installation, setting a root password is crucial to ensuring secure and functional access to the database. Overlooking this can lead to connection issues.
3. Using MariaDB in safe mode: Starting MariaDB in safe mode is a useful technique for bypassing password issues, allowing root access without authentication and enabling further investigation.
4. The role of FLUSH PRIVILEGES query: To make sure those changes are applied immediately.
5. Handling suspended processes on linux: Using Ctrl + Z suspends processes and doesn't terminate them, which might cause issues when managing services that run on the terminal like MariaDB console. I learned how to bring back a suspended process with fg %1 and properly stop it.
6. Patience when problem-solving: Even though using tools like ChatGPT can provide quick solutions, the hands-on troubleshooting approach taught me valuable skills, deepened my understanding, and built my confidence with databases. This is something I learned ever since chatgpt was released but I still like knowing what I'm doing.