MySQL for the Internet of Things

ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs

Overview of the Design

The goal is to make the node fault tolerant of the database connection. More specifically, should the
database become unreachable, we want to store the data locally (caching the data) to the SD card and then,
once the database is reachable, save that cached data to the database.
The database is a simple affair with a table designed to capture only the soil moisture sensor value and
the data and time the value was read (stored). The following shows the SQL statements needed to create the
sample database for this project:

CREATE DATABASE plant_moisture;
CREATE TABLE plant_moisture.plants (
id int(11) NOT NULL AUTO_INCREMENT,
moisture_raw int(11) DEFAULT NULL,
date_saved timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=257 DEFAULT CHARSET=latin1;

Notice we create a simple table with an auto-increment column, a column for the soil moisture, and a
date-stored column. I forgo the more complicated annotation for the soil moisture for simplicity, but feel
free to add that column and trigger as an exercise.

Assemble the Hardware

The hardware for this project will be an Arduino, a soil moisture sensor, an Ethernet shield, and a real-time
clock module. Figure 8-7 shows how to wire up everything.

Figure 8-7. Arduino Mega with Ethernet shield, RTC module, and moisture sensor