0

I have a new mysql server 8 install on Ubuntu 20.04, and I'm simply trying to set the root password, which is required by an application I'm trying to install. I've been at it an hour, and every website I find says the exact same thing. This is what I'm doing:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Then I restart mysql-server just to be safe, then try and login:

mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I have no idea what is going wrong. I do see that authentication_string for root@localhost is empty, which doesn't seem right.

Edit:

It's becoming clear that "changing the password" isn't really the issue. Something else is going on here. I'm failing to login even after setting the password with the command "sudo mysql_secure_installation".

"plugin" for root@localhost is set to "auth_socket". I don't know what that means yet, but it seems like some external process has been made responsible for authenticating that user.

Edit2:

It looks like auth_socket just checks to see if your logged in on linux as the user you are trying to log in with on mysql. That means that no non-root linux user could ever log in as root on mysql. So it seems I'll have to remove this auth_socket plugin and just switch to a normal mysql authentication method. Anybody know the best way to do this without breaking anything? Would removing the user and recreating it break a lot of stuff?

Dave
  • 1,326
  • 2
  • 11
  • 22
  • "Query OK, 0 rows affected (0.01 sec)" means query is not working, see if the idetified by or username is correct or not – mahen3d May 04 '21 at 23:40
  • @mahen3d incorrect. If the user didn't exist it would have an error in the form `ERROR 1396 (HY000): Operation ALTER USER failed for 'nosuchuser'@'localhost'`. `flush privileges` and restart are not required. Are you connecting to the same server? Can you look at `show create user root@localhost` to see if its locked/expired. – danblack May 05 '21 at 00:10
  • Hope, this will help you - https://stackoverflow.com/a/42484420/9348637 – Iynga Iyngaran Iyathurai May 05 '21 at 02:55
  • You need to do this *as an adminstrator* from the operating system's point of view. Otherwise the change only lasts as long as your MySQL session. – user207421 May 05 '21 at 03:51
  • This is what the show create user gives: CREATE USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT – Dave May 05 '21 at 13:11
  • Trying doing this while accessing mysql as linux root (sudo mysql -u root) but still not working. – Dave May 05 '21 at 13:13
  • Does this answer your question? [MySQL: How to reset or change the MySQL root password?](https://stackoverflow.com/questions/16556497/mysql-how-to-reset-or-change-the-mysql-root-password) – Progman May 24 '21 at 09:42

1 Answers1

1

Finally got it working. I was right that the problem was the authentication plugin. Changing it to "mysql_native_password" allowed me to set the password as expected.

sudo mysql -u root
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'root';
FLUSH PRIVILEGES;
QUIT;
Dave
  • 1,326
  • 2
  • 11
  • 22