My normal combination on the big-server side would be Apache + MySQL (or PostgreSQL), but on the RPi this seems to be absolute overkill. For data-logging operations I would not use the local system anyway (looking at MQTT as well as Remote MongoDB datastore via REST Webservices).
After some poking around and reading up on the options I decided to go for the following combo: LightHTTPD + SQLite. Both are lightweight replacement of their fully-featured big-server counterparts (Apache HTTP & MySQL) and have very familiar configurations. There would be other options that have even less resource usage, but I really don’t have the time to start from scratch somewhere.
Another reason to go for this combination is that these are very well supported systems with regular security audits. Even though I am not planning to use my RPi’s for anything mission-critical this is always worth a consideration as you don’t need to unnecessarily introduce vulnerabilities to your network.
Install & configure LightHTTPD
sudo apt-get install lighttpd php5 php5-cgi php5-sqlite
sudo lighty-enable-mod fastcgi
sudo lighty-enable-mod fastcgi-php
Further config changes can be also made via the config file.
sudo vim /etc/lighttpd/lighttpd.conf
sudo service lighttpd force-reload
Install & configure SQLite
sudo apt-get install sqlite3
sqlite3 /home/username/database_name.db
All other commands are standard SQL from the ‘sqlite>’ command prompt or via SQL scripts like
sqlite3 /home/username/database_name.db < sql_script.sql
Access Databases from the webserver (using PHP)
< ?php
$db = new SQLite3('mysqlitedb.db');
$results = $db->query('SELECT bar FROM foo');
while ($row = $results->fetchArray()) {
var_dump($row);
}
?>
See PHP: SQLite3::query – Manual for more details.
