MySQL8.0修改密碼的正確姿勢分享
前言
mysql 更新完密碼,總是拒絕連接、登錄失敗?MySQL8.0 不能通過直接修改 mysql.user 表來更改密碼。正確更改密碼的方式備註: 清空root密碼
MySQL8.0 不能通過直接修改 mysql.user 表來更改密碼。
因為authentication_string字段下隻能是MySQL加密後的43位字符串密碼,其他的導致錯誤。錯誤不報出,但是無法再登錄mysql,總是會提示 無法認證。
參考:MySQL8.0
mysql> USE mysql; Database changed mysql> UPDATE user SET authentication_string="123456" WHERE user="root"; Query OK, 1 row affected (0.39 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> FLUSH privileges; # 刷新保存 Query OK, 0 rows affected (0.13 sec)
mysql 5.7.9 之後取消瞭password 函數,authentication_string=password("123456") 會報錯
C:\WINDOWS\system32>mysql -u root -p
Enter password: ******
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
1234567891011121314
如果 你已經這樣更改密碼,並且導致瞭無法進入mysql。本人表示同情之時,還為瞭你提供瞭詳細的解決方案。請查看本文備註:清空root 密碼
正確更改密碼的方式
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY "your_password"; mysql> USE mysql; Database changed mysql> ALTER USER 'root'@'localhost' IDENTIFIEED WITH mysql_native_password BY "markjun"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIEED WITH mysql_native_password BY "markjun"' at line 1 mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY "markjun"; Query OK, 0 rows affected (0.18 sec) mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY "123456"; Query OK, 0 rows affected (0.08 sec) mysql> SELECT user, authentication_string FROM user; +------------------+------------------------------------------------------------------------+ | user | authentication_string | +------------------+------------------------------------------------------------------------+ | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------------------+------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> FLUSH privileges; Query OK, 0 rows affected (0.38 sec) mysql> quit; Bye F:\MySQL\mysql-8.0.13-winx64\bin>mysql -u root -p Enter password: ******* ... mysql>
- 備註: 清空root密碼
停止 MySQL 任務 net stop MySQL
mysqld 命令 mysqld –console –skip-grant-tables –shared-memory
無密碼進入mysql mysql -u root
清空root 密碼 UPDATE user SET authentication_string="" WHERE user=“root”;
另一個終端無密碼進入
F:\MySQL\mysql-8.0.13-winx64\bin>mysql -u root -p Enter password: ******* ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) F:\MySQL\mysql-8.0.13-winx64\bin>mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 ... Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> USE mysql; Database changed mysql> SELECT user, authentication_string FROM user; +------------------+------------------------------------------------------------------------+ | user | authentication_string | +------------------+------------------------------------------------------------------------+ | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | root | 123456 | +------------------+------------------------------------------------------------------------+ 4 rows in set (0.34 sec) mysql> UPDATE user SET authentication_string="" WHERE user="root"; Query OK, 1 row affected (0.20 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT user, authentication_string FROM user; +------------------+------------------------------------------------------------------------+ | user | authentication_string | +------------------+------------------------------------------------------------------------+ | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | root | | +------------------+------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> FLUSH privileges; Query OK, 0 rows affected (0.10 sec) mysql> quit; Bye
停止 mysqld 任務,Ctrl+C 結束任務,或者直接關閉 運行 mysqld 的 cmd 終端。
需要先停止運行上述 mysqld 任務,否則報錯
F:\MySQL\mysql-8.0.13-winx64\bin>net start mysql
MySQL 服務正在啟動 .
MySQL 服務無法啟動。服務沒有報告任何錯誤。
請鍵入 NET HELPMSG 3534 以獲得更多的幫助。
先停止上述 mysqld 任務
F:\MySQL\mysql-8.0.13-winx64\bin>net start mysql
MySQL 服務正在啟動 …
MySQL 服務已經啟動成功。
現在 mysql root 已經沒有瞭密碼
F:\MySQL\mysql-8.0.13-winx64\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.…
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
總結
到此這篇關於MySQL8.0修改密碼的正確姿勢的文章就介紹到這瞭,更多相關MySQL8.0修改密碼姿勢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Windows下mysql 8.0.29 winx64安裝配置方法圖文教程
- Navicat連接MySQL8.0的正確方法(親測有效)
- win10下安裝mysql8.0.23 及 “服務沒有響應控制功能”問題解決辦法
- MySQL8.0.23版本的root密碼重置最優解法
- 找回MySQL管理員root密碼的3個妙招