Solve MySQL login error: 'Access denied for user 'root'@'localhost'

First of all, I don't know why I can't log in to MySQL using the command line or workbench, and it prompts 'Access denied for user 'root'@'localhost'.
Uninstalling and reinstalling the database several times did not work. It seems that the data is not cleaned up properly. The pitfalls encountered during the solution process are recorded and shared here.

Valid operation records:

1. First, skip the permissions to log in to MySQL and view the user table.

Stop mysql service~ $ sudo service mysql stop

Start MySQL~$ sudo mysqld_safe --skip-grant-tables &

Notice:

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

Solution: (I tried and found that sudo must be added)

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

Execute the above safe mode again to start mysql ~$ sudo mysqld_safe --skip-grant-tables &

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

This time you can log in without a password: mysql -u root Press Enter to log in

The sql statement to query the mysql.user table is as follows:

Add pictures (to be added later)

Someone else suggested doing it this way, but I didn't try it.

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

2. Found that the user's plugin is socket_plugin, changed to mysql_native_password

Modify the plugin permissions of root:

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

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

3. There are other users in the user table. Root cannot log in, but you can log in with other users. After searching, I found the following valid operations:

In the installation directory of mysql, there is usually a debain.cnf file in /etc/mysql, which contains user and password. Use this file to log in and copy the password. Then you can modify the plugin of the user table root again. The operation is the same as above.

Restart the mysql service, sudo service mysql restart;

You can log in as the root user.

Reference blog:

MySQL ERROR 1698 (28000) error https://www.jb51.net/article/117566.htm

mysql How to view the currently used configuration file my.cnf https://www.jb51.net/article/110395.htm

linux -- Ubuntu view and modify mysql login name and password, install phpmyadmin https://www.jb51.net/article/174925.htm

Summarize

The above is the editor's introduction to solving the MySQL login error: ''Access denied for user ''root''@''localhost''. I hope it will be helpful to everyone!