Log in into MySQL account with unix_socket

Question

I have created a forwarder (via SSH) from machine A to machine B. Machine B has a MySQL instance running, with a root account using unix_socket authentication:

+----------+-----------+-------------+
| User     | Host      | plugin      |
+----------+-----------+-------------+
| root     | localhost | unix_socket |
+----------+-----------+-------------+

I am unable to log in as root from machine A, regardless of the password I use (blank, the Linux root password, etc...):

machineA:~$ mysql -h 127.0.0.1 -P 1111 -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
machineA:~$ mysql -h 127.0.0.1 -P 1111 -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The same happens if I try to log in into mysql from machine B with an account that is not the root account.

How can I log in into MySQL?

Answer 1

To connect to a remote MySQL server, be it directly or through a SSH tunnel, you can't use the unix_socket authentication plugin which requires local access to the unix socket file on the DB Server operating system.

The unix_socket plugin is implemented using a special type of file (unix socket) which is a form of inter-process communication (IPC) in *nix systems. It allows your mysql CLI client to talk to the DB and it requires local access to the socket file (i.e: /tmp/mysql.sock). When you connect to the socket, the unix_socket plugin (server-side) will get the uid of the user connected to the socket (i.e: youruser) and will automatically authenticate you without the need of a password.

Possible solutions:

If you connect to Machine B with a non-root user, you need to create a DB user with the same name as your non-root operating system user account name.

GRANT ALL PRIVILEGES ON *.* TO 'youruser'@'localhost' IDENTIFIED VIA unix_socket;

Now you can connect to mysql using your user account and the mysql CLI client by simply running:

youruser:~$ mysql

If you want to connect to the DB with the root user, then you either need access to the root account or a sudo policy associated to your user to run the mysql client.

youruser:~$ sudo mysql

You can obviously enable regular authenticated network access to your DB root account. This can lead to security issues so it's better to limit it to localhost:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION;

Now you can login from any local operating system account if you can authenticate your login:

youruser:~$ mysql -u root -p