You might look into MySQL Proxy. Features include:
- load balancing
- failover
- query analysis
- query filtering and modification
I haven't run it myself, but it seems to cover what you want.
UPDATE
Apparently the Read/Write splitting is a proof of concept and not for production use
The reason you have to rebuild the old master from scratch is because as soon as you make one single change to the database on the new master (former slave), then the database on the old master is (by definition) stale, out of date, inconsistent, and obsolete.
Promoting a slave to master, as you likely realize already, is not technically a MySQL concept, since every server has the potential to be both, simultaneously. Promotion of a slave to master is really nothing more than "choosing which server the application will be permitted to write to."
If your intention is to make the old master into a new slave and preserve data integrity, you would configure the two machines to be each other's slaves before you promote the current slave to master... this is master/master a.k.a. circular replication. If no data is being directly manipulated on the original slave, then any binlog events the slave may send back to the master would be those that originated on the original master, which would be stamped with the master's server_id
, so the master will skip over and disregard them.
When you switch things around with your application, the old master/new slave will immediately start acting on replication events it sees in the binlog that were generated by writes to the old slave/new master.
You have likely read negative things about master/master in MySQL, but there's nothing inherently problematic about it as long as you understand that there is no provision for handling conflicting writes made simultaneously to both systems. If your application is only ever connecting to 1 of the machines at a time, then conflicting writes cannot occur.
Best Answer
First, let's discuss what benefit you will get from partitioning. Please provide
SHOW CREATE TABLE
(with the partitioning). Partitioning, in itself, does not provide any performance benefits. Let's see the query/queries that you think will benefit.pt-online-schema-change is likely to be a way to convert to partitioning with virtually no downtime. But check carefully; there may be restrictions on partitioning.
The manual says nothing about whether you can have replication between a non-partitioned on the master and a partitioned table on the slave. The silence of the manual worries me; there could be issues.