I’ve been looking into Strapi for some time now and am not sure whether or not it will work in my case.
So this is the planned setup:
I am building a web app and which has to be of high availability. By that I mean that the databases will be stored on several servers to ensure maximum up-time. I am planning to have at least 3 MySQL databases (Master - Slave - Slave) in the beginning and possibly more in the future.
My question: How should I set up the data transfer (copying) from database to database?
I could do it the MySQL way, but I am scared that Strapi might not be okay with that.
The other way I thought of was writing something like a cron job to mirror the databases from one server to another through Strapi API, however I think that might be more resource intensive.
Has anyone had a similar problem? What should I do?
I would suggest MariaDB Galera clusters over MySQL clustering, and if you really want to take it to the next level using ProxySQL to split read/writes.
This is a bit of an old network/software layout I did for my previous deployment of a hobby project, but is still quite a powerful layout for both load balancing and fault tolerance (minus my firewall being a single point of failure, but it was a bare metal deployment)
I had 3 “Virtual Machine hosts” running XCP-NG (Xenserver FOSS Fork)
Each of the Strapi, Nginx, and ProxySQL (minus 1) where distributed on the 3 hosts
I had 2 Hosts dedicated to the database and 1 virtual machine
Each of the dedicated DB hosts held one Write and one Read DB VM (these hosts had a lot of SSD based storage for speed)
The Arbitrator node was in a virtual machine on the primary VM pool as it only handled Backups/Scripts/arbitration and would never see application traffic unless the other 4 died (in which case I much bigger problems)
ProxySQL handles the bulk read/write split and connection pools with an active/failure config for maintenance downtime.
Over the course of 3 years I had a grand total of about 30 minutes of downtime (hard down). And at peak traffic I was handling between 3 to 4 million requests per month or about 1.6 requests per second (It was much higher during active load times as I typically saw a decrease in traffic for about an 8 hour period).
Each of the Strapi virtual machines ran a PM2 cluster of 4 nodes for a grand total of 12 Strapi backend “instances” (admin panel was served as a static resource from Nginx).
All of this is for a hobby project as a 3rd party API for a video game called Elite Dangerous
To answer the bulk of your question, Strapi supports whatever Knex and Tarn support (minus certain other databases like MSSQL/Oracle SQL). While I have not tested MySQL clustering because I find the single master system a bit dated, if Knex supports it then Strapi will. If all else fails:
I ended up going for a slightly easier solution with a Galera cluster of MySQL databases without ProxySQL and HAProxy layers.
One thing I quickly understood is that even though I make changes in the db cluster through one Strapi, the other Strapis will only see updates from existing tables but not new tables (I mean Collection Types). I guess I have to copy the code that was generated by the first Strapi into the other so it would know how to read that data from the database.
