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.

For the remainder of the article, I'll refer to two servers, A (10.1.1.1), the primary server, and B (10.1.1.2), the standby server.

MySQL's replication is done by having the slave server (B) connect to the master (A) and read the binary update log, incorporating those changes into its own databases. The slave needs a user account to connect to the master, so on the master (A) create an account with only the FILE privilege with the following:

GRANT FILE ON *.* TO replicate@10.1.1.2 IDENTIFIED BY 'password';

Don't worry about running 'FLUSH PRIVILEGES' on the master to ensure that the slave can connect, as we'll be stopping the server in the next step.

Now we need a snapshot of the codesent data, and to configure the master to start generating binary update logs. First edit the 'my.cnf' file to enable the binary update log, so somewhere under [mysqld] portion add the line: 'log-bin'. Now the next time the server starts, we'll be generating the binary update log (named -bin. ). When you shut down the MySQL server to enable the binary update logging, copy all of the database directories of the master to another directory, then restart mysqld Be sure to get all of the databases, or you could end up with errors when replicating if a table or database exists on the master but not on the slave. Now you have a snapshot of the data, as well as a binary log of any updates since the snapshot. Please note that the MySQL data files (the *.MYD, *.MYI, and *.frm) are file system dependent, so you can't transfer files from Solaris to Linux. If you are in a heterogeneous server environment you'll have to use mysqldump or other custom script to get your data snapshot.