mysql8.0 데이터베이스 구축+ 원격(외부)접속

mysql 8.0 데이터베이스가 있는 서버 환경은 아래와 같다.

프로세서	intel(R) Core(TM) i5-8500 cpu @ 3.00GHz 3.00 GHz
RAM	16.0GB
운영체제	Windows 10 Pro

해당 데이터베이스를 외부로부터 접속할 Client는 Raspberry Pi 4 model 4B 이다.

​

1. 서버에 데이터베이스 구축하기

(1) mysql 8.0을 윈도우 환경의 컴퓨터에 설치해준다.

• 설치 시에 Microsoft Visual Studio 2019 와 Python이 필요하다. mysql 인스톨러를 실행하기 전에 앞에서 말 한 두가지 프로그램을 꼭 설치하자. - 이거 때문에 cmd에서 mysql -u root -p 로 로그인이 안되는 문제가 있었었다. access denied가 발생했던 것 같다.
• 필자는 workbench보다 cmd에서 작업하는 것을 선호한다. 그렇기 때문에 mysql8.0을 설치한 후에 환경변수를 설정해서 cmd에서 바로 사용할 수 있게 path를 추가했다.
• 명령 프롬프트를 키고 mysql -u root -p password 를 입력해서 mysql에 접속하자. 이때 -u 뒤 문자열(여기서는 root) 과 -p뒤 문자열(여기서는 password) 자리에는 자신이 mysql를 인스톨 할 때 설정했던 id와 password를 쓴다. 때때로 mysql -u root -p password가 안 될 때가 있는데, 그럴 때는 mysql -u root -p 먼저 치고 엔터 후에 > enter password: < 라는 문구가 나오면 그때 password를 쓰자.

​

(2) 이제 create database 명령어를 이용해서 데이터베이스를 만들어주자.

• create database 데이터베이스 이름; (create database testDB;)
• 데이터베이스를 만들었으면 해당 데이터베이스에 접속하자. use 데이터베이스 이름;(use testDB;)

​

(3) 데이터베이스에 접속했으면 자신이 사용할 테이블을 만들어주자.

• create table 테이블이름(

필드이름, 필드타입, null 여부,

필드2 이름, 필드2 타입, null 여부,

.....

primary key(필드이름)

);

​

(4) 테이블에 데이터를 집어넣자.

• insert into 테이블이름 values(필드이름,필드2이름,필드3이름...);

​

(5) 테이블에 집어넣은 데이터를 확인하자.

• select * from 테이블이름;

* 자신이 만든 테이블의 형태를 파악하고 싶다면 desc 테이블이름; 을 해보자.

​

위를 차례대로 수행하면 데이터베이스는 성공적으로 구축되었을 것이다.

이제부터 이 글의 핵심 내용을 서술하도록 하겠다.

​

2. 해당 서버로부터 외부 클라이언트들이 데이터베이스에 접속할 수 있도록 설정하기

이 작업은 크게 3가지로 나누어야 한다. 왜냐하면 우리는 윈도우 환경에서 구축을 할 것이기 때문이다.

​

(1) 방화벽 설정

첫 번째로는 방화벽이다.

인바운드 규칙을 추가해주자.

제어판- 방화벽- 고급설정- 인바운드 규칙에 마우스 우클릭- 새 규칙

프로그램으로 선택하고 다음을 누르자.

C:\Program Files\MySQL\MySQL Server 8.0\bin 처럼 MySQL이 설치 되어있는 폴더에 쭉 들어가보면 bin 파일이 있다. 해당 파일에는 mysqld.exe이 있을 것이다. 다음 프로그램 경로(T)에 C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe 라고 써주자.

​

다음!

다음.

다음

이름,설정은 마음대로 써도 된다.

마침을 눌러주면 인바운드 규칙이 추가된다.

​

​

그리고 포트 인바운드 설정도 해주었다.

포트

보통 mysql은 3306을 사용하고 있을 것이다. 혹시라도 본인이 어떤 포트를 사용하고 있는 지 모르면 cmd를 켜서 netstate -ano 를 쳐보자. 그러면 포트와 PID가 쭉 뜰 것이다.

PID와 프로세스는 작업관리자에서 확인해볼 수 있다.

다음 작업부터는 위에서 설명한 것들과 동일하다.

 

위의 작업을 시행했음에도 불구하고 되지 않는다면 방화벽을 아예 off하고 테스트해보자.

​

(2) mysql 계정 설정

mysql은 보안상의 이유로 localhost 접속만을 허용하고 있다. 그렇기 때문에 우리가 사용할 mysql 계정에 원격접속을 할 수 있도록 권한을 주어야 한다. 이 과정에서 사용하는 명령어는 mysql 8.0만 보장한다. mysql 5.2는 조금 달랐던 것으로 기억한다.

​

우선 mysql -u root -p password를 통해 로그인을 한다.

use mysql 을 입력하여 데이터베이스를 change 한다.

GRANT ALL PRIVILEGES ON *.* to 'root'@'%'; 을 이용하여 root 계정에 권한을 부여해주자. 꼭 root 일 이유는 없다. 자신이 권한을 부여하고자 하는 id를 root자리에 기입하면 된다.

​

flush privileges; 를 이용해서 새로고침 하자.

​

이제 정상적으로 등록이 되었는지 확인하자.

select host, user, password from mysql.user;

 

 

맨 아래처럼 host가 % 이면 성공한 것이다. 이제 외부 접속이 되는지 확인하자.

mysql에 로그인할 때 원격으로 접속할 시스템의 주소와 id, password를 써주면 된다.

mysql -h ip주소 -u root -p

이때, 외부로부터 접속이 가능하게 해주는 ip주소는 ipconfig로는 알 수 없다.

내이버에 '내 아이피 주소 확인하기' 를 검색해서 나오는 ip주소로 시도하라.

하지만!! 아직 해야할 일이 남았기 때문에 위의 명령어는 나중에 실행할 것이다.

​

(3) 포트포워딩

해당 포트로 접속할 수 있게 포트포워딩을 해주어야 한다.

자신의 공유기에 맞는 주소로 들어가자. 필자의 경우 iptime을 사용하고 있기 때문에 192.168.0.1이다.

​

로그인해서 들어가면 저런 목록이 보인다. 박스 표시 해 둔 곳으로 들어가자.

현재 접속된 ip주소를 체크해주고, 외부포트 내부포트를 모두 3306으로 설정해준다. (mysql에서 사용하는 포트이다.)

​

포트포워딩까지 끝내면 정말 진짜로 원격접속을 할 준비가 다 끝났다.

이제 cmd를 켜서 mysql -h ip주소 -u root -p 를 해보자.

​

짜잔

성공이다.

​

이제 서버의 원격접속 준비는 끝이다.

​

이제 클라이언트인 라즈베리파이 설정을 하자.

다른 컴퓨터에서 모두 원격 접속이 가능했는데 이상하게 라즈베리파이에서만 원격접속이 불가능 했다.

​

라즈베리파이 commend 창에서 mysql을 설치해주자.

해당 시스템에서는 mariadb를 설치하였다. 

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

를 이용해서 설정파일에 들어간다. port=3306 주석이 되어있다면 풀어준다. 

​

라즈베리파이에도 mysql을 설치하고, sudo -u root -p 로 접속했다. 초기에는 비밀번호가 없으니 그냥 엔터치고 들어가면 되었던 것 같다. 들어가서 root 비밀번호 update 해주자.

 

참고

필자는 access denied for user root@'ip주소' (155) 문제가 있었다.

vnc viewer을 이용하여 같은 컴퓨터 내에서 라즈베리파이와 데이터베이스 서버를 동시에 운영하고 있었다.

그래서 그런지 포트 충돌이 났었던 것 같다.

​

라즈베리파이는 랜선을 연결해서 유선네트워크를 사용했다

 

​