EmergencyEMERGENCY? Get 24/7 Help Now!

Internet of Things, Messaging and MySQL

 | October 23, 2015 |  Posted In: MySQL

PREVIOUS POST
NEXT POST

Personal Projects with the Internet of Things

So you want to do a personal project with the Internet of Things (maybe a home automation or metrics collection or something else)? In this blog post I will tell about my recent experience with this. I will give a talk on this topic at Oracle OpenWord 2015 (Tuesday, Oct 27, 6:15 p.m., Moscone South, 274).

Here is what I did: I was looking for the best place to plant some trees in my backyard. I learned that I need to know how much sun I have in the backyard before planting (some trees need full sun, while others need partial sun or shade). So I needed to measure the sunlight in different spots.

The “old” way of doing it was to use Arduino or Raspberry Pi. (Of course this can be done by just manually checking the spot every hour and writing the measurements on a piece of paper, but this was out of the question.) Both solutions require additional components (i.e. Wi-Fi module for Arduino or connecting Raspberry Pi to Arduino).

As the Internet of Things is getting more and more popular for personal projects, there are a number of new low-priced and easy-to-use hardware solutions. I decided to use a Particle Photon board.

photon-new-3e010e9f
Particle Photon

Major advantages:

  • Wi-Fi chip, which enables you to send readings to the Internet right from the board
  • A self-sufficient board, which can be programmed/flushed over the air (Web IDE)
  • $19 pricetag
  • Sensors for Arduino should work for Photon

100% Cloud

This is what my architecture looks like:

  1. Particle Photon + light sensor (photo resistor) + temperature sensor (DHT11) will take measurements every minute.
  2. Particle Photon is connected to my home Wifi router and sends the information over the Internet to an MQTT server. Particle hosts the MQTT server and a library for that, a RESTFull API for that server, as well as a web-based dashboard. You can also use any other MQTT server, assuming it has a library for Photon.
  3. I also wanted to record all measurements, so I needed to store them. I started an EC2 micro instance with MySQL and NodeJS and used Particle JavaScript API to “subscribe” to the events and save it to MySQL.

The whole system is 100% in the cloud; it is sending all events to the Particle’s server and I’m receiving those events from yet another server in AWS. Thus this system only needs: a) Particle Photon and b) a Wi-Fi router/access point. I can then pass the assembled Photon board to my friends and they can just connect it to another Wi-Fi router (this can be done with the phone app).

Fully Assembled Project

The system runs on a standard USB battery (the black “thing” below the board). I placed a quarter on the board to demonstrate the size.

I followed the instructions from the open home automation blog post to attach a DHT11 sensor for temperature and humidity measurements.

Particle Build provides the Web IDE; from there I can add those lines to publish my events:

... Spark.publish("temperature", String(temperature)); Spark.publish("humidity", String(humidity)); Spark.publish("light", String(light)); ...

Now I will need to subscribe to the events and load the readings to MySQL. I used the Particle JavaScript API to do that; here is my simple code (I did not insert a date but used MySQL’s timestamp. You can use the event’s date instead, which will probably be better).

That is it.  Here is the listing:

Conclusion

As the Internet of Things gets more and more popular, it is also becoming extremely easy to do a home project with it. The Particle Photon is a great option. Other options include:

  1. Intel Edison, $60, which is similar to Raspberry Pi and has more functions
  2. WiFi Module – ESP8266, $6. This is a much simpler module and will require an additional board to “flush” the code
PREVIOUS POST
NEXT POST
Alexander Rubin

Alexander joined Percona in 2013. Alexander worked with MySQL since 2000 as DBA and Application Developer. Before joining Percona he was doing MySQL consulting as a principal consultant for over 7 years (started with MySQL AB in 2006, then Sun Microsystems and then Oracle). He helped many customers design large, scalable and highly available MySQL systems and optimize MySQL performance. Alexander also helped customers design Big Data stores with Apache Hadoop and related technologies.

4 Comments

  • Take a look at the Sparkfun “Thing” too. https://www.sparkfun.com/products/13231 I have one monitoring the temperatures of my freezers. Very capable little board, programmable with the arduino environment, a touch cheaper than the photon, and has a built in charge controller and connector that would take the place of your usb power pack.

  • Jeff, thank you for the update. Yes, “Thing” looks very interesting and it is based on the same ESP8266. The only thing it is missing (if I’m not mistaken) is the over-the-air code flush.

Leave a Reply