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.

Development environment

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)

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

--Perform SSH authentication (for accessing from Windows)

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]

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

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


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

Recommended Posts

I sent the data of Raspberry Pi to GCP (free)
I tried to automate the watering of the planter with Raspberry Pi
I talked to Raspberry Pi
I want to be notified of the connection environment when the Raspberry Pi connects to the network
I tried to move ROS (Melodic) with the first Raspberry Pi (Stretch) at the beginning of 2021
I tried to estimate the pi stochastically
Use Raspberry Pi to solve the problem of insufficient mobile Wi-Fi connection
[Note] I want to completely preprocess the data of the Titanic issue-Age version-
[I touched the Raspberry Pi (1)] I summarized the basic operations of Minecraft Pi Edition (2015.5.23 pre-release)
I connected the thermo sensor to the Raspberry Pi and measured the temperature (Python)
I tried to save the data with discord
I tried to find 100 million digits of pi
I tried to touch the API of ebay
I tried to correct the keystone of the image
Try the free version of Progate [Python I]
I want to get League of Legends data ②
I want to customize the appearance of zabbix
I want to get League of Legends data ①
I made an npm package to get the ID of the IC card with Raspberry Pi and PaSoRi
Read the data of the NFC reader connected to Raspberry Pi 3 with Python and send it to openFrameworks with OSC
I tried to predict the price of ETF
I tried to vectorize the lyrics of Hinatazaka46!
Connect to the console of Raspberry PI and display local IP and SD information
I tweeted the illuminance of the room with Raspberry Pi, Arduino and optical sensor
I played RPG Maker 2000 Ruina ~ The Story of the Abandoned City ~ on Raspberry Pi
I tried to rescue the data of the laptop by booting it on Ubuntu
How to make a Raspberry Pi that speaks the tweets of the specified user
I want to grep the execution result of strace
I tried to summarize the basic form of GPLVM
Try to visualize the room with Raspberry Pi, part 1
I tried to predict the J-League match (data analysis)
How to use the Raspberry Pi relay module Python
Take the value of SwitchBot thermo-hygrometer with Raspberry Pi
Log the value of SwitchBot thermo-hygrometer with Raspberry Pi
I want to fully understand the basics of Bokeh
I tried using the API of the salmon data project
I tried to visualize the spacha information of VTuber
I tried to erase the negative part of Meros
I tried to classify the voices of voice actors
I want to increase the security of ssh connections
I tried to summarize the string operations of Python
[First data science ⑥] I tried to visualize the market price of restaurants in Tokyo
I tried to visualize the running data of the racing game (Assetto Corsa) with Plotly
I just wanted to extract the data of the desired date and time with Django
Introduced python3-OpenCV3 to Raspberry Pi
I tried to find the entropy of the image with python
[Horse Racing] I tried to quantify the strength of racehorses
I tried to get the location information of Odakyu Bus
I tried to display the point cloud data DB of Shizuoka prefecture with Vue + Leaflet
I want to save the photos sent by LINE to S3
I tried to find the average of the sequence with TensorFlow
Make a note of what you want to do in the future with Raspberry Pi
I tried using the Pi Console I / F of the Raspberry Pi IoT starter kit "anyPi" from Mechatrax.
I want to get the operation information of yahoo route
Play to notify Slack of environmental data using AWS PaaS from SensorTag via Raspberry Pi3
I made a function to check the model of DCGAN
I tried to analyze the data of the soccer FIFA World Cup Russia tournament with soccer action
I want to disable interrupts on Raspberry Pi (≒ DI / EI)
How to run the Export function of GCP Datastore automatically
I tried using the DS18B20 temperature sensor with Raspberry Pi
[Python] I tried to visualize the follow relationship of Twitter