[Raspberry Pi] 운영체제 라즈비안(Raspbian)에서 linux(Ubuntu)OS로 MySQL DB에 값 전송, 저장하기

운영체제 라즈비안(Raspbian)에서 linux(Ubuntu)OS로 MySQL DB에 값 전송, 저장하기

 

---

<MySQL 설치>

우선 Linux에서 MySQL을 사용하기 위해서는 Linux환경에 MySQL을 install 해 주어야 합니다.

1. ~$ sudo apt-get update

패키지 관리자를 최신화 합니다.

 

2. ~$ sudo apt-get install mysql-server

MySQL을 install 합니다.

 

3.  ~$ mysql_secure_installation

MySQL 보안설정 입니다. root 계정의 패스워드를 설정하고 외부 로그인 설정 등 여러가지 설정을 합니다.

비밀번호를 설정하고 Disallow root login remotely? [Y/n]에서 외부 로그인 설정만 n해주고 나머지는 y로 했습니다.

여기까지 하면 MySQL root계정이 만들어 집니다.

 

 

이제 root계정으로 접속해서 사용자 계정을 추가해 주겠습니다.

외부에서 Data를 받아와야 하기 때문에 root계정으로 바로 받아오기 보다는 외부접속을 허용한 계정을 새로 만들어서 받아오게 되면 보안상의 문제도 줄어들 것이라고 생각합니다.

 

 

<MySQL 새로운 계정 추가 및 권한 부여>

sudo mysql -u root -p

명령어를 통해 위에서 설정한 비밀번호를 입력하고 root계정으로 접속합니다.

 

create database 데이터베이스명칭;

사용자 계정이 사용할 데이터베이스를 만들어 보았습니다.

 

show databases;

데이터베이스 목록을 확인할 수 있습니다.

 

create user '사용자 계정명'@'%'identified by '비밀번호'

새로운 계정을 만들어 줍니다. @뒤에 %는 다른 시스템에서 외부로 접속이 가능하다는 뜻입니다.

%부분에 localhost를 입력하면 MySQL이 설치된 시스템에서만 접속이 가능합니다.

 

계정을 만들었다면 해당 계정에 권한을 부여해야 합니다.

grant all privileges on 데이터베이스명칭.*to '사용자계정명'@'%';

위에서 만든 데이터베이스를 외부접속이 허용된 계정에서 사용할 수 있도록 권한을 부여하겠습니다.

 

exit로 root계정을 나가준 뒤

sudo mysql -u 사용자계정명 -p

새로 만든 계정으로 접속합니다.

 

show databases;

다시 한번 데이터베이스를 조회 해 보면 아까 권한을 허용해 준 데이터 베이스가 있다면 성공입니다.

 

create database sensor_data;
use sensor_data;
create table value1(
id int primary key not null,
ip varchar(20) not null,
value int not null
);

 

 

<포트포워딩, 접속 포트 번호 변경>

이제 MySQL을 exit로 닫아주고 

sudo -i

root 계정으로 접속해 줍니다.

 

cd /etc/mysql/mysql.conf.d/

mysqld.cnf 파일이 있는 곳으로 이동해 줍니다.

 

nano mysqld.cnf

mysqld.cnf로 들어가 포트 번호 설정을 해 주겠습니다.

포트 번호를 다른 포트와 겹치지 않는 번호로 설정 해 줍니다.

저는 임의로 9876 이라는 번호를 사용했습니다.

MySQL은 기본적으로 localhost(127.0.0.1)에서 접근 가능합니다.

외부에서 접속을 허용하기 위해 주석처리 해 주었습니다.

이 부분 주석처리를 풀어주었습니다.

max_allowed_packet은 서버로 질의하거나 받게되는 패킷의 최대 길이를 나타내는 시스템 변수입니다.

전송하려는 Data의 크기에 따라서 오류가 날 수 있기 때문에 전송 할 때 오류가 난다면 값을 확인해 주어야 합니다.

thread_stack은 thread 당 할당되는 stack 사이즈 입니다. 값이 작으면 stack 할당을 못해서 MySQL 서버가 핸들링 할 수 있는 복잡한 구문을 처리하지 못할 수 있습니다.

 

sudo service mysql restart

MySQL 서버를 다시 시작해서 변경된 설정을 적용시켜 줍니다.

 

 

linux 포트포워딩과 window 방화벽 인바운드 규칙을 설정 해 주겠습니다.

저는 Oracle VM VirtualBox로 Ubuntu를 사용하고 있습니다.

1

 

2

 

3

 

위에서 설정한 9876 포트로 포트포워딩을 해 주었습니다.

 

이번엔 Window에서 고급 보안이 포함된 Windows Defender 방화벽으로 들어갑니다.

1

2

 

포트 선택 -> 특정 로컬 포트에 설정한 포트 입력 (예 : 9876) - > 계속 다음 누르다가 이름 설명 원하는 거 쓰고 마침

여기까지 모든 준비가 끝났습니다. 이제 Data를 전송시켜 봅시다.

from random import *
import time
import pymysql
 
count = 0
sensor_value = 0
index = 1
while(1):
    i = randint(100,999)
    sensor_value=sensor_value+i
    time.sleep(1)
    count=count+1
 
    if count==5:
        conn = pymysql.Connect(host='IPv4 주소', user='사용자 계정',
            password='사용자 계정 비밀번호', db='사용할 DB 이름', charset='utf8',
            port=설정해 준 포트 번호)
        curr = conn.cursor()
        res=sensor_value/5
        sql = "insert into value1 values('"+str(index)+"',
            "+"'192.168.xxx.xx','"+str(res)+"')"
 
        print(sql)
        print(curr.execute(sql))
        conn.commit()
        count=0
        sensor_value=0
        index += 1
        curr.close()
        conn.close()
        conn = None
        curr = None

sql문과 execute로 1이 초마다 출력됩니다.

 

다시 linux의 MySQL 사용자 계정으로 접속해

select * from 데이터베이스명.테이블명;

으로 들어온 값을 조회 해 봅니다.

값이 잘 들어오고 있는 것을 확인할 수 있습니다.