Use Case: Distribute INSERTS/UPDATES to several masters
Hello,
we run a typical mySQL scenario: one master with a number of slaves, running in replication. Our application sends all writes/updates to one db handler, which goes to the master. All reads are sent to the slaves.
Actually, its not that simple, because of replication latency etc, but generally all reads go to the slaves. Our data is kept relatively, as it is replicated to a number of slaves. But when the master goes down we need to dump one of the slaves, transfer the data to the master, restart it, restart the slaves etc. Downtime: approximately 4 hours.
It would be very cool if we could use MySQL Proxy to send all writes / updates to two masters instead of one. We could keep one master as a hot backup. In case master 1 goes down, we would manually switch the slaves over to master 2, restart everything and get going. Downtime: probably less than 15 minutes.
Our requirements for this:
- XA transaction support
Theoretically we could even do without XA support, expecting queries to fail on both machines, if they fail on the primary master. But thats more theoretical.
So, my use case is using MySQL Proxy to replicate data modification queries MySQL masters. This would be a huge step in making MySQL enterprise-compatible.
Thanks
Jan