How and Where MySQL Stores Data

The MySQL database system stores data via an interesting mechanism of programmatic isolation; it is called a storage engine, which is governed by the handler interface. The handler interface permits the use of interchangeable storage components in the MySQL server so that the parser, the optimizer, and all manner of components can interact in storing data on disk using a common mechanism. This is also referred to as a pluggable storage engine.⁵

What does this mean to you? It means you have the choice of different mechanisms for storing data. You can specify the storage engine in the table CREATE statement shown in the following code sample. Notice the last line in the command; this is how a storage engine is specified. Leaving off this clause results in MySQL using the default storage engine.

Create Table:

CREATE TABLE `books` (
  `ISBN` varchar(15) DEFAULT NULL,
  `Title` varchar(125) DEFAULT NULL,
  `Authors` varchar(100) DEFAULT NULL,
  `Quantity` int(11) DEFAULT NULL,
  `Slot` int(11) DEFAULT NULL,
  `Thumbnail` varchar(100) DEFAULT NULL,
  `Description` text
) ENGINE=                                          MyISAM                                                                                    ;                  

Great! Now, what storage engines exist on MySQL? You can discover which storage engines are supported by issuing the following command. As you see, there are a lot to choose from. I cover a few that may be pertinent to planning sensor networks :

mysql> SHOW STORAGE ENGINES G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
8 rows in set (0.00 sec)                                                                       

As of version 5.6, MySQL uses the InnoDB storage engine by default. Previous versions used MyISAM as the default. InnoDB is a fully transactional, ACID⁶ storage engine. A transactionis a batch of statements that must all succeed before any changes are written to disk. The classic example is a bank transfer. If you consider a system that requires deducting an amount from one account and then crediting that amount to another account to complete the act of moving funds, you would not want the first to succeed and the second to fail or vice versa!

Wrapping the statements in a transaction ensures that no data is written to disk until and unless all statements are completed without errors. Transactions in this case are designated with a BEGIN statement and concluded with either a COMMIT to save the changes or a ROLLBACK to undo the changes. InnoDB stores its data in a single file (with some additional files for managing indexes and transactions).

The MyISAM storage engine is optimized for reads. MyISAM has been the default for some time and was one of the first storage engines available. In fact, a large portion of the server is dedicated to supporting MyISAM. It differs from InnoDB in that it does not support transactions and stores its data in an indexed sequential access method format. This means it supports fast indexing. You would choose MyISAM over InnoDB if you did not need transactions and you wanted to be able to move or back up individual tables.

Another storage engine that you may want to consider, especially for sensor networks, is Archive. This engine does not support deletes (but you can drop entire tables) and is optimized for minimal storage on disk. Clearly, if you are running MySQL on a small system like a Raspberry Pi, small is almost always better! The inability to delete data may limit more advanced applications, but most sensor networks merely store data and rarely delete it. In this case, you can consider using the Archive storage engine.

There is also the CSV storage engine (where CSV stands for comma-separated values). This storage engine creates text files to store the data in plain text that can be read by other applications, such as a spreadsheet application. If you use your sensor data for statistical analysis, the CSV storage engine may make the process of ingesting the data easier.

So where is all this data? If you query the MySQL server and issue the SHOW VARIABLES LIKE "datadir"; command, you see the path to the location on disk that all storage engines use to store data. In the case of InnoDB, this is a single file on disk located in the data directory. InnoDB also creates a few administrative files, but the data is stored in the single file. For most other storage engines except NDB and MEMORY, the data for the tables is stored in a folder with the name of the database under the data directory. Listing 13-2 shows how to determine the location of the data directory (it is typically in a protected folder).

Listing 13-2. Finding Where Your Data Is Located

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

If you navigate to the location (path) shown and (with elevated privileges) issue a directory listing command, you can see the InnoDB files identified by the ib and ibd prefixes. You may also see a number of directories, all of which are the databases on this server.

Although it is unlikely that you would require a transactional storage engine for your IoT solutions, MySQL 5.6 has one, and it’s turned on by default. A more likely scenario is that you would use the MyISAM or Archive engine for your tables.

For more information about storage engines and the choices and features of each, please see the online MySQL Reference Manual section “Storage Engines” ( http://dev.mysql.com/doc/ ).

The MySQL Configuration File

The MySQL server can be configured using a configuration file, similar to the way you configure other Windows applications. On Windows, the MySQL configuration file is named my.ini and located in the program data folder under the MySQL server version (e.g., C:ProgramDataMySQLMySQL Server 5.7). This file contains several sections, one of which is labeled [mysqld]. The items in this list are key-value pairs: the name on the left of the equal sign is the option, and its value on the right. The following is a typical configuration file (with many lines suppressed for brevity):

[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 5.7Data
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="CHUCKSURFACE.log"
slow-query-log=1
slow_query_log_file="CHUCKSURFACE-slow.log"
long_query_time=10
log-error="CHUCKSURFACE.err"
server-id=1

As you can see, this is a simple way to configure a system. This example sets the TCP port, base directory (the root of the MySQL installation, including the data as well as binary and auxiliary files), data directory, and server ID (used for replication, as discussed shortly) and turns on the general log (when the Boolean switch is included, it turns on the log). There are many such variables you can set for MySQL. See the online MySQL reference manual for details concerning using the configuration file.

How to Get and Install MySQL

The MySQL server is available for a variety of platforms, including most Linux and Unix platforms, Mac OS X, and Windows. To download MySQL server, visit http://dev.mysql.com/downloads/ and click Community and then MySQL Community Server. This is the GPLv2 license of MySQL. The page automatically detects your operating system. If you want to download for another platform, you can select it from the drop-down menu.

Oracle has provided a special installation packaging for Windows named the Windows Installer. This package includes all the MySQL products available under the community license, including MySQL Server, Workbench, Utilities, Fabric, and all the available connectors (program libraries for connecting to MySQL). This makes installing on Windows a one-stop, one-installation affair. How cool is that? Figure 13-1 shows the download page for the Windows installer.

Figure 13-1. Download page for Windows installer

To install MySQL, begin by choosing either the Windows installer 32- or 64-bit installation package that matches your Windows version. Once the file is downloaded, click the file to begin installation. Note that some browsers, such as the new Edge browser, may ask you if you want to launch the installation. You may need to reply to a dialog permitting the installation.

The installation is fully automated with a series of dialogs to help you configure your installation. Like most Windows installation packages, you can choose what you want to install as well as where to install it. I recommend accepting the defaults since these are optimized for typical use cases. You can even choose to automatically start MySQL. You may also be given a temporary password for the root user account. Be sure to write that down and change it later (with the SET PASSWORD command; see the following) .

How to Start, Stop, and Restart MySQL

While working with your databases and configuring MySQL on your Windows system, you may need to control the startup and shutdown of the MySQL server. The default mode for installing MySQL is to automatically start on boot and stop on shutdown, but you may want to change that, or you may need to stop and start the server after changing a parameter. In addition, when you change the configuration file, you need to restart the server to see the effect of your changes.

You can start, pause (stop), and restart the MySQL server with the Services application in Windows. Simply type Services in the search box and choose the application. You can then scroll down, choose MySQL from the list and start, stop, or restart the server using the links provided. Figure 13-2 shows an example of the application with the commands highlighted.

Figure 13-2. Services application

Creating Users and Granting Access

You need to know about two additional administrative operations before working with MySQL: creating user accounts and granting access to databases. MySQL can perform both of these with the GRANT statement, which automatically creates a user if one does not exist. But the more pedantic method is first to issue a CREATE USER command followed by one or more GRANT commands . For example, the following shows the creation of a user named sensor1 and grants the user access to the database room_temp:

CREATE USER 'sensor1'@'%' IDENTIFIED BY 'secret';
GRANT SELECT, INSERT, UPDATE ON room_temp.* TO 'sensor1'@'%';

The first command creates the user named sensor1, but the name also has an @ followed by another string. This second string is the host name of the machine with which the user is associated. That is, each user in MySQL has both a user name and a host name, in the form user@host, to uniquely identify them. That means the user and host [email protected] and the user and host [email protected] are not the same. However, the % symbol can be used as a wildcard to associate the user with any host. The IDENTIFIED BY clause sets the password for the user.

SET PASSWORD FOR sensor1@"%" = PASSWORD("secret");

The second command allows access to databases. There are many privileges that you can give a user. The example shows the most likely set that you would want to give a user of a sensor network database: read (SELECT), add data (INSERT), and change data (UPDATE). See the online reference manual for more about security and account access privileges.

The command also specifies a database and objects to which to grant the privilege. Thus, it is possible to give a user read (SELECT) privileges to some tables and write (INSERT, UPDATE) privileges to other tables. This example gives the user access to all objects (tables, views, and so on) in the room_temp database.

As mentioned, you can combine these two commands into a single command. You are likely to see this form more often in the literature. The following shows the combined syntax. In this case, all you need to do is add the IDENTIFIED BY clause to the GRANT statement. Cool!

GRANT SELECT, INSERT, UPDATE ON room_temp.* TO  "sensor1 "@'%" IDENTIFIED BY "secret ";

Now that you know more about MySQL, let’s talk about the project and how you will develop the database portion.

New Project

You will use a C# project template for this project—the Blank App (Universal Windows) template. Use the name WeatherDatabase for the project name. You can save the project wherever you like or use the default location. Once the project opens, double-click the MainPage.xaml.cs file. There are a number of namespaces you need to include. Go ahead and add those now, as shown next.

using System.Threading.Tasks;        // Add for Task.Run()
using MySql.Data.MySqlClient;        // Add for MySQL connection
using System.Diagnostics;            // Add for debugging
using Glovebox.IoT.Devices.Sensors;  // Add for BMP280 (or BME280)

Here you added namespaces for threading so you can run a task (for the BMP280 library), the MySQL database client, the diagnostics for writing debug statements to the log, and finally, the sensor library from Glovebox. You’ll see how to add the MySQL and Glovebox references in the next sections.

Next, you need to add some variables. You add a variable for the DispatcherTimer class, a variable for the MySQLConnection class, and a variable for the BMP280 class. The following shows the correct code for defining these variables. These are placed in the MainPage class.

private DispatcherTimer bmpTimer;    // Timer
private MySqlConnection mysql_conn;  // Connection to MySQL  
private BMP280 tempAndPressure;      // Instance of BMP280 class

Next, you need to add some constants for the database. First, you add a connection string. This string contains several parts that you need to modify to match your systems. I highlight each in bold italics. These are the IP or hostname of the server, the user account and password that you want to use to connect to the MySQL server (you can use what is shown if you issued the preceding commands to create the user), the port (3306 is the default), the default database (can be omitted), and the SSL mode (which must be set to None because the RT version of the connector does not support SSL). Next, you add a formatted string so that you can issue the INSERT statement using parameters for the values.

// String constants for database
private string connStr = "server=10.0.1.18;user=w_user;password=secret;" +
                         "port=3306;database=weather;sslMode=None";
private string INSERT = "INSERT INTO weather.history VALUES (null, null, " +
                                "{0}, {1}, {2}, {3}, {4})";

You may notice that the first two columns for the INSERT statement are null. You do this to tell the database server to use the default values for the specific columns. In this case, the auto increment column named Id and the timestamp column named dateRecorded. I should note that you could have used a different form of the INSERT statement by specifying the columns by name but passing null is a bit easier if not a bit lazy.

The code in the MainPage() function initializes the components, the BMP280 class and call a new method imaginative named Connect() to connect to the database server. You make this a new method to keep things easier to write (and the code easier to understand). You’ll see this new method shortly. You also set up the timer. In this case, you use a value of 5000, which is 5 seconds. You may want to consider making value greater if you plan to use the project for practical use cases. Listing 13-4 shows the complete MainPage() method.

Listing 13-4. MainPage Method

public MainPage()
{
    this.InitializeComponent();

    // Instantiate a new BMP280 class instance
    tempAndPressure = new BMP280();

    // Connect to MySQL. If successful, setup timer
    if (this.Connect())
    {
        this.bmpTimer = new DispatcherTimer();
        this.bmpTimer.Interval = TimeSpan.FromMilliseconds(5000);
        this.bmpTimer.Tick += BmpTimer_Tick;
        this.bmpTimer.Start();
    }
    else
    {
        Debug.WriteLine("ERROR: Cannot proceed without database connection.");
    }
}

Notice that if the Connect() method returns false, you issue a debug statement stating you cannot connect to the MySQL server. Thus, you should run this project with debug to ensure that you are connecting to the server properly (see the “Why Can’t I Connect?” sidebar).

Now you need three references added to the solution. You need the Glovebox.IoT.Devices library from NuGet, which in turn requires the Units.NET library from NuGet. You add the reference to the Connector/Net library. Finally, you need the Windows 10 IoT Extensions library. Let’s discuss each of these in turn.

Glovebox.IoT.Devices

Glovebox.IoT.Devices is a library that supports a host of sensors—each presented as a separate class to make it easy to use for specific sensors, like the BMP280. It also takes care of the I2C communication for you. All you need to do is instantiate the class and call the methods to read the data.

To add the Glovebox.IoT.Devices library, use the NuGet Package Manager from the Tools ➤ NuGet Package Manager ➤ Manage NuGet Packages for Solution... menu. Click the Browse tab and then type glovebox in the search box, as shown in Figure 13-4.

Figure 13-4. NuGet Package Manager, results for Glovebox

Select the entry named Glovebox.IoT.Devices in the list, tick the project name (solution) in the list on the right, and finally, click Install. The installation starts and Visual Studio downloads a number of packages and then asks you permission to install them. Go ahead and let the installation complete. A dialog box will open to tell you that the installation is complete.

Unlike the lightning library you used in Chapter 11, there are no additional steps needed to use the library, however, the library requires another library—the Units.NET library, also available via NuGet.

Units.NET

Glovebox.IoT.Devices requires another library that provides helper methods for converting units of measure. The notes state you must have the Units.NET library installed.

To add the Units.NET library, use the NuGet Package Manager from the Tools ➤ NuGet Package Manager ➤ Manage NuGet Packages for Solution... menu. Click the Browse tab and then type units in the search box, as shown in Figure 13-5.

Figure 13-5. NuGet Package Manager, results for Units

Select the entry named Units.NET in the list, tick the project name (solution) in the list on the right, and finally, click Install. The installation starts and Visual Studio downloads a number of packages and then ask you permission to install them. Go ahead and let the installation complete. A dialog box opens to tell you when the installation is complete.

Now, let’s install the database connector library.

Connector/Net

Connector/Net is a Microsoft .NET database library for connecting to a MySQL database server. You can download the connector from Oracle at http://dev.mysql.com/downloads/connector/net/ .

You need to download this library to use with the project. You can download the Windows (x86 or 64-bit) installer separately but if you installed MySQL with the MySQL Windows Installer, it is already installed on your system. However, you need a different version of the library to run on the Raspberry Pi.

In this case, you need the RT version of the connector. You can download it from the URL listed earlier, but instead of downloading the Windows version, click the drop-down menu and choose the .NET & Mono version. Figure 13-6 shows an excerpt from the download page with the correct version selected. This link downloads a compressed file named mysql-connector-net-6.9.8-noinstall.zip (or similar if using a different version).

Figure 13-6. Selecting the .NET & Mono version of Connector/Net for download

Once the file has downloaded, extract it and find it at <downloads folder>mysql-connector-net-6.9.8-noinstallRTMySql.Data.RT.dll, replacing the path with your downloads folder.

Now, return to Visual Studio and right-click the References item in the Solution Explorer and choose Add Resource .... When dialog opens, choose the Browse tab, click the Browse button, locate the MySql.Data.RT.dll file, and select it. This adds the library to your project.

Windows 10 IoT Extensions

Finally, you must add the reference to the Windows 10 IoT Extensions from the project property page. You do this by right-clicking the References item on the project in Solution Explorer. Once you have loaded the three references, Solution Explorer should show the resources shown in Figure 13-7.

Figure 13-7. References for the WeatherDatabase project

Now that you have all the resources you need, let’s see the code for connecting to MySQL.

Connecting to MySQL

Connecting to MySQL with Connector/Net is pretty easy. You simply need to instantiate a MySQLConnection object passing it the connection string defined earlier, and then you open the connection. You wrap the Open() call in a try block so that you can detect whether the connection fails (the library throws an exception). You add some debug statements to help diagnose the problem—just remember to run the project in debug to see the messages. Listing 13-5 shows the Connect() method .

Listing 13-5. Connecting to MySQL

private Boolean Connect()
{
    mysql_conn = new MySqlConnection(connStr);
    try
    {
        Debug.WriteLine("Connecting to MySQL...");
        mysql_conn.Open();
        Debug.WriteLine("Connected to " + mysql_conn.ServerVersion + ".");
    }
    catch (Exception ex)
    {
        Debug.Write("ERROR: ");
        Debug.WriteLine(ex.ToString());
        return false;
    }
    return true;
}

Now, let’s see the code for reading the weather data .

Reading the Weather Data

Now let’s add the code to read the weather data from the BMP280. A timer is used to fire an event every 5 seconds to read (and save) the data. Thus, you need the event for the DispatcherTimer object defined earlier, named BmpTimer_Tick().

However, instead of putting the code to read the data in this event, you use another method and run it as a task (in a new thread). This is because the BMP280 object cannot run in the user interface thread.⁹ Thus, you create a new method named getData() to read the data. You also write the data to MySQL in the same method, but you look at that in the next section.

The BMP280 class from the Glovebox.IoT.Devices library provides a variety of data from the sensor, including temperature in both Fahrenheit and Celsius, and barometric pressure in several units. You read all of these with the Temperature and Pressure attributes, as shown in Listing 13-6. This listing only shows the code for reading from the sensor. The code for writing the database is shown in the next section.

Listing 13-6. Reading the Weather Data

private void BmpTimer_Tick(object sender, object e)
{
    var t = Task.Run(() => getData());
}

public void getData()
{
    var degreesCelsius = tempAndPressure.Temperature.DegreesCelsius;
    var degreesFahrenheit = tempAndPressure.Temperature.DegreesFahrenheit;
    var bars = tempAndPressure.Pressure.Bars;
    var hectopascals = tempAndPressure.Pressure.Hectopascals;
    var atmospheres = tempAndPressure.Pressure.Atmospheres;

    Debug.WriteLine(degreesCelsius);
    Debug.WriteLine(degreesFahrenheit);
    Debug.WriteLine(bars);
    Debug.WriteLine(hectopascals);
    Debug.WriteLine(atmospheres);
...                                                                               

Writing the Data to the Database

Writing data to the database is also easy to do. You create a string constant that you can use to fill in the data as parameters. Thus, you use the INSERT constant with the String.Format() method and pass in the data you read. You must put these values in the same order as the table columns. This means that you supply temperature in Celsius, temperature in Fahrenheit, bars, hectopascals, and atmospheres for the pressure.

Next, you create a new class instance for a MySQLCommand class passing the query you just formatted. Once the class is instantiated, you then call the method ExecuteNonQuery() to run the query. This method is the one you use if there are no results returned. Other methods are provided for reading data from the database server (see the documentation for more information).

You wrap all of this code in a try block so that you can capture any exceptions and display them using debug statements. Listing 13-7 shows the rest of the getData() method with the database code highlighted.

Listing 13-7. Writing Data to MySQL in the getData() Method

...
    try
    {
        // Format the query string with data read
        String insert_str = String.Format(INSERT, degreesCelsius, degreesFahrenheit,
            bars, hectopascals, atmospheres);
        // Create a new command and setup the query
        MySqlCommand cmd = new MySqlCommand(insert_str, mysql_conn);
        // Execute the query
        cmd.ExecuteNonQuery();
        Debug.WriteLine("Data inserted.");
    }
    catch (Exception ex)
    {
        Debug.Write("ERROR: ");
        Debug.WriteLine(ex.ToString());
    }
}

A debug statement was added to inform you that the data was inserted in the database. This could be helpful if you are debugging the code.

That’s it! The code is complete and ready for compilation. Be sure to check the earlier listings to ensure that you have all the code in the right place. Once you have entered all the code, you should now attempt to compile the solution. Correct any errors you find until the code compiles without errors or warnings.