Solve: Can’t connect to local MySQL server through socket /tmp/mysql.sock

Contributor Icon Contributed by qmchenry Date Icon October 31, 2004  
Tag Icon Tagged: MySQL

A frequent error message received when using the mysql command line utility is: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ While this error message can be frustrating, the solution is simple.


When connecting to a MySQL server located on the local system, the mysql client connects thorugh a local file called a socket instead of connecting to the localhost loopback address 127.0.0.1. For the mysql client, the default location of this socket file is /tmp/mysql.sock. However, for a variety of reasons, many MySQL installations place this socket file somewhere else like /var/lib/mysql/mysql.sock.

While it is possible to make this work by specifying the socket file directly in the mysql client command

mysql --socket=/var/lib/mysql/mysql.sock ...

it is painful to type this in every time. If you must do so this way (because you don’t have permissions to the file in the solution below), you could create an alias in your shell to make this work (like alias mysql=”mysql –socket=/var/lib/mysql/mysql.sock” depending on your shell).

To make your life easier, you can make a simple change to the MySQL configuration file /etc/my.cnf that will permanently set the socket file used by the mysql client. After making a backup copy of /etc/my.cnf, open it in your favorite editor. The file is divided into sections such as

[mysqld]
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/usr/local/mysql

If there is not currently a section called [client], add one at the bottom of the file and copy the socket= line under the [mysqld] section such as:

[client]
socket=/var/lib/mysql/mysql.sock

If there is already a [client] section in the my.cnf file, add or edit the socket line as appropriate. You won’t need to restart your server or any other processes. Subsequent uses of the mysql client will use the proper socket file.

Previous recipe | Next recipe |
 
  • hans gruber
    This may *sound* like a kludge as this will **CONFIRM** the abovestated steps.

    Simply create a symbolic link under the "/tmp" directory BACK to the "mysql.sock" file. Make sure that it's worldwide read/write-able. To do so would be like this:

    cd /tmp
    ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

    Try it then -- if it is still having trouble, then you might have something else wrong; otherwise, this is because of the RPM installation that you might've used from Red Hat (aka "Fedora").

    Just a friendly hint....cheers!

    hg
  • Paradigm
    Also, this message could mean the the deamon mysqld isn't running, and no socket file has been created.
  • Anonymous
    For SUSE uers (maybe others)..

    :: QUOTE ::
    YaST stops a little short of fully installing MySQL. ...

    I checked the /var/lib/mysql/ directory only to find it empty! This directory is supposed to include the MySQL foundation files that are necessary to make it all work.

    I found the following in the MySQL documentation:

    1. Log in as the root user. ("Start" => "Switch User")
    2. Open a terminal window (the icon that looks like a monitor behind a clamshell).
    3. Type "cd /bin" enter (no quotes). :: usually not nessecary ::
    4. Type "mysql_install_db --user=mysql

    This is from: http://www.novell.com/coolsolutions/feature/595...
  • Anonymous
    <ul id="quote"><h6>hans gruber wrote:</h6>This may *sound* like a kludge as this will **CONFIRM** the abovestated steps.

    Simply create a symbolic link under the "/tmp" directory BACK to the "mysql.sock" file. Make sure that it's worldwide read/write-able. To do so would be like this:

    cd /tmp
    ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

    Try it then -- if it is still having trouble, then you might have something else wrong; otherwise, this is because of the RPM installation that you might've used from Red Hat (aka "Fedora").

    Just a friendly hint....cheers!

    hg</ul>

    Actually, this helped tremendously. I am on OS X 10.5 and I don't seem to have a my.cnf file. However, when I tried to connect to mysql, I got the norm error can't connect to . . . through socket . . . "/var/mysql/mysql.sock". A quick search confirmed that /var/mysql/mysql.sock did not exist. I would not have known how to fix this (as a copy paste would not have worked for a socket file) so your post was the solution I implemented, creating a link in /var/mysql that targeted the /tmp/mysql.sock


    Thanks!

    crab
  • Jonathan Dear
    Should I be able to connect to MySQL over my LAN? I am running MAMP on Tiger, and can connect using MAMP's built in tools, but preferred the look of Sequel Pro, which I can use to connect with MySQL on the local machine using LocalHost, root user, and password, port 8889 and Socket: /Applications/MAMP/tmp/mysql/mysql.sock. However when I try to connect to that Mac from another on the LAN, which works to MAMP fine via http://192.168.0.4:8888/ It says Can't connect to local MySQL server through socket '/Applications/MAMP/tmp/mysql/mysql.sock' (2). I'm confused. MAMP's using port 8888, and MySQL's using port 8889. How can I get pass both in the URL? Or am I all wrong here? Any suggestions?
  • özcan
    turkey
  • arun
    I found the following in the MySQL documentation:

    1. Log in as the root user. ("Start" => "Switch User")
    2. Open a terminal window (the icon that looks like a monitor behind a clamshell).
    3. Type "cd /bin" enter (no quotes). :: usually not nessecary ::
    4. Type "mysql_install_db --user=mysql

    This is from: http://www.novell.com/coolsolutions/feature/595...
  • obaid
    can this type of error be generated by something like:
    mysql_fetch_assoc();
    /*as you can see the resource parameter of mysql_fetch_assoc(<$resource>)*/ is missing
    do help please...!!!
  • gogster
    I am on client side. I can't change serwer settings. Can send php scripts only! It would be usefull if it will be my own serwer, but it isnt.. So your's advice is useless..

    The reason fo client is bad serwer name in "mysql_connect" function. For example database is:
    "http://abc.def.ghi.com/mysql5/"
    so you should use it properly like this:

    mysql_connect("abc.def.ghi.com",...);

    insted of not properly like this:

    mysql_connect("http://abc.def.ghi.com/mysql5/",...);
  • gogster
    of course in this problem command:

    mysql_connect("localhost",...);

    not work
  • David C. Rankin, J.D.,P.E.
    Excellent nugget of information:

    It came in real handy when the geniouses at opensuse broke an update of mysql to mysql-5.1.34-29.1 by putting the mysql.sock and mysqld.pid in /var/run/mysql instead of /var/lib/mysql as normal. Great quick fix was found here. In addition to /etc/my.cnf, the init script at /usr/sbin/rcmysql needed to be fixed as well.

    Thanks
  • Steven Sheffey
    Hi guys, I to have this problem and I'm new to mySQL and PHP. Where are you guys locating all these directories such as: /var/lib/mysql or /usr/local/mysql/data ?
  • JEME
    Good day qmchenry,

    I try with de line:
    mysql --socket=/var/lib/mysql/mysql.sock ...

    but now the terminal list this error:

    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


    would you can help me
blog comments powered by Disqus