MQTTで publish されたデータを PHP でログとして保存するプログラム

MQTTで publish されたデータは何らかのプログラムでデータを受信して
そのデータをDBへ保存したりする処理を実装する必要があります。

今回 さくらのVPSサーバーに MQTTブローカー(Mosquitto) を立ててエッジデバイスからPublishされたデータをログに保存するブログラムを書いてみました。

##構成
###さくらVPSサーバー
　3コア
　2GB メモリ
　100GB SSD

###OS
　Ubuntu Linux 20.04.2

###MQTTブローカー
　Mosquitto


Mosquittoのインストールに関する手順は今回割愛します。
(色々な情報が公開されているので)


###PHPによるsubscript プログラム

```mqtt_subscribe.php
<html>
<body>
<?php
date_default_timezone_set('Asia/Tokyo');        // 時間を JST に設定
$payload = $_REQUEST['payload'];                // Payload(data) 取得
$now = date('Y-m-d H:i:s');                     // 現在時刻の取得
$fp = fopen('./log/data.txt', 'a');             // ログファイル オープン
fwrite($fp, $now);                              // 現在時刻保存
fwrite($fp, " : " );
fwrite($fp, $payload);                          // Payload データ保存
fwrite($fp, "\r\n" );
fclose($fp);                                    // ログファイル クローズ
/*
  ここにデータ処理プログラム
  (データベースに保存等)
*/
?>
</body>
</html>
```

このプログラムをMQTTにデータがPublishされる毎に実行すればデータが保存されます。

データがPublishされる毎にmqtt_subscribe.phpをコールするスクリプトを作成します。

```
mosquitto_sub -t "#" -v | xargs -I@ curl  --request POST "ホスト名/mqtt_subscribe.php" --data "payload='@'" --header "Content-Type:application/x-www-form-urlencoded"
```

このスクリプトをコンソールで実行するとコンソールを開いている間はデータが保存されますが、コンソールを閉じるとこのスクリプトは実行されません。

そこでこのスクリプトをSystemdを使ってサービス化します。
mqtt_subscribe.sh ファイルを作成して実行権(755)を与えます。

```mqtt_subscribe.sh
#!/bin/bash
mosquitto_sub -t "#" -v | xargs -I@ curl  --request POST "ホスト名/mqtt_subscribe.php" --data "payload='@'" --header "Content-Type:application/x-www-form-urlencoded"
```

次に
/etc/systemd/system/ の下にUnit定義ファイルを作ります。

```mqtt_subscribe.service
[Unit]
Description = mqtt subscribe daemon

[Service]
ExecStart = /opt/mqtt_subscribe.sh
Restart = always
Type = simple

[Install]
WantedBy = multi-user.target
```

mqtt_subscribe.serviceをenableしてstartします

```
# 自動起動on
$ sudo systemctl enable mqtt_subscribe
# 起動
$ sudo systemctl start mqtt_subscribe
```

これでMQTTにデータがpublishされる度にそのデータをログに保存します。

```/log/data.txt
2021-06-12 14:15:53 : 'test/publish/u7AOVuGjFNmI {module:u7AOVuGjFNmI,type:channels,datetime:2021-06-12T05:15:53.212979642Z,payload:{channels:[{channel:1,type:i,value:25879,datetime:2021-06-12T05:15:53.104980436Z},{channel:2,type:f,value:30.923334,datetime:2021-06-12T05:15:53.116980436Z},{channel:3,type:f,value:41.39209,datetime:2021-06-12T05:15:53.128980436Z},{channel:4,type:i,value:380,datetime:2021-06-12T05:15:53.140980436Z},{channel:5,type:f,value:1007.57104,datetime:2021-06-12T05:15:53.152980436Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:15:53.164980436Z},{channel:7,type:i,value:2009,datetime:2021-06-12T05:15:53.176980436Z},{channel:8,type:i,value:116,datetime:2021-06-12T05:15:53.188980436Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:15:53.200980436Z}]}}'
2021-06-12 14:16:46 : 'test/publish/ucCIBvjX5qfX {module:ucCIBvjX5qfX,type:channels,datetime:2021-06-12T05:16:46.017488371Z,payload:{channels:[{channel:1,type:i,value:28130,datetime:2021-06-12T05:16:45.910489446Z},{channel:2,type:f,value:30.824167,datetime:2021-06-12T05:16:45.922489446Z},{channel:3,type:f,value:44.65088,datetime:2021-06-12T05:16:45.934489446Z},{channel:4,type:i,value:393,datetime:2021-06-12T05:16:45.946489446Z},{channel:5,type:f,value:1007.77136,datetime:2021-06-12T05:16:45.958489446Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:16:45.970489446Z},{channel:7,type:i,value:33,datetime:2021-06-12T05:16:45.982489446Z},{channel:8,type:i,value:24,datetime:2021-06-12T05:16:45.994489446Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:16:46.006489446Z}]}}'
2021-06-12 14:18:19 : 'test/publish/u7AOVuGjFNmI {module:u7AOVuGjFNmI,type:channels,datetime:2021-06-12T05:18:18.890485947Z,payload:{channels:[{channel:1,type:i,value:25879,datetime:2021-06-12T05:18:18.783488303Z},{channel:2,type:f,value:30.924168,datetime:2021-06-12T05:18:18.795488303Z},{channel:3,type:f,value:39.93709,datetime:2021-06-12T05:18:18.807488303Z},{channel:4,type:i,value:380,datetime:2021-06-12T05:18:18.819488303Z},{channel:5,type:f,value:1007.5743,datetime:2021-06-12T05:18:18.831488303Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:18:18.843488303Z},{channel:7,type:i,value:2130,datetime:2021-06-12T05:18:18.855488303Z},{channel:8,type:i,value:108,datetime:2021-06-12T05:18:18.867488303Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:18:18.879488303Z}]}}'
2021-06-12 14:19:10 : 'test/publish/ucCIBvjX5qfX {module:ucCIBvjX5qfX,type:channels,datetime:2021-06-12T05:19:10.309508171Z,payload:{channels:[{channel:1,type:i,value:28130,datetime:2021-06-12T05:19:10.202509413Z},{channel:2,type:f,value:30.825832,datetime:2021-06-12T05:19:10.214509413Z},{channel:3,type:f,value:44.628174,datetime:2021-06-12T05:19:10.226509413Z},{channel:4,type:i,value:393,datetime:2021-06-12T05:19:10.238509413Z},{channel:5,type:f,value:1007.7976,datetime:2021-06-12T05:19:10.250509413Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:19:10.262509413Z},{channel:7,type:i,value:28,datetime:2021-06-12T05:19:10.274509413Z},{channel:8,type:i,value:25,datetime:2021-06-12T05:19:10.286509413Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:19:10.298509413Z}]}}'
2021-06-12 14:20:42 : 'test/publish/u7AOVuGjFNmI {module:u7AOVuGjFNmI,type:channels,datetime:2021-06-12T05:20:41.934950557Z,payload:{channels:[{channel:1,type:i,value:25879,datetime:2021-06-12T05:20:41.827951864Z},{channel:2,type:f,value:30.949167,datetime:2021-06-12T05:20:41.839951864Z},{channel:3,type:f,value:39.58415,datetime:2021-06-12T05:20:41.851951864Z},{channel:4,type:i,value:380,datetime:2021-06-12T05:20:41.863951864Z},{channel:5,type:f,value:1007.5328,datetime:2021-06-12T05:20:41.875951864Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:20:41.887951864Z},{channel:7,type:i,value:1738,datetime:2021-06-12T05:20:41.899951864Z},{channel:8,type:i,value:116,datetime:2021-06-12T05:20:41.911951864Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:20:41.923951864Z}]}}'
2021-06-12 14:21:34 : 'test/publish/ucCIBvjX5qfX {module:ucCIBvjX5qfX,type:channels,datetime:2021-06-12T05:21:34.572576796Z,payload:{channels:[{channel:1,type:i,value:28130,datetime:2021-06-12T05:21:34.465577896Z},{channel:2,type:f,value:30.831667,datetime:2021-06-12T05:21:34.477577896Z},{channel:3,type:f,value:44.601727,datetime:2021-06-12T05:21:34.489577896Z},{channel:4,type:i,value:393,datetime:2021-06-12T05:21:34.501577896Z},{channel:5,type:f,value:1007.7565,datetime:2021-06-12T05:21:34.513577896Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:21:34.525577896Z},{channel:7,type:i,value:27,datetime:2021-06-12T05:21:34.537577896Z},{channel:8,type:i,value:20,datetime:2021-06-12T05:21:34.549577896Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:21:34.561577896Z}]}}'
2021-06-12 14:23:06 : 'test/publish/u7AOVuGjFNmI {module:u7AOVuGjFNmI,type:channels,datetime:2021-06-12T05:23:06.283712978Z,payload:{channels:[{channel:1,type:i,value:25879,datetime:2021-06-12T05:23:06.175714268Z},{channel:2,type:f,value:30.959166,datetime:2021-06-12T05:23:06.187714268Z},{channel:3,type:f,value:39.11833,datetime:2021-06-12T05:23:06.199714268Z},{channel:4,type:i,value:380,datetime:2021-06-12T05:23:06.211714268Z},{channel:5,type:f,value:1007.5093,datetime:2021-06-12T05:23:06.223714268Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:23:06.235714268Z},{channel:7,type:i,value:1718,datetime:2021-06-12T05:23:06.247714268Z},{channel:8,type:i,value:116,datetime:2021-06-12T05:23:06.259714268Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:23:06.271714268Z}]}}'
2021-06-12 14:23:59 : 'test/publish/ucCIBvjX5qfX {module:ucCIBvjX5qfX,type:channels,datetime:2021-06-12T05:23:58.861333653Z,payload:{channels:[{channel:1,type:i,value:28130,datetime:2021-06-12T05:23:58.754334687Z},{channel:2,type:f,value:30.840834,datetime:2021-06-12T05:23:58.766334687Z},{channel:3,type:f,value:44.59831,datetime:2021-06-12T05:23:58.778334687Z},{channel:4,type:i,value:393,datetime:2021-06-12T05:23:58.790334687Z},{channel:5,type:f,value:1007.7432,datetime:2021-06-12T05:23:58.802334687Z},{channel:6,type:i,value:4,datetime:2021-06-12T05:23:58.814334687Z},{channel:7,type:i,value:26,datetime:2021-06-12T05:23:58.826334687Z},{channel:8,type:i,value:20,datetime:2021-06-12T05:23:58.838334687Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:23:58.850334687Z}]}}'
2021-06-12 14:25:31 : 'test/publish/u7AOVuGjFNmI {module:u7AOVuGjFNmI,type:channels,datetime:2021-06-12T05:25:30.596649821Z,payload:{channels:[{channel:1,type:i,value:25879,datetime:2021-06-12T05:25:30.489650921Z},{channel:2,type:f,value:30.918333,datetime:2021-06-12T05:25:30.501650921Z},{channel:3,type:f,value:38.688152,datetime:2021-06-12T05:25:30.513650921Z},{channel:4,type:i,value:380,datetime:2021-06-12T05:25:30.525650921Z},{channel:5,type:f,value:1007.5612,datetime:2021-06-12T05:25:30.537650921Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:25:30.549650921Z},{channel:7,type:i,value:1790,datetime:2021-06-12T05:25:30.561650921Z},{channel:8,type:i,value:116,datetime:2021-06-12T05:25:30.573650921Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:25:30.585650921Z}]}}'
2021-06-12 14:26:23 : 'test/publish/ucCIBvjX5qfX {module:ucCIBvjX5qfX,type:channels,datetime:2021-06-12T05:26:23.169199784Z,payload:{channels:[{channel:1,type:i,value:28130,datetime:2021-06-12T05:26:23.062200815Z},{channel:2,type:f,value:30.84,datetime:2021-06-12T05:26:23.074200815Z},{channel:3,type:f,value:44.5861,datetime:2021-06-12T05:26:23.086200815Z},{channel:4,type:i,value:393,datetime:2021-06-12T05:26:23.098200815Z},{channel:5,type:f,value:1007.7732,datetime:2021-06-12T05:26:23.110200815Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:26:23.122200815Z},{channel:7,type:i,value:25,datetime:2021-06-12T05:26:23.134200815Z},{channel:8,type:i,value:20,datetime:2021-06-12T05:26:23.146200815Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:26:23.158200815Z}]}}'
2021-06-12 14:27:55 : 'test/publish/u7AOVuGjFNmI {module:u7AOVuGjFNmI,type:channels,datetime:2021-06-12T05:27:54.94993752Z,payload:{channels:[{channel:1,type:i,value:25879,datetime:2021-06-12T05:27:54.842938856Z},{channel:2,type:f,value:30.875834,datetime:2021-06-12T05:27:54.854938856Z},{channel:3,type:f,value:38.071777,datetime:2021-06-12T05:27:54.866938856Z},{channel:4,type:i,value:380,datetime:2021-06-12T05:27:54.878938856Z},{channel:5,type:f,value:1007.55615,datetime:2021-06-12T05:27:54.890938856Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:27:54.902938856Z},{channel:7,type:i,value:1689,datetime:2021-06-12T05:27:54.914938856Z},{channel:8,type:i,value:112,datetime:2021-06-12T05:27:54.926938856Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:27:54.938938856Z}]}}'
2021-06-12 14:28:47 : 'test/publish/ucCIBvjX5qfX {module:ucCIBvjX5qfX,type:channels,datetime:2021-06-12T05:28:47.487875973Z,payload:{channels:[{channel:1,type:i,value:28130,datetime:2021-06-12T05:28:47.380876893Z},{channel:2,type:f,value:30.8425,datetime:2021-06-12T05:28:47.392876893Z},{channel:3,type:f,value:44.588787,datetime:2021-06-12T05:28:47.404876893Z},{channel:4,type:i,value:393,datetime:2021-06-12T05:28:47.416876893Z},{channel:5,type:f,value:1007.76263,datetime:2021-06-12T05:28:47.428876893Z},{channel:6,type:i,value:4,datetime:2021-06-12T05:28:47.440876893Z},{channel:7,type:i,value:24,datetime:2021-06-12T05:28:47.452876893Z},{channel:8,type:i,value:24,datetime:2021-06-12T05:28:47.463876893Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:28:47.475876893Z}]}}'
2021-06-12 14:30:19 : 'test/publish/u7AOVuGjFNmI {module:u7AOVuGjFNmI,type:channels,datetime:2021-06-12T05:30:19.175492402Z,payload:{channels:[{channel:1,type:i,value:25879,datetime:2021-06-12T05:30:19.068493174Z},{channel:2,type:f,value:30.835,datetime:2021-06-12T05:30:19.079493174Z},{channel:3,type:f,value:37.709797,datetime:2021-06-12T05:30:19.091493174Z},{channel:4,type:i,value:380,datetime:2021-06-12T05:30:19.103493174Z},{channel:5,type:f,value:1007.53986,datetime:2021-06-12T05:30:19.115493174Z},{channel:6,type:i,value:4,datetime:2021-06-12T05:30:19.127493174Z},{channel:7,type:i,value:1508,datetime:2021-06-12T05:30:19.139493174Z},{channel:8,type:i,value:108,datetime:2021-06-12T05:30:19.151493174Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:30:19.164493174Z}]}}'
2021-06-12 14:31:12 : 'test/publish/ucCIBvjX5qfX {module:ucCIBvjX5qfX,type:channels,datetime:2021-06-12T05:31:11.811869584Z,payload:{channels:[{channel:1,type:i,value:28130,datetime:2021-06-12T05:31:11.704870638Z},{channel:2,type:f,value:30.845001,datetime:2021-06-12T05:31:11.715870638Z},{channel:3,type:f,value:44.58716,datetime:2021-06-12T05:31:11.727870638Z},{channel:4,type:i,value:393,datetime:2021-06-12T05:31:11.739870638Z},{channel:5,type:f,value:1007.7358,datetime:2021-06-12T05:31:11.752870638Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:31:11.764870638Z},{channel:7,type:i,value:23,datetime:2021-06-12T05:31:11.776870638Z},{channel:8,type:i,value:20,datetime:2021-06-12T05:31:11.788870638Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:31:11.800870638Z}]}}'
2021-06-12 14:32:43 : 'test/publish/u7AOVuGjFNmI {module:u7AOVuGjFNmI,type:channels,datetime:2021-06-12T05:32:43.572757814Z,payload:{channels:[{channel:1,type:i,value:25879,datetime:2021-06-12T05:32:43.465758982Z},{channel:2,type:f,value:30.835833,datetime:2021-06-12T05:32:43.477758982Z},{channel:3,type:f,value:41.368977,datetime:2021-06-12T05:32:43.489758982Z},{channel:4,type:i,value:380,datetime:2021-06-12T05:32:43.501758982Z},{channel:5,type:f,value:1007.48254,datetime:2021-06-12T05:32:43.513758982Z},{channel:6,type:i,value:5,datetime:2021-06-12T05:32:43.525758982Z},{channel:7,type:i,value:1482,datetime:2021-06-12T05:32:43.537758982Z},{channel:8,type:i,value:120,datetime:2021-06-12T05:32:43.549758982Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:32:43.561758982Z}]}}'
2021-06-12 14:33:36 : 'test/publish/ucCIBvjX5qfX {module:ucCIBvjX5qfX,type:channels,datetime:2021-06-12T05:33:36.194094536Z,payload:{channels:[{channel:1,type:i,value:28130,datetime:2021-06-12T05:33:36.087095245Z},{channel:2,type:f,value:30.848333,datetime:2021-06-12T05:33:36.099095245Z},{channel:3,type:f,value:44.58317,datetime:2021-06-12T05:33:36.111095245Z},{channel:4,type:i,value:393,datetime:2021-06-12T05:33:36.123095245Z},{channel:5,type:f,value:1007.6644,datetime:2021-06-12T05:33:36.135095245Z},{channel:6,type:i,value:4,datetime:2021-06-12T05:33:36.147095245Z},{channel:7,type:i,value:22,datetime:2021-06-12T05:33:36.159095245Z},{channel:8,type:i,value:24,datetime:2021-06-12T05:33:36.171095245Z},{channel:9,type:i,value:0,datetime:2021-06-12T05:33:36.183095245Z}]}}'
```

次回は
mqtt_subscribe.php を改造して
データをMySQLデータベースに保存してみます。