Master & Slave database setup with Strapi

System Information
  • Strapi Version: 3.4.4
  • Operating System: Ubuntu 18.04
  • Database: MySQL 8.0
  • Node Version: 12.20.1
  • NPM Version: 6.14.10

Hey everyone!

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)

The driving factor in why I ran this layout was:

  • I had a physical firewall (PFSense)
  • 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 :stuck_out_tongue:

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:

FYI some resources to research if interested:

Severalnines for an awesome monitoring solution (and even deployment in their EE versions) =>
Percona (don’t recommend their database/clustering but they have some really awesome tooling) => MySQL Tools and Management Software to Perform System Tasks by Percona
ProxySQL Example with Galera Clusters => Native Galera Support In ProxySQL - ProxySQL
HAProxy as a TCP proxy alternative to ProxySQL just no read/write splitting =>

Thank you @DMehaffy for your help!

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.

Hey Mike, can you share the setup code for strapi because I’m new in strapi and need same configuration for my strapi project.

Thank you.