PHPBuilder - Using MySQL's Built-In Replication Page 4

RSS Twitter

Using MySQL's Built-In Replication - Page 4

by: Michael Tanoviceanu
September 12, 2000

Step Five: An Improved Database Connection Routine


function db_connect_plus()

returns a link identifier on success, or false on error
function db_connect_plus(){
$username "username";
$password "password";
$primary "";
$backup "";
$timeout 15;  // timeout in seconds

if($fp fsockopen($primary3306, &$errno, &$errstr$timeout)){
$link mysql_connect($primary$username$password);
$fp fsockopen($secondary3306, &$errno, &$errstr$timeout)){
$link mysql_connect($secondary$username$password);


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.

« Previous Page

Comment and Contribute

Your comment has been submitted and is pending approval.

Michael Tanoviceanu



(Maximum characters: 1200). You have characters left.