Mysql – Can’t connect to local MySQL server through socket ‘/var/run/thesqld/thesqld.sock’ (2 “No such file or directory”) when configuring cluster

clusteringgaleramariadbmariadb-10.1MySQL

I have 3 MariaDB nodes.
Want to configure cluster.
Specified in gcomm:// in my.cnf ips of all members in cluster according to this.

And added ?pc.wait_prim=no at then of gcomm://ip1,ip2,ip3

Then mysqld process starts on each VM, but when i try to it connect with mysql -uroot -ppass in order to run 

SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

error pops:

Can't connect to local MySQL server through socket
'/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

error.log of each process shows:

2018-02-01 15:13:23 140529975699392 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer '10.0.0.42:,10.0.0.44:,10.0.0.43:'
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 71d7b5ed tcp://10.0.0.44:45
2018-02-01 15:13:23 140529975699392 [Warning] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') address 'tcp://10.0.0.44:4567' points to own listeddress, blacklisting
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 6ac0d002 tcp://10.0.0.42:45
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 6bd1b1f9 tcp://10.0.0.43:45
2018-02-01 15:13:23 140529975699392 [Note] WSREP: gcomm: connected
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Opened channel 'my_wsrep_cluster'
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Waiting for SST to complete.
2018-02-01 15:13:23 140529635424000 [Note] WSREP: declaring 6ac0d002 at tcp://10.0.0.42:4567 stable
2018-02-01 15:13:23 140529635424000 [Note] WSREP: declaring 6bd1b1f9 at tcp://10.0.0.43:4567 stable
2018-02-01 15:13:23 140529635424000 [Warning] WSREP: no nodes coming from prim view, prim not possible
2018-02-01 15:13:23 140529635424000 [Note] WSREP: view(view_id(NON_PRIM,6ac0d002,2) memb {
        6ac0d002,0
        6bd1b1f9,0
        71d7b5ed,0
} joined {
} left {
} partitioned {
})
2018-02-01 15:13:23 140529627031296 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 2, memb_num = 3
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Flow-control interval: [28, 28]
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Trying to continue unpaused monitor
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Received NON-PRIMARY.
2018-02-01 15:13:23 140529975253760 [Note] WSREP: New cluster view: global state: :-1, view# -1: non-Primary, number of nodes: 3, my ind protocol version -1
2018-02-01 15:13:23 140529975253760 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2018-02-01 15:13:26 140529635424000 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection to peer 71d7b5ed with addr tcp://10.0.0.44timed out, no messages seen in PT3S
2018-02-01 15:13:26 140529635424000 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') turning message relay requesting off

Why does it happen and what's wrong?

Best Answer

Check your config files /etc/my.cnf and /etc/my.cnf.d/server.cnf and look for the socket parameter. Make sure it's the same for client and server. A good place for the socket parameter is in the [client-server] section in /etc/my.cnf. The socket file is created by the MariaDB server when it starts up.

Note that there are other places where MariaDB config files could reside, and these could potentially override your settings in the two files mentioned above. For more details, see this page in the MariaDB KB.

Also note that you may be able to connect to MariaDB through TCP rather than through the socket. You can specify that you want to use TCP rather than the socket with mysql ... --protocol=TCP. If this is successful, you can then do SHOW VARIABLES LIKE 'socket'; to find out where the socket file really is (or should be). You can then connect with mysql ... --socket=/path/to/file.sock or edit the config files so that the client reads it correctly from there.

In some cases you may also be able to figure out what socket file location is used by listing the processes: ps -ef | grep mysql which could output something like

root     23900     1  0 13:27 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/vserv-03.pid
mysql    24402 23900  0 13:27 ?        00:00:50 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep_on=ON --wsrep_provider=/usr/lib64/galera/libgalera_smm.so --log-error=/var/lib/mysql/vserv-03.err --open-files-limit=65535 --pid-file=/var/lib/mysql/vserv-03.pid --socket=/var/lib/mysql/mysql.sock --wsrep_start_position=98b22222-aa16-1114-ba1a-886633c6b8eb:55133333

As you can see here (scroll right), it's using a socket at /var/lib/mysql/mysql.sock.

EDIT: A really good way of finding the location of the socket file is:

netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     22418    /var/lib/mysql/mysql.sock
Related Question