Resolve mysql login error: 'Access denied for user' root '@' localhost '

First of all, I don't know why mysql suddenly uses the command line, but workbench can't log in, and they all prompt 'Access denied for user' root '@' localhost '.
The database can't be uninstalled and reinstalled several times. It seems that the data is not cleaned up. The process of solving the pit encountered, record and share it here.

Valid operation records:

1. First, skip the permission to log in to mysql and view the user table.

Stop mysql service ~ $ sudo service mysql stop

Start in Safe Mode MySQL~$ sudo mysqld_safe --skip-grant-tables &

Note:

May prompt mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don 't exist

Solution: (The author tried to find that sudo must be added)

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

Perform the above safe mode startup again mysql ~$ sudo mysqld_safe --skip-grant-tables &

This prompt statement shows that, mysqld_safe Staring mysqld deamon with database from /var/lib/mysql

You can log in this time without password: mysql-u root Enter and log in

The sql statement queries the mysql. user table as follows:

Add pictures (to be supplemented later)

Others suggested doing this as follows, but did not try

Open & Edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distro.
Add skip-grant-tables under [mysqld]

2. It is found that plugin of user is socket_plugin and changed to mysql_native_password

Modify plugin permissions for root:

update mysql.user set authentication_string=PASSWORD('newPwd'), plugin='mysql_native_password' where user='root';
flush privileges;
quit;

(Note that the modification 1 must be correct here, so as not to be like the author 1, carelessly modifying the content of plugin by one letter less, and having the following tossing)

3. There are other users in user table. root can't log in. It's OK to log in with other users. Query found that 1 valid operation:

In the installation directory of mysql, there is an debain. cnf file in/etc/mysql, which contains user and password. With this, we use login and password to copy at last, and then we can modify user table root. The operation is the same as above.

Restart the mysql service, sudo service mysql restart;

Users can log in with root.

Reference blog:

MySQL ERROR 1698 (28000) Error https://www.ofstack.com/article/117566. htm

mysql Method for viewing the currently used configuration file my. cnf https://www.ofstack.com/article/110395. htm

linux-Ubuntu View and modify the login name and password of mysql, and install phpmyadmin https://www.ofstack.com/article/174925. htm

Summarize

The above is the solution to mysql login error introduced by this site: ''Access denied for user'' root ''@'' localhost '', hoping to help you!