The database behind every site keeps answering when one machine fails
Every site we host stores its data in one central database cluster on our hardware in Tyrol: the Tagfalter sites and shops, the WordPress sites, the community sites and Nextcloud. The cluster is three machines, and each of them holds the complete data of every site. When one of the three fails, the sites keep answering. Nobody has to do anything.
I built it this way because of how the company works. The data stays in Tyrol on our own disks, and I am the only person who runs the platform. So the database had to survive a lost machine on its own, and the backups had to run on their own too.
Three copies of every write
The cluster runs MariaDB with Galera, a replication mode where all three nodes are equal. When a shop writes an order, the write is replicated to the other two nodes synchronously, so it does not count as done until all three have it.
The three database nodes run inside our Kubernetes cluster, on machines reserved for databases, and they keep their data on the local disks of those machines. The shared cluster storage that the rest of the platform uses would have been simpler to manage. I chose local disks because of latency: a shop feels every millisecond the database waits for a disk, and the local disk is the shortest path.
A proxy decides where each query goes
No site connects to a database node by itself. In front of the three nodes sits a database proxy (MaxScale), and the proxy itself runs three times. It checks every database node every two seconds, sends writes to one node and spreads reads over the others. When a node becomes unhealthy, the proxy stops sending traffic to it without anybody doing anything, and the sites see nothing of it.
One setting of this proxy took me a while to get right. A page that writes something and reads it back right away must not get old data from a node that is a moment behind. The first setting I ran made the proxy wait for that node to catch up. That worked on a quiet site. Under many shop orders at once, pages stalled for exactly ten, twenty or thirty seconds. The setting we run now sends such a read to a node that is already up to date. I tested it on the test sites first and only then moved it to production.
Inside the cluster each site has its own database and its own database user. One site cannot read another site's tables.
Backups every three hours, in another facility
A backup job dumps every database every three hours, each database into its own compressed file, plus one dump of everything. A dump is written under a temporary name and renamed only when it is complete, so a half written file never looks like a backup. A dump that fails is retried three times. The files go to a storage system in another facility, so a problem in the data center does not take the backups with it.
The dumps are thinned out by age. Every three-hourly dump is kept for three days, then one per day for 30 days, one per week for 90 days and one per month for two years. A site owner who notices a deleted page two days later has eight dumps from the day it happened to choose from.
The cleanup of these old dumps once failed without a sound. It was written in a way that stopped working beyond roughly twenty thousand files, and the backup store had grown to several terabytes before I saw it. I rewrote the cleanup so the number of files no longer matters.
Restoring one database is one command: it validates the dump file, asks for a typed confirmation and imports. The same tool checks the health of the cluster and can restart the nodes one after the other. For the case that all three nodes are gone there is a written procedure for restoring the whole cluster from nothing. The cluster and the proxy report into our monitoring, so a node that drops out shows up on the same screen as everything else on the platform.
This cluster carries the databases of every Tagfalter site and shop, of the WordPress sites we host, the community sites and Nextcloud. There is no open item on it at the moment. What I watch is the size of the backup store, because the cleanup failed quietly once, and I think the full restore from nothing is the part I trust least, since it is a written procedure that I run rarely.