Heidisql 활용을 위한 SQL 유저생성, 권한설정 및 DB 외부접속

본 포스팅에서는 터미널을 활용하지 않고 직관적인 UI로 데이터베이스를 관리 할 수 있는 프로그램인 Heidisql에 대하여 포스팅한다. Heidisql의 활용을 위해서는 DB의 설치후 DB 외부접속을 위해 포트를 열어주거나 생성된 유저의 DB 접근 권한등을 설정해줘야 하는 경우가 존재한다. 본 포스팅에서는 외부접속을 위한 방법과 외부접속 툴인 Heidisql에 대하여 포스팅한다.

1. SQL 포트 확인 및 유저 권한 설정

일차적으로 SQL을 설치하고 나면 SQL이 기본적으로 사용하는 3306 포트가 Listen 상태가 아닌 경우가 존재한다. 이러한 포트 확인을 통해 3306 포트가 열려있지 않다면, 외부접속이 불가능하기 때문에 포트를 열어주는 작업이 필요하다. 또한 포트를 열어준뒤에 sql 접속을하여 계정 생성및 계정이 특정 DB 혹은 모든 DB에 접근할 수 있도록 접근권한 설정이 필요하다. 따라서 이번에는 SQL 포트 열기 -> 계정생성 -> DB 생성 -> 계정권한 설정 순으로 진행해보도록 한다.

// sql 포트 확인 및 firewall을 이용한 포트 허용

# netstat -tnlp

# firewall-cmd --add-port/3306/tcp

// 외부접속 가능한 사용자 생성 및 사용자 확인

(1) 로컬접속만 가능 유저 생성

(2) 외부접속 가능한 유저생성

# create user username@'localhost' identified by 'password';  // (1)

# create user username@'%' identified by 'password'; // (2)

# user mysql; -> select user from user;

나의 경우에는 neo라는 유저를 외부접속 허용으로 만들었고 권한확인한 결과 아래와 같다.

ex) # create user neo@'%' identified by '123123';

// 변경권한 적용 및 생성된 유저 외부(내부) 접속 권한 확인

# flush privileges;

# show grants for 'neo'@'%'

// 유저에게 DB 권한 설정 해당 데이터베이스에 모든 테이블 접근 권한 설정

(1) 특정 데이터베이스에 모든 테이블 접근권한 설정

(2) 모든 데이터베이스 및 테이블 접근권한 설정

# grant all privileges on dbname.* to 'username'@'%';

# grant all privileges on *.* to 'username'@'%';

ex) # grant all privileges on ns_sensor.* to 'neo'@'%';  // (1)

// 변경권한 적용 및 생성된 유저 외부(내부) 접속 권한 확인

# flush privileges;

# show grants for 'neo'@'%'

아래 그림의 경우 위와는 다르게 2 row 컬럼으로 늘어난것을 확인 할 수 있다. 위의 경우에는 유저의 상태 (외부접속 가능 혹은 내부접속만 가능)가 확인 가능하였고 방금의 DB 권한여부 설정을 통해서 해당 유저가 접근 가능한 데이터베이스와 테이블을 보여준다.

2. HeidiSQL 설치 및 외부접속을 통한 DB 생성

HeidiSQL이란 Mysql, Mssql, MariaDB를 통합적으로 관리할 수 있는 오픈소스 소프트웨어 이며, HeidiSQL을 활용하면 터미널에 접속하지 않고도 DB의 접속과 테이블 생성 및 간단한 테스트 데이터의 입력을 할 수 있다.

HeidiSQL 다운로드 링크: https://www.heidisql.com/download.php

설치 후에 세션관리자가 뜨면 SQL이 설치된 IP를 적고 포트 3306으로 한 후에 앞전에 설정한 외부접속 가능한 유저와 유저의 암호를 입력하여 열기를 누르면 간단하게 접속이 가능하다.

그림과 같이 간단히 데이터베이스를 생성할 수 있으며 데이터베이스 생성 후에 테이블도 바로 생성하여 데이터의 입력도 가능하다.

앞으로의 개발을 위해서 HeidiSQL의 활용이 높기 때문에 꼭 외부접속이 가능한지 시도해보는것이 좋을것 같다. 

이번 포스팅에서는 단순한 SQL의 외부접속을 위한 포스팅이였지만, 다음 포스팅에서는 일전에 설치한 PHP를 활용하여 실제 POST를 이용해 데이터베이스에 데이터를 저장하는 방법에 대해서 포스팅 하도록 하겠다. 만약 APM의 개념 및 설치 방법에 대해서 궁금하다면 일전에 포스팅한 (APM) Apaceh, PHP, MySQL 이론 및 설치에 들어가서 설치를 하면될것 같다. 

링크: https://neosla.tistory.com/12?category=805051 

[참고]

일전 포스팅에서 MySQL 설치 방법에 대하여 포스팅 하였지만 본 포스팅의 경우 마리아DB를 사용하였다. MySQL과 MariaDB의 문법상 차이는 존재하지 않지만 MariaDB의 설치 커맨드에 대해서 작성해 보았다.

1. 업데이트

yum install make kernel-devel gcc perl bzip2

yum update kernel*

2. repo 등록

vim /etc/yum.repos.d/MariaDB.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.1/centos7-amd64

gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck = 1

3. MariaDB Package

yum install MariaDB-client-10.1.36-1.el7.centos MariaDB-server-10.1.36-1.el7.centos MariaDB-common-10.1.36-1.el7.centos MariaDB-shared-10.1.36-1.el7.centos boost-program-options-1.53.0-27.el7 galera-25.3.23-1.rhel7.el7.centos jemalloc-3.6.0-1.el7

4. mysql.h

yum install MariaDB-devel-10.1.36-1.el7.centos

5. MariaDB Service 등록

systemctl start mariadb

systemctl enable mariadb

6. MariaDB Setting

mysql_secure_installation

(1)현재 PASSWORD 입력 (Default는 없음)

(2)root PASSWORD 사용여부 및 입력

(3)anonymous users 제거여부

(4)원격 root 로그인 거부여부

(5)test DB 제거 여부

(6)특정권한 table 리로드 여부