忘記密碼，無法登入資料庫
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)'
解決辦法
[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 in set (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 in set (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 in set (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 in set (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:
ERROR 1045 (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 interface can 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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/37316.html
標籤：其他
下一篇：【Redis】主從復制