Re: Forwarding writes to master

[Rick James]

Have you determined that there is enough traffic to warrant read/write separation?

Note: All writes on the Master are replayed on the Slave, so there is generally no savings with a single Slave.

Automatic redirecting is possible via "mysql_proxy".

Automatic redirecting is folly. Consider the "critical read" or "read after write" case. The case is
1. write something (to Master)
2. read (from Slave), but expecting to see what was just written. Example: You post a blog entry, then look at your postings; you would expect to see the thing you just posted.
Replication is _usually_ "instantaneous", but there is no guarantee; it might be delayed by any number of things.

For read scaling, your application must separate your readonly operations from your write operations (plus their supporting SELECTs). That is not sufficient to handle the "read after write" case. That may be handled by altering user expectations, or designing the UI so that the 'problem' rarely happens.

Recommendation:
1. For now, send _all_ traffic to the Master; have the Slave as backup, and maybe a few "reports".
2. Revamp the code, gradually migrating some actions to the Slave.
3. Add a second Slave. Beginning with the second slave, you can get some "read scaling". (This, of course, requires some form of "load balancer".)