root@localhost ~]# mysql -uroot -p //密码输入错误，无法登入
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost'(using password: YES)[root@localhost ~]# mysqladmin -u root -p password '123456' //忘记原始密码，也无法靠mysqladmin修改密码方式登入
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
1
2
3
4
5
6
7
1
2
3
4
5
6
7
解决办法
[root@localhost ~]# vi /etc/my.cnf //修改配置[mysqld]
……省略部分
skip-grant-tables //插入空授权登入配置[root@localhost ~]# systemctl restart mysqld //重启MySQL[root@localhost ~]# mysql -uroot -p
Enter password://直接回车，登入成功
Welcome to the MySQL monitor. Commands end with; or \g.
Your MySQL connection id is 4
Server version:5.7.20 Source distribution
……省略部分
mysql>
mysql> desc mysql.user;//查看表结构，找到密码存放字段，5.0版本在authentication_string字段中+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host |char(60)|NO|PRI|||| User |char(32)|NO|PRI|||| Select_priv |enum('N','Y')|NO||N||| Insert_priv |enum('N','Y')|NO||N||| Update_priv |enum('N','Y')|NO||N||| Delete_priv |enum('N','Y')|NO||N||| Create_priv |enum('N','Y')|NO||N||| Drop_priv |enum('N','Y')|NO||N||| Reload_priv |enum('N','Y')|NO||N||| Shutdown_priv |enum('N','Y')|NO||N||| Process_priv |enum('N','Y')|NO||N||| File_priv |enum('N','Y')|NO||N||| Grant_priv |enum('N','Y')|NO||N||| References_priv |enum('N','Y')|NO||N||| Index_priv |enum('N','Y')|NO||N||| Alter_priv |enum('N','Y')|NO||N||| Show_db_priv |enum('N','Y')|NO||N||| Super_priv |enum('N','Y')|NO||N||| Create_tmp_table_priv |enum('N','Y')|NO||N||| Lock_tables_priv |enum('N','Y')|NO||N||| Execute_priv |enum('N','Y')|NO||N||| Repl_slave_priv |enum('N','Y')|NO||N||| Repl_client_priv |enum('N','Y')|NO||N||| Create_view_priv |enum('N','Y')|NO||N||| Show_view_priv |enum('N','Y')|NO||N||| Create_routine_priv |enum('N','Y')|NO||N||| Alter_routine_priv |enum('N','Y')|NO||N||| Create_user_priv |enum('N','Y')|NO||N||| Event_priv |enum('N','Y')|NO||N||| Trigger_priv |enum('N','Y')|NO||N||| Create_tablespace_priv |enum('N','Y')|NO||N||| ssl_type |enum('','ANY','X509','SPECIFIED')|NO||||| ssl_cipher | blob |NO||NULL||| x509_issuer | blob |NO||NULL||| x509_subject | blob |NO||NULL||| max_questions |int(11) unsigned |NO||0||| max_updates |int(11) unsigned |NO||0||| max_connections |int(11) unsigned |NO||0||| max_user_connections |int(11) unsigned |NO||0||| plugin |char(64)|NO|| mysql_native_password ||| authentication_string | text |YES||NULL||| password_expired |enum('N','Y')|NO||N||| password_last_changed | timestamp |YES||NULL||| password_lifetime |smallint(5) unsigned |YES||NULL||| account_locked |enum('N','Y')|NO||N||+------------------------+-----------------------------------+------+-----+-----------------------+-------+45 rows inset(0.00 sec)
mysql> select host,user,authentication_string from mysql.user;//查看host，user，authentication_string上字段的数据+-----------+---------------+-------------------------------------------+| host | user | authentication_string |+-----------+---------------+-------------------------------------------+| localhost | root |*6691484EA6B50DDDE1926A220DA01FA9E575C18A || localhost | mysql.session |*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE|| localhost | mysql.sys |*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE||%| myadm |*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C || localhost | myadm |*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C ||%| bbs |*6691484EA6B50DDDE1926A220DA01FA9E575C18A || localhost | bbs |*6691484EA6B50DDDE1926A220DA01FA9E575C18A || localhost | ltp_admin |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+---------------+-------------------------------------------+8 rows inset(0.00 sec)
mysql> select host,user,authentication_string from mysql.user where user='root' and host='localhost';//精准过滤出root用户的密码字段+-----------+------+-------------------------------------------+| host | user | authentication_string |+-----------+------+-------------------------------------------+| localhost | root |*6691484EA6B50DDDE1926A220DA01FA9E575C18A |+-----------+------+-------------------------------------------+1 row inset(0.00 sec)
mysql> update mysql.user set authentication_string=null where user='root' and host='localhost';
Query OK,1 row affected(0.01 sec)
Rows matched:1 Changed:1 Warnings:0
mysql> select host,user,authentication_string from mysql.user where user='root' and host='localhost';+-----------+------+-----------------------+| host | user | authentication_string |+-----------+------+-----------------------+| localhost | root |NULL|+-----------+------+-----------------------+1 row inset(0.00 sec)
mysql> \q
Bye
[root@localhost ~]# vi /etc/my.cnf //修改配置[mysqld]
……省略部分
# skip-grant-tables //将这条免密登入注释掉[root@localhost ~]# systemctl restart mysqld //重启生效配置[root@localhost ~]# mysql -uroot -p
Enter password:ERROR1045(28000): Access denied for user 'root'@'localhost'(using password:YES)[root@localhost ~]# mysql -uroot -p
Enter password://已经是空密码状态了，直接回车登入
Welcome to the MySQL monitor. Commands end with; or \g.
……省略部分
mysql>
mysql> \q //退出MySQL ，可以重新设置密码了
Bye
[root@localhost ~]# mysqladmin -u root -p password
'123456'//重新设置密码
Enter password:
mysqladmin:[Warning] Using a password on the command line interfacecan be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.[root@localhost ~]# mysql -uroot -p //验证，使用新密码登入成功
Enter password:
Welcome to the MySQL monitor. Commands end with; or \g.
……省略部分
mysql>
