I followed the instructions for Option 2 in the question ERROR 1698 (28000): Access denied for user 'root'@'localhost' and used superuser1 as my 'YOUR_SYSTEM_USER' (see code below).
$ sudo mysql -u root
mysql> USE mysql;
mysql> CREATE USER 'superuser1'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'superuser1'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='superuser1';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ service mysql restart
Now I get ERROR 1698 (28000): Access denied for user 'superuser1'@'localhost' when trying to log in to mysql as suggested in that answer, this error occurs even if I prepend sudo to the mysql command as shown below
hms011@hms011-OptiPlex-9010-AIO:~$ sudo mysql -u superuser1
[sudo] password for hms011:
ERROR 1698 (28000): Access denied for user 'superuser1'@'localhost'
hms011@hms011-OptiPlex-9010-AIO:~$
I'm using Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu) on Linux Mint 19 on a new desktop installation. As a new mysql user I am being somewhat tentative when creating users until I become more familiar. Should I perhaps change the plugin for my superuser1 to mysql_native_password? The solution I followed suggested using no password (i.e. '') for the new user - will this affect any change of plugin? I did look at the question MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES) and most other similar questions but could not see how any of the suggestions there would solve my issue - the suggested solution to my original issue did include using FLUSH PRIVILEGES which I followed (as I stated at the outset). My original issue (which remains) was ERROR 1698 (28000): Access denied for user 'root'@'localhost' Unlike some other users, it was not solved by using the recommended Option 2 in that thread. Hence I have raised my own issue, perhaps I was too succinct in my original description so have now included the code for option 2 solution mentioned above together with the resulting error when logging in.
In the absence of any assistance I have tried:
hms011@hms011-OptiPlex-9010-AIO:~$ sudo mysql -u root
[sudo] password for hms011:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)
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> grant all privileges on *.* to 'superuser1'@'localhost' identified by 'toFFee8_&cHeese' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
hms011@hms011-OptiPlex-9010-AIO:~$ mysql -u superuser -p
Enter password: {password was entered}
ERROR 1045 (28000): Access denied for user 'superuser'@'localhost' (using password: YES)
hms011@hms011-OptiPlex-9010-AIO:~$ sudo mysql -u superuser -p
Enter password: {password was entered}
ERROR 1045 (28000): Access denied for user 'superuser'@'localhost' (using password: YES)
So now I do get the ERROR 1045 (28000) but am none the wiser about why it (or the other errors) occurred. Finally I went back to 'root' and re-granted all privileges to superuser1 I can now log in but without the password that was set up. While this seems to be satisfactory I would prefer to know why it is so, in order to have confidence in what I have done
Solved
Eventually the whole problem seems to be solved after
Altering the plugin for both users to 'mysql_native_password'
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'auth_socket'
'auth_socket' was the existing plugin for root but for other Linux users it might be 'unix_socket'; and - Setting a password for 'root' that does NOT include '&'
