데이터 수집용 데이터베이스 생성과 php를 통해 데이터 삽입하기

Google Cloud Platform Compute Engine의 Linux VM (Centos 8)에 MariaDB를 이전에 설치했다.

센서와 아두이노를 활용하여 수집한 데이터를 이 VM의 데이터베이스에 저장하기위해 데이터베이스를 생성하는 과정과 저장을 위한 php 코드 작성 및 삽입을 진행해본다.

 

수집한 데이터를 저장하기위한 데이터베이스 생성은 MariaDB 콘솔에서 진행하거나 기타 프로그램을 사용하여 데이터베이스를 생성해도 된다. 선택은 본인의 몫이다.

 

나는 SQL 구문을 익히기위해 콘솔환경에서 DB관련 작업을 진행한다.

데이터베이스 생성과 데이터베이스 접근용 사용자 생성 작업은 MariaDB의 root계정으로 진행해야하므로

MariaDB콘솔에 root계정으로 로그인한다.

데이터베이스 생성은 쉽다. MariaDB 콘솔창에서 다음의 명령을 입력한다.

create database SensorData;

SensorData 대신에 다른 DB명을 사용하고자 한다면 다른 DB명으로 바꿔서 명령을 진행하면 된다.

이후 생성된 SensorData 데이터베이스에 테이블을 생성한다. 온도와 습도를 측정 할 수 있는 DHT11이라는 센서를 사용해 수집한 값을 저장할 것이므로 테이블명을 DHT11로 정했다.

 

테이블 생성 전에 방금 생성한 SensorData에대한 전용 계정을 만들어보자. 외부에서 root계정으로 db에 접근하는것은 매우 위험한 행동이므로 전용 계정을 만들어 관리하는것이 좋다.

 

root계정으로 MariaDB콘솔에 로그인한 상태로 다음 명령을 입력한다.

%는 외부에서 접속하기위한 것이며 localhost는 같은 내부에서 접근할때 이용하기 위함이다. (VM에 php파일과 DB가 같이 있기 때문이다. 사실 DB와 웹서버는 분리시켜야 안전하다!)

create user '유저명'@'%' identified by '비밀번호';
create user '유저명'@'localhost' identified by '비밀번호';

계정을 생성했다면 이제 권한을 부여해줘야한다. 그냥 데이터 추가 삭제 수정 검색용이면 INSERT, DELETE, UPDATE, SELECT만 주면 된다. 다른권한은 줄 필요없다.

 

GARNT SELECT,INSERT,UPDATE,DELETE ON SensorData.* TO '유저명'@'%' identified by '비밀번호';
GARNT SELECT,INSERT,UPDATE,DELETE ON SensorData.* TO '유저명'@'localhost' identified by '비밀번호';

 

이제 SensorData 데이터베이스에 DHT11테이블을 생성해보자

MariaDB 콘솔에 다음과같이 입력을 한다. 한줄에 무리하게 입력하지 않아도 된다.

엔터를 통해 다음줄로 넘어가도 세미콜론을 만나기 전까지는 계속 명령 입력을 받는다.

 

CREATE TABLE DHT11 (
Seq BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Value BIGINT NOT NULL,
Time DATETIME NOT NULL
);

저장 순서를 의미하는 Seq와 값을 의미하는 Value의 타입은 BIGINT로 정했다. 데이터를 수집한 날짜와, 시간을 뜻하는 Time 컬럼의 타입은 DATETIME으로 정했다.

 

방금 만든 테이블에 임의로 데이터를 한번 삽입해보자. 콘솔창에 다음 쿼리를 입력한다.

 

INSERT INTO DHT11 (Value, Time) VALUES (4545,"2020-05-01 20:04:05");

Query OK라는 응답이 나온다면 이후에 Select * FROM DHT11;을 진행하여 방금 insert한 값이 테이블에 있는지 확인한다.

 

삽입이 잘 되었다.

 

이제 웹 요청을 통해서 테이블에 값을 입력해보자. 그 전에 설정해야할 것이 있다.

SELinux설정을 변경해줘야한다. 다음 명령으로 http를 통해 db에 접근할 수 있는지 확인한다.

 

getsebool -a | grep ^http | grep db

 

httpd_can_network_connect_db 이 부분이 off로 되어있는데 on으로 변경해줘야 한다.

변경하지 않으면 httpd를 통해 db에 연결할 수 없다. 다음 명령을 입력하여 off를 on으로 변경하자

 

sudo setsebool -P httpd_can_network_connect_db=1

이 명령을 입력 후 다시 getsebool -a | grep ^http | grep db 명령으로 off -> on으로 변경되었는지 확인한다.

setsebool의 -P 옵션은 재부팅 이후에도 계속 설정이 유지되도록 영구 유지 하는 옵션이다.

 

이제 php를 통해 db에 데이터를 삽입해보자.

 

다음 php코드를 작성하여 /var/www/html에 저장한다.

<?php
        $host = "Host";    // VM의 주소를 입력한다.
        $dbname = "SensorData"; // 데이터베이스명을 입력한다.
        $sqluser = "USER";      // 데이터베이스에 접근할 계정을 입력한다.
        $sqlpass = "USERPASSWORD"; // 계정의 비밀번호를 입력한다.
        $dbcon = "mysql:host={$host};dbname={$dbname};charset=utf8";
                        // 데이터베이스에 연결한다.
                        // 만약 port가 기본 3306이 아닐경우 port=yourport를 위에 추가한다.
                // 또한 charset을 utf8로 설정한다.
        try{
                $Value = $_POST['Value'];
                $Time = $_POST['Time'];
                // POST방식으로 데이터를 전송할 것이므로 POST로 설정 GET으로 데이터 전송시 GET으로 바꾸면 된다.

                $regchk = preg_match("/^(19|20)\d{2}-(0[1-9]|1[012])-(0[1-9]|1[0-9]|2[0-9]|3[0-1])\s(0[0-9]|1[0-9]|2[0-3]):(0[0-9]|1[0-9]|2[0-9]|3[0-9]|4[0-9]|5[0-9]):(0[0-9]|1[0-9]|2[0-9]|3[0-9]|4[0-9]|5[0-9])$/",$Time);

                // 날짜형식이 2020-05-20 20:01:13 과 같이 정상적 날짜 형식인지 체크하는 정규식이다.
                // 위 정규식에 일치하지않으면 값을 추가할 수 없다.

                if(empty($Value) || empty($Time)){
                        echo "파라미터값 중 일부가 빈값 입니다.";
                        // body중에 빈값이 있는지 확인
                }else if($regchk == true){

                                // 빈값체크,날짜 정규식 체크를 모두 통과할때 코드

                $tablename = "DHT11";
                // DHT11 테이블에 삽입할것이다

                $db = new PDO($dbcon, $sqluser, $sqlpass);
                //PDO 라는 객체를 이용하여 db접근

                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                // 에러 발생시 에러를 EXCEPTION으로 스로잉한다.

                $st = $db->prepare("INSERT INTO DHT11 (Value,Time)
                        VALUE (:Value,:Time)
                ");
                // 선처리 질의문을 이용하여 쿼리를 진행한다.
                // 선처리 질의문은 SQL Injection 방어에도 좋다.

                $st->bindValue(":Value",$Value);
                $st->bindValue(":Time",$Time);
                //POST를 통해 입력 받은 값을 bind 처리해준다.

                $st->execute();
                // execute를 통해 최종적으로 db에 값을 삽입한다.

                echo "데이터 입력이 완료되었습니다.";
                // 모두 완료되면 메시지를 띄운다.

                }else{
                        echo "시간값이 올바르지 않습니다.";
                        // 날짜 정규식 체크를 통과하지 못할 경우 메시지
                }
        } catch(PDOException $e){
                echo "ERROR! Code is ".$e->getCode();
                // DB관련 에러 발생시 에러코드를 띄운다.
                                // 자세한 메시지를 출력시 DB명 테이블명 컬럼명이 드러날 위험이 있다.
        }
?>

 

이제 postman 프로그램을 통해 post 요청을 한번 날려보자.

일부러 바디의 일부 값을 비워봤다.

 

저렇게 메시지가 나오고 값이 입력된것이 없다.

이제 제대로 값을 채워보고 실행해보자.

 

DB에 값이 잘 들어갔을까?

 

입력이 되었다. Seq가 44인 이유는 이전에 테스트로 넣어봤던 레코드들은 모두 삭제했다.

truncate table하면 깔끔히 제거할 수 있는데 그렇게 삭제를 안했다 ㅋㅋ

 

여기까지 DB와 테이블을 생성하고 PHP를 통해 데이터를 삽입하는 과정을 진행해봤다.