iot mysql_IoT Messages MySQL

展开查看详情

1. About Me

Alexander Rubin, Principal Architect, Percona

• Working with MySQL for over 10 years

– Started at MySQL AB, Sun Microsystems, Oracle

(MySQL ConsulDng)

– Joined Percona in 2013

2.$ vim toc.txt

1. Gardening for Geeks + IoT

2. Smallest MySQL Server in the World

3. MySQL can make toast

~

~

~

~

~

-- INSERT --

3.Apricot Tree…

4. Level of Light

Every plant comes with one – a little

plastic label tucked into the pot with

an icon on it that lets you know what

level of light it needs.

5.Tons of devices

to use for IoT…

6.WiFi Module (board)

- ESP8266 $2-$6

https://

www.sparkfun.com/

products/13678

7. WiFi + BLE Module

(board) ESP32 - ~$8

https://

espressif.com/en/

products/hardware/

esp32/overview

hMp://esp32.net/

8.Particle Photon $19

hMps://store.parDcle.io

9.Particle Demo…

10.Demo Recap… Cloud

100% Cloud IoT

11.Wi-Fi

MQTT Node/JS +

ParDcle API

console.parDcle.io

12.Measurement Results…

13.• Easy to use + • WiFi is power

quick start hungry

• Send metrics • Rely on the

directly internet

14.Non-cloud setup with ESP32

$ vim goals.txt

1. One year on a single battery

2. No internet dependency

3. Less than $100 total

~

~

-- INSERT --

15.Bluetooth Low Energy (BLE)

hMps://github.com/espressif/arduino-esp32/tree/master/libraries/SimpleBLE

16. Storing data in MySQL: wide table

CREATE TABLE `sensor_wide` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`light` int(11) DEFAULT NULL,

`temp` double DEFAULT NULL,

`humidity` double DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

+ Storage is good

- Alter table is a hard, not flexible

17. Storing data in MySQL: key/value

CREATE TABLE `cloud_data` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

`data` text DEFAULT NULL,

`updated_at` timestamp NOT NULL DEFAULT

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11106 DEFAULT

CHARSET=latin1

+ Flexible

- High overhead on storage

18. Storing data in MySQL 5.7: JSON store

CREATE TABLE `event_stream_json` (

`doc` json DEFAULT NULL,

`_id` varchar(32) GENERATED ALWAYS AS

(json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL,

`name` varchar(255) GENERATED ALWAYS AS

(json_unquote(json_extract(`doc`,'$.name'))) VIRTUAL,

UNIQUE KEY `_id` (`_id`),

KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

+ Very Flexible + indexes

- Some overhead on storage

19.Smallest

MySQL Server

in the World

20.Intel Edison ~$60

hMp://www.intel.com/

content/www/us/en/

do-it-yourself/

edison.html

21.MySQL Bug #2

Does not make toast

http://

bugs.mysql.com/2/

22.

23.http://bugs.mysql.com/bug.php?id=2

[12 Sep 2002 5:26] Mark Matthews

Description:

Other JDBC drivers

I have used make toast for breakfast.

MySQL Connector/J doesn't make toast,

it can only pour a bowl of froot loops.

When I ask it to make toast,

the "NoToast" exception is thrown.

I think you should look into this.

24.Fixing MySQL Bug#2

25.

26.

27.Bug fixed, please enjoy your MySQL toast...

28. Alexander Rubin

h"ps://www.linkedin.com/in/alexanderrubin