"MySQL ERROR 2003 (HY000): Can't connect to MySQL server on"의 두 판 사이의 차이

(사용자 3명의 중간 판 4개는 보이지 않습니다)
4번째 줄: 4번째 줄:
==문제상황==
==문제상황==
*[[MySQL에 원격 접속 허용]]하였으나 접속 안됨
*[[MySQL에 원격 접속 허용]]하였으나 접속 안됨
<source lang='cli'>
<source lang='console'>
[root@zetawiki ~]# mysql -h135.79.246.80 -uroot -pP@ssw0rd
[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)
ERROR 2003 (HY000): Can't connect to MySQL server on '135.79.246.80' (111)
10번째 줄: 10번째 줄:


==클라이언트측 확인==
==클라이언트측 확인==
<source lang='cli'>
<source lang='console'>
[root@zetawiki ~]# nmap 135.79.246.80 -p 3306 -Pn
[root@zetawiki ~]# nmap 135.79.246.80 -p 3306 -Pn


21번째 줄: 21번째 줄:
Nmap done: 1 IP address (1 host up) scanned in 0.11 seconds
Nmap done: 1 IP address (1 host up) scanned in 0.11 seconds
</source>
</source>
<source lang='cli'>
<source lang='console'>
[root@zetawiki ~]# nc -z 135.79.246.80 3306
[root@zetawiki ~]# nc -z 135.79.246.80 3306
[root@zetawiki ~]# telnet 135.79.246.80 3306
[root@zetawiki ~]# telnet 135.79.246.80 3306
30번째 줄: 30번째 줄:


==서버측 확인==
==서버측 확인==
<source lang='cli'>
<source lang='console'>
root@jmnote02:~# netstat -antp | grep mysql
root@jmnote02:~# netstat -antp | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*              LISTEN      2593/mysqld   
tcp        0      0 127.0.0.1:3306          0.0.0.0:*              LISTEN      2593/mysqld   
</source>
</source>
:→ 3306 포트가 로컬호스트(127.0.0.1)에 대해서만 열려 있음
:→ 3306 포트가 로컬호스트(127.0.0.1)에 대해서만 열려 있음
<source lang='cli'>
<source lang='console'>
root@jmnote02:~# grep ^bind-address /etc/mysql/my.cnf  
root@jmnote02:~# grep ^bind-address /etc/mysql/my.cnf  
bind-address = 127.0.0.1
bind-address = 127.0.0.1
46번째 줄: 46번째 줄:
</source>
</source>
;변경 전
;변경 전
<source lang='autoconf'>
<source lang='aconf'>
# Instead of skip-networking the default is now to listen only on
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# localhost which is more compatible and is not less secure.
52번째 줄: 52번째 줄:
</source>
</source>
;변경 후
;변경 후
<source lang='autoconf'>
<source lang='aconf'>
# Instead of skip-networking the default is now to listen only on
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# localhost which is more compatible and is not less secure.
59번째 줄: 59번째 줄:


==MySQL 재시작 및 확인==
==MySQL 재시작 및 확인==
<source lang='cli'>
<source lang='console'>
root@jmnote02:~# service mysql restart
root@jmnote02:~# service mysql restart
mysql stop/waiting
mysql stop/waiting
mysql start/running, process 3432
mysql start/running, process 3432
</source>
</source>
<source lang='cli'>
<source lang='console'>
root@jmnote02:~# netstat -antp | grep mysql
root@jmnote02:~# netstat -antp | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*              LISTEN      3432/mysqld
tcp        0      0 0.0.0.0:3306            0.0.0.0:*              LISTEN      3432/mysqld
71번째 줄: 71번째 줄:


==클라이언트측 확인 2==
==클라이언트측 확인 2==
<source lang='cli'>
<source lang='console'>
[root@zetawiki ~]# nc -z 135.79.246.80 3306
[root@zetawiki ~]# nc -z 135.79.246.80 3306
Connection to 135.79.246.80 3306 port [tcp/mysql] succeeded!
Connection to 135.79.246.80 3306 port [tcp/mysql] succeeded!
</source>
</source>
<source lang='cli'>
<source lang='console'>
[root@zetawiki ~]# mysql -h135.79.246.80 -uroot -pP@ssw0rd
[root@zetawiki ~]# mysql -h135.79.246.80 -uroot -pP@ssw0rd
Welcome to the MySQL monitor.  Commands end with ; or \g.
Welcome to the MySQL monitor.  Commands end with ; or \g.
95번째 줄: 95번째 줄:
*클라이언트측의 [[OUTBOUND]] 방화벽 막힘
*클라이언트측의 [[OUTBOUND]] 방화벽 막힘
:예: 다른 곳으로 3306 포트 접근이 막힌 경우
:예: 다른 곳으로 3306 포트 접근이 막힌 경우
*클라이언트측과 MySQL 버전이 서로 상이하면 막힘
:→ 본적이 없는데 어떤 버전이 그런가요? --[[사용자:Jmnote|Jmnote]] ([[사용자토론:Jmnote|토론]]) 2016년 8월 19일 (금) 16:23 (KST)


==같이 보기==
==같이 보기==

2016년 8월 19일 (금) 16:23 판

ERROR 2003 (HY000): Can't connect to MySQL server on '135.79.246.80' (111)

1 문제상황

[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 클라이언트측 확인

[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
[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 서버측 확인

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)에 대해서만 열려 있음
root@jmnote02:~# grep ^bind-address /etc/mysql/my.cnf 
bind-address		= 127.0.0.1

4 my.cnf 수정

  • my.cnf를 열어 bind-address를 주석처리한다.
vi /etc/mysql/my.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
변경 후
# 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 재시작 및 확인

root@jmnote02:~# service mysql restart
mysql stop/waiting
mysql start/running, process 3432
root@jmnote02:~# netstat -antp | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3432/mysqld
→ 이제 모든 IP에서 접속 가능

6 클라이언트측 확인 2

[root@zetawiki ~]# nc -z 135.79.246.80 3306
Connection to 135.79.246.80 3306 port [tcp/mysql] succeeded!
[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 버전이 서로 상이하면 막힘
→ 본적이 없는데 어떤 버전이 그런가요? --Jmnote (토론) 2016년 8월 19일 (금) 16:23 (KST)

8 같이 보기

문서 댓글 ({{ doc_comments.length }})
{{ comment.name }} {{ comment.created | snstime }}