24HOURSMEDIA

This page contains a free AdoDb based class to use ADODB in a replicated MySQL Master/Slave environment. With this ADODB class, you can efficiently migrate to a replication database setup. Switching from a single MySQL database to a replicated database solution can be cumbersome. All your update and insert queries may not be performed on the slave databases, or else the replication will be broken. This class should help you how to manage the migration more easily.

This will take you hours of coding and testing, and even then you may expect some problems in the production environment.

If your site uses the ADODB Abstraction layer, overriding the AdoDb class may provide a simple solution to help you get up your site quickly in a replicated environment. A similar solution may also be used with other database abstraction layers.

How it works

The new adodb database layer simply detects if UPDATE,INSERT,DELETE or REPLACE queries are executed. In that case, the AdoDb class simply connects to the design master database to execute these queries. But for SELECT queries, the layer simply connects to the slave for fast querying.

Only when required a connection is made to the master database!

Usage example.

Usage is easy and similar to making an adodb connection normally. Difference is that you provide a db login to connect to the master database if it is required:

// include the class file (see next section, ‘the class’) here.
require_once(”adoreplicateddb.inc.php”);

     

// set up a connection using the new class

$connection = &ADONewReplicationConnection(”mysql”);

// connect to a slave DB. This login should have SELECT permissions only, or
// else the replication may be broken!
$ok = $connection->Connect(”localhost”, “web”, “”, “database”);
if (!$ok) trigger_error(”Could not connect to slave “);

// enter your db login etc below for the design master.
// it should have UPDATE,INSERT,DELETE permissions
$connection->masterServer = “designmaster server”;
$connection->masterUser = “user”;
$connection->masterPass = “pass”;
$connection->masterDb = “db”;

// READY, you now may use connection normally. Read operations are executed on the slave,
// write operations on the master database!

The class

Below is the PHP code for the new class, and a function to create the database (see the usage example). Note that this code has undergone only limited testing, but for most comon operations (INSERTS, UPDATES, DELETES) it appears to be working ok!

<?
 /**
   * @name  adoreplicateddb.inc.php
   * @abstract adodb for replicated servers
   *
   * this class inherits from ADODB class, and makes it possible to use
   * the adodb layer on load balanced servers. it is in beta stage.
   *
   * @author  EB —  24hoursmedia.com
   * @since   20071112
   * @version  1.0
   * @final
   *
   * @todo  
   */

 // make these location point to the correct adodb directories.
 require_once (dirname(__FILE__) . “/../adodb/adodb.inc.php”);
 require_once (dirname(__FILE__) . “/../adodb/drivers/adodb-mysql.inc.php”);

 class ADOReplicatedConnection extends ADODB_mysql {

   // connection info to design master
   var $masterDbType = “mysql”;
   var $masterServer = “”;
   var $masterUser = “”;
   var $masterPass = “”;
   var $masterDb = “”;

   var $logMasterExecutions = false; // for debugging/monitoring
   var $masterExecLog = array();

   // adodb handle voor de master db. Wordt pas geinstantieerd na een
   // update/insert/replace statement..
   var $masterDbHandle = null;

   /**
    * Maakt verbinding met de master database voor update/schrijf operaties.
    *
    * @return boolean  true on success, error or false on failure
    */
   function masterConnect() {
    if (is_object($this->masterDb)) {
     return true;
    }
    $this->masterExecLog[] = array(”connect to master”);
    $connection = &ADONewConnection(’mysql’);
    $ok = $connection->Connect($this->masterServer, $this->masterUser, $this->masterPass, $this->masterDb);
    if (!$ok) {
     trigger_error(”Could not connect to DB Design master: ” . $connection->ErrorMsg(), E_USER_ERROR);
     return false;
    }
    $this->masterDb = $connection;
    return true;
   }

   function &Execute($sql,$inputarr=false) {
    if (stristr($sql, “update”) || stristr($sql, “insert”) || stristr($sql, “replace”) || stristr($sql, “delete”)) {
     if ($this->logMasterExecutions) {
      $this->masterExecLog[] = array(
       ”sql” => $sql
      );
     }

     // an update, insert, replace or delete query is done.
     // route these queries t the design master.
     $this->masterConnect();
     $result = $this->masterDb->Execute($sql);
     return $result;
    } else {
     // normale verwerking query.
     $result = ADODB_mysql::Execute($sql);
     return $result;
    }
   }

   // here are some functions to route information from the design master
   // back to the main adodb replicated class, so correct info is provided.
   // override more functions when you need it.
   function _insertid()
   {
    $this->masterExecLog[] = array(”insertid”);
    $this->masterConnect();
    return $this->masterDb->_insertid();
   }

   function Affected_Rows()
   {
    $this->masterExecLog[] = array(”affectedrows”);
    $this->masterConnect();
    return $this->masterDb->Affected_Rows();
   }

 }

 function ADOReplicatedConnection() {
  return new ADOReplicatedConnection();
 }

 function &ADONewReplicationConnection($db=”) {
  global $ADODB_NEWCONNECTION;
  $oldConn = $ADODB_NEWCONNECTION;
  $ADODB_NEWCONNECTION = “ADOReplicatedConnection”;
  $connection = &ADONewConnection($db);
  $ADODB_NEWCONNECTION = $oldConn;
  return $connection;
 }
?>

Finally,

Have fun and it hopes this saves you some time and worries!

Tags: adodb, database, database abstraction layer, db, design master, load balanced, load balancing, master, mysql, replicated, replication, server, slave

This entry was posted on Friday, November 16th, 2007 at 4:25 pm and is filed under Development Blog. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.