Friday, January 29, 2016

Raspberry and Amazon-hosted mySQL

RASPBERRY + AMAZON-hosted mySQL

Let me share my latest adventure, exploring the world of cloud-hosted databases.

Turned out to be quite simple to get started…with plenty room for improvements.

I’ll show you how easy it is to store log data from your Raspberry (temperature, DHCP logs)
to a MySQL database hosted on Amazon AWS RDS.

Requirements:
Raspberry or similar Linux-powered device (also works from Mac/PC )
Amazon AWS account (free)
2..3 hrs of time
Basic SQL and shell scripting experience


SIGN UP TO AMAZON AWS RDS (CLOUD-HOSTED MYSQL)

Just head over to https://aws.amazon.com/de/rds/ and sign up for free. 
Then setup a MySQL database instance, fire it up, create user accounts.


Check in the AWS RDS Dashboard that your instance is up and running, and that you know the full endpoint server name (screenshot below)

AWS RDS Console

ACCESS YOUR REMOTE MYSQL SERVER 

Let’s familiarize ourselfes with SQL again, setup some tables, users.  Run SQL queries, insert & delete data…

Download the mySQL workbench from https://www.mysql.de/products/workbench/ and connect to your AWS database.

First head over to “Users and Privileges” to setup additional users (for your remote queries).

mySQL Workbench - Users and Privileges


Then go to the tables and create some tables, insert data, familiarize yourself with basic SQL queries. The queries you see in the workbench can later be executed by scripts. There are plenty of SQL tutorials out there to get you started…

mySQL Workbench - Table

BACK TO THE RASPBERRY

Now that we have a running mySQL database, and we know we can access it remotely, and we know some basic SQL queries, it’s time to setup the scripts on the Raspberry PI.

  • Install the mySQL client (with sudo apt-get install mysql-client).
    We don’t need the PHP libaries or the sql-server package!
  • Now, let’s check if we can access mySQL over the Internet by visiting a public database.
    https://genome.ucsc.edu/goldenpath/help/mysql.html 
  • From your prompt run
    mysql --user=genome --host=genome-mysql.cse.ucsc.edu
    and then select now(); Should look like the screenshot. 
  • Remember: commands have to be finished with a semicolon!
mySQL cient - first connection
  • Now we can start accessing our own AWS instance with the following command:
    mysql -h instance.blabla.eu-central-1.rds.amazonaws.com -u USER -pPASS database
    • Insert the full instance name behind the –h
    • No space between –p and the password!
    • If your user has access permissions it should look like this:
mySQL client - accessing our own AWS RDS instance

Does it work? 
Great, now let’s setup the actual database tables and scripts to populate the data.

FINAL SETUP

  • First, setup the database table from the mysql shell with
    create table heatlog (Nummer INT NOT NULL AUTO_INCREMENT, Zeitpunkt datetime NOT NULL DEFAULT NOW(), machine VARCHAR(40), heat FLOAT,PRIMARY KEY(Nummer));
  • Then insert a test record into the table with
    insert into heatlog (machine, heat) VALUES ("RASPI02",48.7);
  • With select * from heatlog; you should see the newly created record.
  • Now create a shell script (don’t forget to make it executable with chmod +x filename.sh
#Extract Board Temp and save into mySQL
#!/bin/sh
#echo "Logging Raspi Temperature"
SQLsrv='instance.blabla.eu-central-1.rds.amazonaws.com'
SQLusr=”username"
SQLpwd="passwd"
SQLdbs="database name"
HOST=$(hostname)
heat=$(/opt/vc/bin/vcgencmd measure_temp|cut -c6-9)
SQLfile="/home/pi/heat.sql"
echo "insert into heatlog (machine, heat) VALUES (\"$HOST\",$heat);" > $SQLfile
#cat $SQLfile
#now pipe the SQL commands into mySQL
mysql -h $SQLsrv -u $SQLusr -p$SQLpwd $SQLdbs <$SQLfile

  • Now execute the script (a few times), have a look at the SQL commands (cat heat.sql) and test if the data was actually inserted. 
  • launch mySQL again with
    mysql -h instance.blabla.eu-central-1.rds.amazonaws.com -u USER -pPASS database
    and then run select * from heatlog;
Hooray - our first data entries are online!
  •  If it works well, then launch the script every our via crontab. 
  • Type sudo crontab –e and then add the following lines
    shell=/bin/sh
    @hourly /home/pi/heatmon.sh
crontab entry


SUCCESS

Whoa! That was easy, wasn’t it? You now have your own mySQL database on AWS and your Raspi is logging it’s temperature at every hour. Cool shit!

What’s next?

  • Securing your AWS RDS with proper credentials and not just username/password
  • Sanitizing input (error handling, etc...)
  • Upload other data (DHCP lease info, DNS queries...)
  • Query and analyse the mySQL data, trigger actions, reports, etc….
  • Scripts to query the data and launch specific actions….
  • Setup AWS IoT with the same data, etc…
  • Endless possibilities!

No comments:

Post a Comment