I'm trying to find the best setup to horizontally scale an ecommerce which is currently on a single dedicated server, but even reading some of the replies here on DBA I'm still confused on what is the best solution for my use case. The sync should be synchronous (I think but I may be wrong here) and it will be integrated with an HAproxy for load balancing (HAProxy will manage also WWW loadbalancing that's why I mention it, but If I have to use MySQL Router o SQLProxy that's ok too). I also need the same autoincrement progression between all the MySQL instances (more below, in the M\M part). Lastly it should be easy to recover if any node goes down.
These are the options I found by googling:
- MySQL Replication M/M
- MySQL Replication/Group Replication
- Percona XtraDB Cluster / Galera
- MySQL NDB
MySQL Replication M/M
I used this in the past, but I had the problem of using the auto_increment_increment/offset setting, resulting in non-continuos order/cart/etc IDs. AFAIK it cannot be configured for synchronous replication. Also is not the most professional approach to the problem, with many cons.
MySQL Replication/Group Replication
I don't exactly understand the difference between the two, except that the latter seems to have a nicer way to manage/control the cluster. But this seems to do the job I need. If I understood correctly, using it in semisynchronous mode with rpl_semi_sync_source_wait_for_replica_count equal to the amount of MySQL instances is like running in synchronous mode. Is that correct? Also I'm a bit confused about Single o Multi Primary Mode. I think every instance must be RW, so MultiPrimary, if I want to balance also the write queries. Or is better to have a Single Primary RW node and redirect only the reading queries on the secondary nodes (if that's possible with HA, i don't know)?
Percona XtraDB Cluster / Galera
This looks like a Group Replication with additional features and management tools to make it easier, possibly performing even better. If I understood correctly synchronous mode is enforced here, for more consistency. I'm not really sure what else differs from the MySQL one, that's part of my confusion.
I read this is the best solution for a synchronous replication. However I don't get if it can be safely used with PHP application wrote with InnoDB in mind. I know I can just re-import the whole InnoDB database with the NDB engine, but it is compatible with any MySQL query I can do on InnoDB? This is the biggest doubt I have. I don't want to move eveything on NDB to find out later that I can't do something or even worse with errors in my app queries.
So, here I am, full of doubt to choose the best option for my use case, since this is a choice I cannot undo. I hope I didn't forget anything useful to know. Can any DBA expert help me in doing the right choice? Thanks.