IoT Messages MySQL

2017年6月10日,Alexander Rubin在Linuxfest东南部展示了“时间序列数据、物联网(现场演示)和数据库”。


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:// products/13678

7. WiFi + BLE Module (board) ESP32 - ~$8 https:// products/hardware/ esp32/overview hMp://

8.Particle Photon $19 hMps://

9.Particle Demo…

10.Demo Recap… Cloud 100% Cloud IoT

11.Wi-Fi MQTT Node/JS + ParDcle API

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

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:// content/www/us/en/ do-it-yourself/ edison.html

21.MySQL Bug #2 Does not make toast http://


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



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

28. Alexander Rubin h"ps://