How to prevent mysql command line to append "@locahost" after -u <user>?

[Theeraphong Thitayanun]

Hi,

Posted this in the newbie forum, but got no luck...

Thanks for any advices,
Theeraphong

+++
Hi,

I am new to mysql, so please help advise...

Testcase 1: Do not specify -u or -p ==> Failed
[root@server1 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Testcase 2: Specify -u only ==> Failed
[root@server1 ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Testcase 3: Specify -u and -p (but no password) ==> Worked!
[root@server1 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15147
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user,host,password from mysql.user;
+----------------+-----------+-------------------------------------------+
| user | host | password |
+----------------+-----------+-------------------------------------------+
| root | % | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| glance | % | *E2EE7F01E29C810FFF6BCB76A4CB6993F4CC4DBF |
| gnocchi | % | *A3AA909181FA7271166ECDB41EF7BCCC0EDC820B |
| cinder | % | *944430B288F827F77B28CA4547F4D7DABD03CCE0 |
| heat | % | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| neutron | % | *184A587CA281EAFABAE6ACF6E714904B4191B650 |
| nova | % | *B3A5EAB50E3FA2654DA3EC936C39994E800AC0B4 |
| nova_api | % | *B3A5EAB50E3FA2654DA3EC936C39994E800AC0B4 |
| nova_placement | % | *B3A5EAB50E3FA2654DA3EC936C39994E800AC0B4 |
| keystone_admin | % | *138F2849212A104BAE2960AF33C2BF6A26D9B2B2 |
| keystone_admin | 127.0.0.1 | *138F2849212A104BAE2960AF33C2BF6A26D9B2B2 |
+----------------+-----------+-------------------------------------------+
11 rows in set (0.000 sec)

MariaDB [(none)]> exit
Bye

Testcase 4: Specify -u and -p (with password) ==> Worked!
[root@server1 ~]# mysql -uroot -ppassword
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15404
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Testcase 5: Specify -u, -p and @localhost ==> Failed
[root@server1 ~]# ping localhost
PING localhost (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.038 ms
^C
--- localhost ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.038/0.038/0.038/0.000 ms

[root@server1 ~]# mysql -uroot@localhost -ppassword
ERROR 1045 (28000): Access denied for user 'root@localhost'@'localhost' (using password: YES)

[root@server1 ~]# mysql -uroot@127.0.0.1 -ppassword
ERROR 1045 (28000): Access denied for user 'root@127.0.0.1'@'localhost' (using password: YES)

Testcase 6: Specify -u, -p and @server1 ==> Failed
[root@server1 ~]# ping server1
PING server1 (192.168.122.172) 56(84) bytes of data.
64 bytes from server1 (192.168.122.172): icmp_seq=1 ttl=64 time=0.041 ms
^C
--- server1 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.041/0.041/0.041/0.000 ms

[root@server1 ~]# mysql -uroot@server1 -ppassword
ERROR 1045 (28000): Access denied for user 'root@server1'@'localhost' (using password: YES)

[root@server1 ~]# mysql -uroot@192.168.122.172 -ppassword
ERROR 1045 (28000): Access denied for user 'root@192.168.122.172'@'localhost' (using password: YES)

[root@server1 ~]# mysql -uroot@1.1.1.1 -ppassword
ERROR 1045 (28000): Access denied for user 'root@1.1.1.1'@'localhost' (using password: YES)

==> Looks like if we use -u<user>@<host>, it will always fail since mysql somehow appended "@localhost" to the value of -u option...

==> How can I inform mysql not to append @locahost to -u option?

Thank you very much for any advices,
Theeraphong T.

Note:

1.If I use -h<host>, it works...

Testcase 7: Specify -u,-p and -h
[root@server1 ~]# mysql -uroot -ppassword -hlocalhost ==> Worked!
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17214
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye

[root@server1 ~]# mysql -uroot -ppassword -hserver1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17215
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye

2. I am testing packstack on RHEL, and got the following error during packstack:
--
ERROR : Error appeared during Puppet run: 192.168.122.172_controller.pp
Error: Failed to apply catalog: Execution of '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe SELECT CONCAT(User, '@',Host) AS User FROM mysql.user' returned 1: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
--
+++