1

I know, the question may sound weird at first but let me explain some things, I'm sure you will understand my problem after that:

I have a raspberry pi recording all data from my weather station at home. All this data gets stored in a MariaDB database. It gets updated every minute. The raspberry pi is not visible from the outside.

Now I want to replicate this database to my webserver. It hasn't to be totally in sync, a delay of 1-5 minutes would be okay (if there is no better solution).

My problem is that the master is the client and the slave is the server. I cannot (and I don't want to) make the client visible from the outside. Is there any solution to my problem?

I created the following image to visualize my idea:

My Idea

MeineHTMLCodes
  • 121
  • 5
  • 1
    AFAIK all MariaDB and MySQL replication has the replica pulling the data from the source, rather than the source pushing the data to the replica. So native replication is not an option. Instead I would modify the code that polls your weather station and records the values in your local database to simply make a second record on the webserver. – Bob Feb 22 '21 at 10:41
  • How can I do that? How can I connect to my database on the webserver? – MeineHTMLCodes Feb 22 '21 at 10:46
  • 1
    Connect it to the network? This really is not on topic here though. – Michael Hampton Feb 22 '21 at 16:31
  • Okay I'll read more about this topic. Thanks for your help! – MeineHTMLCodes Feb 23 '21 at 07:45

1 Answers1

1

The PI has a MariaDB (or MySQL) Primary (aka "Master"), correct? And you are thinking about having a Replica in the "public network.

As already mentioned, the Replica "pulls" from the Primary, which is not easy because of the network setup. But it is possible if you are on Linux -- Have the PI set up an "ssh tunnel".

You imply that the connection is flaky? The PI would have to be able to notice the failure and reestablish the connection when necessary.

Another approach avoids multiple issues -- Skip the replication concept. After all, what use do you have on the PI other than holding the data for transfer to the real database.

It should not be a problem for the PI to send an HTTP request to a 'public' webserver. It would be a small amount of PHP (or other) code to store one minute's worth of readings in the database. (One request/minute is trivial; no problem.)

1-5 minutes' delay? I don't see that happening in any of the plans. Even if it did, simply lose the data. Weather-like data (including, perhaps, swimming pool depth) does not change rapidly enough to warrant even one-per-minute.

On another topic -- check out how much data there will ultimately be. In a year there will be 0.5M temperatures. Try sending that much to a graphing program; it may choke. I suggest you summarize the data, either as it comes in, or on, say, an hourly basis -- min, max, last. For average, keep the sum and count; then compute the average as sum-of-sums / sum-of-counts.

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • Thank you for your detailed explanations. I'm going to skip the replication concept. I think I'll do it using my own python program. Nethertheless I want to keep the 1 minute interval. If you would love the weather like me, you would understand why :D To avoid the mentioned problems I'm going to create two databases. The first database stores the recent week's data using an 1 minute interval, the second stores all data ever recorded using an 1 hour interval, like you suggested. – MeineHTMLCodes Feb 24 '21 at 19:10
  • @MeineHTMLCodes - Perhaps you mean "table", not "database"? Whether to have both a small by-hour table _and_ a big by_minute table: It depends on the queries to be used against them and disk size and performance issues. Do the math: 100 bytes/row times number of years times 500K minutes/year. – Rick James Feb 24 '21 at 20:35