Question 46923 in Debian Question posted in
The oficial documentation can be found here.

Password login not functioning in MySQL – Debian

I installed MySQL server 8.0 in my ubuntu 20.04 EC2 instance and also did the secure installation using $ sudo mysql_secure_installation where I set the password for the root user. I also disabled login into MySQL through anonymous users.

I used $ sudo mysql -u root -p to login to MySQL and used the wrong password to confirm whether or not the correct password was enabled and was surprisingly able to login.

Also, I was able to login without the password using just $ sudo mysql .

After this I tried to set the password using ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD'; followed by FLUSH PRIVILEGES; in MySQL but got the same results as before.

I also tried modifying the user table directly using UPDATE mysql.user SET authentication_string=PASSWORD('NEW_USER_PASSWORD') WHERE User='root' AND Host='localhost'; but was met with a syntax error : –

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right s
yntax to use near '('NEW_USER_PASSWORD') WHERE User='root' AND Host='localhost'' at line 1

Edit: The mysql.user table before I changed the authentication plugin of root user from auth_socket to caching_sha2_password .

     +-----------+------------------+-----------------------+
     | host      | user             | plugin                |
     +-----------+------------------+-----------------------+
     | localhost | debian-sys-maint | caching_sha2_password |
     | localhost | mysql.infoschema | caching_sha2_password |
     | localhost | mysql.session    | caching_sha2_password |
     | localhost | mysql.sys        | caching_sha2_password |
     | localhost | root             | auth_socket           |
     +-----------+------------------+-----------------------+

How can I solve these problems and set my password for the root user?

Tags:

2

Answers


  1. 0

    Mysql will permit the (operating system) root user to login without a password based on the principle that if you’re root, you have access to the data anyway. Dont use sudo if you want to test a non-root user login.

    Login or Signup to reply.
  2. 0

    In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password

    You can change root password like bellow:

    ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_pass';

-- If first fail because you have changed default authentication plugin you can use mysql_native_password 

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pass';

FLUSH PRIVILEGES;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search