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.05 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에서 접속 가능
