✔️ CentOS 6 → 우분투 12.04에서 테스트하였습니다.
- ERROR 2003 (HY000): Can't connect to MySQL server on '135.79.246.80' (111)
1 문제상황[ | ]
- MySQL에 원격 접속 허용하였으나 접속 안됨
Console
Copy
[root@zetawiki ~]# mysql -h135.79.246.80 -uroot -pP@ssw0rd
ERROR 2003 (HY000): Can't connect to MySQL server on '135.79.246.80' (111)
2 클라이언트측 확인[ | ]
Console
Copy
[root@zetawiki ~]# nmap 135.79.246.80 -p 3306 -Pn
Starting Nmap 5.51 ( http://nmap.org ) at 2014-12-13 03:24 KST
Nmap scan report for jmnote02 (135.79.246.80)
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
Console
Copy
[root@zetawiki ~]# nc -z 135.79.246.80 3306
[root@zetawiki ~]# telnet 135.79.246.80 3306
Trying 135.79.246.80...
telnet: connect to address 135.79.246.80: Connection refused
- → 방화벽에 막히지는 않았으나 서버가 거부
3 서버측 확인[ | ]
Console
Copy
root@jmnote02:~# netstat -antp | grep mysql
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 2593/mysqld
- → 3306 포트가 로컬호스트(127.0.0.1)에 대해서만 열려 있음
Console
Copy
root@jmnote02:~# grep ^bind-address /etc/mysql/my.cnf
bind-address = 127.0.0.1
4 my.cnf 수정[ | ]
- my.cnf를 열어 bind-address를 주석처리한다.
Bash
Copy
vi /etc/mysql/my.cnf
- 변경 전
aconf
Copy
# 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
- 변경 후
aconf
Copy
# 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
5 MySQL 재시작 및 확인[ | ]
Console
Copy
root@jmnote02:~# service mysql restart
mysql stop/waiting
mysql start/running, process 3432
Console
Copy
root@jmnote02:~# netstat -antp | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3432/mysqld
- → 이제 모든 IP에서 접속 가능
6 클라이언트측 확인 2[ | ]
Console
Copy
[root@zetawiki ~]# nc -z 135.79.246.80 3306
Connection to 135.79.246.80 3306 port [tcp/mysql] succeeded!
Console
Copy
[root@zetawiki ~]# mysql -h135.79.246.80 -uroot -pP@ssw0rd
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.5.40-0ubuntu0.12.04.1 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
7 기타 다른 원인?[ | ]
- 클라이언트측의 OUTBOUND 방화벽 막힘
- 예: 다른 곳으로 3306 포트 접근이 막힌 경우
- 클라이언트측과 MySQL 버전이 서로 상이하면 막힘
8 같이 보기[ | ]
편집자 Jmnote Jmnote bot 121.126.197.4
로그인하시면 댓글을 쓸 수 있습니다.