This is the third part of the series Save yourself from a disaster: Redundancy on a budget.
How can we make sure our most important asset (which is the DB) is safely secured in case of a disaster?
We could do mainly 2 things (and you better do both of them):
- Backups
- Redundancy
This guide won’t cover everything, it won’t be a comprehensive guide, and the steps that are shown need to be carefully reviewed and tested in your development/pre-production environment. I don’t take any responsibility for any damage, interruption of service nor leak/loss of data for the use of the instructions in the ebook (nor from any external website I’ve mentioned).
Redundancy
Create a cluster to have at least a structure like master/slave primary/secondary, 3 nodes will be recommended so we’ll have the flexibility to do planned maintenance without suffering and/or affecting the performance of the whole cluster.
Spin up a secondary node
Create another VM somewhere else (better if in another availability zone/region/provider), then configure a MySQL/MariaDB/Percona/… instance and plug it in as a secondary node.
We can set it up even with fewer resources and make it the write-only node (in case we have less writing activity, otherwise the read-only one).
Balancing requests
I prefer to use something like HAProxy as a TCP load balancer, or (even better) using ProxySQL (which has a nice query caching capability). I’d go with ProxySQL load balancing the 2 nodes created, then just change the database connection string in the application and the setup is done (we could even partition the queries and define to which node they should be sent).
In my case, a primary/secondary topology could more than enough, but I went for a primary/primary configuration (you can follow a simple tutorial or a more structured configuration) without balancing (because each web node will access their local DB instance).
Security
The replica must be done over a secure connection, so you need to generate a certificate and use it.
The next post will be about Redundancy of Storage, Stay Tuned.
Check out the whole version of this post in the ebook.
