0

I have just setup a new vps with Ubuntu 18, I did a fresh installation of mysql-server:

sudo apt-get update
sudo apt-get install mysql-server
sudo mysql_secure_installation

In this menu, I typed in my new root password (and confirm) Remove anonymous users? Yes Disallow root login remotely? Yes Remove test databases and access to it? Yes Reload privileges table now? Yes

And done! ...or not

I attempted to login using mysql -u root -p but it kept telling my access denied, for a second I was confused, then I realized I probably needed to login using sudo, so I did.

But now, using "mysql -u root" or "mysql -u root -p" (using ANY password) I can login just fine?!

I am completely dumbfounded.. I have attempted doing alot of different things, like using a query to alter the root password, but nothing seems to work. I can always log in with any password (or none).. And this is a completely fresh mysql installation (I removed and reinstalled it 3 times now to see if I do something differently..)

The only thing I noticed that seemed different, is that in online tutorials on how to install mysql-server the first question in "mysql_secure_installation" it should ask you if you want to set a password for root, which I did not get asked for, just prompted to enter the new password.

I tried googling this issue, but could only find "solutions" for the reverse (people either not able to login, or wanting to set an always correct password)

I have no idea where to go from here...

Alex
  • 11
  • 2
  • Well now I am even in deeper trouble.. I can't login from root user anymore either. It says access denied but I am 100% correct the password is correct. – Alex Nov 01 '19 at 17:12

1 Answers1

1

After visiting the "possible duplicate" I was further from a solution than before, but eventually I did find a solution. But first I had to launch mysqld_safe to be able to access mysql again without a password: BUT now I ran into even more trouble! I could not connect to the Mysqld safe socket.. This is a solution I found posted by someone else on stackoverflow:

I found that the mysql.sock is deleted when the mysql service is stoped and mysqld_safe can't create it (I couldn't find the reason), so my solution was back up the sock folder and restore before start mysqld_safe

Start server $ sudo service mysql start

Go to sock folder $ cd /var/run

Back up the sock $ sudo cp -rp ./mysqld ./mysqld.bak

Stop server $ sudo service mysql stop

Restore the sock $ sudo mv ./mysqld.bak ./mysqld

Start mysqld_safe $ sudo mysqld_safe --skip-grant-tables --skip-networking &

Init mysql shell mysql -u root

After that I attempting resetting the password again using various given examples.. none did the job, but the solution was in the plugin, the "possible duplicate" suggested setting the plugin to '', but that did not work.

However, what did work for me was setting it to the native mysql password and I ran the following commands:

use mysql;
update user set authentication_string=PASSWORD("") where User='root';
update user set plugin="mysql_native_password" where User='root';  # THIS LINE

flush privileges;
quit;

Now MySQL acts as expected: I can login to mysql root from any user (not neccesarily while using sudo) and no longer all passwords are accepted. (I had no idea there was some kind of auth plugin, this isn't the first time I setup a mysql database but this plugin is completely new to me)

Alex
  • 11
  • 2