Simultaneous clone to mySQL for weewx and weewx-WD?

[Chris Alemany]

Hi all,

I think I've seen this discussion embedded elsewhere but I'm not sure so I'll ask it by itself.

Is it possible to have a concurrent 'clone' of the local database and a remote database?

For background: While using wview I used the standard sqlite database on the local machine connected to my Vantage Pro II system and then wview would periodically (5min) update a remote mySQL database on my web server through cron.

I see that weeWX can be configured to use SQLIte "or" mySQL ... the simple way to ask it then I guess is: Can WeeWX (and by extension weewx-WD) use SQLite *and* mySQL at the same time?

I like the SQLite file on the local machine because it greatly simplifies backup but there are obvious advantages to a 'real' mySQL database on the remote/web end and now with WeeWX-WD extension creating a 2nd local database in parallel, is it best to go all mySQL or?

Advice appreciated!

Hope all have rung in the New Year well! :)

Cheers,

Chris

[vince]

On Monday, January 1, 2018 at 12:56:27 PM UTC-8, Chris Alemany wrote:

Is it possible to have a concurrent 'clone' of the local database and a remote database?

 

Switching to mysql will get you that kind of functionality, albeit at the added complexity and compute capacity needs that mysql brings with it.

(cloud example - amazon RDS with multi AZ enabled is essentially this)

I guess the question is how important it is to you.  Many folks just make periodic db dumps of their sqlite3 db(s) and stash them in Dropbox or the like.  There's something to be said for the simplicity of sqlite3.

[Neville Davis]

Yes I for one use to just dump and save but when I decided to use mySql I also decided to use replication, which I set up on a QNAP NAS... it has worked without fault as a realtime backup of the db on my pi. 

Nev

[Chris Alemany]

So my understanding then is that it is an either/or proposition. Either you use sqlite or mysql, but never both?

--
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/xCWvbw-4l_I/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[gjr80]

The weeWX StdArchive service (the service that handles the database) uses a single database binding so a standard weeWX install will use one or the other. Thinking outside the box though, it may be possible to run a 2nd StdArchive service with a different binding (which is similar to how weeWX-WD supports its database) to use a db of another flavour, but it's not going to be a simple case of just change this setting and it's up and running. It's going to take a little development.

Gary

[vince]

On Wednesday, January 3, 2018 at 2:06:19 PM UTC-8, gjr80 wrote:

The weeWX StdArchive service (the service that handles the database) uses a single database binding so a standard weeWX install will use one or the other. Thinking outside the box though, it may be possible to run a 2nd StdArchive service with a different binding (which is similar to how weeWX-WD supports its database) to use a db of another flavour, but it's not going to be a simple case of just change this setting and it's up and running. It's going to take a little development.

Question I'd wonder about is 'why' - once you work hard enough to use mysql why not just use mysql ?

Or is there a use case I'm not seeing somewhere.... 

[Chris Alemany]

The why on keeping the sqlite database would be for its ‘local’ benefits. Easy backup, easy access, simple administration.  The remote site is a web server and so the mySQL database is more appropriate for that setting anyway and backups are harder to do from a user perspective.

[Glenn McKechnie]

I had a need for an sqlite database to be generated at the same time
as the mysql one that I actually use. It's flipped compared to your
case though - mysql is my main database, sqlite is the duplicate.

https://groups.google.com/forum/#!topic/weewx-development/-rOoK6oE1xY

I haven't taken it any further than that, very rough, version as it
works well for the intended purpose. ie: I get a simple database that
I can transfer easily and that allows the wxobs demo to work on the
remote, non weewx, machine. I also get to keep the main mysql
database for its other benefits.




Cheers
Glenn

rorpi - read only raspberry pi & various weewx addons
https://github.com/glennmckechnie

> 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

[vince]

On Wednesday, January 3, 2018 at 2:39:04 PM UTC-8, Chris Alemany wrote:

The why on keeping the sqlite database would be for its ‘local’ benefits. Easy backup, easy access, simple administration.  The remote site is a web server and so the mySQL database is more appropriate for that setting anyway and backups are harder to do from a user perspective.

Why have a db on the remote webserver at all ?

I use sqlite on my LAN-only weewx box, and the 'rsync' feature in weewx to push the locally-generated output files and  up to my Amazon lightsail internet-facing VM.  No need for any database at all on the Internet VM, just nginx serving the files. 

[Christopher Joseph Alemany]

To enable dynamic, database driven web content, graphs etc. (Like Wunderground or any of the other larger system do)

Chris Alemany

3854 6th Ave

Port Alberni, BC

V9Y4M2

h:250-723-0889

w: 250-753-3245 x2106

m: 250-731-7930

e: chri...@gmail.com

w: www.chrisalemany.ca

w: www.alberniweather.ca

w: www.vifreightbyrail.ca

--

[Huw Pearce]

Chris,
You might like to look at the following:

Sqlite-sync is a framework for synchronizing data between a Sqlite database and an MS SQL/MySQL/Oracle/PostgreSQL database. With this framework your application can work completely offline (Airplane Mode), then perform an automated Bidirectional Synchronization when an internet connection becomes available.

SQLite-sync - ampliapps.com

https://ampliapps.com/sqlite-sync/

Kind regards,
Huw

[Chris Alemany]

Oooh, interesting!  Thank you for this. Will check it out!

Chris

[vince]

On Friday, January 5, 2018 at 11:06:27 AM UTC-8, Huw Pearce wrote:

SQLite-sync - ampliapps.com

https://ampliapps.com/sqlite-sync/

yeccch - requires tomcat and it's binary only

cool idea though.... 

[Les Niles]

I wrote a little tool for syncing weewx data from one database to another, which I think can address Chris’ cloning request.  It’s weewx-specific — when run, it copies all archive records from the source database that are later than the latest record in the destination database, to the destination database.  The motivation was to do incremental backups to a remote mysql database, though mysql’s built-in cloning might be better for that.  But it uses weewx’s database interface so it is supports the same types of databases and access that weewx does, with the same configuration files.  To do the cloning it just needs to get fired off after every archive interval, either from a cron job or by packaging as a report generator.  

To use from a cron job or manually, put it in the same directory as wee_reports, wee_extension, etc.  The comment describes the config file(s) that specify the source and destination. The intent is that weewx.conf will specify the source in the usual case where the source is the active weewx database.  The destination specification can be added to weewx.conf, or put in a separate config file.  

  -Les

--
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.

[Chris Alemany]

This is great Les thank you! That sounds very much like a tool that could work.  Do you have an example dbackup.conf I could use?

[Les Niles]

Yes,  I forgot to attach the conf file the first time.  The attached has examples for both mysql and sqlite, for both source & destination.  For mysql, the host, user, and password of course need to be filled in; likewise for the path(s) to the sqlite database file(s).  The database name for a mysql destination can be whatever you want; in particular, “weewx” would work fine if you’ve got another weewx installation reading from that db.

  -Les

[Chris Alemany]

Thanks! I'll let you know how it goes!

[Chris Alemany]

initial run is on the go... 11,000 of 1.2 million records already done.  Gonna be a minute or 500 :)

[Les Niles]

Yup, I should’ve mentioned that it’s not particularly fast.  But that’s OK, it’s not well tested either. :)

Let me know if it works out for you, and if any issues crop up.  

  -Les

[Chris Alemany]

That's ok! It is only marginally slower than wee-database for making the 'big' initial transfer.

Because it was so slow (for both) I actually installed mysql locally, and used wee-database --transfer to make the initial transfer.

Note:  I could not get wee-database to work using "localhost" as the hostname. It worked fine as soon as I used my local IP address instead. Not sure what was going on there but it stumped me for awhile.

Anyway, it was made the transfer in just a few minutes, then I dumped the database with mysqldump, transferred to my remote server, again, much faster over sftp :)... and then imported it into the database on the remote server.

That took care of the speed issue.  Then your wee_dbbackup script worked just fine dandy and speed is was not an issue of course with just a couple records.

I do like your suggestion in the notes on setting up a 'read-only' user on the main weewx SQLite database. 

How do I do that?

Cheers! 

Chris

[Chris Alemany]

Question:

I'm using mwall's realtime Cumulus extension to produce text files from the loop data.

Is there anything stopping that extension from outputting to mysql/sqlite bindings instead?

Chris