Centos7解决MySQL登录ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)

refer: https://www.cnblogs.com/hangun/p/13330832.html

问题描述：
Virtual machine in VMware workstation: Centos7.7
登录mysql,report 下面的问题：

[root@localhost ~]# mysql -u root -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

解决方法：
Step1：屏蔽权限，输入命令vim /etc/my.cnf打开配置文件, 添加skip-grant-tables；

[root@localhost ~]# vi /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
"/etc/my.cnf" 28L, 978C written
[root@localhost ~]#

然后保存退出。

Step2：重启mysql服务，service mysqld restart

[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost ~]#

Step3：登录mysql，输入mysql -u root mysql并回车，此时登录成功。

[root@localhost ~]# mysql -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> [root@localhost ~]#

Step4：检查mysql表是否可以看见，输入show databases;并回车。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gogs               |
| istester           |
| mysql              |
| performance_schema |
| sys                |
| testDB             |
+--------------------+
7 rows in set (0.10 sec)

mysql> 

step5：进入mysql，修改密码。
执行后报错 　ERROR 1054(42S22) Unknown column ‘password’ in ‘field list’

错误的原因是 5.7版本下的mysql数据库下已经没有password这个字段了，password字段改成了authentication_string

mysql>  update user set password=password('123') where user='root' and host='localhost'; 
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('password') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> update mysql.user set authentication_string=password('password') where user='cynthia' and host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

mysql>

step6, remove skip-grant-tables from /etc/my.cnf, save, and restart mysql. 登录mysql成功

[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>