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

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 through 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.

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

  • Anonymous

    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?

  • 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()*/ 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

  • NikoJr

    Man u have saved my life hehehe… very useful.

    Thanku very much

  • Anonymous

    Wouldn’t the link be removed at server reboot and the problem appear again??

    I had the same problem but it is because my.cnf was not in the default locations

    CLIENT:
    Default options are read from the following files in the given order:
    /etc/my.cnf ~/.my.cnf /usr/local/mysql/etc/my.cnf

    So even when I added the [client] section I got the same error, so I did a link

    #ln -s /my/cnf/file_location/my.cnf /etc/my.cnf

    And it worked great.

  • Anonymous

    i am getting this message

    Warning: mysql_pconnect() [function.mysql-pconnect]: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) in /home/content/07/5479507/html/kb/install/install_step3.php on line 61
    Could not connect to the server: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

    what should i do?

  • Anonymous

    Please make sure that your disk having enough space..!

    • Anonymous

      Another way to give the path for socket file is this:
      connection=MySQLdb.connect(user=”root”,passwd=””,db=”python”,unix_socket=”/opt/lampp/var/mysql/mysql.sock”)

      This did wonders for me and I am really very thankful to this: http://www.oesf.org/forum/index.php?showtopic=24885.

      Hope this will help many others as it did for me.

  • Albert

    Fantastic!
    I had the same problem and that was the solution.
    Thanks a lot!

  • Anonymous

    Excellent explanation. But I still have a question. I have a different scenario. My mySQL database is on a different server with a hosting company and I am connecting from my client in my home. I am getting the same error. Is there something that can be done from my end to fix this error? Thanks anyways.

    • Anonymous

      Continuing to my earlier post I am not receiving the above error while connecting to the database but when I am running first ever query in my web page. Can somebody suggest another explanation?

  • Anonymous

    Yes…this solution looks very simple. Now, where the heck is the /etc/my.cnf file?! I can’t find it anywhere. I’m so frustrated right now. I have a hard time when I spend sooo much time just trying to get software working so I can actually get some work done!
    Thanks for the help.

  • Benjamin See

    I would like to point out that, when performing tasks in an automated environment, such as setting up your mysql during kickstart installation, it important to note that if you execute mysql-related commands immediately after you start the mysql service, it is likely that your commands failed due to the mysql service “still in the progress” of starting up.

    And the error message that you get would be similar that mysql.sock is not found.

    The solution is simple. Allow for some delay between starting your mysql service, and performing your first mysql tasks, so that when the first mysql command is issued, the service is started and running properly.

    • Pat

      Just want to add my humble thank you to the list. I was updating our php version in preparation for another software update when things went south. Could not figure out for the life of me why apache could not connect to mysql. Manual connection …. no problem…. apache -> php -> mysql …. problem

      mysql.sock …. running amok. symbolic link solved the problem!

      What a relief!

      cheers!

  • muzie

    thanks brother!

  • Marco

    Usually there’s a hickup in your my.ini that will get you this message at the first mysql server restart after upgrading or changing your configuration.
    Locate the .err file in your mysql/datadir and see what option is deprecated this time.

  • Deepak Frazer

    it worked for me on CentOS. mysql Server version: 5.0.77
    Thanks dude

  • http://www.facebook.com/gamchan Heri Anto マスター

    pkill mysqld
    su mysql
    mysql_install_db
    chown -R mysql.mysql /var/lib/mysql/
    mysqld_safe &

  • http://www.facebook.com/gamchan Heri Anto マスター

    try this work for me

    pkill mysqld
    su mysql
    mysql_install_db
    chown -R mysql.mysql /var/lib/mysql/
    mysqld_safe &

  • cave_person_2

    Good lord, can SOMEONE say how to do it as if, and I’m just giving you an example, as if not everyone was computerically inclined? I think I must be one of the last 4 in thje USA and we’re all sorry. I really have no idea what you’re talking about, what these things mean and where all these icons with clamshells could be found, etc? So theres another user for my system? Where? And then again, please explain it as if I weren’t a techie too if you can. I would be forever greatful….

    • computerically inclined?

      Calm down, maybe you are on the wrong message board. This is a technical message board. Sounds like you are looking for the Fisher Price Computer Users Group.

      • Kathryn Brewer

        that was mean. The user has a legitimate concern. Can we be a little less techy and help the person without making them feel like they are 5? Meanie.

  • Arik_harel

    For xampp this command worke for me:
    ln -s /opt/lampp/var/mysql/mysql.sock /var/run/mysqld/mysqld.sock

  • CJ

    Hello. I’m a real newbie to all this so would be grateful for answers in words of one syllable and sorry if this is obvious to everyone else, but most comments are going over my head. I think if I can get past this bit, then I should be OK to do everything I want to do, I’m just stuck here, trying to get a connection.

    I’m getting the ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)’ error message.

    I’ve set up my first MySQL database and its hosted on a remote web server so I don’t have access to config files. I’m on the client side, trying to connect so I can create some dynamic pages for my website.

    My web hosting company are being less than helpful in helping me to sort this out. What sort of questions should I be asking them and then if they say I’m using the wrong socket, what php script would I need to specify (I would need it in full)?

    Sorry, this must be really basic stuff for everyone else but it’s causing me a lot of grief and if I can just get through this I can actually do some work….

    Thanks in advance for any suggestions.

    CJ

  • Anonym

    BTW also this occurs when your site under ddos attack.
    In this situation mysqld will load high cpu usage too.

  • Ajimol

    Thanks a lot.it worked for me too.

  • Yahya Nikandish
  • Gopinathkarangula

    Hi to all for me this problem occurred twice, so i just thought and understood the problem, when ever you are changing some config file related to mysql, better stop the service, edit you config, and start the service, then mysqld problem you will never face..

  • Chandan Singh Pachwal

    try runing it as
    /data/(folder in which mysql is installed)/mysql/bin/mysql

  • Chandan Singh Pachwal

    try runing it as
    /data/(folder in which mysql is installed)/mysql/bin/mysql

  • rana

    When I upload my php and MySQL website of web hosting sites then i am getting this message.

    Warning: mysql_pconnect() [function.mysql-pconnect]: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) in /srv/disk2/833826/www/mycprogamsolution.co.cc/Connections/check_mag.php on line 9

    Fatal error: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) in /srv/disk2/833826/www/mycprogamsolution.co.cc/Connections/check_mag.php on line 9

    what should i do?

  • http://www.roymj.com/ Roy M J

    Hi i am having the same error, How should my connnection code look like? Currently its

    mysql_connect(“localhost:/tmp/mysql5.sock”,”dbo377327247″,”password”);
    But the error says

    Can’t connect to local MySQL server through socket ‘/tmp/mysql5.sock’ (2)

    How should i link the socket to this code?

    Thanks

  • susu

    fantastic illustration ,, but i couldn’t change the ‘my.cnf’ cz it’s read only.. how can i change it’s permission???
    thx alot ;D

  • ashraf

    i faced the same problem before
    it was on godaddy server
    i just replaced (localhost) with the DBHOST given by godaddy and it worked fine.just visit db info and find database host.it will be a long name..
    i hope this helps someone.
    thanks

  • TQuang

    Yes, that right. I finding that.
    Thank

  • amritpal

    did not works???

  • Bolt Ram

    I don’t know what flavor(s) you guys are using, but on CEntOS 6.2/MySQL 5.5.21 this fix (thanks, by the way) did NOT work until I restarted mysql. If you’re using the command line (via SSH, for example), you may need to use the following command to start her back up:

    # /etc/init.d/msyql start

  • Wolf

    I am trying to set up MySQL for roundcube on Mac OS X 10.8 and want to create a database with the command (current path: /usr/local/mysql-5.5.27-osx10.6-x86_64/bin/)
    ./mysqladmin create roundcube
    which gives the error above. However, the solution doesn’t work for me because there is no socket file anywhere and not even a /var/lib/mysql folder. In order to edit my.cnf I also had to create it first. (I copied the file /usr/local/mysql/support-files/my-huge.cnf to /etc/my.cnf)
    Then I tried to start the daemon by hand, so I ran
    /usr/local/mysql-5.5.27-osx10.6-x86_64/bin/mysqld
    which gives the following output:

    120820 14:10:18 [Warning] Can’t create test file /usr/local/mysql-5.5.27-osx10.6-x86_64/data/.lower-test
    120820 14:10:18 [Warning] Can’t create test file /usr/local/mysql-5.5.27-osx10.6-x86_64/data/.lower-test
    ./mysqld: File ‘./mysql-bin.index’ not found (Errcode: 13)
    120820 14:10:18 [ERROR] Aborting

    Somewhere else it is said this has to do with database privileges. But I haven’t created any database yet!???
    I would be really happy if anyone could help.

    Thanks

  • Sidheeque

    I renamed mysql username

    after that i got one error 0 rows effected

    after that i am not able to log in MySQL database with old and new user name

    Please help me

  • edd

    It’s work for me. Since I moved to the new Dedicated server It’s been more than a week I’m looking the solution of this kind of error.

    Thank a lot Quinn McHenry

  • Harihar Pai

    Do this it will work mysql -uadmin -h10.13.133.238 -P3307 -p where capital P is for port and p is for password. -u is user and -h is ip of host

  • http://www.ilce.edu.mx Liza

    Thanks a lot
    You saved my life !!!

  • chris

    i can not get into immortalnight

  • ajay

    but i cant edit in “my.cnf” file please help me

    thanks in advance

  • Apoorv

    I am building a quiz as an intern for a website hosted on yahoo, and my quiz is giving the same error. I cannot change the mysql config file on yahoo servers, so what should I do?

  • DelDeldago

    Thanks dude – you are my hero :)

  • Soft Sunrise

    i cant understand what you said and how is this to change be made

  • miguel leaños

    hi i have do that, works perfectly but now when i restart my server the service mysql cannot stop
    thanks

  • Gale

    My site uses Mysql and php to populate the content. When my local host server went down, no web pages showed just this:
    Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
    Is their something i can do so an another file will display instead. Maybe a static html file.

  • Faizal Umer

    You have no idea how helpful this post has been to me. The [client] section did the trick after some really hair pulling days trying to make MySQL 5.6.17 work. I used socket=/tmp/mysql.sock, since this is where the log file showed it was being created.

  • Valorie Clemons

    A person built a website for us and we are no longer in contact with this person, but a few days ago I tried to log into my wordpress admin and it gave me this:

    Error establishing a database connection

    This either means that the username and password information in your wp-config.php file is incorrect or we can’t contact the database server at localhost:/var/lib/mysql/mysql.sock. This could mean your host’s database server is down.

    Are you sure you have the correct username and password?

    Are you sure that you have typed the correct hostname?

    Are you sure that the database server is running?

    If you’re unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums.

    The other problem is my web host is on linode.com which are bare bones servers that do not come with extras such as control panels. I have no technical knowledge to know how to fix this so my website is up and running. Or can I even fix this on my end?

    Any help would be much appreciated.