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.

Quite intuitive, but here is a step-by-step explanation http://blog.clearpathsg.com/blog/bid/343084/Creating-a-MySQL-Instance-Using-AWS-RDS-and-Accessing-it-Using-MySQL-client  

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!

TIFU - when a dash is not a dash.

Originally posted on http://imgur.com/8rPBKac and on
https://www.reddit.com/r/raspberry_pi/comments/42t96g/tifu_when_a_dash_is_not_a_dashdont_cutpaste_from/


Let me share my silly little story.
Took me two nights of ever increasing frustration to figure out.
Makes me feel a little stupid.   ;-)

So I am doing multiple small projects on my Raspberry, playing around on my home network with DNSMASQ, recording aircraft positions with Dump1090/ADS-B, etc. Now I started using cloud-hosted MySQL database (AWS RDS) to store and analyze my data. Setting up the DB on Aamazon was quick, connecting to it with the MySQL Workbench worked fine. But I wanted to insert data from the Raspberry shell, using the mysql-client software. Again, installing it was easy (apt-get…yada yada..).

But then…launching the mysql monitor shell on a remote DB server…what exactly was the right syntax again?
                        mysql -h mydb.blablabla.aws-region.rds.amazonaws.com -u username -ppassword

Easy...but mysql just displayed the help info. Or „Access denied“. I googled and googled, and tried all the tips (yes, I reset the password, yes, I created a new DB user, yes, I granted all the privileges, yes, I checked the DB access rules, ....).  Either mysql showed me the help info or an „access denied“ error. Password in quotation marks and ticks. Without ticks. Still no success. Reinstalled mysql. Reimaged the Raspberry image. Still, no success.  WTF??

Then it hit me. I am using MS Word to keep notes. Cut& paste commands. Screenshots.
Copy in Raspberry, paste in Word. Copy in Word, paste in Raspberry.

Word is very nice to help me format my text to make it look nice.
And subtly changing the text. Like changing a a short dash (-) to a long dash (–). 

And the evil bit: when you copy the long or short dash to the prompt you don’t see any difference.

Do you spot a difference between the following lines?  In Word you can...on the shell you cannot:

mysql -h database.instance.eu-central-1.rds.amazonaws.com -u espmaster –p   

mysql -h database.instance.eu-central-1.rds.amazonaws.com -u espmaster -p

So, I learned that a –  is not the same as a  -.  Even if it looks identical.

long and short dash are not the same...

Who is your smart home talking to?

SmartTVs are using Google Analytics. Amazon Alexa/FireTV listens to your voice. Security cameras watch your living room. Temperature sensors detect your presence. Your HP printer orders the next cartridge when you printed enough pages.

All of these devices are now on your home network. Connected to the internet. Calling home. Day and night.

Do you know what is going on in your home network? 

I certainly did not. I wanted to find out. I wanted to learn, to play, to experiment. I did not want to spend a lot of money.

Still with me? Then let’s go exploring the shadow world of the IoT — Internet of Things.

Introducing my Home Network

Now let me introduce my home network. Fairly average, I guess you’ll find a pretty similar setup in your home as well. It is amazing how many devices end up online, even if your fridge and TV are not “smart” yet.

My Initial Home Network — Fairly Similar To Yours?

If you want to analyze all Internet traffic you need to do it somewhere between cable/DSL modem and your local network.

Learning to love Linux (and hate Wifi routers)

I wanted to log all Internet traffic. I had a Raspberry Pi available, so I set up a firewall/proxy on the Raspberry Pi, but performance simply sucked. Okay for isolated analysis, but certainly not good enough for everyday usage.

Next step was to install Linux/OpenWRT on a cheap 40€ WifiRouter (TP-Link 3600). Worked pretty well, even managed to sniff network traffic with tshark. Transparent Proxy was a bit tricky, which did not work for HTTPS and performance was not acceptable.

Then I remembered Domain Name Services (DNS) — the tiny service that translates internet addresses (www.domain.com) to its IP address (127.0.0.1). Pretty much all Internet transactions require name resolution via DNS — and logging these is quite simple, without impacting network performance (I’ll describe how, further down).

Logging DNS requests — even when I am sleeping

Just one tiny problem: Network Address Translation (NAT)

All consumer grade Wifi routers have NAT built in — which makes it impossible to identify which of your devices are talking, because they all show up with the same IP address.

Additionally, most Wifi devices route all DNS-requests to your customitzed destination and do not tell the devices to go directly to the DNS server. Again, your own DNS server cannot separate the clients as they all use the router IP.

Learning to love real Wifi Access Points (AP)

Finally, ARStechnica motivated me to ditch my Wifi router for a real Wifi Access Point. Cost was a bit steep at first (200€ for a dual-band Ubiquitiy AP), but the features and performance rocked. Wireless performance is much much better — all over our house. Opening a guest network with access code a breeze. And I did not miss all these other useless features( USB storage on a Wifi router? DECT telephones when I already have a base station?).

Home Network — with added Raspberry PI and new WiFi AP

Besides improving my WiFi reception throughout the house, the new AP is not messing with my network traffic, allowing me to track and analyze each individual device, wired or wireless.

Learning to love Linux (and DNSMASQ)

Now, let’s get back to Internet traffic logging. As mentioned earlier, I want to log every internet interaction for every client on my home network.

DNSMASQ to the rescue — a simple DNS+DHCP server that can easily be installed on a Raspberry Pi. With DHCP I can control which client gets what IP address, which gateway and DNS servers to use. And with the built-in DNS server I can log (and manipulate) every DNS request.

dnsmasq.log detailing every DNS query

With DNSMASQ DHCP options I can select which devices send their DNS requests directly to the Internet (labelled "GREEN" network) or to my Raspberry Pi logger (labelled "RED" network). There is no need to change the settings manually on every device!

Green and Red network settings provided via DHCP options

And the winners are…

Through this journey I have learned a lot, spent a few evenings not watching TV and spent some money (hey, it’s always fun to play with new toys!).

What did I learn throughout this journey?

• Consumer grade equipment is for consumers.
  (if you want more, you need to spend more...time)
• Linux and the Internet are truly powerful. And complex.
  (Always compare tutorials…some of them are not correct).
• Network protocols are actually quite easy.
  (Have fun exploring DHCP options!)
• Goofing around with a Raspberry PI is like grown-up LEGO
  (assembly instructions on the Internet)

…advertising and content networks.

Now, who did you expect your devices to talk to?  How are Facebook and Google making money? Come on….don’t be surprised. 

Here is aggregated data from my home network over the last couple of days (I manually categorized the domains):

DNS Queries by category

Where to go from here?

Now that I have spent plenty of time on understanding my home network, what shall I do next?

• Block Advertising/Tracking networks
  (just add the domains to /etc/hosts)
• Sniff & manipulate network traffic
  (what data is actually sent?
  replace all advertising with corn flower blue pictures?)
• Publish detailed technical step-by-step tutorial
  (cut & paste from the linked articles)
• …?

Your feedback, questions and ideas are appreciated!