mysql讀寫分離(PHP類)

部落格分類：

php
mysql

自己實現了php的讀寫分離,並且不用修改程式

優點:實現了讀寫分離,不依賴伺服器硬體配置,並且都是可以配置read伺服器,無限擴充套件

缺點:錯誤轉移不購明確,現只有3次嘗試機會,失敗了就會選擇其他的伺服器.當伺服器恢復時,需要手動去消除錯誤日誌不人性化

修改於discux x 的mysql類

配置檔案

“db”=>array(
        `1`=>array(
            `dbhost`=> `127.0.0.1`,
            `dbuser` => `root`,
            `dbpw` => `xxx`,
            `dbcharset` => `utf8`,
            `pconnect` => `0`,
            `dbname` => `test`,
            `tablepre` => `w_`,
        ),
        `2`=>array(
            `dbhost`=> `127.0.0.1`,
            `dbuser` => `root`,
            `dbpw` => `xxx`,
            `dbcharset` => `utf8`,
            `pconnect` => `0`,
            `dbname` => `test2`,
            `tablepre` => `w_`,
        ),

        `3`=>array(
            `dbhost`=> `127.0.0.1`,
            `dbuser` => `root`,
            `dbpw` => `xxx`,
            `dbcharset` => `utf8`,
            `pconnect` => `0`,
            `dbname` => `test3`,
            `tablepre` => `w_`,
        ),
        `writes`=>array(`2`,`3`),
        `reads`=>array(`2`,`1`),
        `broken`=>array(),
        `broken_file`=>str_replace(`\`,`/`,dirname(__FILE__)) .”/____mysql_broken_file.php”,
        //`broken_file`=>array(),
     )

類檔案

<?php

/*
[Discuz!] (C)2001-2009 Comsenz Inc.
This is NOT a freeware, use is subject to license terms

$Id: db_mysql.class.php 20294 2009-09-23 06:00:37Z zhaoxiongfei $
*/

if(!defined(`IN_APP`)) {
exit(`Access Denied`);
}

class lib_db_mysql extends app_lib
{
    var $tablepre;
    var $version = “;
    var $querynum = 0;
    var $curlink;
    var $link = array();
    var $config = array();
    var $sqldebug = array();
    var $map = array();

    var $reads=array();//讀伺服器id
    var $writes=array();//寫伺服器id
    var $broken=array();//重試3次壞掉的伺服器
    var $broken_file=””;

    function init(){
        $this->set_config(config_item(`db`));
    }

        if(!empty($this->config[`broken_file`])) {
            $this->broken_file=$this->config[`broken_file`];
            unset($this->config[`broken_file`]);
        }else{
            $this->broken_file=str_replace(`\`,`/`,dirname(__FILE__)) .”/____mysql_broken_file.php”;
        }

        $this->broken=$this->get_broken($this->config[`broken`]);
        unset($this->config[`broken`]);

        //-讀寫分離
    }

function connect($serverid = 1) {

        if(empty($this->config) || empty($this->config[$serverid])) {
            $this->set_broken($serverid);
            $this->halt(`config_db_not_found`);
        }

        $this->link[$serverid] = $this->_dbconnect(
            $this->config[$serverid][`dbhost`],
            $this->config[$serverid][`dbuser`],
            $this->config[$serverid][`dbpw`],
            $this->config[$serverid][`dbcharset`],
            $this->config[$serverid][`dbname`],
            $this->config[$serverid][`pconnect`],
            $serverid
            );
        $this->curlink = $this->link[$serverid];
    }

    function select_db($dbname) {
        return mysql_select_db($dbname, $this->curlink);
    }

    function fetch_array($query, $result_type = MYSQL_ASSOC) {
        return mysql_fetch_array($query, $result_type);
    }

    function fetch_first($sql) {
        return $this->fetch_array($this->query($sql));
    }

    function result_first($sql) {
        return $this->result($this->query($sql), 0);
    }

    /**
    * 讀寫分離,並從池中選中隨機選中資料庫
    * 如果沒有read連線將使用write代替
    *
    * @param string $sql
    */
    function switch_server($sql,$default=1){
        $sql=ltrim($sql);
        if(preg_match(`/^select/i`,$sql)){
            $type=`reads`;
        }else{
            $type=`writes`;
        }

        $servers=array_diff($this->$type,$this->filter_broken($this->broken));
        if(!$servers&&$type==`reads`){
            $type=`writes`;
            $servers=array_diff($this->$type,$this->filter_broken($this->broken));
        }

        $t=count($servers);
        if($t>0){
            //有合理的服務
            /**
            * 根據ip的hash隨機抽伺服器執行,
            */
            $servers=array_values($servers);
            $onlineip = “;
            if(getenv(`HTTP_CLIENT_IP`) && strcasecmp(getenv(`HTTP_CLIENT_IP`), `unknown`)) {
                $onlineip = getenv(`HTTP_CLIENT_IP`);
            } elseif(getenv(`HTTP_X_FORWARDED_FOR`) && strcasecmp(getenv(`HTTP_X_FORWARDED_FOR`), `unknown`)) {
                $onlineip = getenv(`HTTP_X_FORWARDED_FOR`);
            } elseif(getenv(`REMOTE_ADDR`) && strcasecmp(getenv(`REMOTE_ADDR`), `unknown`)) {
                $onlineip = getenv(`REMOTE_ADDR`);
            } elseif(isset($_SERVER[`REMOTE_ADDR`]) && $_SERVER[`REMOTE_ADDR`] && strcasecmp($_SERVER[`REMOTE_ADDR`], `unknown`)) {
                $onlineip = $_SERVER[`REMOTE_ADDR`];
            }

            !$onlineip&&$onlineip=rand(100);

$i=abs(crc32($onlineip))%$t;

            return $servers[$i];
        }
        return $default;
    }


    function set_broken($server_id){
        $this->broken[$server_id]++;
        if($this->broken_file){
            $str=var_export($this->broken,true);
            $c=”<?php
                $broken=$str;
            ?>”;
            return file_put_contents($this->broken_file,$c);
        }
    }

    function filter_broken($broken){
        $error_try=3;
        foreach($broken as $id=>$num){
            if($num<$error_try){
                unset($broken[$id]);
            }
        }
        return array_keys($broken);
    }

    function get_broken($realbroken){
        $broken=array();
        $error_try=3;
        if($this->broken_file&&is_file($this->broken_file)){
            @include($this->broken_file);
        }
        settype($realbroken,`array`);
        foreach($realbroken as $id){
            $broken[$id]=$error_try;
        }
        return $broken;
    }

    /**
    * 讀寫分離
    *
    * @param mixed $sql
    * @param mixed $type
    */

    function query($sql, $type = “) {

        if(defined(`DISCUZ_DEBUG`) && DISCUZ_DEBUG) {
            $starttime = dmicrotime();
        }

        //讀寫分離
        $server_id=$this->switch_server($sql,1);

        if($this->link[$server_id]){
            $this->curlink=$this->link[$server_id];
        }else{
            $this->connect($server_id);
        }
        //-讀寫分離



        $func = $type == `UNBUFFERED` && @function_exists(`mysql_unbuffered_query`) ?
        `mysql_unbuffered_query` : `mysql_query`;
        if(!($query = $func($sql, $this->curlink))) {
            if(in_array($this->errno(), array(2006, 2013)) && substr($type, 0, 5) != `RETRY`) {
                $this->connect($server_id);
                return $this->query($sql, `RETRY`.$type);
            }
            if($type != `SILENT` && substr($type, 5) != `SILENT`) {
                $this->halt(`query_error`, $sql);
            }
        }

        if(defined(`DISCUZ_DEBUG`) && DISCUZ_DEBUG) {
            $this->sqldebug[] = array($sql, number_format((dmicrotime() – $starttime), 6), debug_backtrace());
        }

        $this->querynum++;
        return $query;
    }

    function affected_rows() {
        return mysql_affected_rows($this->curlink);
    }

    function error() {
        return (($this->curlink) ? mysql_error($this->curlink) : mysql_error());
    }

    function errno() {
        return intval(($this->curlink) ? mysql_errno($this->curlink) : mysql_errno());
    }

    function result($query, $row = 0) {
        $query = @mysql_result($query, $row);
        return $query;
    }

    function num_rows($query) {
        $query = mysql_num_rows($query);
        return $query;
    }

    function num_fields($query) {
        return mysql_num_fields($query);
    }

    function free_result($query) {
        return mysql_free_result($query);
    }

    function insert_id() {
        return ($id = mysql_insert_id($this->curlink)) >= 0 ? $id : $this->result($this->query(“SELECT last_insert_id()”), 0);
    }

    function fetch_row($query) {
        $query = mysql_fetch_row($query);
        return $query;
    }

    function fetch_fields($query) {
        return mysql_fetch_field($query);
    }

    function version() {
        if(empty($this->version)) {
            $this->version = mysql_get_server_info($this->curlink);
        }
        return $this->version;
    }

    function close() {
        return mysql_close($this->curlink);
    }

    function halt($message = “, $sql = “) {
        global $_G;
        $dberror = $this->error();
        $dberrno = $this->errno();
        $phperror = `<table style=”font-size:11px” cellpadding=”0″><tr><td width=”270″>File</td><td width=”80″>Line</td><td>Function</td></tr>`;
        foreach (debug_backtrace() as $error) {
            $error[`file`] = str_replace(DISCUZ_ROOT, “, $error[`file`]);
            $error[`class`] = isset($error[`class`]) ? $error[`class`] : “;
            $error[`type`] = isset($error[`type`]) ? $error[`type`] : “;
            $error[`function`] = isset($error[`function`]) ? $error[`function`] : “;
            $phperror .= “<tr><td>$error[file]</td><td>$error[line]</td><td>$error[class]$error[type]$error[function]()</td></tr>”;
        }
        $phperror .= `</table>`;
        $helplink = “http://faq.comsenz.com/?type=mysql&dberrno=”.rawurlencode($dberrno).”&dberror=”.rawurlencode($dberror);
        @header(`Content-Type: text/html; charset=`.$_G[`config`][`output`][`charset`]);
        echo `<div style=”position:absolute;font-size:11px;font-family:verdana,arial;background:#EBEBEB;padding:0.5em;line-height:1.5em”>`;

        echo “<b>PHP Backtrace</b><br />$sql error<br /> $dberror</div>”;
        exit();
    }


    function delete($table, $condition, $limit = 0, $unbuffered = true) {
        if(empty($condition)) {
            $where = `1`;
        } elseif(is_array($condition)) {
            $where = $this->implode_field_value($condition, ` AND `);
        } else {
            $where = $condition;
        }
        $sql = “DELETE FROM “.$this->table($table).” WHERE $where “.($limit ? “LIMIT $limit” : “);
        return $this->query($sql, ($unbuffered ? `UNBUFFERED` : “));
    }

function insert($table, $data, $return_insert_id = false, $replace = false, $silent = false) {

$sql = $this->implode_field_value($data);

$cmd = $replace ? `REPLACE INTO` : `INSERT INTO`;

$table = $this->table($table);
$silent = $silent ? `SILENT` : “;

$return = $this->query(“$cmd $table SET $sql”, $silent);

return $return_insert_id ? $this->insert_id() : $return;

}

    function implode_field_value($array, $glue = `,`) {
        $sql = $comma = “;
        foreach ($array as $k => $v) {
            $sql .= $comma.”`$k`=`$v`”;
            $comma = $glue;
        }
        return $sql;
    }

}

mysql讀寫分離(PHP類)

mysql讀寫分離(PHP類)

相關文章