For synchronization extra layer might be required, something like http://www.rabbitmq.com/. Not entirely sure if RabbitMQ is good for this task, but there are alternatives.

Potential problems are that databases will have same data, but not in same order between masters.

Can device communicate over HTTPS and have built in certificate validation?

Edit: Seems like that would be too much for micro-controller 😛

• This reply was modified 10 years, 4 months ago by Hexide.

I had similar project going where program executable had to had unique key in it before all users downloaded.
My approach to this was to take and compile 4 different excecutables with different known keys in them, then using hexedit find differences between those files. Knowing where each difference was I was able to write relatively small script ( in php ) which used single compiled file and replaced key inside of it before it was served to user.

Maybe similar strategy could be used here.
To generate binaries with unique keys, record those keys to database.

Then it would be in human hands to get bin images inside controllers.

>A cron script to compute average/month, ran once per month?
Yes, it could be. However if there will me many devices, high load will be generated on server. So to prevent this from happening there are couple ways:

1. Calculate each average with intervals, this will take longer for data to be generated but it works well.
2. Load entire months worth of data into a memory table and request it from there.
3. Load entire months worth of data into script variable and use it within script.
4. Have a read-only mysql replication and use it to query data.

Once averages are collected, raw data for previous month can be moved into archive table ( MySQL has special storage engine for this ), but downside of this engine is that we can’t search for data ( use WHERE clause ). So regular table should be used for archiving.

When displaying full historical graph, we can cache all data for at least few hours. So data from database it’s requested every few hours for single device.

There is nice trick that DNS has:
You can simply create multiple DNS A records for same data domain with different ip addresses and client will round robbin across until working one is located.

Here is good example with google DNS records: https://och.re/x1hICjzW6VmXd1O2UlXWBdqEvqcW9OGR/file.txt

Hello,

Data aggregation will be pretty much mandatory when amount of devices increases.
But using views to get data for any time period from main data table ( which contains raw data ) is extremely CPU and I/O intensive. So for example requesting 12 months of data for specific device still requires mysql to go through all 525600 ( 60x24x365 ) records for said device and calculating averages for each month. Which is still 43800 times more work ( excluding time which takes to grab averages ) than just selecting aggregated rows from table containing monthly averages/minimums/maximums. This data would get there at the end of every month via cron script.