MySql 접속 안되는 경우 확인

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

1 문제상황

  • MySQL에 원격 접속 허용하였으나 접속 안됨
[root@mysql ~]# mysql -hxxx.xxx.xxx.xxx -uroot -pP@ssw0rd
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (111)

2 클라이언트측 확인

[root@mysql ~]# nmap xxx.xxx.xxx.xxx -p 3306 -Pn
Starting Nmap 5.51 ( http://nmap.org ) at 2021-12-13 03:24 KST
Nmap scan report for jmnote02 (xxx.xxx.xxx.xxx)
Host is up (0.00074s latency).
PORT     STATE  SERVICE
3306/tcp closed mysql
Nmap done: 1 IP address (1 host up) scanned in 0.11 seconds
[root@mysql ~]# nc -z xxx.xxx.xxx.xxx 3306
[root@mysql ~]# telnet xxx.xxx.xxx.xxx 3306
Trying xxx.xxx.xxx.xxx...
telnet: connect to address xxx.xxx.xxx.xxx: Connection refused

→ 방화벽에 막히지는 않았으나 서버가 거부

3 서버측 확인

root@mysqlserver:~# netstat -antp | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      2593/mysqld

→ 3306 포트가 로컬호스트(127.0.0.1)에 대해서만 열려 있음

4 my.cnf 수정

  • mysqld.cnf 를 열어 bind-address를 주석처리한다.
vi /etc/mysql/mysql.conf.d/mysqld.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#mysqlx-bind-address    = 127.0.0.1
bind-address            = 0.0.0.0
mysqlx-bind-address     = 0.0.0.0

5 MySQL 재시작 및 확인

root@mysqlserver:~# service mysql restart
mysql stop/waiting
mysql start/running, process 3432

root@mysqlserver:~# netstat -antp | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3432/mysqld

→ 이제 모든 IP에서 접속 가능

Leave a Reply

Your email address will not be published. Required fields are marked *