Raspberry Pi – Controlling Using MySQL Database

So I set up one Raspberry pi as a LAMP server then created a test database. I then created a basic html page with a button and PHP script to modify a value in the test database from the web server. I setup another Raspberry Pi with a test loop to control an LED based on the value in the mysql database. After some tweaks, I have it working to where one Raspberry pi can pull data from the Mysql database and I can tweak it via the webpage. More work to come as I believe I can use the LAMP server as a configuration database along with a data repository for logging.

4 thoughts on “Raspberry Pi – Controlling Using MySQL Database

  1. RonRon

    Now what you need to do is write a PHP Web Service to deliver configuration data from the LAMP server to each individual Pi. Pi makes the call to the web service with a unique ID, and the web service returns the configuration data. This can be used to hide the DB server from prying eyes if the only machine that can talk to it is the LAMP server, and everything else has to go through the web service to get data.

  2. ZZ Post author

    Great idea. I’ll probably bug you as I get further as I’ve got some ideas on what I want to do but don’t have much experience related to databases. I don’t think I’m going to need much for the applications I want to utilize the Raspberry Pis for but I want to set the tables up as efficiently as possible. One item I need to think about a little more is if there’s a way I can push changes to a remote Pi when the user makes configuration changes on the web server. If I can’t do that “easily”, I’m thinking I can have the remote Raspberry Pi poll the database on some sort of time interval to see if things have changed. I figure I can have a flag in the database that tells the Pi data has changed so update then the Pi can reset the flag and keep checking. This seems inefficient but as it would be better if there was a way the server could tell the remote Pi a change has occurred so go ahead and update. This probably isn’t a huge deal with everything I want to do but could be an issue depending on how the Raspberry Pi LAMP server can handle the frequent remote requests.

    1. RonRon

      I’ve got a new Pi coming in the mail in the next week or so (along with a bunch of other stuff from Adafruit). I’ll see what I can do to ping the Pi that a change as happened, and see if I can get the Pi to update it’s config values, without having to poll the db server.

      This will be written in C/C++, since I’m not even a beginner in Python. I’ll let you know when I get something that seems to work.

      1. ZZ Post author

        Cool! Let me know if you get it working. If there’s a way with C/C++ hopefully there’s a similar way with Python. I think I’m closer to working more on this side of things if I can get this CT dialed in. I want to hook it up to our water heater for a month to log the usage before I try controlling it. I’m planning on dumping the data into the mysql database.

Leave a Reply