원격 DB (MySQL) 와 연결하기

지금 내가 쓰는 컴퓨터(macOS)로는 frontend만 다루고, 다른 컴퓨터(Linux)에 DB를 구축하고 싶었다.

약간의 시행착오를 토대로, 원격지의 DB에 연결하는 방법을 정리해두었다.

참고로 클라이언트 측 컴퓨터에 굳이 MySQL이 깔려있을 필요는 없다.

 

글은 다음과 같은 순서로 진행될 예정이다.

1. 권한이 있는 사용자 만들기
2. 방화벽과 MySQL 설정
3. 최종 접속 테스트

 

1. 권한이 있는 사용자 만들기

 

물론 사용자를 새로 만들지 않고, 이미 있는 사용자에게 권한을 부여할 수도 있다. 그 방법도 함께 적어두겠다. 또한, ubuntu 기준으로 작성하였으므로 참고가 되었으면 좋겠다. 먼저terminal을 실행한다.

 

1) MySQL 루트 계정으로 로그인한다.

sudo mysql -u root -p

 

2) 새로운 사용자 만들기

CREATE USER 'new_user'@'%' IDENTIFIED BY 'new_password';

 

모든 권한을 부여해줄 new_user를 만든다.

이 때, '%'는 모든 IP에서 접속 가능한 권한을 주는 것이다.

'%' 대신 'localhost'를 입력하면 로컬(현재 서버)에서만 접속 가능한 계정이 된다.

 

3) 사용자 목록 확인하기

SELECT user, host FROM mysql.user;

+----------+-----------+
| user     | host      |
+----------+-----------+
| root     | localhost |
| new_user | %         |
| userr    | localhost |
+----------+-----------+

 

목록에서 new_user가 잘 추가되어 있는 것을 볼 수 있다.

단, 여기에는 함정이 있다.  다음 명령어를 입력한다.

SHOW GRANTS FOR 'new_user'@'%';

 

그러면 new_user의 권한을 확인해볼 수 있다.

만약 결과가 다음처럼 나왔다면,

 

+-------------------------------------------------------------+
| Grants for new_user@%                                      |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'new_user'@'%'                       |
| GRANT SELECT ON test_db.* TO 'new_user'@'%'            |
+-------------------------------------------------------------+

 

권한에서 USAGE만 있고 ALL PRIVILEGES가 없다면 아직 모든 권한이 부여된 것이 아니다.

또는 마지막 줄 처럼 SELECT 권한만 있어서 DB에 완전한 접근 권한이 없을 수 있다.

이를 해결하기 위해서는 new_user에게 전체 권한을 다시 부여하면 된다.

이 때, 굳이 새로운 사용자를 만들지 않았어도 다음 과정을 똑같이 따라하면 된다.

 

4) 모든 권한 부여하기

GRANT ALL PRIVILEGES ON test_db.* TO 'new_user'@'%';
FLUSH PRIVILEGES;

 

flush privileges로 권한을 적용해주면 1단계는 마무리되었다.

 

2. 방화벽과 MySQL 설정

1) 방화벽에서 3306 포트 열기

sudo ufw allow 3306/tcp
sudo ufw reload

 

ufw : Ubuntu 방화벽 설정

 

이걸 하는 이유는, 이제까지 local 환경에서는 3306 포트를 통해 '네트워크'까지 거칠 필요는 없었지만 원격에서 MySQL을 접속하려면 '네트워크를 통해'야만 하기 때문이다. 즉, 위 명령어를 통해 MySQL 서버가 외부(원격)에서 접속할 수 있도록 방화벽이 허용된다.

 

왜 하필 3306번 포트일까? 그냥 기본적으로 MySQL 서버는 포트 3306을 사용해서 클라이언트와 통신한다.

 

2) MySQL 설정 파일 열기

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

 

여기서 아래와 같이 bind-adress 항목을 찾는다.

 

bind-adress = 127.0.0.1

 

3) bind-address 변경하기

bind-address = 0.0.0.0

 

4) MySQL 재시작

 sudo systemctl restart mysql

 

3. 최종 접속 테스트

1) terminal을 이용해 간단하게 확인하기

mysql -u new_user -p -h 192.168.XXX.XXX -D test_db

 

-u new_user : 새로 만든 사용자

-p : 비밀번호

-D test_db : 접속할 데이터베이스 이름

192.168.XXX.XXX : 원격 MySQL 서버(DB 컴퓨터)의 ip 주소

 

패스워드까지 입력해주고, 접속에 성공하면 DB연결에 성공한 것이고, 실패하면 무언가 잘못된 것이다.

 

2) php 코드를 이용해서 확인하기.

 

<?php
$servername = "192.168.XXX.XXX"; // 원격 MySQL 서버 IP 또는 도메인
$username = "new_user"; // MySQL 사용자명
$password = "new_password"; // MySQL 비밀번호
$dbname = "test_db"; // 사용할 데이터베이스 이름

// MySQL 연결 시도
$conn = new mysqli($servername, $username, $password, $dbname);

// 연결 확인
if ($conn->connect_error) {
    die("연결 실패: " . $conn->connect_error);
} else {
    echo "원격 MySQL 서버에 성공적으로 연결되었습니다!";
}

$conn->close();
?>

 

php를 가동시킨 뒤 웹에서 해당 페이지로 이동하면, 

 

이렇게 연결에 성공한 것을 확인할 수 있다.