SQLite Forum

Distributed backups from a database of databases

Distributed backups from a database of databases

(1) By anonymous on 2021-12-21 15:05:43 [link] [source]

Would it be possible to store SQLite databases within an MySQL database? The clients would be able to download the SQLite databases as needed, and cache them locally. That would not only save resources like web cache, but act as backup of the database that is distributed across multiple clients.

I've posted on reddit, but didn't get a response. It obviously wouldn't work for terabytes of data, but would just be a different way to query a database and back it up at the same time.

(2) By Richard Damon (RichardDamon) on 2021-12-21 15:37:23 in reply to 1 [link] [source]

An SQLite database is just a file of bytes, so you can store that in a MySQL database.

The biggest issue is that if two clients download the database and both make changes, there is no 'easy' way to create a new database that combines the changes.

If the clients only treat it as a read-only database or expect any changes they make to be transitory until a later version is downloaded, then there shouldn't be a problem.

(5) By anonymous on 2021-12-21 18:48:43 in reply to 2 [link] [source]

Yeah, you got it! There are different types of databases out there, and this combines backups and cache. Would it be worth it for someone to create a database like this, or do you think there isn't really any use for it? It's literally a database query... and uses SQLite.

(3) By Simon Slavin (slavin) on 2021-12-21 16:42:06 in reply to 1 [link] [source]

A closed SQLite database all fits in one file. So just write code to read that file and store it however you want to store it, probably as a sequence of octets. Be certain that the file is closed - there are no open connections to it.

An alternative way to do it is to have SQLite serialize the database:


This doesn't need the database to be closed, but it takes up a lot of memory.

By the way, instead of storing the SQLite databases in a MySQL database, had you considered storing the SQLite databases in a SQLite database ?

(4) By Richard Damon (RichardDamon) on 2021-12-21 17:06:27 in reply to 3 [source]

Perhaps to be pedantic, a CLEANLY CLOSED SQLite database will be a single file. There are ways to end the program using SQLite which leave a log file that needs to be kept with the database to avoid corruption.

My understanding is the OP is looking at network distribution of the SQLite Database, and accessing an SQLite Database over a network can be a fussy operation.

(6) By Warren Young (wyoung) on 2021-12-21 18:55:42 in reply to 1 [link] [source]

If you want a distributed SQLite database, why do it that way? Use one of the several distributed SQLite variants and be done with it. The major ones are BedrockDB, rqlite, and dqlite. Now all machines in your network have a copy of the database, and it's kept up-to-date.

(7) By anonymous on 2021-12-21 19:11:59 in reply to 6 [link] [source]

Okay, my mind has been blown. I had no idea there was such a thing. This database thing is real. Thanks!