Using Multiple MySQL databases

[Seth Ratner]

Simple concept, but I'd like to use one local and one network db. Poosible?

Seems easy enough to add the second database to the config, but I don't know if [DataBindings] can accept the second database. 

Anyone tried this?

Seth

[Seth Ratner]

Looks like I can just add another binding in [DataBingings], but in [StdArchive] I'm not sure I can have two archive processes run.

I think these sections are all fine.

[DataBindings]
    [[wx_binding]]
        database = archive_mysql
        table_name = archive
        manager = weewx.manager.DaySummaryManager
        schema = schemas.wview_extended.schema
    [[backup_binding]]
        database = archive_backup
        table_name = archive
        manager = weewx.manager.DaySummaryManager
        schema = schemas.wview_extended.schema
##############################################################################
[Databases]

    [[archive_sqlite]]
        database_name = weewx.sdb
        database_type = SQLite

    # MySQL
    [[archive_mysql]]
        database_name = weewx
        database_type = MySQL

    [[archive_backup]]
        database_name = weewx
        database_type = MySQL2
##############################################################################

#   This section defines defaults for the different types of databases.

[DatabaseTypes]

    [[SQLite]]
        driver = weedb.sqlite
        # Directory in which the database files are located
        SQLITE_ROOT = /var/lib/weewx

    # Defaults for MySQL databases
    [[MySQL]]
        driver = weedb.mysql
        host = localhost
        user = weewx
        password = gt9drSvlB3ImZGS

    [[MySQL2]]
        driver = weedb.mysql

        host = 192.168.30.110
        user = weewx
        password = test

But the [StdArchive] section only accepts one binding as far as I can tell:

[StdArchive]
    archive_interval = 300
    record_generation = software
    loop_hilo = True
    data_binding = wx_binding      <-- This is where I think I would need to be able to list two bindings... right?
    log_success = True
    log_failure = True

[Doug Jenkins]

I am not understanding the use case. If you are running MySQL both locally and remotely for the archive, why don't you just replicate the local MySQL instance to the remote on a schedule?

I run multiple weewx databases (aqi, forecast, archive) on mariadb and just back it up daily to my NAS. If I needed a remote db, I would just restore my backup on a separate instance.

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/34a25b20-3d32-485d-a1e8-e627dcb712a0n%40googlegroups.com.

[Seth Ratner]

That's an option, though I would want the databases to be mirrored +/- one or two archive intervals, and that might get messy with mariabackup. I have some services that will run at another location (Home Server) using the archive data, but due to a bad internet connection I can't rely on accessing the database on the Weewx computer (Farm) directly. 

I was thinking the local db would use SQLite which reduces the load on the Rasperry Pi Zero, and MySQL for the remote db. 

Perhaps there is a simple way to mirror the two separated databases?

Separately, could you point me to any documentation on the AQI and forecast databases in WeeWX? I'd like to go down that rabbithole a bit.

Cheers,

Seth

[vince]

On Wednesday, September 14, 2022 at 3:50:16 PM UTC-7 Seth Ratner wrote:

That's an option, though I would want the databases to be mirrored +/- one or two archive intervals, and that might get messy with mariabackup. I have some services that will run at another location (Home Server) using the archive data, but due to a bad internet connection I can't rely on accessing the database on the Weewx computer (Farm) directly. 

Not understanding the 'why' at all on this one...but perhaps MQTT might be one possibility.  Publish from the weewx site and subscribe on the other site.

 

Separately, could you point me to any documentation on the AQI and forecast databases in WeeWX? I'd like to go down that rabbithole a bit.

There are no such things in core weewx.  You have to pick your extension(s) of choice and check out the code, or alternately spin up a simulator VM and add them to a throwaway instance of weewx perhaps...

[Seth Ratner]

My Weewx is somewhere with a pretty terrible internet connection. Disconnects a lot. So I need a database on the WeeWx host, or I'd lose a ton of intervals. But I also run various programs at home that reference the archive, and they won't work when the WeeWx host has lost internet. 

So I need two copies of the archive available at all times, one for each location. 

I use mariabackup, however that doesn't clone the database so much as package it. It needs to be extracted to be used, and that would be impractical to do every 5-10 minutes. There might be a way to mirror a database (without querying the entire db each time), but I don't know it. I'm open to ideas, and I really appreciate everyone taking the time to respond. 

Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/PPGSbaoX_ns/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/92149663-4379-405c-b29f-f62efca19362n%40googlegroups.com.

[p q]

https://stackoverflow.com/questions/16032825/method-to-replicate-sqlite-database-across-multiple-servers

You received this message because you are subscribed to the Google Groups "weewx-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAHTssjNabT8X%2B304Nv9Dv1mGTbaArgnXRsiz%2B66CdjEZ%3DHMw5A%40mail.gmail.com.

--

Peter Quinn
(415)794-2264

[vince]

On Wednesday, September 14, 2022 at 4:47:53 PM UTC-7 Seth Ratner wrote:

But I also run various programs at home that reference the archive, and they won't work when the WeeWx host has lost internet. 

So I need two copies of the archive available at all times, one for each location. 

Still unclear why without knowing what you're running at the non-weewx site.   Can you run that on the weewx host and upload the results to home occasionally ?   Does the non-weewx site need to be totally up to date always ? Is your internet 'slow' in terms of bandwidth or just unstable ?

You're jumping to a solution but not really explaining the problem(s) and why you have a split setup.....

[Doug Jenkins]

Seth:

I think you have a couple of options to choose from. One concern I would have with MQTT is that it always publishes the latest record, so if your farm location is down for 1 hour, your remote will not get the records up to the last archive.

thinking about your use case now, It may make sense to setup the station on SQLite and rsync the database to your remote location after the archive period. that way if your farm location lost connection, you would know based on the last archive record at home and once you have a connection again, you would get all the data.

here is a wiki article on the github weewx repo that kind of explains this : Using the RSYNC skin as a backup solution · weewx/weewx Wiki (github.com)

To answer your question about my other databases, for forecast and AQI, as Vince indicated earlier, there are a host of extensions available. The one I use for forecast is Weewx-Forecast by Matthew Wall ( matthewwall/weewx-forecast: forecasting extension for weewx (github.com)) and I use a AQI extension to pull from my Davis Airlink AQI device ( chaunceygardiner/weewx-airlink: WeeWX extension to support Davis AirLink air quality sensor. (github.com)) 

hope this helps!

Doug Jenkins

[Seth Ratner]

Thanks Doug and Vince,

It looks like the best solution is MySQL replication. I had been using the backup option without realizing replication was built in. This will be more than adequate, and will full account for the internet going in and out between the locations. 

I also appreciate the tip on the extensions. I think the forecast one will be useful in my integrations with Home Assistant.

Cheers!

--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/PPGSbaoX_ns/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/9e98036d-592b-4ff4-8c7c-dc9ecbe3a7ffn%40googlegroups.com.

[Neville Davis]

Hi

Yes I use SQL replication to a spare Raspberry pi, its is a live backup and I also use it as a second source of data in another weewx project I am building.

I have used replication for years (on QNAP NAS and now Pi), and it is extremely reliable and recovers from power failures, device shutdowns etc without issue so far.

Neville

[Seth Ratner]

Yup, replication is what I wanted. Thanks everyone for helping me get to the right answer eventually!