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

Screen Shot 2016-04-03 at 9.20.27 AM

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 =

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.

Screen Shot 2016-04-03 at 7.38.19 AM

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

Screen Shot 2016-04-03 at 7.41.51 AM

Then I create a database for storing the creekdata.

Screen Shot 2016-04-03 at 7.44.51 AM

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

Screen Shot 2016-04-03 at 7.47.18 AM

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

Screen Shot 2016-04-03 at 7.49.46 AM

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

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster