MySQL replication-savvy middleware

[steve]

In our small server farm, we run some apache web servers and MySQL database servers. We generally replicate dbServerA to dbServerB and also maybe to dbServerC and dbServerD.

We configure s9y to read to a master dbServer within the cluster (a machine other than the web server serving s9y).

Anyone up to a i/o middleware that:
IF replication = ON
master replication host = 192.168.0.50
mysql slaveA= 192.168.0.51
msyql slaveB= 192.168.0.52
etc.

s9y should write to the master, and read from the pool of slaves.

We run our apps this way to give us another level of redundancy.

btw congrats on the recognition (s9y rec'd along w/ smarty etc!)

[garvinhicking]

I think this could be easily achieved...here's a small "proof of concept" drop-in replacement for include/db/mysql.inc.php

I'd love if you could try it and report back any issues. The whole thing requires you to overwrite the current mysql.inc.php and set $serendipity['dbHostWrite'] within serendipity_config_local.inc.php to point to the Write Server. The Read-Server is the standard dbHost.

Regards,
Garvin

Code: Select all

<?php # $Id: mysql-slave.inc.php,v 0.0 2005/01/26 14:14:06 garvinhicking Exp $
# Copyright (c) 2003-2005, Jannis Hermanns (on behalf the Serendipity Developer Team)
# All rights reserved.  See LICENSE file for licensing details

function serendipity_db_begin_transaction(){
    serendipity_db_query('start transaction');
}

function serendipity_db_end_transaction($commit){
    if ($commit){
        serendipity_db_query('commit');
    }else{
        serendipity_db_query('rollback');
    }
}

/* Issues a query to the underlying database;
 * returns:
 *   false if there was an error,
 *   true if the query succeeded but did not generate any rows
 *   array of field values if it returned a single row and $single is true
 *   array of array of field values if it returned row(s)
 */
function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = false, $assocKey = false, $assocVal = false, $expectError = false) {
    global $serendipity;
    static $type_map = array(
                         'assoc' => MYSQL_ASSOC,
                         'num'   => MYSQL_NUM,
                         'both'  => MYSQL_BOTH
    );

    // highlight_string(var_export($sql, 1));

    if (preg_match('@^\s*SELECT@i', $sql)) {
        $conn =& $serendipity['dbConn'];
    } else {
        $conn =& $serendipity['dbConnWrite'];
    }
    
    if ($expectError) {
        $c = @mysql_query($sql, $conn);
    } else {
        $c = mysql_query($sql, $conn);
    }

    if (!$expectError && mysql_error($conn) != '') {
        return '<pre>' . $sql . '</pre> / ' . mysql_error($conn);
    }

    if (!$c) {
        if (!$expectError && !$serendipity['production']) {
            print '<pre>' . $sql . '</pre> / ' . mysql_error($conn);
            if (function_exists('debug_backtrace') && $reportErr == true) {
                highlight_string(var_export(debug_backtrace(), 1));
            }
        }

        return false;
    }
    if ($c === true) {
        return true;
    }

    $result_type = $type_map[$result_type];

    switch(mysql_num_rows($c)) {
        case 0:
            if ($single) {
                return false;
            }
            return true;
        case 1:
            if ($single) {
                return mysql_fetch_array($c, $result_type);
            }
        default:
            if ($single) {
                return mysql_fetch_array($c, $result_type);
            }

            $rows = array();
            while (($row = mysql_fetch_array($c, $result_type))) {
                if (!empty($assocKey) && !empty($assocVal)) {
                    // You can fetch a key-associated array via the two function parameters assocKey and assocVal
                    $rows[$row[$assocKey]] = $row[$assocVal];
                } else {
                    $rows[] = $row;
                }
            }
            return $rows;
    }
}

function serendipity_db_insert_id() {
    global $serendipity;

    return mysql_insert_id($serendipity['dbConnWrite']);
}

function serendipity_db_affected_rows() {
    global $serendipity;

    return mysql_affected_rows($serendipity['dbConn']);
}

function serendipity_db_updated_rows() {
    global $serendipity;

    preg_match(
        "/^[^0-9]+([0-9]+)[^0-9]+([0-9]+)[^0-9]+([0-9]+)/",
        mysql_info($serendipity['dbConnWrite']),
        $arr);
        // mysql_affected_rows returns 0 if rows were matched but not changed.
        // mysql_info returns rows matched AND rows changed
        return $arr[2];
}

function serendipity_db_matched_rows() {
    global $serendipity;

    preg_match(
        "/^[^0-9]+([0-9]+)[^0-9]+([0-9]+)[^0-9]+([0-9]+)/",
        mysql_info($serendipity['dbConnWrite']),
        $arr);
        // mysql_affected_rows returns 0 if rows were matched but not changed.
        // mysql_info returns rows matched AND rows changed
        return $arr[1];
}

function serendipity_db_escape_string($string) {
    return mysql_escape_string($string);
}

function serendipity_db_limit($start, $offset) {
    return $start . ', ' . $offset;
}

function serendipity_db_limit_sql($limitstring) {
    return ' LIMIT ' . $limitstring;
}

function serendipity_db_connect() {
    global $serendipity;

    if (isset($serendipity['dbConn'])) {
        return $serendipity['dbConn'];
    }

    if (isset($serendipity['dbPersistent']) && $serendipity['dbPersistent']) {
        $function = 'mysql_pconnect';
    } else {
        $function = 'mysql_connect';
    }

    $serendipity['dbConn'] = $function($serendipity['dbHost'], $serendipity['dbUser'], $serendipity['dbPass']);
    mysql_select_db($serendipity['dbName'], $serendipity['dbConn']);

    $serendipity['dbConnWrite'] = $function($serendipity['dbHostWrite'], $serendipity['dbUser'], $serendipity['dbPass']);
    mysql_select_db($serendipity['dbName'], $serendipity['dbConnWrite']);

    return $serendipity['dbConn'];
}

function serendipity_db_schema_import($query) {
    static $search  = array('{AUTOINCREMENT}', '{PRIMARY}',
        '{UNSIGNED}', '{FULLTEXT}', '{FULLTEXT_MYSQL}', '{BOOLEAN}');
    static $replace = array('int(11) not null auto_increment', 'primary key',
        'unsigned'  , 'FULLTEXT', 'FULLTEXT', 'enum (\'true\', \'false\') NOT NULL default \'true\'');

    $query = trim(str_replace($search, $replace, $query));
    if ($query{0} == '@') {
        // Errors are expected to happen (like duplicate index creation)
        return serendipity_db_query(substr($query, 1), false, 'both', false, false, false, true);
    } else {
        return serendipity_db_query($query);
    }
}

/* probes the usability of the DB during installation */
function serendipity_db_probe($hash, &$errs) {
    global $serendipity;

    if (!function_exists('mysql_connect')) {
        $errs[] = 'No mySQL extension found. Please check your webserver installation or contact your systems administrator regarding this problem.';
        return false;
    }

    if (!($c = @mysql_connect($hash['dbHost'], $hash['dbUser'], $hash['dbPass']))) {
        $errs[] = 'Could not connect to database; check your settings.';
        $errs[] = 'The mySQL error was: ' . mysql_error();
        return false;
    }

    $serendipity['dbConn'] = $c;

    if (!@mysql_select_db($hash['dbName'])) {
        $errs[] = 'The database you specified does not exist.';
        $errs[] = 'The mySQL error was: ' . mysql_error();
        return false;
    }

    return true;
}

function serendipity_db_concat($string) {
    return 'concat(' . $string . ')';
}

/* vim: set sts=4 ts=4 expandtab : */
?>

[nohn]

hacking this into s9y is the wrong approach. use something like C-JDBC (you can only use that with php when using the php-java bridge and the JDBC-driver) or SQLrelay