Using MySQL’s Built-In Replication Page 3

Step Three: Create A Mutual Master-Slave Relationship

First go ahead and add ‘log-bin’ to the [mysqld] portion the my.cnf file on B and restart mysqld, then create
the account for the replication user on it with:

GRANT FILE ON *.* TO [email protected] IDENTIFIED BY 'password';

Go ahead and run a ‘FLUSH PRIVILEGES’ on B to load the new grant tables after adding the replication user,
and go back to server A, and add the following to it’s ‘my.cnf’ file:

master-host=10.1.1.2
master-user=replicate
master-password=password

After restarting server A, we now have a mutual master-slave relationship between A and B. A record that is
updated or inserted on either server will be replicated to the other. A word of caution: I’m not sure just
how fast a slave incorporates bin-log changes, so it might not be good idea to load-balance your insert or
update statements to codevent any corruption of your data.

Step Four: Alter Your Database Connection Routine

Now that you have established a mutual relationship between servers A and B, you need to alter your database connection
routines to take advantage of this. The function below first attempts to connect to server A, and then to
server B if no connection can be made.

<?php
/********************************************************
function db_connect()
returns a link identifier on success, or false on error
********************************************************/
function db_connect(){
$username = "replUser";
$password = "password";
$primary = "10.1.1.1";
$backup = "10.1.1.2";
# attempt connection to primary
if(!$link_id = @mysql_connect($primary, $username, $password))
# attempt connection to secondary
$link_id = @mysql_connect($secondary, $username, $password)
return $link_id;
}
?>

I tested my setup using the above technique under two conditions, with the primary MySQL server shutdown,
but otherwise the server was running, and with the primary server shutdown. Connections were made to the
backup immediately if just mysqld was shut down on the primary, but if the entire server was shutdown,
there was an indefinite hang (I lost track after two minutes – short attention span) while PHP was looking
for a non-existent server. Unfortunately, the mysql_connect function does not have any time-out parameters
like fsockopen, however we can use fsockopen to fake a timeout for us.