Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I was trying to tune the performance of a running mysql-server by running this command:

mysqld_safe --key_buffer_size=64M --table_cache=256 --sort_buffer_size=4M --read_buffer_size=1M &

After this i'm unable to connect mysql from the server where mysql is running. I get this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

However, luckily i can still connect to mysql remotely. So all my webservers still have access to mysql and are running without any problems. Because of this though i don't want to try to restart the mysql server since that will probably mess everything up.

Now i know that mysqld_safe is starting the mysql-server, and since the mysql server was already running i guess it's some kind of problem with two mysql servers running and listening to the same port.

Is there some way to solve this problem without restarting the initial mysql server?

UPDATE: This is what ps xa | grep "mysql" says:

11672 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe
11780 ?        Sl   175:04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
11781 ?        S      0:00 logger -t mysqld -p daemon.error
12432 pts/0    R+     0:00 grep mysql

UPDATE 2: Tried to restart now. Stopping worked fine but starting failed. To my surprise the mysql-server is still running without problems though so i don't really know what it stopped.

UPDATE 3: I'm pretty sure this is the problem now: When i started mysql the first time it wrote its processid to /var/run/mysqld/mysqld.pid and the socket to /var/run/mysqld/mysqld.sock.

Then when i runned the mysqld_safe command it removed these files. Now i cant stop the mysql-server since the stop command needs these files. Is there some way i can restore these files? I guess the pid-file is just a file with the process id? I don't want to just kill the process because there are very many writes to this server and killing mysql usally ends in corrupted databases.

share|improve this question

migrated from stackoverflow.com May 19 '10 at 13:23

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 4 down vote accepted

Check the bind-address in your my.cnf. It should be 0.0.0.0 in order to allow both local and remote connections.

Check with ps xa | grep mysql how many instances are run.

Edit
Apparanently:

A MySQL client on Unix can connect to the mysqld server in two different ways: By using a Unix socket file to connect through a file in the file system (default /tmp/mysql.sock), or by using TCP/IP, which connects through a port number. A Unix socket file connection is faster than TCP/IP, but can be used only when connecting to a server on the same computer. A Unix socket file is used if you don't specify a host name or if you specify the special host name localhost.

Check here for some useful tip.

Edit2
One of possible reasons of Can't connect to local MySQL server, that seems to be the problem here:

Someone has removed the Unix socket file that mysqld uses (/tmp/mysql.sock by default). For example, you might have a cron job that removes old files from the /tmp directory. You can always run mysqladmin version to check whether the Unix socket file that mysqladmin is trying to use really exists. The fix in this case is to change the cron job to not remove mysql.sock or to place the socket file somewhere else. See Section B.5.4.5, “How to Protect or Change the MySQL Unix Socket File”.

Martin, make sure you have socket = /var/run/mysqld/mysqld.sock in my.cnf, I believe mysqld restart will solve your problem, it should recreate mysqld.sock and you should be able to connect locally.

share|improve this answer
    
Updated the question with the result of "ps". Is mysqld_safe supposed to be running? –  Martin May 19 '10 at 12:56
    
The bind_address is set to the ip right now. But it worked with this before? I guess changing it will require a restart o mysql? –  Martin May 19 '10 at 12:58
    
I guess so, I have it too. Seems that you have only one instance. –  Dmitry Yudakov May 19 '10 at 12:59
    
yes, changing bind_address would require restart, but I guess this parameter is not what causing the problem here –  Dmitry Yudakov May 19 '10 at 13:09
    
Does /var/run/mysqld/mysqld.sock exist? –  Dmitry Yudakov May 19 '10 at 13:18

What is in your mysql log-error log (different distros have different locations)?

What yuor instance settings are? Connect to mysql and run command

show global variabes

Watch for variables socket and log-error

EDIT: If mysql is running and you can connect to it, you could shutdown it using command

mysqladmin shutdown
share|improve this answer
    
sock and pid file should be in /var/run/mysqld/ says global variables but they are not. All logs are empty. I'll try the mysqladmin shutdown thing now :) –  Martin May 19 '10 at 20:01

Just add the -h flag with your hostname when connnecting to see it if works. Also try adding the --socket= flag to MySQLd

mysql -h myserver.null.com -P 3306 -u bob -P dbname

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.