Pressflow 6 with mysql Replication

[ drupal php pressflow ] 27 Jun 2011

Pressflow6 should support database replication according to the pressflow wiki. but when I configured master and slave, pressflow instance always pick master. If master was down, site couldn’t load and site redirected to the site-offline error page.

By doing some modifications to database.inc, database.mysqli.inc and settings.php I was able to route DB calls to slave when master is down.

##Scenario

If mysql master is inactive, connect to Slave
If slave is inactive but Master is active, then connect to Master
if Master and Slave both are inactive, Redirect user to Offline page

####database.inc

Below change will modify the db_connect call to Master instance by passing 2nd parameter (“master”) and by assigning $db_slave_conns[$name] values to $active_db.
Validate before slave connection create. If master is active we are not calling Slave by adding $db_conns[$name]->connect_errno > 0

function db_set_active($name = 'default') {
  global $db_url, $db_slave_url, $db_type, $active_db, $active_slave_db;
  static $db_conns, $db_slave_conns, $active_name = FALSE;


  if (empty($db_url)) {
    include_once 'includes/install.inc';
    install_goto('install.php');
  }


  if (!isset($db_conns[$name])) {
    // Initiate a new connection, using the named DB URL specified.
    if (is_array($db_url)) {
      $connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default'];
      if (is_array($db_slave_url[$name])) {
        $slave_index = mt_rand(0, count($db_slave_url[$name]) - 1);
        $slave_connect_url = $db_slave_url[$name][$slave_index];
      }
      else {
        $slave_connect_url = $db_slave_url[$name];
      }
    }
    else {
      $connect_url = $db_url;
      if (is_array($db_slave_url)) {
        $slave_index = mt_rand(0, count($db_slave_url) - 1);
        $slave_connect_url = $db_slave_url[$slave_index];
      }
      else {
        $slave_connect_url = $db_slave_url;
      }
    }


    $db_type = substr($connect_url, 0, strpos($connect_url, '://'));
    $handler = "./includes/database.$db_type.inc";


    if (is_file($handler)) {
      include_once $handler;
    }
    else {
      _db_error_page("The database type '". $db_type ."' is unsupported. Please use either 'mysql' or 'mysqli' for MySQL, or 'pgsql' for PostgreSQL databases.");
    }


    $db_conns[$name] = db_connect($connect_url,'master');
    if ($db_conns[$name]->connect_errno > 0 && !empty($slave_connect_url)) {
      $db_slave_conns[$name] = db_connect($slave_connect_url);
    }
  }

  $previous_name = $active_name;
  // Set the active connection.
  $active_name = $name;
  $active_db = $db_conns[$name];
  if (isset($db_slave_conns[$name])) {
    $active_slave_db = $db_slave_conns[$name];
    $active_db = $db_slave_conns[$name];
  }
  else {
    unset($active_slave_db);
  }


  return $previous_name;
}

####database.mysqli.inc

db_connect function accept a 2nd parameter. default value will be slave
wrapped _db_error_page(mysqli_connect_error()) with slave server validation. this will stop error page redirection if master server is down.
wrapped with else block to void calling mysqli_query with uninitiated mysql resource.

function db_connect($url,$server='slave') {
  // Check if MySQLi support is present in PHP
  if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
    _db_error_page('Unable to use the MySQLi database because the MySQLi extension for PHP is not installed. Check your php.ini to see how you can enable it.');
  }


  $url = parse_url($url);


  // Decode url-encoded information in the db connection string
  $url['user'] = urldecode($url['user']);
  // Test if database url has a password.
  $url['pass'] = isset($url['pass']) ? urldecode($url['pass']) : '';
  $url['host'] = urldecode($url['host']);
  $url['path'] = urldecode($url['path']);
  if (!isset($url['port'])) {
    $url['port'] = NULL;
  }


  $connection = mysqli_init();
  @mysqli_real_connect($connection, $url['host'], $url['user'], $url['pass'], substr($url['path'], 1), $url['port'], NULL, MYSQLI_CLIENT_FOUND_ROWS);


  if (mysqli_connect_errno() > 0) {
    if($server == 'slave'){
  _db_error_page(mysqli_connect_error());
    }
  }else {


   // Force UTF-8.
   mysqli_query($connection, 'SET NAMES "utf8"');
  }
  return $connection;
}

####settings.php

$db_url = array();
$db_url['default'] = 'mysqli://user:password@master-host/database';


$db_slave_url = array();
$db_slave_url['default'] = 'mysqli://user:password@slave-host/database';

Thilanga Pitigala

Pressflow 6 with mysql Replication

Bisura - Thriloka