[MySQL] MySQL 외부접속 허용 설정

서버 환경

• Ubuntu 14.04 LTS
• PHP 7.1.0
• Apache Web Server 2.4.7
• MySQL 5.5.62

원격 접속 환경

• Windows 10 64bit

Mysql WorkBench 8 로 ubuntu mysql서버에 접속하려고 하니

Failed to Connect to MySQL at \[server ip:3306\] with user ...

라는 오류가 발생했습니다.

1. mysql server listen ip 대역 확인

mysql server 의 listen ip 대역을 확인하기 위해 아래 명령어를 실행합니다.

sudo net stat -ntlp | grep mysqld

결과

tcp 0 0 127.0.0.1:3306 0.0.0.0:\* LISTEN 7931/mysqld

2. LISTEN IP 변경(bind-address)

변경하기 위해 ubuntu에서 my.cnf 파일로 이동합니다.

cd /etc/mysql/my.cnf

sudo vi my.cnf

bind-address 를 주석 처리합니다.

이 라인을
bind-address = 127.0.0.1

이렇게 주석처리
#bind-address = 127.0.0.1

my.cnf를 저장하고 빠져나온다.

3. LISTEN ADDRESS ( bind-address ) 변경 확인

변경을 적용하기 위해 mysql을 재시작 해줍니다.

sudo service mysql restart

그리고 다시 listen ip를 확인해 준다

sudo net stat -ntlp | grep mysqld

tcp 0 0 0.0.0.0:3306 0.0.0.0:\* LISTEN 1890/mysqld

이렇게 listen ip가 변경된 것을 확인할 수 있습니다.

4. mysql user access (mysql 유저 접속 권한 확인)

이제 mysql-server로의 접속은 가능합니다.
하지만, 이제 mysql 계정별로 원격접속 권한을 줘야합니다.

기본적으로 localhost에서 접속이 가능하게 되어있을 것입니다.
일단 ubuntu에서 mysql에 접속하여 이 쿼리를 날려봅니다

SELECT Host,User,authentication_string FROM mysql.user;

+-----------+------------------+-------------------------------------------+
| Host      | User             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *8024A6913C57E024BDFC6E813A57DFB924E6803A |
| 127.0.0.1 | root             | *8024A6913C57E024BDFC6E813A57DFB924E6803A |
| ::1       | root             | *8024A6913C57E024BDFC6E813A57DFB924E6803A |
| localhost | debian-sys-maint | *02CE4A6D8F13BE579AC2468F0357B9D1468025A7 |
+-----------+------------------+-------------------------------------------+

이런 식으로 결과가 나올 것입니다.
그렇다면 본인 상황에 맞게 추가로 계정별 원격접속 허용을 해주면 된다.

4.1 모든 ip 허용

INSERT INTO mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) VALUES ('%','root',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

4.2 IP 대역 허용 ( ex : 111.222.xxx.xxx )

INSERT INTO mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) VALUES ('111.222.%','root',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.%';
FLUSH PRIVILEGES;

4.3 특정 IP 허용

INSERT INTO mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) VALUES ('111.222.33.44','root',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.33.44';
FLUSH PRIVILEGES;

4.4 원상 복귀

DELETE FROM mysql.user WHERE Host='%' AND User='root';
FLUSH PRIVILEGES;