https://www.peterdavehello.org/2019/12/set-mysql-password-login-and-create-database-on-ubuntu-18-04-and-later/
mysql mysql 進到 MySQL 後透過 SQL 語法查詢:
mysql> SELECT user, authentication_string, plugin, host FROM mysql.user WHERE user = 'root';
以上查詢會得到這樣的結果,可以看到 root 使用 auth_socket
plugin 且 authentication_string
為空:
+------+-----------------------+-------------+-----------+ | user | authentication_string | plugin | host | +------+-----------------------+-------------+-----------+ | root | | auth_socket | localhost | +------+-----------------------+-------------+-----------+ 1 row in set (0.00 sec)
這邊透過ALTER
語法把 root 登入的方式改為密碼驗證,在最後password
的地方改為自己要設定的密碼即可:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.06 sec)
再次透過同樣的 SELECT
語法確認,可以看到 authentication_string
已經有密碼的雜湊值、plugin
已經從 auth_socket
變為 mysql_native_password
:
mysql> SELECT user, authentication_string, plugin, host FROM mysql.user WHERE user = 'root'; +------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------+-------------------------------------------+-----------------------+-----------+ | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | mysql_native_password | localhost | +------+-------------------------------------------+-----------------------+-----------+ 1 row in set (0.00 sec)
這時就可以做FLUSH PRIVILEGES
的操作讓上述修改生效:
mysql> FLUSH PRIVILEGES;
再執行exit
或quit
指令結束這次的 MySQL 操作,準備實際測試看看是不是已經不能直接透過系統的 root 身份登入 MySQL 、而是必須透過密碼的方式進行驗證
mysql -u root -p