MySql Access Denied for User root without sudo

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) is not something you want to see when you try and login to mysql using the default root account with no password, especially when there is no password, on a fresh install!

So this was the problem I faced when I installed MySql on Ubuntu Server 18.04 TLS.

I SSH into my server since its a headless machine, just sat there with no peripherals connected at all. Just an ethernet cable and power cable. I was beginning to set up the machine as a local network server for a database heavy project I’m working on, so I installed MySql with sudo apt install mysql-server and immediately tried logging in to create a non-root user for my tasks:

mysql -u root

I typed. It threw me that error above. So I tried it with sudo and sure enough it worked. I’m not entirely sure why, but it did. Perhaps because my machine is referred to as the-lab and MySql just didn’t know who that was, because it isn’t localhost. Who knows.

Anyway, to remedy this particular issue I had to run a series of commands that removed the root user and added it again, after logging in with sudo. The steps were as follows, in case you find yourself in a similarly annoying situation:

  1. sudo mysql -u root
  2. drop user 'root'@'localhost'; This one is a bit scary because, well, you’re deleting the root user. But rest assured you can still continue to run commands after this, I promise.
  3. create user 'root'@'localhost' identified by '';
  4. grant all privileges on *.* to 'root'@'localhost' with grant option;
  5. flush privileges;

Now exitMySql with exit; or CTRL+D and try to log in again without sudo, job done!

Bookmark the permalink.

Comments are closed.