Raspberry Pi에서 MariaDB에 Mosquitto MQTT Message 저장하기

 

 

안녕하세요.

 

  이전 내용(“Raspberry Pi에서 MariaDB Docker Container와 Python 연동하기 (Docker-compose 사용)”)에서 MariaDB와 MQTT를 위한 다른 Container까지 생성하는 스크립트를 작성해 보았습니다. 이번에는 MQTT로 데이터를 보내서 MariaDB에 저장할 수 있도록 Broker 역할의 프로그램을 Python으로 작성해 보도록 하겠습니다.

---

 1. 이전 내용 확인

  - Docker Compose를 통해 Container가 동작하고 있다고 생각하고 내용을 진행하겠습니다. 

  pi@raspberrypi:~/mariaDB_compose $ docker-compose up -d

2. Table 생성

  - 먼저, Maria DB Container에 연결합니다. 그리고 root 계정으로 접속해서 테이블을 생성합니다.   

pi@raspberrypi:~/mariaDB_compose $ docker exec -it forAll_mariaDB bash
root@d2faa46b5ecb:/# mysql -u root -p

 

  - Table 생성하기 전에 DB에 연결을 해야 하므로 use 명령으로 사용할 DB로 먼저 연결합니다. (이전내용에서 만들어 놓은 sensorDB 사용)   

MariaDB [(none)]> use sensorDB;

 

  - 테이블 명은 mqtt_data_tb 하고 저장할 데이터는 타임스탬프(TIMESTAMP)와 문자열(TEXT)을 입력받을 수 있게 Table을 생성합니다(Query 문으로 생성). “HeidiSQL” 프로그램에서 자동으로 생성되는 코드를 보면 아래와 같습니다.   

CREATE TABLE `mqtt_data_db` (
           `idx` TIMESTAMP NULL DEFAULT NULL,
           `data` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci'
);
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

3. MQTT DB 저장을 위한 Python 코드 작성

3.1 Python 코드 작성

  - MariaDB Container에서 나와서 MQTT를 위해 추가로 만든 Container에 접속합니다.   

  pi@raspberrypi:~/mariaDB_compose $ docker exec -it ubuntu_mqtt bash
  pi@raspberrypi:~/mariaDB_compose $ vim mqtt_db.py

 

   - 소스상 바꿔야 하는 DB부분을 보면, host 주소(DB 설치한 PC IP), user, password, database를 설치한 내용으로 변경합니다.

 

  - MQTT Broker를 설치하지 않고 테스트할 수 있는 무료 서버를 이용해 봅니다. (이전 내용에서 Mosquitto broker 소스를 컴파일한 도커도 사용할 수 있습니다.) 코드와 동일하게 사용해도 되지만 MQTT_Topic을 동일하게 하면 다른 사람이 올리는 값도 전달받기 때문에 변경하는 것을 추천드립니다.

                 

 - Query문으로 생성했던 테이블(mqtt_data_db)에 맞게 필드를 맞추어서 데이터를 추가하는 insert문을 추가해 줍니다. 그리고 기본적으로 무한루프를 돌면서 수신이벤트가 발생하면 Callback 함수가 호출되도록 연결되어 있습니다.

#!/usr/bin/python -u

import mysql.connector as mariadb
import paho.mqtt.client as mqtt
import ssl
import datetime

mariadb_connection = mariadb.connect(host='192.168.0.67',user='sensor1', password='sensor1', database='sensorDB')
cursor = mariadb_connection.cursor()


MQTT_Broker = "test.mosquitto.org"
MQTT_Port = 1883
Keep_Alive_Interval = 60
MQTT_Topic = "/mqtt_test/sensorDB/#"

# Subscribe
def on_connect(client, userdatia, flags, rc):
  mqttc.subscribe(MQTT_Topic, 0)

def on_message(mosq, obj, msg):
  timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
  testdb = 'mqtt_data_db'
  byte_obj = msg.payload
  org_data = msg.payload.decode("utf-8")
  print("debug : " + timestamp)

  #print('{} {} [QOS {} Retain {}]'.format(msg.topic, msg.payload, msg.qos, msg.retain))

  sql = "INSERT INTO {0} SET `idx`='{1}',`data`='{2}'" \
          .format(testdb, timestamp, org_data)
  print("SQL exec: '{}'".format(sql))

  try:
    cursor.execute(sql)
  except mariadb.Error as error:
    print("Error : {}".format(error))

  mariadb_connection.commit()


def on_subscribe(mosq, obj, mid, granted_qos):
  pass

mqttc = mqtt.Client()

# Assign event callbacks
mqttc.on_message = on_message
mqttc.on_connect = on_connect
mqttc.on_subscribe = on_subscribe

# Connect
#mqttc.tls_set(ca_certs="ca.crt", tls_version=ssl.PROTOCOL_TLSv1_2)
mqttc.connect(MQTT_Broker, int(MQTT_Port), int(Keep_Alive_Interval))

# Continue the network loop & close db-connection
print("==== MQTT DB Broker Start ====")
mqttc.loop_forever()
mariadb_connection.close(

 

3.2 동작 방법

 - 소스를 저장했다면, 소스를 실행합니다. DB Server와 Test Mosquitto Broker Server에 정상적으로 접속이 되면 “MQTT DB Broker Start”라고 메시지를 볼 수 있습니다.

4. 필요 프로그램 설치

4.1 MQTT 메시지 보내는 프로그램 설치

  - mosquitto client를 설치했다면 커멘드 상(CLI)에서도 가능한데, 좀 더 편하게 이용하게 위해서 GUI 프로그램을 이용해 보았습니다. 다운로드하여 설치한 후 실행합니다.

http://mqtt-explorer.com/

 

 - test.mosquitto.org 사이트에 CONNECT 합니다.

 

 

  - Topic 내용은 Python 소스상에 MQTT_Topic 경로(토픽)을 입력해 주고 raw (data)를 선택하고 메시지를 적어서 PUBLISH 버튼을 누릅니다. 그러면 코멘드 창에 받은 메시지 정보가 나타납니다. 테스트용으로 mosquitto Broker 서버를 이용하기 때문에 계속 연결해 놓으면 계속데이터가 들어오기 때문에 메모리에 누적되는 현상이 발생합니다.

 

 

4.2 DB viewer 설치

  - MariaDB 역시 커멘드 상(CLI)에서도 볼 수 있지만, 실행되는 Query도 보고, GUI로 데이터를 볼 수 있어서 이용해 보았습니다. 테스트해 볼 프로그램을 설치한 후 실행합니다.

https://www.heidisql.com/download.php

 

  - 위에서 설치한 HeidisSQL프로그램으로 MariaDB에 연결을 합니다.

 

 

  - 연결이 되면 sensorDB 목록의 mqtt_data_db 선택하고 데이터 탭을 누르면 저장된 데이터를 확인할 수 있습니다. 이제 이것을 좀 더 안정화시켜서 사용하면 다른 재미난 것들을 만들 수 있지 않을까 합니다.

 

 

감사합니다.