Should we consider anything in terms of how application works with database berfore migrating from standalone to cluster? like is there any type of operation that it was possible with standalone but cluster may fail to do it?
Considerations before moving from standalone mysql to xtradb cluster
- All writes should go to a single node.
- LOCK TABLES will not lock across all nodes
- Some ALTER TABLE will block all transactions across the cluster.
- Use ProxySQL to load-balance SELECT across other nodes for scalability
all is clear but 3rd “Some ALTER TABLE will block all transactions across the cluster.” does it matter since we gonna write to one node? does not regular standalone mysql also block transactions for that some alter table commands?
If you are running an ALTER on node1, that table will be locked on node2 and node3, thus blocking any reads on those nodes. In standalone MySQL, some alter will still allow reads.