Now that you know what sensor networks are and even how to build sensor nodes using an Arduino and a Raspberry Pi, it’s time to do something really cool with your Raspberry Pi. The last chapter discussed the various ways you can store data from your sensors. One of the most reliable and the most versatile is storing your sensor data in a database. This chapter explores using a Raspberry Pi as a database server.
While this has always been an option with the Raspberry Pi starting from the older version 2B boards, it is even more an option now that the Raspberry Pi 4B boards are out. They have more than enough processing power and (now, thankfully) more memory for heavier database work. Cool!
You begin with a short introduction to MySQL and then jump into getting MySQL up and running on a Raspberry Pi.1 If you have experience with installing and using MySQL, you may want to skip ahead to the “Building a Raspberry Pi MySQL Server” section.
What Is MySQL?
MySQL is the world’s most popular open source database system for many excellent reasons. First and foremost, it is open source, which means anyone can use it for a wide variety of tasks for free.2 Best of all, MySQL is included in many platform repositories, making it easy to get and install. If your platform doesn’t include MySQL in the repository (such as aptitude), you can download it from the MySQL website (http://dev.mysql.com).
Oracle Corporation owns MySQL. Oracle obtained MySQL through an acquisition of Sun Microsystems, which acquired MySQL from its original owners, MySQL AB. Despite fears to the contrary, Oracle has shown excellent stewardship of MySQL by continuing to invest in the evolution and development of new features as well as faithfully maintaining its open source heritage. Although Oracle also offers commercial licenses of MySQL—just as its prior owners did in the past—MySQL is still open source and available to everyone.
Open source software grew from a conscious resistance to the corporate-property mindset. While working for MIT, Richard Stallman, the father of the free software movement, resisted the trend of making software private (closed) and left MIT to start the GNU (GNU’s Not Unix) project and the Free Software Foundation (FSF).
Stallman’s goal was to reestablish a cooperating community of developers. He had the foresight, however, to realize that the system needed a copyright license that guaranteed certain freedoms. (Some have called Stallman’s take on copyright “copyleft,” because it guarantees freedom rather than restricts it.) To solve this, Stallman created the GNU Public License (GPL). The GPL, a clever work of legal permissions that permits the code to be copied and modified without restriction, states that derivative works (the modified copies) must be distributed under the same license as the original version without any additional restrictions.
There was one problem with the free software movement. The term free was intended to guarantee freedom to use, modify, and distribute; it was not intended to mean “no cost” or “free to a good home.” To counter this misconception, the Open Source Initiative (OSI) formed and later adopted and promoted the phrase open source to describe the freedoms guaranteed by the GPL license. For more information about open source software, visit www.opensource.org.
MySQL runs as a background process (or as a foreground process if you launch it from the command line3) on your system. Like most database systems, MySQL supports Structured Query Language (SQL). You can use SQL to create databases and objects (using data definition language [DDL]), write or change data (using data manipulation language [DML]), and execute various commands for managing the server.
To issue these commands, you must first connect to the database server. MySQL provides a client application that enables you to connect to and run commands on the server. The application is named MySQL Shell (mysqlsh) and has many improvements over the older client including a better interface as well as SQL, Python, and JavaScript modes. If you have used MySQL in the past, you may be familiar with the older MySQL client (mysql), which you can also use, but MySQL Shell is much easier to use. Please see the online reference manual for MySQL Shell (https://dev.mysql.com/doc/mysql-shell/8.0/en/) to learn more about how to use it, but those who’ve used the older client or who are following along in the tutorial will take to it quickly.
It is best to use the older mysql client when working on the Raspberry Pi because it requires one less compilation and installation step, but you can build and install the MySQL Shell on Raspberry Pi.
If you don’t already have the MySQL Shell installed, visit https://dev.mysql.com/downloads/shell/ and download it and then install it on your system. For macOS and Linux, follow the platform-specific installation procedures that you use for any other software. For Windows, you can download a separate MySQL Shell installation (.msi) or you can download the Windows Installer, which contains all MySQL applications, tools, and drivers. In that case, you simply select the components you want at the start of the installation.
Of course, you will also need to have access to a MySQL server running some place. The good news is you can install it on your PC! Just download the correct installer from the website (community edition) https://dev.mysql.com/downloads/mysql/, and install it on your system. It is very easy to install, but if you want a step-by-step instruction, see the online reference manual for help (https://dev.mysql.com/doc/refman/8.0/en/).
Commands Using the MySQL Shell
If you’ve not used the MySQL Shell yet, take a look at how I started the shell. Notice I typed in the user credentials in a different format, which is quite intuitive and a bit easier than separate options. Notice also the shell permits me to save the password for faster subsequent logins. Nice!
In this example, you see DML in the form of the CREATE DATABASE and CREATE TABLE statements, DDL in the form of the INSERT and SELECT statements, and a simple administrative command to set a global server variable. Next, you see the creation of a database and a table to store the data, the addition of a row in the table, and finally retrieval of the data in the table.
A great many commands are available in MySQL. Fortunately, you need master only a few of the more common ones. The following are the commands you will use most often. The portions enclosed in <> indicate user-supplied components of the command, and [...] indicates that additional options are needed:
You must terminate each command with a semicolon (;) or G.
CREATE DATABASE <database_name>: Creates a database
USE <database>: Sets the default database
CREATE TABLE <table_name> [...]: Creates a table or structure to store data
INSERT INTO <table_name> [...]: Adds data to a table
UPDATE [...]: Changes one or more values for a specific row
DELETE FROM <table_name> [...]: Removes data from a table
SELECT [...]: Retrieves data (rows) from the table
Although this list is only a short introduction and nothing like a complete syntax guide, there is an excellent online reference manual that explains each and every command (and much more) in great detail. You should refer to the online reference manual whenever you have a question about anything in MySQL. You can find it at https://dev.mysql.com/doc/refman/8.0/en/.
If you are thinking that there is a lot more to MySQL than a few simple commands, you are absolutely correct. Despite its ease of use and fast startup time, MySQL is a full-fledged relational database management system (RDBMS). There is much more to it than you’ve seen here. For more information about MySQL, including all the advanced features, see the reference manual.
The name MySQL is a combination of a proper name and an acronym. SQL is Structured Query Language. The My part isn’t the possessive form—it is a name. In this case, My is the name of one of the founder’s daughter. As for pronunciation, MySQL experts pronounce it “My-S-Q-L” and not “my sequel.” Indeed, the mark of a savvy MySQL user is in their correct pronunciation of the product.
Getting Started with MySQL
Now that you know what MySQL is and how it is used, you need to know a bit more about RDBMSs and MySQL in particular before you start building your first database server. This section discusses how MySQL stores data (and where it is stored), how it communicates with other systems, and some basic administration tasks required in order to manage your new MySQL server.
I present this information as a tutorial or primer on MySQL. You install MySQL on the Raspberry Pi in a later section.
But first, let’s review what a relational database system is and why it matters.
What’s a Relational Database Management System?
An RDBMS is a data storage-and-retrieval service based on the relational model of data as proposed by E. F. Codd in 1970. These systems are the standard storage mechanism for structured data. A great deal of research is devoted to refining the essential model proposed by Codd, as discussed by C. J. Date in The Database Relational Model: A Retrospective Review and Analysis.4 This evolution of theory and practice is best documented in The Third Manifesto.5
The relational model is an intuitive concept of a storage repository (database) that can be easily queried by using a mechanism called a query language to retrieve, update, and insert data. The relational model has been implemented by many vendors because it has a sound systematic theory, a firm mathematical foundation, and a simple structure. The most commonly used query mechanism is SQL, which resembles natural language. Although SQL is not included in the relational model, it provides an integral part of the practical application of the relational model in RDBMSs.
The data are represented as related pieces of information (attributes or columns) about a certain event or entity. The set of values for the attributes is formed as a tuple (sometimes called a record or row). Tuples are stored in tables that have the same set of attributes. Tables can then be related to other tables through constraints on keys, attributes, and tuples.
Tables can have special mappings of columns called indexes that permit you to read the data in a specific order. Indexes are also very useful for fast retrieval of rows that match the value(s) of the indexed columns.
Now that we know a bit about the theory, let’s see how MySQL works to store our data.
How and Where MySQL Stores Data
The MySQL database system stores data via an interesting mechanism of programmatic isolation called a storage engine that 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.6 While MySQL supports several storage engines, the default storage engine is called the InnoDB, which is a transactional storage engine.
Common Storage Engines
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, ACID7 storage engine. A transaction is 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, minimizing disk usage may be a goal. 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.
Where Is My Data Stored?
So where is all this data? If you query the MySQL server and issue the command SHOW VARIABLES LIKE 'datadir';, 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 8-2 shows an example. The database folders are shown in bold. Some files omitted for brevity.
When you use sudo for the first time, you are required to enter the password for the root user.
Finding Where Your Data Is Located
This example first queries the database server for the location of the data directory (it is in a protected folder on this machine). If you issue a listing command, you can see the InnoDB files identified by the ib and ibd prefixes. You also see a number of directories, all of which are the databases on this server. Below that is a listing of one of the database folders. Notice the files with the extension .MY?: these are MyISAM files (data and index).
For more information about storage engines and the choices and features of each, please see the online MySQL reference manual section “Storage Engines” (https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html).
The MySQL Configuration File
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. You will change this file when you set up MySQL on the Raspberry Pi.
How to Start, Stop, and Restart MySQL
While working with your databases and configuring MySQL on your Raspberry Pi, 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.
The first restart restarts the server with the general logon, and the second restarts the server without the log enabled (assuming it isn’t in the configuration file). It’s probably a good idea to make sure no one is using the server when you restart it.
Start: sudo systemctl start mysqld
Stop: sudo systemctl stop mysqld
Restart: sudo systemctl restart mysqld
Status: sudo systemctl status mysqld
You may be tempted to just power down your Raspberry Pi database server like you do your Arduino sensor nodes, but you should avoid that temptation. The Raspberry Pi is a real computer with active file systems that require a synchronized shutdown. You should always execute a controlled shutdown before powering down.
To shut down the Raspberry Pi, recall that you issue the sudo shutdown –h now command. To reboot, you can use the sudo shutdown –r now command.
Creating Users and Granting Access
The first command creates the user named sensor1, but the name also has an @ followed by another string. This second string is the hostname of the machine with which the user is associated. That is, each user in MySQL has both a user and a hostname, 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.
Also be careful about using the wildcard % for the host. Although it makes it easier to create a single user and let the user access the database server from any host, it also makes it much easier for someone bent on malice to access your server (once they discover the password).
Another consideration is connectivity. As with the Raspberry Pi, if you connect a database to your network and the network is in turn connected to the Internet, it may be possible for other users on your network or the Internet to gain access to the database. Don’t make it easy for them—change your root user password, and create users for your applications.
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),8 add data (INSERT), and change data (UPDATE). See the online reference manual for more information 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.
Now that you’ve had a short (and perhaps a bit terse) introduction to MySQL, let’s get started on your MySQL Raspberry Pi database server.
Building a Raspberry Pi MySQL Server
It is time to get your hands dirty and work some magic on your unsuspecting Raspberry Pi! Let’s begin by adding a USB drive to it. A flash drive with fast read/write speeds can work especially well considering it does not need as much power as a traditional external hard drive. Depending on the size of your data, you may want to seriously consider doing this.
If your data will be small (never more than a few megabytes), you may be fine using MySQL from your boot image SD card. However, if you want to ensure that you do not run out of space and keep your data separate from your boot image, you should mount a USB drive that automatically connects on boot. This section explains how to do this in detail.
If you plan to use an external hard drive, be sure you use a good-quality powered USB hub to host your external drive. This is especially important if you are using a traditional spindle drive, because it consumes a lot more power. Connecting your external drive directly to the Raspberry Pi may rob it of power and cause untold frustration. Symptoms include random reboot (always a pleasant surprise), failed commands, data loss, and so on. Always be sure you have plenty of power for your peripherals as well as your Raspberry Pi.
The choice of what disk to use is up to you. You can use a USB flash drive, which should work fine if it has plenty of space and is of sufficient speed (most newer models are fast). You can also use a solid-state drive (SSD) if you have an extra one or want to keep power usage and heat to a minimum. On the other hand, you may have an extra hard drive lying around that can be pressed into service. This section’s example uses a surplus 250GB laptop hard drive mounted in a typical USB hard drive enclosure.
Using an external hard drive—either an SSD or traditional spindle drive—is much faster than accessing data on a flash drive. It is also typically cheaper per unit (gigabyte) or, as I mentioned, can be easily obtained from surplus.
Partitioning and Formatting the Drive
Before you can use a new or an existing drive with a file system incompatible with the Raspberry Pi, you must partition and format the drive. Because the surplus drive in this example had an old Windows partition on it, I had to follow these steps. Your Raspberry OS may be able to read the format of your old drive, but you should use the ext4 file system for optimal performance. This section shows you how to partition and format your drive.
What you see here are all the devices attached to the Raspberry Pi. If you are new to Linux or partitioning drives, this may look like a lot of nonsense. I’ve highlighted the interesting rows in bold. Notice that the output identifies a 64GB drive located on a device designated as /dev/sda. All the interesting data about the drive is shown as well.
As I mentioned, there is already a partition on this drive, indicated by the row with the name of the device plus the number of the partition. Thus, /dev/sda1 is the one and only partition on this drive. Let’s delete that partition and create a new one. You execute both operations using the fdisk application as shown in Listing 8-3.
If you have a partition on your drive that has data you want to keep, abort now and copy the data to another drive first. The following steps erase all data on the drive!
Partitioning the USB Drive
The first command, d, deletes a partition. In this case, there was only one partition, so you select it by entering 1. You then create a new partition using the command n and accept the defaults to use all the free space. To check your work, you can use the p command to print the device partition table and metadata. It shows (and confirms) the new partition.
If you are worried that you may have made a mistake, do not panic! The great thing about fdisk is that it doesn’t write or change the disk until you tell it to with the w or write command. In the example, you issue the w command to write the partition table. To see a full list of the commands available, you can use the h command or run man fdisk.
For all Linux commands, you can view the manual file by using the command man <application>.
Formatting the Drive
Now you have a new partition, and it has been properly formatted. The next step is associating the drive with a mount point on the boot image and then connecting that drive on boot, so you don’t have to do anything to use the drive each time you start your Raspberry Pi.
Setting Up Automatic Drive Mounting
External drives in Linux are connected (mounted) with mount and disconnected (unmounted) with umount . Unlike with some operating systems, it is generally a bad idea to unplug your USB drive without unmounting it first. Likewise, you must mount the drive before you can use it. This section shows the steps needed to mount the drive and to make the drive mount automatically on each boot.
These commands are easy to discern and are basic file and folder commands. However, the tuning steps using tune2fs (tune file system) are used to first reset the number of blocks used for privileged access (which saves a bit of space) and then label the drive as MYSQL. Again, these are optional, and you may skip them if you like.
You can unmount the drive with sudo umount /dev/sda1.
At this point, the drive is accessible and ready to be used. You can change to the /media/HDD folder and create files or do whatever you’d like. Now let’s complete the task of setting up the drive for automatic mounting.
The best way to do this is to refer to the drive by its universally unique identifier (UUID). This is assigned to this drive and only this drive. You can tell the operating system to mount the drive with a specific UUID to a specific mount point (/media/HDD).
Remember the /dev/sda device name from earlier? If you plugged your drive into another hub port—or, better still, if there are other drives connected to your device and you unmount and then mount them—the device name may not be the same the next time you boot! The UUID helps you determine which drive is your data drive, frees you from having to keep the drive plugged in to a specific port, and allows you to use other drives without fear of breaking your MySQL installation if the drive is given a different device name.
Notice the line in bold. Wow! That’s a big string. A UUID is a 128-byte (character) string. Copy it for the next step.
Now you are ready to build a MySQL database server! The following section details the steps needed to do this using your Raspberry Pi.
Project: Installing MySQL Server on a Raspberry Pi
Turning a Raspberry Pi into a MySQL database server is easy. Well, almost. The latest version of MySQL (8.0) is not available for the Raspberry Pi.10 However, since MySQL is open source, we can build (compile and link) MySQL from source on our Raspberry Pi. How cool is that? This section shows you how to acquire the source code for MySQL, build, and install it. We then learn how to move its default data directory from your boot image to the new external drive you connected in the previous section.
Savvy readers may already be aware of variants of MySQL that are available from other vendors. While most claim to be 100% compatible with Oracle’s MySQL (owners of the source code), there are some differences that can make development more difficult. For example, the MySQL database connector for Arduino (called Connector/Arduino) is known to have issues with some versions of some variants. Thus, it is this author’s opinion you should always use Oracle’s release of MySQL rather than variants.
In this section, we will use Raspberry Pi computers rather than more expensive mainstream server hardware. If you would like to follow along and use more traditional server hardware, you can do, but remember that some of the commands used on the Raspberry Pi are very similar to those you would use on typical Linux-based platforms. You may need to substitute platform-specific versions to use the following on your PC.
Build MySQL.
Install MySQL manually.
Configure MySQL.
This list is like the process you would use to set up MySQL on commodity hardware, but the build and configuration steps are required to make MySQL work on Raspbian (because there are no installation packages). It is important to note that these extra steps are not unique to Raspbian.
In fact, building, installing, and configuring MySQL from source is a viable alternative to using installation packages. You can find instructions on building MySQL for various platforms in the section entitled “Installing MySQL from Source” in the online reference manual (https://dev.mysql.com/doc/refman/8.0/en/source-installation.html).
The process is straightforward involving a few minor system configuration items to prepare our system and two commands: cmake and make. This section will walk you through all those steps with ample examples and every step documented.
The task of building MySQL from source code may seem daunting to those who have never programmed or for those who haven’t written a program in a while, but do not despair. The hardest part of compiling MySQL on the Raspberry Pi is waiting for the process to complete. That is, it may take an hour or so to compile everything. But that is a small price to pay for being able to use Raspberry Pi computers to experiment with MySQL!
Let’s dive into compiling MySQL on Raspbian starting with the prerequisites.
Prerequisites
There are several things you need to install to prepare your Raspberry Pi to compile MySQL including hardware and software prerequisites MySQL.
The hardware requirement is that the latest version of MySQL (8.0.18 at the time of this writing) requires using a Raspberry Pi 4B with 2GB or 4GB (4GB is faster) board. Thus, you should consider whether you want to build MySQL on the same Raspberry Pi 4B where you want to install it. Why is this important? It is important because you may want to run MySQL on older Raspberry Pi boards. More specifically, while it is best to compile MySQL on the 4B, you can install and run it on the 3B+ without any issues. We will see how to do this later. The reason we need to use the 4B is largely due to memory. MySQL simply requires more than the 1GB of RAM found on the 3B boards.11
You need to install Curses 5 (libncurses5-dev).
You need to install Bison.
You need to install OpenSSL (libssl-dev).
You need to install CMake.
Download MySQL server source code
OK, now we’re ready to build the MySQL server.
Building MySQL Server
There are just three steps to building MySQL on Raspberry Pi. We first run the preprocessor called CMake, then build the code with make, and finally build an installation package with the make package command. We can use this package to install MySQL on another Raspberry Pi. Let’s see the details of each of these steps beginning with CMake.
CMake (cmake.org) is another open source product used to build, test, and package software. Recall, we installed CMake in the previous section. There are many variations of options you can use to build software and many that apply to MySQL. In fact, you can spend a lot of time customizing the CMake command options to build for almost any platform. Since we downloaded the MySQL source code for generic Linux with the Boost libraries, we’ve got everything we need.
You should set -DWITH_UNIT_TESTS=OFF to save compile time (not needed).
You should set the PREFIX to set the installation path to make it easy to install.
We need to turn off the “gold” linker.
We must build with the release code (debug requires too much memory for the Raspberry Pi).
We must add additional compiling and build flags to ensure the code builds properly on ARM32.
Running CMake (Preparing to Compile)
Running the CMake Command (ARM32)
Don’t worry if this command looks strange, and it is not necessary to understand all the special settings we’ve used for the compile and link phases. However, if you do want to learn more about these options, you can see the documentation on the GNU compiler (http://gcc.gnu.org/onlinedocs/gcc/Option-Summary.html) and linker (https://gcc.gnu.org/onlinedocs/gcc/Link-Options.html) options.
The command could take a few minutes to run. Be sure there are no errors and that the last lines indicate the build files have been written to the build folder. Pay special attention to the LINK_FLAGS messages at the end. The options in the CMake command do not include spaces. If you accidentally added spaces, the comma-separated list would show them in the CMake output. Be sure there are no spaces. If there are spaces, you may get an error stating --icf=safe (or other) options are invalid. If that happens, run the command again without the spaces.
If you’ve gotten this far without errors, you can almost relax. The next step, compiling the code is easy, but it can take a while to run on a Raspberry Pi 4B (at least 1–2 hours).
Running Make (Compiling)
The next step is to compile the code. This is done simply with the make command. This command allows us to specify how many parallel threads we want to use. For the Raspberry Pi 4B and a total of four CPU cores, it is safe to use three cores for compiling. If you have a watcher for CPU usage running, you will see those three and possibly at times all four cores running at 100%. If your Raspberry Pi is mounted in a case, make sure you have adequate ventilation or a fan blowing over the board.
Listing 8-6 shows the compilation step of the MySQL server code using the command make -j3. The listing is an excerpt of the messages you will likely see (there will be thousands of lines), but the important ones to note are the last several. These ensure the code has compiled without errors.
You may see minor warnings flow past when the code is compiling, which you can ignore. However, you should not see any compilation errors. If you do, go back and check your CMake command and rerun it if necessary. If all else fails, delete the build directory and start over.
Compiling MySQL Server
Once the compilation is complete, the next step is to build a package (TAR file) we can use to install MySQL on our server.
Making the Package
Building the TAR Package
That’s it! We’ve built MySQL on the Raspberry Pi! That wasn’t so bad, was it? Now, let’s see how to install and test MySQL on our server.
Installing MySQL Server
If we built MySQL on a different Raspberry Pi, we need to copy the TAR file to a removable drive to copy the file to the target Raspberry Pi.
Notice the last command uses an option to strip one component (the first folder—mysql-8.0.18-linux-armv71) from the extracted file directories. This ensures the MySQL files get copied to /usr/local/mysql.
Installing from the TAR file requires more steps than installing from a typical platform-specific package. This is because installation packages typically take care of several required configuration steps, all of which are detailed in the online reference manual section entitled “Installing MySQL on Unix/Linux Using Generic Binaries” (https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html).
Configuring MySQL Server
Now that we have the files copied, we can finish the setup. The process is not tedious but does involve several commands run from a terminal, so some patience is needed to ensure all the commands are entered correctly.
Connecting to MySQL for the First Time
Starting MySQL Automatically or Manually with systemctl
That’s it! We’ve installed MySQL server and tested that is works. It would also be a good idea to install the MySQL Shell on each server. In the next section, you tell MySQL to use the external drive instead for storing your databases and data.
Moving the Data Directory to the External Drive
Recall that you want to use MySQL to store your sensor data. As such, the sensor data may grow in volume and over time may consume a lot of space. Rather than risk filling up your boot image SD, which is normally only a few gigabytes, you can use an external drive to save the data. This section shows you how to tell MySQL to change its default location for saving data.
If you get permission errors, try changing the owner of the /media/mysql folder to mysql:mysql.
Testing the New Data Directory
In the output, the new database name is represented as the folder testme.
Well, there you have it—a new MySQL database server running on a Raspberry Pi!
If you are curious about what more you can do with your new database server, read on. In the next section, you tackle a very popular feature of MySQL called replication. It permits two or more servers to have copies of databases. For your purposes, it may be handy to use the copies as a backup, so you don’t have to do any manual file copying from your Raspberry Pi.
Advanced Project: Using MySQL Replication to Back Up Your Sensor Data
One of the nicest things about using an external drive to save your MySQL data is that at any point you can shut down your server, disconnect the drive, plug it in to another system, and copy the data. That may sound great if your Raspberry Pi database server is in a location that makes it easy to get to (physically) and if there are periods when it is OK to shut down the server.
However, this may not be the case for some sensor networks. One of the benefits of using a Raspberry Pi for a database server is that the server can reside in close proximity to the sensor nodes. If the sensor network is in an isolated area, you can collect and store data by putting the Raspberry Pi in the same location. But this may mean trudging out to a barn or pond or walking several football field lengths into the bowels of a factory to get to the hardware if there is no network to connect to your database server.
But if your Raspberry Pi is connected to a network, you can use an advanced feature of MySQL called replication to make a live, up-to-the-minute copy of your data. Not only does this mean you can have a backup, but it also means you can query the server that maintains the copy and therefore unburden your Raspberry Pi of complex or long-running queries. The Raspberry Pi is a very cool small-footprint computer, but a data warehouse it is not.
What Is Replication, and How Does It Work?
MySQL replication is an easy-to-use feature and yet a very complex and major component of the MySQL server. This section presents a bird’s-eye view of replication for the purpose of explaining how it works and how to set up a simple replication topology. For more information about replication and its many features and commands, see the online MySQL reference manual (http://dev.mysql.com/doc/refman/5.5/en/replication.html).
Replication requires two or more servers. One server must be designated as the origin or master. The master role means all data changes (writes) to the data are sent to the master and only the master. All other servers in the topology maintain a copy of the master data and are by design and requirement read-only servers. Thus, when your sensors send data for storage, they send it to the master. Applications you write to use the sensor data can read it from the slaves.
The copy mechanism works using a technology called the binary log that stores the changes in a special format, thereby keeping a record of all the changes. These changes are then shipped to the slaves and re-executed there. Thus, once the slave re-executes the changes (called events), the slave has an exact copy of the data.
The master maintains a binary log of the changes, and the slave maintains a copy of that binary log called the relay log. When a slave requests data changes from the master, it reads the events from the master and writes them to its relay log; then another thread in the slave executes those events from the relay log. As you can imagine, there is a slight delay from the time a change is made on the master to the time it is made on the slave. Fortunately, this delay is almost unnoticeable except in topologies with very high traffic (lots of changes). For your purposes, it is likely when you read the data from the slave, it is up to date. You can check the slave’s progress using the command SHOW SLAVE STATUS; among many other things, it shows you how far behind the master the slave is. You see this command in action in a later section.
Now that you have a little knowledge of replication and how it works, let’s see how to set it up. The next section discusses how to set up replication with the Raspberry Pi as the master and a desktop computer as the slave.
How to Set Up Replication
This section demonstrates how to set up replication from a Raspberry Pi (master) to a desktop computer (slave). The steps include preparing the master by enabling binary logging and creating a user account for reading the binary log, preparing the slave by connecting it to the master, and starting the slave processes. You conclude with a test of the replication system.
Preparing the Master
The first line sets the server ID of the master. In basic replication (what you have for version 5.5), each server must have a unique server ID. In this case, you assign 1 to the master; the slave will have some other value, such as 2. Imaginative, yes?
The next line sets the location and name of the binary log file. You save it to your external drive because, like the data itself, the binary log can grow over time. Fortunately, MySQL is designed to keep the file to a reasonable size and has commands that allow you to truncate it and start a new file (a process called rotating). See the online reference manual (https://dev.mysql.com/doc/refman/8.0/en/replication.html) for more information about managing binary log files. Once the edits are saved, you can restart the MySQL server (or simply stop and then start).
Information Needed from the Master for Replication
Item from Master | Value |
|---|---|
IP Address or Hostname | |
Port | |
Binary log file | |
Binary log file position | |
Replication user ID | |
Replication user password |
The MySQL server you want to use as a slave should be the same version as the server on the Raspberry Pi or at least a server that is compatible. The online reference manual specifies which MySQL versions work well together. Fortunately, the list of versions with issues is very short. In this section, you should have a server installed on your desktop or server computer and ensure that it is configured correctly.
This example uses the IP address of the Raspberry Pi, the port number (3306 is the default), the log file and position from the SHOW MASTER STATUS command, and the user and password for the replication user. If you typed the command correctly, it should return without errors. If there are errors or warnings, use the SHOW WARNINGS command to read the warnings and correct any problems.
The next step is to start the slave processes. This command is simply START SLAVE. It normally does not report any errors; you must use SHOW SLAVE STATUS to see them. Here are both of these commands in action as shown in Listing 8-11.
For wide results, use the G option to see the columns as rows (called vertical format).
Starting the Slave
Take a moment to slog through all these rows. There are several key fields you need to pay attention to. These include anything with error in the name and the state columns. For example, the first row (Slave_IO_State) shows the textual message indicating the state of the slave’s I/O thread. The I/O thread is responsible for reading events from the master’s binary log. There is also a SQL thread that is responsible for reading events from the relay log and executing them.
For this example, you just need to ensure that both threads are running (YES) and there are no errors. For detailed explanations of all the fields in the SHOW SLAVE STATUS command , see the online MySQL reference manual (https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html).
Wait! Where did it go? Wasn’t this example supposed to replicate everything? Well, yes and no. It is true that your slave is connected to the master and will replicate anything that changes on the master from this point on. Recall that you used the SHOW MASTER STATUS command to get the binary log file and position. These values are the coordinates for the location of the next event, not any previous events. Aha: you set up replication after the testme database was created.
- 1.
Stop the slave.
- 2.
Go to the master and drop the database.
- 3.
Get the new SHOW MASTER STATUS data.
- 4.
Reconnect the slave.
- 5.
Start the slave.
Got that? Good. If not, it is a good exercise to go back and try these steps on your own.
Success! Now your Raspberry Pi database server is being backed up by your desktop computer.
Component Shopping List
Components Needed
Item | Vendors | Est. Cost USD | Qty Needed |
|---|---|---|---|
Surplus hard drive | Any USB hard drive (surplus or purchased) | Varies | 1 |
Components Reused from Previous Chapters
Item | Vendors | Est. Cost USD | Qty Needed |
|---|---|---|---|
Raspberry Pi Model 4B 2GB or 4GB RAM | sparkfun.com, adafruit.com, thepithut.com | $50 and up | 1 |
Mini-HDMI cable | Most online and retail stores | Varies | 1 |
HDMI or DVI monitor | Most online and retail stores | Varies | 1 |
USB keyboard | Most online and retail stores | Varies | 1 |
USB-C power supply | Most online and retail stores | Varies | 1 |
SD card, 32GB or more | Most online and retail stores | Varies | 1 |
Summary
This chapter introduced MySQL and gave you a crash course on how to use it. You also compiled and installed MySQL on a Raspberry Pi and saw how to use more advanced features of MySQL, like replication.
Although it does not have nearly the sophistication of a high-availability, five-nines uptime (99.999%) database server, the low-cost Raspberry Pi with an attached USB hard drive makes for a very small-footprint database server that you can put just about anywhere.
This is great because sensor networks, by nature and often by necessity, need to be small and low cost. Having to build an expensive database server is not usually the level of investment desired.
Furthermore, depending on your choice of host for the sensor, saving data is difficult. If you choose an Arduino as the host, saving the data to a database requires a connection to the Internet and reliance on another service to store your data. This is fine for cases where you can actually connect the sensor nodes to the Internet12 (or the sensor network’s aggregator node); but if you cannot or do not want to connect to the Internet, it is difficult to get data into a database server from the Arduino.
That is, it was until recently. As you will see, there is indeed a way to save sensor data from a sensor node. In the next chapter, you build a sensor node that saves its data in your new database server—directly from an Arduino!