Hello,
I'm currently writing a PHP application and drivers (classes) for the database engines. I was wondering if I need to write a replication support (master-slave)? I'm a bit new to this, so, what kind of things should my project or classes worry about if I want to support load balancing/replication? Oh and this is about MySQL.
If you think you will use the slaves to read and the master to write, then your Class needs to support at least several connections at once.
I will show you the API I used, If you choose that way, I can send you the class.
ShusterDb::getInstance('read')->select($sql); //makes sure this is a SELECT in the method. ShusterDb::getInstance('write')->scalar($sql);
The way we use our master-slave db, is to use the master for all "active usage", and the slave for all reporting (where it doesn't matter if the data is still "catching up" slightly). Depending on your needs, you could have -all- data manipulation occur on the master, and -all- data reading occur on the slave. This especially helps when you have blocking inserts or updates. (Note: Also consider the "insert delayed" MySQL syntax where possible, which helps avoid blocking too.)
As far as the PHP support for this, all you really need is to keep clean handling for multiple (two) database connections, and use the master (read/write) or slave (ONLY READ) db connection as desired.