1

I'm running Linux Mint as my dev platform. Installed mysql Ver 8.0.28-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu)). My problem is that mysql is ONLY connectable for (system) root user, i.e. I have to run sudo mysql to connect to localhost. I tried to use setuid on mysql client, which works, but I am pretty sure I should not do this...

Further more, I can now only connect to local server via mysql command line tool, mysql-workbench, adminer (php based web client)... none can connect to local server.

How can I let the server be accessible to normal non-root users.

Again I mean system (linux) non-root user, not the database user.

9
  • Do you use mysql -u $useranme -p ? By default the root user is autheticatet thru a PAM module Apr 13, 2022 at 13:11
  • could you explain a bit more? I don't know why PAM has anything to do here. Using mysql-workbench, it should communicate with local server via mysql protocol (TCP), seems nothing to do with system authentication? This is the only machine that's having such weird problem. I just need help to check and fix this error.
    – xrfang
    Apr 13, 2022 at 13:18
  • By default, the mysql root user is only able to login with the pam module. Can you try to create a second user in mysql and grant all rights to it? You can also try to start mysql-workbench as root if thats possible on your maschine Apr 13, 2022 at 13:30
  • mysql-workbench does not work for root user. I will try fix the pam stuff first.
    – xrfang
    Apr 13, 2022 at 13:33
  • askubuntu.com/a/801950 mybe this will hlep you Apr 13, 2022 at 13:35

1 Answer 1

1

This problem is explained in this post:

Even if you have setup password for MySQL root user, you still can't authenticate with the database server as root user with a password. Because, the MySQL root user is set to authenticate using the auth_socket plugin by default instead of the password in Ubuntu systems running MySQL 5.7 and newer versions. Hence, you can can't access the MySQL server with root user and its password.

The symptom is, if you try:

mysql -u root -p

and give the correct password, you still get:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The solution is to change authentication method of the MySQL root user from auth_socket to something else, like caching_sha2_password. The procedure to do so is explained in the above post. Thanks to Henrik Halbritter for the comment.

Use chmod to "setuid" the MySQL program /usr/bin/mysql will eliminate the need to use sudo in order to connect to my local server. However, this does not solve the problem that mysql-workbench still cannot connect.

There might be other settings in mysql-workbench, for example, select appropriate authentication method, but I didn't try.

Final remark: change authentication for the root user should only be used in development environment. In production, a non-root mysql user should be created and used.

1
  • Query used to change authentication methods: ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'my_password';
    – Skeets
    Oct 14, 2022 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.