0

I am able to connect to mysql database and query it. But, I am NOT able to find the socket file.

$ps -ef|grep mysql
mysql    31408 30874  0 18:46 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe     --defaults-file=/mysql/admin/ofile/TEST1.cnf

mysql    31959 31408  0 18:46 pts/1    00:00:01 /usr/sbin/mysqld --defaults-    file=/mysql/admin/ofile/TEST1.cnf --basedir=/usr --    datadir=/mysql01data/TEST1/data --plugin-dir=/usr/lib64/mysql/plugin --log-    error=/mysql/admin/TEST1/errors/mysqld_safe.err --pid-    file=/mysql/admin/TEST1/run/mysqld_safe.pid

Here is my socket file entry in TEST1.cnf:

$ cat /mysql/admin/ofile/TEST1.cnf|grep sock
socket                            = /mysql/admin/TEST1/run/TEST1.sock

The corresponding directory only contains pid file. There is no socket file.

-sh-4.1$ cd /mysql/admin/TEST1/run
-sh-4.1$ ls -lrt
total 4
-rw-rw---- 1 mysql mysql 6 Apr 29 18:46 mysqld_safe.pid

This is the MySQL 5.6 version I installed through RPM's on RHEL 6.5. I have my old custom scripts which uses socket file to connect to the database.

So, I am wondering how I can use the socket file to connect to the database? Why the socket file is not created by default?

vins
  • 55
  • 7
  • you can find socket file under mysql database directory – Altmish-E-Azam Apr 30 '16 at 05:33
  • 1
    Does `sudo lsof -a -U -p $(pgrep -d, -f /usr/sbin/mysqld)` provide any useful information? – Michael - sqlbot May 01 '16 at 01:04
  • @Michael-sqlbot Yes, I do see socket file created in /var/lib/mysql. But, why the socket file is not obeying my 'socket' parameter in the conf file? `-sh-4.1$ lsof -a -U -p $(pgrep -d, -f /usr/sbin/mysqld) COMMAND PID USER FD TYPE SIZE/OFF NAME mysqld 5042 mysql 20u unix 0t0 /var/lib/mysql/mysql.sock` – vins May 02 '16 at 21:50
  • 1
    Is it configured in the `[mysqld]` section of the config file? Is there only one entry? – Michael - sqlbot May 02 '16 at 22:12
  • @Michael-sqlbot The socket parameter is in the [client] section. I changed it to [mysqld] section now and restarted mysqld. Now the socket file is created in the custom path instead of default path. Thanks a lot! – vins May 02 '16 at 22:27
  • Your best bet would be to put it in both, particularly if there's ever chance of a client also using that same `my.cnf`. – Michael - sqlbot May 02 '16 at 22:50

1 Answers1

2

The socket file for a running instance of MySQL Server should be something that can be found with this shell command:

sudo lsof -a -U -p $(pgrep -d, -f /path/to/your/running/mysqld)

One possible cause of being unable to find the socket file would be if it had been deleted after the server was started. In that case the above command should work, and show something like (deleted) after the path.

That was my original assumption on this question... but here the issue was a configuration oversight. The "defaults file," commonly called my.cnf contains multiple sections. The [client] section configures client utilities, like mysql and mysqldump, while the [mysqld] section configures the server daemon. If the socket directive isn't in the appropriate section, the server (and/or client utilities) will look in the location compiled in by default, with /tmp/mysql.sock or /var/lib/mysql/mysql.sock being a couple of examples of common default locations.

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427