1

Background

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 root.

I have run:

  • mysql_secure_installation

... 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';

Question

When I attempt to login, I simply am automatically logged in if I type anything from:

  • mysql
  • mysql -u root ... etc

I assume it's not just "working out that I'm server root" and letting me in.

How do I configure this?

Info

Running select user,host from mysql.user; gives:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
7
  • Responded in the main question
    – elb98rm
    Mar 23, 2022 at 19:46
  • ERROR 1054 (42S22): Unknown column 'password' in 'field list'
    – elb98rm
    Mar 23, 2022 at 20:27
  • 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 'PASSWORD('...')' at line 1
    – elb98rm
    Mar 23, 2022 at 20:55
  • 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 '('...') WHERE user='root'' at line 1
    – elb98rm
    Mar 23, 2022 at 21:33
  • Ubuntu's "root" is not the same as MySQL's "root". Do not confuse them.
    – Rick James
    Mar 24, 2022 at 19:48

1 Answer 1

2

MySQL

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
root@ergesttstsrv:~# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@ergesttstsrv:~# 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>
1
  • Worked a charm. Upvoted and marked as correct answer.
    – elb98rm
    Mar 23, 2022 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.