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!