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:
sudo mysql -u root
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.
create user 'root'@'localhost' identified by '';
grant all privileges on *.* to 'root'@'localhost' with grant option;
Now exitMySql with exit; or CTRL+D and try to log in again without sudo, job done!