Can't set root password MySql Server

November 3, 2019 120 views
MySQL

Hi All -

I have a new droplet with Ubuntu 18.04 and am installing MySql using

sudo apt-get install mysql server

It seems to install fine but does not ask for a root password on install, I am pretty sure this is as it is expected to work.

With this from the cli I can log into it with just using ‘mysql’, while that is handy it is not very secure.

So I stop the services and use

sudo mysql_secure_installation

I go through the steps, set a root password, remove anon users, stop remote root access etc..

Restart the service

I have tried flushing privileges

I can still log in with no password.

I have then tried a couple of other methods that were outlined here, again no luck.

https://www.cyberciti.biz/faq/mysql-change-user-password/

Any thoughts ? What am I forgetting or missing ?

I am beginning to feel real dumb.

Thanks

1 Answer

Hello,

I was able to replicate the same behavior at my end. What I had to do to get this fixed was:

  • Connect to MySQL
mysql
  • Check your authentication screen, initially you should see an empty filed:
SELECT user,authentication_string FROM mysql.user;
  • Set a root password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pass_here';
  • Flush the privileges:
FLUSH PRIVILEGES;
  • Again check your authentication string and make sure that it is not empty:
SELECT user,authentication_string FROM mysql.user;

That is pretty much it, after that you should always be prompted for a password.

Hope that this helps!
Regards,
Bobby

Have another answer? Share your knowledge.