MySQL for the Internet of Things

Chapter 5 ■ MySQL priMer

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 on Windows

While working with your databases and configuring MySQL on your computer, 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, stop, and restart the MySQL server with the notifier tray application or via the Windows
services control panel. Simply select the MySQL service and right-click to stop or start the service. This will
execute a controlled shutdown and startup should you need to do so.

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.

a NOte aBOUt SeCUrItY

it is always a good idea to create a user for your application that does not have full access to the MySQL

system. this is so you can minimize any accidental changes and also to prevent exploitation. For sensor

networks, it is recommended that you create a user with access only to those databases where you

store (or retrieve) data. you can change MySQL user passwords with the following command:

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

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).