Mariadb - unable to connect locally using host=localhost on the second instance (mysqld@2 )

English Network/Internet
1

Hello.
I can connect to my mariadb server on the second instance (mysqld@2) on my local network from any workstation using :

mysql -P 63332 -u postfix_user -p'mysql_pwd' -h 192.168.130.100 mysql

or

mysql -P 63332 -u postfix_user -p'mysql_pwd' -h LINUX-TEST-123 mysql

I can connect locally on the server using the same commands.
But I am unable to connect using host=localhost.

mysql -P 63332 -u postfix_user -p'mysql_pwd' -h localhost mysql
                                                                                                 
ERROR 1045 (28000): Access denied for user 'postfix_user'@'localhost' (using password: YES)

ther is no anonymous user :

MariaDB [mysql]> SELECT USER, password, host from user ;
+---------------+------------------------------------------------+----------------+
| USER              | password                                                      | host           |
+---------------+------------------------------------------------+----------------+
| postfix_mysql | *D6C0C2C5A998B36A931E6BDADFF50579D22B5588 | localhost      |
| postfix_mysql | *D6C0C2C5A998B36A931E6BDADFF50579D22B5588 | LINUX-TEST-123 |
| postfix_mysql | *D6C0C2C5A998B36A931E6BDADFF50579D22B5588 | 127.0.0.1      |
| tux_user       |                                                                      | localhost      |
| tux_user       |                                                                      | 127.0.0.1      |
| postfix_mysql | *D6C0C2C5A998B36A931E6BDADFF50579D22B5588 | 192.168.130.%  |
+---------------+-------------------------------------------+----------------+
6 rows in set (0.00 sec)

MariaDB [mysql]> select USER(),CURRENT_USER() ;
+-----------------------------------------+-----------------------------+
| USER()                                                | CURRENT_USER()              |
+-----------------------------------------+-----------------------------+
| postfix_mysql@LINUX-TEST-123.my-dom.nwk | postfix_mysql@192.168.130.% |
+-----------------------------------------+-----------------------------+
1 row in set (0.01 sec)

But if I add the protocol, I can connect

mysql --protocol=TCP -P 63332 -u postfix_user -p'mysql_pwd' -h localhost mysql

but I can connect on the first instance (mysqld) using the 3 possibilities ( by ip, by host name and using local host.

]mysql -P 63330 -u mysql_user -p'mysql_pwd' -h 192.168.130.100 mysql
]mysql -P 63330 -u mysql_user -p'mysql_pwd' -h LINUX-TEST-123 mysql
]mysql -P 63330 -u mysql_user -p'mysql_pwd' -h localhost mysql

here is my config :http://paste.opensuse.org/28049382

Any help is welcome.

2

The behavior of the second instance is the documented one (http://dev.mysql.com/doc/refman/5.5/en/connecting.html). I guess, what makes your “localhost” connections to the first instance work, is the [client] section in your configuration file, pointing to the socket of the first instance.
According to the documentation it should work with “127.0.0.1” instead of “localhost”.

Hendrik

3

The reference link is good.

Specifically, IMO you should inspect this part

The following table shows the permissible --protocol](http://dev.mysql.com/doc/refman/5.5/en/connecting.html#option_general_protocol) option values and indicates the platforms on which each value may be used. The values are not case sensitive. |
|
|TCP
|TCP/IP connection to local or remote server
|All
|
|SOCKET
|Unix socket file connection to local server
|Unix only
|
|PIPE
|Named-pipe connection to local or remote server
|Windows only
|
|MEMORY
|Shared-memory connection to local server
|Windows only
|

                  --shared-memory-base-name=*name*](http://dev.mysql.com/doc/refman/5.5/en/connecting.html#option_general_shared-memory-base-name)         
      On Windows, the shared-memory name to use, for connections          made using shared memory to a local server. The default value          is MYSQL. The shared-memory name is case          sensitive.         
      The server must be started with the           --shared-memory](http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_shared-memory) option to          enable shared-memory connections.         

                  --socket=*file_name*](http://dev.mysql.com/doc/refman/5.5/en/connecting.html#option_general_socket),           -S *file_name*         
      On Unix, the name of the Unix socket file to use, for          connections made using a named pipe to a local server. The          default Unix socket file name is           /tmp/mysql.sock.

So, I strongly suspect that unless you explicitly connect using custom parameters, any attempt to connect to any local MySQL instance will attempt to use the same UNIX socket.

So,
Various potential solutions…

  • Declare a different UNIX Socket (file) using the " --socket=file_name" described in the excerpt above
  • Connect using a TCP connection instead of UNIX Socket. This is fairly simple to do and is automatically setup when you run/configure MySQL for the first time, just specify that remote connections be enabled. A MySQL Admin password will be required whereas UNIX Socket connections won’t. Generally speaking, any IP address 127.x.y.z likely should work.

HTH,
TSU