B BOOKS FOR PROFESSIONALS BY PROFESSIONALS® e THE EXPERT’S VOICE® IN BIG DATA l l M MySQL for the Internet of Things y S Q MySQL for the This book introduces the problems facing Internet of Things developers and explores current technologies and techniques to help you manage, mine, and make sense of the data being L collected through the use of the world’s most popular database on the Internet - MySQL. f o The IoT is poised to change how we interact with and perceive the world around us, and the r possibilities are nearly boundless. As more and more connected devices generate data, we will Internet of Things t need to solve the problem of how to collect, store, and make sense of IoT data by leveraging h the power of database systems. e I The book begins with an introduction of the MySQL database system and storage of sensor n data. Detailed instructions and examples are provided to show how to add database nodes to t IoT solutions including how to leverage MySQL high availability, including examples of how to e protect data from node outages using advanced features of MySQL. r Data management for sensors and n The book closes with a comparison of raw and transformed data showing how transformed e connected devices t data can improve understandability and help you cut through a clutter of superfluous data toward the goal of mining nuggets of useful knowledge. o — f In this book, you’ll learn to: T Charles Bell h • Understand the crisis of vast volumes of data from connected devices i • Transform data to improve reporting and reduce storage volume n • Store and aggregate your IoT data across multiple database servers g • Build localized, low-cost MySQL database servers using small and inexpensive computers s • Connect Arduino boards and other devices directly to MySQL database servers • Build high availability MySQL solutions among low-power computing devices Shelve in: IISSBBNN 99787-81--14-84428-4122-941-2394-3 Databases/MySQL User level: Intermediate–Advanced 9 781484 212943 SOURCE CODE ONLINE www.apress.com MySQL for the Internet of Things Charles Bell MySQL for the Internet of Things Copyright © 2016 by Charles Bell This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the Publisher’s location, in its current version, and permission for use must always be obtained from Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution under the respective Copyright Law. ISBN-13 (pbk): 978-1-4842-1294-3 ISBN-13 (electronic): 978-1-4842-1293-6 Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director: Welmoed Spahr Lead Editor: Jonathan Gennick Development Editor: Douglas Pundick Technical Reviewer: Peter Adams Editorial Board: Steve Anglin, Pramila Balen, Louise Corrigan, Jim DeWolf, Jonathan Gennick, Robert Hutchinson, Celestin Suresh John, Michelle Lowman, James Markham, Susan McDermott, Matthew Moodie, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Gwenan Spearing Coordinating Editor: Jill Balzano Copy Editor: Kim Wimpsett Compositor: SPi Global Indexer: SPi Global Artist: SPi Global Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springer.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail [email protected], or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/bulk-sales. Any source code or other supplementary materials referenced by the author in this text is available to readers at www.apress.com. For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/. I dedicate this book to my major professors Drs. James E. Ames IV and Lorraine M. Parker whose tutelage both academic and personal have instilled in me a love for the art and science of database systems. Dr. Charles Bell Contents at a Glance About the Author ���������������������������������������������������������������������������������������������������xiii About the Technical Reviewer ���������������������������������������������������������������������������������xv Acknowledgments �������������������������������������������������������������������������������������������������xvii Introduction ������������������������������������������������������������������������������������������������������������xix ■ Chapter 1: The Internet of Things and Data �����������������������������������������������������������1 ■ Chapter 2: Hardware for IOT Solutions ����������������������������������������������������������������29 ■ Chapter 3: How IOT Data Is Stored ����������������������������������������������������������������������79 ■ Chapter 4: Data Transformation ������������������������������������������������������������������������101 ■ Chapter 5: MySQL Primer ����������������������������������������������������������������������������������141 ■ Chapter 6: Building Low-Cost MySQL Data Nodes ���������������������������������������������195 ■ Chapter 7: High Availability IOT Solutions ���������������������������������������������������������251 ■ Chapter 8: Demonstration of High Availability Techniques ��������������������������������273 Index ���������������������������������������������������������������������������������������������������������������������311 v Contents About the Author ���������������������������������������������������������������������������������������������������xiii About the Technical Reviewer ���������������������������������������������������������������������������������xv Acknowledgments �������������������������������������������������������������������������������������������������xvii Introduction ������������������������������������������������������������������������������������������������������������xix ■ Chapter 1: The Internet of Things and Data �����������������������������������������������������������1 IOT Solutions ��������������������������������������������������������������������������������������������������������������������2 IOT Is More Than Just Connected to the Internet������������������������������������������������������������������������������������3 IOT Services ��������������������������������������������������������������������������������������������������������������������������������������������6 Example IOT Solutions ����������������������������������������������������������������������������������������������������������������������������7 What Is IOT Data? �����������������������������������������������������������������������������������������������������������11 IOT Predictions: Data Overload? �������������������������������������������������������������������������������������16 Addressing IOT Devices ������������������������������������������������������������������������������������������������������������������������16 IOT and Big Data �����������������������������������������������������������������������������������������������������������������������������������19 IOT Security ��������������������������������������������������������������������������������������������������������������������20 Common Security Threats ��������������������������������������������������������������������������������������������������������������������23 Securing IOT Solutions �������������������������������������������������������������������������������������������������������������������������25 Summary ������������������������������������������������������������������������������������������������������������������������28 ■ Chapter 2: Hardware for IOT Solutions ����������������������������������������������������������������29 Microcontrollers �������������������������������������������������������������������������������������������������������������29 What Is an Arduino? ������������������������������������������������������������������������������������������������������������������������������30 Arduino Models �������������������������������������������������������������������������������������������������������������������������������������31 Arduino Clones �������������������������������������������������������������������������������������������������������������������������������������36 Arduino Tutorial �������������������������������������������������������������������������������������������������������������������������������������45 Additional Arduino Hardware ����������������������������������������������������������������������������������������������������������������52 vii ■ Contents Low-Powered Computing Platforms ������������������������������������������������������������������������������62 Arduino Hybrids ������������������������������������������������������������������������������������������������������������������������������������63 Computer Boards ����������������������������������������������������������������������������������������������������������������������������������66 Sensors ��������������������������������������������������������������������������������������������������������������������������72 Analog Sensors �������������������������������������������������������������������������������������������������������������������������������������73 Digital Sensors �������������������������������������������������������������������������������������������������������������������������������������73 Storing Sensor Data ������������������������������������������������������������������������������������������������������������������������������74 Examples of Sensors ����������������������������������������������������������������������������������������������������������������������������74 Computer Systems ���������������������������������������������������������������������������������������������������������77 Summary ������������������������������������������������������������������������������������������������������������������������78 ■ Chapter 3: How IOT Data Is Stored ����������������������������������������������������������������������79 Distributed IOT ����������������������������������������������������������������������������������������������������������������80 Data Collectors �������������������������������������������������������������������������������������������������������������������������������������81 Data Collectors with Storage ����������������������������������������������������������������������������������������������������������������81 Actionable Device ���������������������������������������������������������������������������������������������������������������������������������81 Data Aggregators ����������������������������������������������������������������������������������������������������������������������������������81 Database Server �����������������������������������������������������������������������������������������������������������������������������������82 Local On-Device Storage ������������������������������������������������������������������������������������������������82 Local Storage on the Raspberry Pi �������������������������������������������������������������������������������������������������������83 Local Storage on the Arduino ���������������������������������������������������������������������������������������������������������������85 Passing the Buck to Aggregators �����������������������������������������������������������������������������������90 Database Storage �����������������������������������������������������������������������������������������������������������92 Benefits ������������������������������������������������������������������������������������������������������������������������������������������������93 Techniques��������������������������������������������������������������������������������������������������������������������������������������������94 Considerations ��������������������������������������������������������������������������������������������������������������������������������������95 Distributed IOT Network Best Practices �������������������������������������������������������������������������96 Node Placement �����������������������������������������������������������������������������������������������������������������������������������96 Data Storage �����������������������������������������������������������������������������������������������������������������������������������������97 Presentation �����������������������������������������������������������������������������������������������������������������������������������������99 Summary ������������������������������������������������������������������������������������������������������������������������99 viii ■ Contents ■ Chapter 4: Data Transformation ������������������������������������������������������������������������101 Making Sense of IOT Data ��������������������������������������������������������������������������������������������102 What Is Being Observed?��������������������������������������������������������������������������������������������������������������������102 Is There Another Way to Make the Observation? ��������������������������������������������������������������������������������102 How Often Do You Need to Record the Observation? �������������������������������������������������������������������������103 What Type of Data Does the Sensor Produce? ������������������������������������������������������������������������������������104 Are There Interpretations Needed for the Observation Data? �������������������������������������������������������������104 What Level of Accuracy Do You Need? �����������������������������������������������������������������������������������������������105 What Is the Lifetime of the Data? �������������������������������������������������������������������������������������������������������105 Annotation ��������������������������������������������������������������������������������������������������������������������106 Recording the Sensor Name or Adding Notes ������������������������������������������������������������������������������������107 Recording the Date and Time �������������������������������������������������������������������������������������������������������������110 Data Type Transformations �����������������������������������������������������������������������������������������������������������������116 Adding Derived or Calculated Data �����������������������������������������������������������������������������������������������������123 Data Interpretations ����������������������������������������������������������������������������������������������������������������������������130 Aggregation ������������������������������������������������������������������������������������������������������������������134 Data from Multiple Sensors ����������������������������������������������������������������������������������������������������������������134 Data from Multiple Nodes �������������������������������������������������������������������������������������������������������������������137 Aggregate Calculations �����������������������������������������������������������������������������������������������������������������������139 Summary ����������������������������������������������������������������������������������������������������������������������140 ■ Chapter 5: MySQL Primer ����������������������������������������������������������������������������������141 Getting Started �������������������������������������������������������������������������������������������������������������141 How Do I Use MySQL? ������������������������������������������������������������������������������������������������������������������������142 How to Get and Install MySQL ������������������������������������������������������������������������������������������������������������144 How Data Is Stored and Retrieved��������������������������������������������������������������������������������157 How and Where MySQL Stores Data ���������������������������������������������������������������������������������������������������158 Common MySQL Commands and Concepts �����������������������������������������������������������������164 MySQL Commands �����������������������������������������������������������������������������������������������������������������������������164 MySQL Concepts ���������������������������������������������������������������������������������������������������������������������������������171 ix ■ Contents Planning Database Storage for IOT Data ����������������������������������������������������������������������176 Example 1: Plant-Monitoring System �������������������������������������������������������������������������������������������������176 Recommendations and Best Practices �����������������������������������������������������������������������������������������������191 Summary ����������������������������������������������������������������������������������������������������������������������193 ■ Chapter 6: Building Low-Cost MySQL Data Nodes ���������������������������������������������195 Introducing the Raspberry Pi ����������������������������������������������������������������������������������������195 Noble Origins ��������������������������������������������������������������������������������������������������������������������������������������197 Models ������������������������������������������������������������������������������������������������������������������������������������������������198 A Tour of the Board �����������������������������������������������������������������������������������������������������������������������������200 Required Accessories �������������������������������������������������������������������������������������������������������������������������201 Recommended Accessories ���������������������������������������������������������������������������������������������������������������202 Where to Buy ��������������������������������������������������������������������������������������������������������������������������������������203 Raspberry Pi Tutorial ����������������������������������������������������������������������������������������������������206 Choosing a Boot Image (Operating System) ���������������������������������������������������������������������������������������206 Booting Up ������������������������������������������������������������������������������������������������������������������������������������������212 MySQL Installation and Setup ��������������������������������������������������������������������������������������215 Partitioning and Formatting the Drive ������������������������������������������������������������������������������������������������216 Setting Up Automatic Drive Mounting �������������������������������������������������������������������������������������������������218 Installing MySQL Server ���������������������������������������������������������������������������������������������������������������������220 Other Platforms ������������������������������������������������������������������������������������������������������������225 BeagleBone Black �������������������������������������������������������������������������������������������������������������������������������225 pcDuino�����������������������������������������������������������������������������������������������������������������������������������������������226 Intel Galileo �����������������������������������������������������������������������������������������������������������������������������������������227 MySQL Clients: How to Connect and Save Data �����������������������������������������������������������230 Introducing Connector/Arduino ������������������������������������������������������������������������������������230 Introducing Connector/Python ������������������������������������������������������������������������������������������������������������242 Summary ����������������������������������������������������������������������������������������������������������������������249 x ■ Contents ■ Chapter 7: High Availability IOT Solutions ���������������������������������������������������������251 What Is High Availability? ���������������������������������������������������������������������������������������������251 High Availability Options for IOT Solutions with MySQL �����������������������������������������������253 Recovery ��������������������������������������������������������������������������������������������������������������������������������������������253 Redundancy ���������������������������������������������������������������������������������������������������������������������������������������254 Scaling �����������������������������������������������������������������������������������������������������������������������������������������������255 Fault Tolerance �����������������������������������������������������������������������������������������������������������������������������������255 High Availability Techniques �����������������������������������������������������������������������������������������256 Backup and Recovery �������������������������������������������������������������������������������������������������������������������������256 MySQL Replication Primer ������������������������������������������������������������������������������������������������������������������264 Fault Tolerance in IOT Nodes ��������������������������������������������������������������������������������������������������������������270 Summary ����������������������������������������������������������������������������������������������������������������������271 ■ Chapter 8: Demonstration of High Availability Techniques ��������������������������������273 MySQL Replication Techniques ������������������������������������������������������������������������������������273 Transaction Processing�����������������������������������������������������������������������������������������������������������������������274 Advanced Replication with Global Transaction Identifiers ������������������������������������������������������������������276 Replication and Database Maintenance Tips ��������������������������������������������������������������������������������������282 Example: Scaling Applications ������������������������������������������������������������������������������������������������������������285 High Availability IOT Nodes �������������������������������������������������������������������������������������������291 Example: Redundant Data Collectors��������������������������������������������������������������������������������������������������291 Example: Fault-Tolerant Data Collector ����������������������������������������������������������������������������������������������300 Summary ����������������������������������������������������������������������������������������������������������������������310 Index ���������������������������������������������������������������������������������������������������������������������311 xi