實用小技巧！ 修改MySQL密碼
如果忘記密碼無法登入資料庫
root@localhost ~]# mysql -uroot -p //密碼輸入錯誤，無法登入
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
解決方案
```sql
[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> 相關文章
- mysql 忘記密碼，mysql修改密碼MySql密碼
- mysql修改root密碼MySql密碼
- 修改mysql root密碼MySql密碼
- mysql8.0修改密碼MySql密碼
- 修改 MySQL 登入密碼MySql密碼
- mysql修改管理員密碼MySql密碼
- 修改mysql的root密碼MySql密碼
- MYSQL修改密碼和忘記ROOT密碼MySql密碼
- 每天一個實用小技巧！教你在Mac上共享WiFi密碼MacWiFi密碼
- 修改Mysql root密碼的方法MySql密碼
- 密碼修改最佳實踐密碼
- MYSQL5.7 ROOT密碼修改教程MySql密碼
- MySQL 8.0之後版本密碼修改MySql密碼
- 修改MySQL密碼的四種方法MySql密碼
- MySQL修改密碼方法總結YRMySql密碼
- mysql修改使用者密碼MySql密碼
- MySQL 5.7修改忘記root密碼MySql密碼
- MySQL修改密碼方法總結 (轉)MySql密碼
- mysql密碼忘記的修改(轉)MySql密碼
- MySQL8.0 忘記 root 密碼下如何修改密碼MySql密碼
- ubuntu系統下mysql重置密碼和修改密碼操作UbuntuMySql密碼
- MySQL修改使用者密碼及重置root密碼MySql密碼
- mysql學習之-密碼管理（預設密碼，修改密碼，解決忘記密碼）MySql密碼
- mysql 8.0.11 以上版本修改root密碼MySql密碼
- 怎麼修改mysql的連線密碼MySql密碼
- MySQL修改賬號密碼方法大全MySql密碼
- 在Linux中修改MySQL的密碼LinuxMySql密碼
- MySQL修改root密碼的多種方法MySql密碼
- mysql root 修改密碼的多種方法MySql密碼
- MySQL修改root密碼的幾種方法MySql密碼
- 介紹Linux小技巧，如何實現免密碼登入Linux密碼
- mysql5.7後修改root使用者密碼（實測有效）MySql密碼
- Tomcat 實用小技巧Tomcat
- 實用的jQuery小技巧jQuery
- mysql如何修改root使用者的密碼MySql密碼
- mysql8.0+修改使用者密碼MySql密碼
- 修改MySQL的root使用者的密碼MySql密碼
- MySql修改root密碼、設定IP訪問MySql密碼