I have got the opportunity recently to upgrade a clients server hardware (yay!) which is running Ignition and MariaDB servers.
My original architecture was a pair of physical servers, with the following configuration:
- Server 1
Ignition master gateway
SQL slave database
- Server 2
Ignition backup gateway (not redundant)
SQL master database
Replication of SQL databases was setup in a master-slave configuration, and backups were taken from the slave to avoid table lock issues with mysqldump.
I’ve noticed some load issues, and was contemplating trying to setup a cluster which can provide SQL load balancing and high availability redundancy.
Was thinking of setting it up like this:
Some things to discuss:
What good software tools are out there to achieve this? I have come across the following:
MariaDB SQL server, MySQL Cluster, keepalived, MariaDB MaxScale, Percona MySQL server, Percona XtraBackup for hot backups.
What are some good ways to backup large databases? complete VM backups? LVM backups? SQL Dumps?
What is the best way to setup SQL failover with Ignition? Use the failover settings in Ignition DB connection settings, or just let the load balancer handle it?
Best ways to transfer all the data from the old server to the new ones? mysqldump and restore? LVM snapshots?
Would love to hear your thoughts, i know there are some really switched on guys here with some great experience to draw from.
I’d also like to credit R.Alamsha on his similar post from 2015 about Ignition HA and MySQL clusters.