4

I keep getting the following error on running sudo **mysql_secure_installation**

... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

Any help will be appreciated.

Updated:

solved it by running sudo mysql which logged me in as root without a password, then I ran ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

Following this, I was able to run mysql_secure_installation again and this time I got to choose to use the existing password (the one I set with above SQL command).

However now I can no longer login without a password, running sudo mysql now denies root user access:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

How can I log in back using sudo mysql again?

nehemuel
  • 61
  • 1
  • 5
  • Does this answer your question? [MySQL 5.7.20 unable to set root password](https://stackoverflow.com/questions/47099351/mysql-5-7-20-unable-to-set-root-password) – esqew May 22 '22 at 01:52
  • @esqew It's work, but now I can no longer login running sudo mysql now denies root user access: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) – nehemuel May 22 '22 at 02:34

3 Answers3

0

I suggest using select user,host from mysql.user first; Statement to check your user permissions, and then make corresponding settings. From the above error, it is mainly a matter of permissions.

dogs Cute
  • 564
  • 3
  • 9
0

Here is the thing. 1st you cannot log in to MySQL now without entering the password as you already set the password. So now to log in without the password, you can save the password somewhere, there are two thing which you can do

1: Connecting with a profile

The first thing you must do is create a profile for passwordless authentication. This is done with the help of the mysql_config_editor tool. Let’s say we’re going to create a profile for the MySQL server, named mysql1, running on IP address 192.168.1.158, with an admin user of dbadmin. To do this, you would issue the command:

mysql_config_editor set –login-path=mysql1 –host=<Server-ip> –user=dbadmin –password

Now let’s connect to that mysql1 server with the profile. From a terminal window, issue the command:

mysql –login-path=mysql1

You will immediately be taken to the MySQL prompt on the MySQL server on . That’s it.

2:Create a file in root home as below

cd ~
vi .my.cnf
##save below

[client]
user=root
password = asdfghjkl

then only type MySQL and you will be able to connect to the server.

Mughees Ahmed
  • 61
  • 2
-1

According to the previous solution MySQL 5.7.20 unable to set root password

You should try

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypasswordhere';
GM8
  • 64
  • 1
  • If you feel the content of another Q&A thread on Stack Overflow sufficiently answers the question at hand, please flag as "Needs improvement -> Duplicate" rather than answer the question to ensure all answers can be centralized and maintained in a single place, rather than having multiple Q&A threads in which the information is not as easily kept up-to-date. – esqew May 22 '22 at 01:52