Using MySQL’s Built-In Replication Page 4

Step Five: An Improved Database Connection Routine

<?php
/********************************************************
function db_connect_plus()
returns a link identifier on success, or false on error
********************************************************/
function db_connect_plus(){
    $username = "username";
    $password = "password";
    $primary = "10.1.1.1";
    $backup = "10.1.1.2";
    $timeout = 15;  // timeout in seconds
    if($fp = fsockopen($primary, 3306, &$errno, &$errstr, $timeout)){
        fclose($fp);
        return $link = mysql_connect($primary, $username, $password);
    }
    if($fp = fsockopen($secondary, 3306, &$errno, &$errstr, $timeout)){
        fclose($fp);
          return $link = mysql_connect($secondary, $username, $password);
    }
    return 0;
}
?>

This new and improved function gives us an adjustable timeout feature that the mysql_connect function lacks.
If the connection fails right away, such as if the machine is up, but mysqld is down, the function immediately
moves to the secondary server. The function above is quite robust, testing to see if the server is listening
on the port before attempting a connection, letting your scripts time out in an acceptable period leaving you
to handle the error condition appropriately. Be sure to alter the port number if you’ve changed it from
the default port of 3306.

Conclusions and Pointers

First of all, be sure to get a good data snapshot. Forgetting to copy a table or database will cause the
slave thread to stop. Timing of the snapashot is critical. You want to be sure that binary logging is not
enabled before you copy the data files, and that it is immediately after you do. If you were to enable
binary logging before getting the snapshot, the slave thread could stop when it tried importing importing
records due to duplicate primary keys. This is best accomplished by the procedure discussed in section two:
shutdown-copy-restart with binary logging. Without those precautions.

You may want to set up replication one-way initially, and keep an eye on the slave server for good
period of time to ensure that it is keeping in step with the master.

I haven’t tested out a system for load balancing with the replication feature, but I would be leery of using such a system
for balancing inserts and updates. For excample, what if two records were given the same auto_increment number on two
servers, would the slave thread on each stop? Questions like those would keep any load balancing scenario as
‘read-only’, where one server handled all of the inserts and updates, while a team of slaves (yes, you can
have multiple slaves off of a master) handled all of the selects.

I’m very happy that MySQL has taken on some sort of replication system, and that it’s so simple to configure.
That should get you started in provding an extra measure of security against events beyond your control. I have
only covered the features of replication that I have tested and used, but there are a few more which are
detailed in section 11 of MySQL’s on-line documentation.

~Michael