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.
Best Answer
Check the
bind-address
in yourmy.cnf
. It should be0.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:
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: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.