PHPBuilder: Using MySQL’s Built-In Replication To Maximize Availability

“Here at Soundbreak we stream out live audio and video 24 hours
a day, so it didn’t take much convincing to be able to run some
testing on MySQL’s new replication feature. We discoverd that we
could use it to keep an up to date standby database server that
could handle all of the queries should the primary fail for any
reason. It’s not dificult to configure two servers for just such a
purpose, and I’ll walk through the procedure, as well as a
discussion as to how you can use PHP to redirect the queries should
the primary server fail.”

“MySQL’s internal replication is built on a master-slave
relationship between two or more servers, with one acting as the
master, and any number acting as slaves. I’ll walk through
configuring two servers as a master slave pair, describing the
process as we move through it. I initially performed the procedure
outlined below on 3.23.22, and have also tested it on 3.23.23. The
MySQL developers recommend that you use the most recent version,
and that both the master and slave use the same version while the
3.23 series is still in beta as versions may not be backward
compatible. I currently have not yet used this procedure on a live
site for that reason, as one of the advantages of having fail-over
capabilities is to be able to upgrade one server without
interrupting any of the queries.”