Solve: Can’t connect to local MySQL server through socket /tmp/mysql.sock
Posted by Quinn McHenry in 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 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
View more articles by Quinn McHenry
The Conversation
Follow the reactions below and share your own thoughts.
November 19, 2008 at 4:47 pm, Anonymous said:
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?
January 06, 2009 at 7:30 am, arun said:
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…
January 29, 2009 at 7:29 am, obaid said:
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…!!!
February 15, 2009 at 10:21 pm, gogster said:
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/”,…);
February 15, 2009 at 10:23 pm, gogster said:
of course in this problem command:
mysql_connect(“localhost”,…);
not work
May 13, 2009 at 3:25 pm, David C. Rankin, J.D.,P.E. said:
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
June 30, 2009 at 12:37 am, Steven Sheffey said:
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 ?
June 30, 2009 at 2:25 pm, JEME said:
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
September 25, 2009 at 12:22 am, NikoJr said:
Man u have saved my life hehehe… very useful.
Thanku very much
October 17, 2009 at 4:55 pm, Anonymous said:
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.
January 11, 2010 at 1:07 am, Anonymous said:
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?
January 15, 2010 at 10:24 pm, Anonymous said:
Please make sure that your disk having enough space..!
January 28, 2010 at 4:26 pm, Anonymous said:
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.
February 15, 2010 at 10:00 am, Albert said:
Fantastic!
I had the same problem and that was the solution.
Thanks a lot!
February 22, 2010 at 2:04 am, Anonymous said:
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.
February 22, 2010 at 2:14 am, Anonymous said:
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?
March 15, 2010 at 4:14 am, Anonymous said:
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.
April 08, 2010 at 2:04 am, Benjamin See said:
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.
June 02, 2010 at 1:35 am, Pat said:
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!
June 08, 2010 at 7:06 am, muzie said:
thanks brother!
August 29, 2010 at 6:28 pm, Marco said:
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.
September 21, 2010 at 1:43 am, Deepak Frazer said:
it worked for me on CentOS. mysql Server version: 5.0.77
Thanks dude
October 14, 2010 at 7:46 pm, Heri Anto マスター said:
pkill mysqld
su mysql
mysql_install_db
chown -R mysql.mysql /var/lib/mysql/
mysqld_safe &
October 14, 2010 at 7:47 pm, Heri Anto マスター said:
try this work for me
pkill mysqld
su mysql
mysql_install_db
chown -R mysql.mysql /var/lib/mysql/
mysqld_safe &
November 10, 2010 at 8:12 am, cave_person_2 said:
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….
March 10, 2011 at 7:11 pm, computerically inclined? said:
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.
December 31, 2012 at 9:54 pm, Kathryn Brewer said:
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.
December 09, 2010 at 4:55 pm, Arik_harel said:
For xampp this command worke for me:
ln -s /opt/lampp/var/mysql/mysql.sock /var/run/mysqld/mysqld.sock
December 17, 2010 at 11:51 am, CJ said:
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
December 21, 2010 at 11:22 pm, Anonym said:
BTW also this occurs when your site under ddos attack.
In this situation mysqld will load high cpu usage too.
February 16, 2011 at 5:52 pm, Yahya Nikandish said:
yahya.nikandish@yahoo.com
June 06, 2011 at 10:54 am, Gopinathkarangula said:
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..
July 25, 2011 at 3:12 pm, Chandan Singh Pachwal said:
try runing it as
/data/(folder in which mysql is installed)/mysql/bin/mysql
July 25, 2011 at 3:12 pm, Chandan Singh Pachwal said:
try runing it as
/data/(folder in which mysql is installed)/mysql/bin/mysql
September 24, 2011 at 2:53 am, rana said:
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?
December 02, 2011 at 6:55 am, Roy M J said:
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
January 02, 2012 at 11:57 am, susu said:
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
March 04, 2012 at 7:02 am, ashraf said:
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
April 25, 2012 at 4:52 am, TQuang said:
Yes, that right. I finding that.
Thank
May 07, 2012 at 10:23 am, amritpal said:
did not works???
August 15, 2012 at 8:24 pm, Bolt Ram said:
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
August 20, 2012 at 6:53 pm, Wolf said:
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
November 08, 2012 at 7:54 am, Sidheeque said:
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
November 10, 2012 at 8:44 am, edd said:
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
January 23, 2013 at 1:24 am, Harihar Pai said:
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
March 21, 2013 at 2:39 pm, Liza said:
Thanks a lot
You saved my life !!!