Arduino and MySQL

Projects Programming
1

Hello

I have two arduinos for controlling my Reef Tank. Now I'm planning to make everything contollable via internet. I decided to use an raspberry pi or another small server with MySQL running on it. The Arduino should send every new Data like temperature or relay status to the mysql server where I can access it with am smartphone (maybe later with an app) or a browser. Also the arduino should get every new configs from the mysql server.
I wonder what would be the best way to access the MySQL database with an arduino. I found the MySQL Connector/Arduino which allows direct access to a database without php or something else.

So what would you do? Use the MySQL Connector/Arduino or another way like php?

best regards

2

hello,

MySQL Connector / Arduino you to actually direct access to the database with the advantage of speed.
That said, I did not Experienced.
it is always better to limit the processing of layers in communication with databases
best regards,

3

I built a temperature/humidity recording system using GET requests to push data into a MySQL database back before the Arduino connector was written.

For a new project, I'd consider the new connector so as to go direct to the database, but it looks like it consumes quite a lot of progmem and potentially RAM too. Fine for a Mega, might cause difficulties on an Uno if your existing sketch is already large. Try it & see.

4

I guess there's nothing wrong with using a traditional LAMP stack, but I do hear that your resouces on a RPi are limited and such a system may have your RPi running sluggish. You'll need to check on the RPi forums about perfomance.

Another option, is to use a different commuincations protocol between your Arduino's and the GNU/linux system like RPi, such as using Modbus, rather than using HTTP.

Using Modbus then allows you to choose between using your USB or if you have already, Ethernet on both of your Arduino's.

Then, on your RPi or what ever you choose, you write a C program, much as you do in your Arduino, that uses a modbus library to then request the data you wish when you wish from any connected device, in your case, your two Arduino's.

So, rather than using a heavy bloated Apaché and PHP and MySQL install on a limited RPi, your C program talks to your Arduino's and then stores the data, say, either in text files or in SQLite database, to then do as you please.

Actually, this is the project I have been working on since April 2014, called QuadlogSCADA.
The current project I'm developing is specifically for small control monitoring systems similar to what you are wanting, but is designed to also fit into small industrial situations.

Quadlog operates on Intel32/86 and ARM based systems like Cubieboard and I guess RPi as well, where it uses Modbus to talk to devices like Arduino via USB/RS485 or Ethernet or actually to any device that talks Modbus.

The project also has an inbuilt HTTP server so you can then access any of your data, be it real time data or stored hstorical data.
The code is very lightweight and there is no need for any LAMP install base, just a few libraries used by the main application.

The project is still in early days, and is under active development and I would say is not quite ready for general use, but is being tested on a couple of sites presently.

Anyhow, if you wish, you can read more and see an example at my signature below, which itself is running on Quadlog at my location.

Paul

5

The Two arduinos are communicating over i2c Bus. Only the Master will get an Ethernet shield and connected to my home network. I'm not sure yet if I use an Raspberry pi or maybe my wd Cloud NAS which also Supports mysql and PHP running on it. But I think a database like mysql would be the best way for data logging.
How the Android app gets the data is another question but First I want all data on a central point.

So the only question for me at the moment is what would be better and faster. Direct connection to the database from the Arduino with the special library or another way with PHP and http requests.

6

MaikB85 wrote:

So the only question for me at the moment is what would be better and faster.

You might like to define what is ment by 'better' and what is ment by 'faster' to you?

Paul

7

I meant what needs less ressources. So maybe what has more advantages. The lot of space the library needs is no problem because it is an Arduino mega 2560. I think with progmen the RAM usage would not be so high.

8

I'd use the connector then. No need for a special web page to allow the arduino to send its data.

9

I highly recommend the arduino connector. If you can get your hands on this book either in electronic or print format do so, it's got detailed information on how to use it.

I'm actually using it right now in a very similar setup as you referenced. I have a mega as master, uno as slave + ethernet. Mega collects sensor readings, sends over i2c to uno, which pushes data to an instance of mySQL on Amazon web services.

It's a very easy process, and not going thru amazon would make it even more simple.

10

I highly recommend the arduino connector.

On the other hand, making a GET request to a PHP script is quite straightforward, and the PHP script can do a lot more with the data you send it than the Arduino and connector library can.

If you want to build a web interface that accesses the same data, then you'll need PHP scripts anyway, so you might as well learn how to create them.

11

Thanks a lot. I will use the Arduino mysql connector library. The book looks great. I will try to get it.