All we need is an easy explanation of the problem, so here it is.
I’m setting up a new server, and I’m attempting to secure the new MySQL installation.
- mysql Ver 8.0.28-0ubuntu0.21.10.3 for Linux on x86_64 ((Ubuntu))
I’m logged in to Ubuntu as
I have run:
… and flushed priveleges and rebooted.
It still does not ask me for a password. I have also tried to update this using:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'test';
When I attempt to login, I simply am automatically logged in if I type anything from:
mysql -u root
I assume it’s not just "working out that I’m server root" and letting me in.
How do I configure this?
select user,host from mysql.user; gives:
+------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Important In MySQL 8.0, caching_sha2_password is the default
authentication plugin rather than mysql_native_password. For
information about the implications of this change for server operation
and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.
Test on my
MySQL 8.0.25 server
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.25 | +-----------+ 1 row in set (0.00 sec) mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyPassword'; Query OK, 0 rows affected (0.05 sec)
Or you could use :
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyPassword'; mysql> exit; Bye [email protected]:~# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [email protected]:~# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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>
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂