I sent the data of Raspberry Pi to GCP (free)

I sent the data of Raspberry Pi to GCP (free)

I wanted to create a mechanism to store Raspberry Pi data in MySQL and check it on the Web. For those who want to set up a server on a VPS and exchange data.

• The purpose of this article is to acquire and send temperature data on the Rasberry Pi.

Development environment

• Windows10 Pro
• Powershell
• Windows Subsystem for Linux(Ubuntu)

About GCP

Abbreviation for Google Cloud Platform, a cloud computing service provided by Google.

https://cloud.google.com/?hl=ja

See here for how to get started with GCP

https://cloud.google.com/gcp/getting-started/?hl=ja

About Windows Subsystem for Linux (WSL)

--Install Ubuntu (version not specified) from Microsoft Store ――It seems that the latest version will be automatically updated. --Home directory is / home / [creation user name] / --Powershell is / mnt / c / Users / [username] / --Move to / mnt / c / Users / [user name] / with the cd command and start working

Set up a web server

--Creating a GCP project --Set up a VM of your favorite OS (I am Ubuntu 18.04 LTS)

• If you want to create it with a free frame, please refer to here.

https://cloud.google.com/free/docs/gcp-free-tier?hl=ja

--Perform SSH authentication (for accessing from Windows)

• Supplement: If you want to send the public key by scp

scp ~/.ssh/[Public key] [username]@[IP of the forwarding server]:~/.ssh/

--Installing apache2

sudo apt-get update
sudo apt-get install apache2 -y
curl http://[External IP]

• External IP is Google Cloud Platform-> Computer Engine-> VM instance

--Firefall settings (ufw installation and settings)

#activation
sudo ufw enable
#TCP you are using/Examine the UDP port
sudo ufw status
#Also possible with nmap
sudo nmap -sTU localhost
#Port release (rule registration)
sudo ufw allow [Port number you want to allow]
#Delete rule
sudo ufw status numbered
sudo ufw delete [number]
#Invalidation
sudo ufw disable

DB construction (MySQL)

sudo apt install mysql-server mysql-client
#Service start confirmation
sudo service mysql status
#MySQL initialization
sudo mysql_secure_installation
#Connect to MySQL server from console
sudo mysql -u root -p

--Confirmation of MySQL user information, etc.

#Status display
mysql> status
#Database list display
mysql> show database;
#User list display
mysql> select user, host from mysql.user;
#Confirmation of authority of a specific user (ex)User: root,Host name: localhost)
mysql> show grants for 'root'@'localhost';
#End
mysql> exit

https://www.yokoweb.net/2018/05/13/ubuntu-18_04-server-mysql/

--Actually build --This time, create two fields, acquisition time and temperature data.

sudo mysql
#Creating a database
mysql> create database [db_name]
mysql> show databases;
#Move database
mysql> use [db_name]
#Creating a table (no need for a single coat)
mysql> create table [tbl_name]( id int(11) NOT NULL AUTO_INCREMENT, 
    -> ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> temp float NOT NULL,
    -> PRIMARY KEY(id)) ENGINE=MyISAM
#Verification
mysql> show tables from [db_name];
mysql> show columns from [tbl_name];
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | NO   | PRI | NULL              | auto_increment              |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| temp  | float     | NO   |     | NULL              |                             |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set

• Transaction processing is not performed, so MyISAM. The above code is for storing only temperature data.

http://zetcode.com/databases/mysqltutorial/storageengines/

--There seems to be a way to access MySQL directly from the outside. - https://qiita.com/yoshiokaCB/items/df4ae185be7cbc4f03ac

Creating a program

PHP preferences

--Install PHP

#If it is not the latest version, this is OK (the latest is ver7).3, in case of apt ver7.2）
sudo apt install php

--The document root (HTML that apache sends to the browser) is / var / www / html / --Change permissions --PHP operation check is possible with phpinfo

<?php phpinfo(); ?>

Confirmation of access from PHP to DB (MySQL)

--PHP Data Objects (PDO) * PDO is used this time --Abstract database access from PHP

• mysqli --MySQL improved extension module

<?php
try{
//Create an object of PDO class
$pdo = new PDO('mysql:host=localhost;dbname=[db_name];charset=utf8', '[username]', '[password]', array(PDO::ATTR_EMULATE_PREPARES => false));
}catch(PDOException $e){
exit('Database connection failed.'.$e->getMessage());
}
//DB processing
switch ($_SERVER['REQUEST_METHOD']) {
    case 'GET':
        $st = $pdo->query("select * from [table name]");
        echo json_encode($st->fetchAll(PDO::FETCH_ASSOC));
        break;

    case 'POST':
        $data = json_decode(file_get_contents('php://input'), true);
        $st = $pdo->prepare("insert into [table name](ts,temp)values(:datetime,:temp)");
        $st->execute($data);
        header('Content-Type: application/json');
        echo json_encode("end");
        break;
}
?>

Data transmission process from Raspberry Pi to PHP (Python)

# coding: utf-8
import requests
import json
from datetime import datetime

def main():

    url = 'http://[External IP]/[program name].php'
    #Temperature acquisition process
    ...
    temp = #value

    data = {'datetime':datetime.now().strftime("%Y/%m/%d %H:%M:%S"),'temp':temp}
    #Encode data in JSON format
    print(json.dumps(data))
    #Send a POST request in JSON format(I want a json format response, so Content-type specification)
    response = requests.post(url, json.dumps(data), headers={'Content-type': 'application/json'})    #If returned correctly, response will be populated
    print(response.json())
    pass

if __name__ == '__main__':
    main()

I want to make good use of sensor data and link with the cloud ...