The Creek: Install MySQL

As I described in the Creek Software Architecture, I am using MySql as the database to hold all of the creek temperature and depth information.  That means I need to get MySql going on the Raspberry Pi.  The process is well documented on the MySql installation website.  To do the installation you need to run:

• sudo apt-get update
• sudo apt-get install mysql-server
• type password for root when it asks

After a bunch of gyrations and pages of information barf onto the screen, you will have MySql going on your Raspberry Pi.  You can verify that by running

• sudo service mysql status

It turns out that the command “service” has a number of useful options including:

• sudo service mysql stop [which will turn off mysql]
• sudo service mysql start [which will start it]
• sudo service mysql restart [which will restart it]

As you guys might have noticed I like to run stuff on my Mac.  In order to get mysql to talk on the network you need to change the networking configuration.  To do this edit the file  “/etc/mysql/my.cnf”

• sudo vi /etc/mysql/my.cnf

Then add a “#” to comment out this bind-address statement

• #bind-address = 127.0.0.1

Then you can restart mysql with the updated options using:

• sudo service mysql restart

Once that is working you can add the ability for the root user to access the mysql service remotely on the network.  To do this run the command line version of mysql

• mysql -u root -p [it will ask you the mysql root password]
• grant all privileges on *.* to ‘root’@’192.168.%.%’ with grant option;
• quit

The “192.168.%.%” will restrict the access to this server/user to only the private IP addresses in my network.

Then you can startup MySqlWorkBench from your Mac (or whatever).  The first thing that I do in MySqlWorkbench is to create a connection to the RPi.  Remember in the previous post I added the IP address of the RPi to my “/etc/hosts” file and called it “iotexpertpi” this lets me refer to the RPi by name.

After I make the connection and click on the “Users and Privileges” button things look like this:

Then I create a database for storing the creekdata.

And a user called “creek” who is allowed to connect only from the Raspberry Pi.

And give that user insert/select privileges on the creekdata database:

That is it for MySql.  In the next posts I will add Tomcat- a JSP Server- to the RPi.