MYSQL ERROR 2003 (HY000): Can’t connect to MySQL server on ‘xx.xx.xx.xx’ (111)

При попытке подключиться к MySQL или Percona сервер с настройками по-умолчанию с другой рабочей станции или сервера, можно получить ошибку
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘xx.xx.xx.xx’ (111)

Далее описаны 3 шага, которые нужно пройти для установления соединения.

The first thing we can check is to see if the user from the remote host is allowed.

1. Login as root on mysql server
mysql -u root -p

2. Select database and show users.
select * from mysql.user\G

**A vertical list will be displayed. Look at the first two fields for each entry. If the user is only allowed to connect from localhost, this may be the problem.
Example:

Host: localhost
User: mydbuser

A user will have to be defined with the same parameters as mydbuser for the remote host (or hosts)
Here’s where your documentation will come in handy (or you can hope the old query exists in the mysql buffer!)

3. Allow remote hosts to connect
grant select,insert,update,delete,create,drop,index,alter on mydbname.* to mydbuser@’192.168.1.%’ identified by ‘mydbpassword’ ;

flush privileges;

Note: if you only want to allow a certain host, specify the IP instead of the wildcard.

The second issue that may cause this error is a MySQL configuration.

1. Open MySQL config file
nano /etc/my.cnf

2. Ensure that the following are commented out.

#skip-external-locking
#skip-networking
#bind-address = xx.xx.xx.xx

Save and exit

3. Restart mysql service
service mysqld start

The third issue that may contribute to this error may be the security configuration rejecting incoming requests on the server.

1. Login as root on db server

2. Add rule to iptables
/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp —destination-port 3306 -j ACCEPT

** this grants access to the entire subnet, use a specific IP where applicable.

service iptables save

3. Restart iptables service
service iptables restart

Test from remote host by using the following:
mysql -h 192.168.my.dbip -u mydbuser -p

MYSQL ERROR 2003 (HY000): Can’t connect to MySQL server on ‘xx.xx.xx.xx’ (111): 2 комментария

  1. Спасибо. Перерыл по этой ошибке кучу форумов и сайтов, а проблема оказалась ближе, чем я думал (bind-address был установлен по-умолчанию в localhost).

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *