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 ...

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