I'm getting an error when I ran mysql_secure

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?

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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';