Current best solutions for SQL HA, Redundancy and Backups

Ignition
1

Hi all,

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:

Ignition Architecture Old
Ignition Architecture Old705×461 26.5 KB

  1. Server 1
    Ignition master gateway
    SQL slave database
  2. 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:

Ignition Architecture New
Ignition Architecture New912×731 53.1 KB

Some things to discuss:

  1. 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.

  2. What are some good ways to backup large databases? complete VM backups? LVM backups? SQL Dumps?

  3. 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?

  4. 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.

2

Let me just say that I am in a similar situation at the moment. I would like to use MariaDB with the MaxScale product. The problem is, the tutorials I find online are not very easy to follow. Here is my general thought though. Have two HA proxys, two MaxScale load balancers, and three MariaDBs in a cluster. Two of the databases would be in Virtual machines across two physical servers and the third would be offsite for data backup only.

As far as Ignition is concerned I want it to connect to the MaxScale load balancers using a floating IP address. So to Ignition this whole process is totally transparent. The real challenge is tying to figure out how exactly that floating IP address works especially in a virtualized environment. I think it will just be a matter of buckling down and doing the work to learn what to do. I think you are on the right track for sure and would love to hear what your end solutions ended up being.

3

i haven’t configured anything yet sadly, there is always a higher priority project to work on. But i agree with you that sounds like a good architecture.
I believe MaxScale sorts out the IP stuff for you though? It knows the IP of each instance. You point to the master MaxScale IP, and it will redirect the requests to whatever instance it deems appropriate.
IP’s need to be fixed though i would have thought.

Have you managed to do any testing on this yet?

4

I have not tried anything yet. The whole setup is in flux at the moment. PostGres is not the current direction and it has it’s own separate challenges regarding HA.