近期，協助一客戶重置mysql 8.0.11的root密碼，碰到了奇怪的問題。
操作系統版本：rhel 7.6
數據庫版本：mysql 8.0.11
根據mysql 8.0官方文檔說明，mysql的用戶密碼有限制，并且被硬編碼，沒有明確說明密碼長度是多少。
The MySQL user name length limit is hardcoded in MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the
mysql database
does not work.
You should never alter the structure of tables in the
mysql database in any manner whatsoever except by means of the procedure that is described in
Section 2.11, “Upgrading MySQL”. Attempting to redefine MySQL's system tables in any other fashion results in undefined and unsupported behavior. The server is free to ignore rows that become malformed as a result of such modifications.
另外，mysql 8.0不再支持password()函數，因此，不能像mysql5.x那樣通過update mysql.user表來修改用戶密碼。
但是，在mysql 8.0的root密碼重置時遇到報錯：
[mysql@mysql8~]$ mysql -uroot -p -S /home/mysql/mysql/tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, 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>
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
--update mysql.user重置用戶密碼失敗
mysql> update user set authentication_string = '123456' where user='root' and host='%' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| root | % | 123456 |
| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
--password函數不被mysql8.0支持
mysql> update user set authentication_string =PASSWORD('123456') where user='root' and host='%' ;
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 '('
123456') where user='root' and host='%'' at line 1
mysql>
--奇怪的是使用alter命令重置root密碼長度為6位時報錯，但是不是語法錯誤
mysql> alter user 'root'@'%' identified by '123456'; ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%' --但是，將root密碼長度增加到8位時獲得成功
mysql> ALTER USER root IDENTIFIED WITH mysql_native_password BY '123456789';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| root | % | *CC67043C7BCFF5EEA5566BD9B1F3C74FD9A5CF5D |
| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
但是，我本地的centos 7.6上的mysql 8.0重置root密碼缺沒有密碼長度限制，兩套庫用的是相同的參數文件。
官方關于用戶密碼長度沒有特別說明，網上也沒有類似密碼長度問題的資料，很奇怪！
來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/29357786/viewspace-2704307/，如需轉載，請注明出處，否則將追究法律責任。
煙臺醫大胃腸醫院 http://www.ytwcbyy.com/
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
