主页 > Linux | MySQL | PHP | WEB开发 > 数据库池连接代理服务器SQL Relay的安装配置使用

数据库池连接代理服务器SQL Relay的安装配置使用

2011 年 12 月 13 日 没有评论

具体的SQL Relay是什么,我相信网上有很多很多的文章,你可以去百度或者是谷歌上面找。
不说废话了,开始安装SQL Relay,到 http://sqlrelay.sourceforge.net/ 去下载相应的软件
安装SQL Relay需要先安装Rudiments,至于为什么,因为这是它以来的gcc编译包。 在 http://sqlrelay.sourceforge.net/download.html 可以找到。
1、安装Rudiments:

# tar vxzf rudiments-0.34.tar.gz
# cd rudiments-0.34
# ./configure --prefix=/usr/local/rudiments
# make
# make install

至此,rudiments安装结束
2、安装SQL Relay:

# tar vxzf sqlrelay-0.43.tar.gz
# cd sqlrelay-0.43
# ./configure --prefix=/usr/local/sqlrelay --with-rudiments-prefix=/usr/local/rudiments --with-mysql-prefix=MySQL安装路径 --with-php-prefix=PHP安装路径
# make
# make install

安装结束,以上编译参数根据个人需要来设定,我用的PHP+MYSQL的环境,所以只需要这两个东西!
安装结束之后会再PHP的extension_dir目录生成一个sql_relay.so文件,这个就是编译生成的SQL Relay的php拓展,我们需要把它加到php.ini文件中。
然后重启php-fpm,查看下phpinfo,看下是否加载了sql_relay拓展!
安装结束之后,就是如何使用了,在我们安装的/usr/local/sqlrelay/share/doc目录下面,是关于sql_relay的PHP的API以及配置参数,全部都在这个目录下面,只不过全是英文的,看起来很吃力啊。
下面是我根据提供的API封装的一个CLASS,

<?php
/**
 *@desc 数据访问层的操作
 *@author fbbin
 */
define('DB_USER',               'fbbin' );
define('DB_PASSWD',             'binbin' );
define('DB_ID',                 'router' );
define('DB_PORT',               9090 );
define('DB_SOCKET',             '/tmp/router.socket' );
define('DB_RETRYTIME',          0 );
define('DB_TRIES',              1 );
define('DB_BUFFER_SIZE',        0 );
define('DB_TIMEOUT',            10 );
define('DB_TRANS_AUTO_COMMIT',  FALSE );
define('DB_OK',                 TRUE );
define('DB_FAIL',               FALSE );
define('DB_DEBUG',              true );
define('DB_CHARSET',            'utf8' );
define('DB_SAVEQUERY',          TRUE );
define('DB_CACHE',              false );
define('DB_CACHE_TTL',          600 );
define('DB_CACHE_PATH',         './' );

class SqlrelayAction
{
	var $connection = '';
	var $cursor = '';
	var $affectedrows = '';
	var $errorNum = -1;
	var $queries = array();
	var $queryCount = 0;
	var $transStatus = true;
	
	/**
	 * construct
	 */
	public function __construct()
	{
		if( !$this->isSupport() )
		{
			$this->errorNum = 4;
			if( DB_DEBUG )
			{
				exit($this->getError());
			}
			return DB_FAIL;
		}
		sqlrcon_setTimeout( DB_TIMEOUT );
		if( !is_resource($this->connection) || !is_object($this->connection) )
		{
			$this->_dbConnect();
		}
		return $this->_init();
	}
	
	/**
	 * @desc 应用初始化,检测数据库是否启动等
	 * @access protected
     * @return void
	 */
	protected function _init()
	{
		$_init = sqlrcon_ping( $this->connection );
		if ( !$_init )
		{
			$this->errorNum = 0;
			if( DB_DEBUG )
			{
				exit($this->getError());
			}
			return DB_FAIL;
		}
		$this->checkTrans();
		$this->_initCursor();
		$this->setCharset();
	}
	
	/**
	 * @desc 创建数据池链接
	 * @access protected
     * @return bool
	 */
	protected function _dbConnect()
	{
		$this->connection = sqlrcon_alloc(DB_ID, DB_PORT, DB_SOCKET, DB_USER, DB_PASSWD, DB_RETRYTIME, DB_TRIES);
		return true;
	}
	
	/**
	 * @desc 获取操作句柄
	 * @access protected
     * @return bool
	 */
	protected function _initCursor()
	{
		$this->cursor = sqlrcur_alloc( $this->connection );
		return true;
	}
	
	/**
	 * @desc 预查询准备
	 * @access protected
     * @return void
	 */
	protected function _preQuery()
	{
		if( DB_CACHE )
		{
			$this->cacheOn( $query );
		}
		//将结果集存入缓冲区
		sqlrcur_setResultSetBufferSize($this->cursor, DB_BUFFER_SIZE);
	}
	
	/**
	 * @desc 执行一条SQL语句
	 * @param  $query 查询的SQL
	 * @access public
     * @return void
	 */
	public function query( $query = '' )
	{
		if( empty($query) )
		{
			$this->errorNum = 1;
			return DB_FAIL;
		}
		$isSelect = $this->isSelect($query);
		if( $isSelect && DB_CACHE )
		{
			$tmp = sqlrcur_openCachedResultSet($this->cursor, MD5($query));
			if( $tmp )
			{
				return $this->fetchAssoc();
			}
		}
		//
		$this->_preQuery( $query );
		
		if( DB_SAVEQUERY )
		{
			$this->queries[] = $query;
		}
		$this->queryCount++ ;
		
		if ( !sqlrcur_sendQuery($this->cursor, $query) ) {
			$this->affectedrows = 0;
			if( DB_DEBUG )
			{
				echo sqlrcur_errorMessage( $this->cursor );
				sqlrcur_free( $this->cursor );exit;
			}
			$this->transStatus = false;
			return DB_FAIL;
		}
		$this->affectedrows = $isSelect ? 0 : sqlrcur_affectedRows( $this->cursor );
		if( DB_CACHE )
		{
			$this->cacheOff();
		}
		if ( $isSelect ) {
			return $this->fetchAssoc();
		}
		return $this->affectedrows;
	}
	
	/**
	 * @desc 遍历查询的结果集
	 * @access protected
     * @return array
	 */
	protected function fetchAssoc()
	{
		$tmp = array();
		for($row = 0, $count = sqlrcur_rowCount($this->cursor); $row < $count; $row++)
		{
			$tmp[] = sqlrcur_getRowAssoc($this->cursor, $row);
		}
		
		if( sqlrcon_getDebug($this->connection) )
		{
			sqlrcon_debugOff( $this->connection );
		}
		sqlrcur_free( $this->cursor );
		
		return $tmp;
	}
	
	/**
	 * @desc 启动事务检测
	 * @access protected
     * @return bool
	 */
	protected function checkTrans()
	{
		if ( DB_TRANS_AUTO_COMMIT ) 
		{
            sqlrcon_autoCommitOn( $this->connection );
        } 
        else 
        {
            //sqlrcon_autoCommitOff( $this->connection );
        }
        return DB_OK;
	}
	
	/**
	 * @desc 启动事务
	 * @access public
     * @return bool
	 */
	public function transStart()
	{
		$this->transStatus = true;
		return sqlrcon_autoCommitOff( $this->connection );;
	}
	
	/**
	 * @desc 提交事务
	 * @access public
     * @return bool
	 */
	public function transCommit()
	{
		if( sqlrcon_commit($this->connection) == 1 )
		{
			return DB_OK;
		}
		else
		{
			$this->errorNum = 2;
			return DB_FAIL;
		}
	}
	
	/**
	 * @desc 获取操作的事务状态
	 * @access public
     * @return bool
	 */
	public function transStatus()
	{
		return $this->transStatus;
	}
	
	/**
	 * @desc 事务回滚
	 * @access public
     * @return bool
	 */
	public function transRollback()
	{
		if( sqlrcon_rollback($this->connection) == 1 )
		{
			return DB_OK;
		}
		else 
		{
			$this->errorNum = 3;
			return DB_FAIL;
		}
	}
	
	/**
	 * @desc 开启SQL Relay缓冲
	 * @param  $query 查询的SQL
	 * @access protected
     * @return bool
	 */
	protected function cacheOn($query = '')
	{
		sqlrcur_cacheToFile($this->cursor, DB_CACHE_PATH.MD5($query));

		sqlrcur_setCacheTtl($this->cursor, DB_CACHE_TTL);
		
		return true;
	}
	
	/**
	 * @desc 关闭SQL Relay缓冲
	 * @access protected
     * @return void
	 */
	protected function cacheOff()
	{
		sqlrcur_cacheOff( $this->cursor );
	}
	
	/**
	 * @desc 设置查询编码
	 * @access protected
     * @return void
	 */
	protected function setCharset()
	{
		return sqlrcur_sendQuery($this->cursor, "set names " . DB_CHARSET);
	}
	
	/**
	 * @desc 检测系统是否支持SQL Relay
	 * @access protected
     * @return bool
	 */
	protected function isSupport()
	{
		if( extension_loaded('sql_relay') && function_exists( 'sqlrcon_alloc' ) )
		{
			return true;
		}
		else 
		{
			return false;
		}
	}
	
	/**
	 * @desc 挂起结果集,挂起会话
	 * @access public
     * @return resoure
	 */
	public function suspendResult()
	{
		sqlrcur_suspendResultSet($this->cursor);
		sqlrcon_suspendSession($this->connection);
		$return['ResultSetId'] = sqlrcur_getResultSetId($this->cursor);
		$return['PortId']      = sqlrcon_getConnectionPort($this->connection);
		$return['SocketId']    = sqlrcon_getConnectionSocket($this->connection);
        return $return;
	}
	
	/**
	 * @desc 恢复结果集,恢复会话
	 * @access public
     * @return bool
	 */
	public function resumeResult( $params )
	{
		if( !$this->connection )
		{
			$this->_dbConnect();
		}
		if( !$this->cursor )
		{
			$this->_initCursor();
		}
		//恢复会话
		sqlrcon_resumeSession($this->connection, $params['PortId'], $params['SocketId'] );
		return sqlrcur_resumeResultSet($this->connection, $params['ResultSetId']);
	}
	
	/**
	 * @desc 检测一条SQL是否是SELECT语句,并发送字段信息与否
	 * @access protected
     * @return bool
	 */
	protected function isSelect( $query = '' )
	{
		$is = ( preg_match('/^\s*SHOW TABLES\s*/si', $query) || preg_match('/^\s*\(?\s*SELECT\s+/si', $query) ) &&
                    !preg_match('/^\s*\(?\s*SELECT\s+INTO\s+/si', $query);
		//告诉服务器是否发送字段信息
		if( $is )
		{
			sqlrcur_getColumnInfo( $this->cursor );
		}
		else 
		{
			sqlrcur_dontGetColumnInfo( $this->cursor );
		}
		return $is;
	}
	
	/**
	 * @desc 获取SQL操作影响的行数
	 * @access public
     * @return intval
	 */
	public function affectedRows()
	{
		return $this->affectedrows;
	}
	
	/**
	 * @desc 结果集的列数
	 * @access public
     * @return intval
	 */
	public function colNums()
    {
        return sqlrcur_colCount($this->cursor);
    }
	
    /**
     * @desc 返回最后一条SQL
     * @access public
     * @return string
     */
    public function lastQuery()
    {
    	return end($this->queries);
    }
    
    /**
     * @desc 获取debug调试信息
     * @access public
     * @return string
     */
    public function showDebugInfo()
    {
    	return sqlrcon_debugOn($this->connection);
    }
    
    /**
     * @desc 返回数据操作的相关版本信息
     * @access public
     * @return array
     */
    public function version()
    {
    	$dbtype = sqlrcon_identify( $this->connection );
    	return array($dbtype.' version'=>sqlrcon_dbVersion( $this->connection ),
    				'SQL Relay server version'=>sqlrcon_serverVersion( $this->connection ),
    				'SQL Relay client version'=>sqlrcon_clientVersion( $this->connection )
    				);
    }
    
    /**
     * @desc 结果集的行数
     * @access public
     * @return intval
     */
	public function rowNums()
    {
        return sqlrcur_rowCount($this->cursor);
    }
    
	/**
	 * @desc 获取错误信息
	 * @access public
     * @return array
	 */
	public function getErrorInfo()
	{
		$errorInfo = array('The Database is down!',
						   'The Sql you commit was empty!',
						   'The Transaction commit fail!',
						   'The Transaction rollback fail!',
						   'The System do not support SQL Relay action!');
		if( $this->errorNum >=0 )
		{
			return $errorInfo[$this->errorNum];
		}
		else {
			return sqlrcur_errorMessage( $this->cursor );
		}
	}
	
	/**
	 * @desc 释放系统资源
	 */
	public function __destruct()
	{
		sqlrcon_endSession($this->connection);
		sqlrcon_free($this->connection);
        $this->connection = null;
        $this->queryCount = 0;
        return DB_OK;
	}
	
}

$obj = new SqlrelayAction();
//$obj->showDebugInfo();
$sel = "update dk_users set `usr_lastname`='fbbin001'";
$up = "select * from `dk_users`";
$arr = $obj->query( $up );

echo $obj->queryCount;

print_r($arr);

?>

这个些都是很简单的一些东西,但是由于是英文的文档,看得我相当吃力啊,花费了不少时间啊,其实主要的功能是在它的一个实现读写分离技术上面,这就需要看他额配置文件,配置文件位于/usr/lcoal/sqlrelay/etc/sqlrelay.conf,下面是我的配置文件:

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>

	<!-- Regular SQL Relay Instance -->
	<instance id="reader" port="9091" socket="/tmp/reader.socket" dbase="mysql" connections="6" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="listener" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no" timequeriessec="-1" timequeriesusec="-1">
		<users>
			<user user="fbbin" password="binbin"/>
		</users>
		<connections>
			<connection connectionid="reader_one" string="host=127.0.0.1;user=fbbin;password=binbin;db=slave;port=3306;charset=utf8" metric="1" behindloadbalancer="no"/>
			<connection connectionid="reader_two" string="host=127.0.0.1;user=fbbin;password=binbin;db=slave_two;port=3306;charset=utf8" metric="1" behindloadbalancer="no"/>
		</connections>
	</instance>

	<instance id="writer" port="9092" socket="/tmp/writer.socket" dbase="mysql" connections="4" maxconnections="15" maxqueuelength="5" growby="2" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="listener" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no" timequeriessec="-1" timequeriesusec="-1">
		<users>
			<user user="fbbin" password="binbin"/>
		</users>
		<connections>
			<connection connectionid="writer_one" string="host=127.0.0.1;user=fbbin;password=binbin;db=master;port=3306;charset=utf8" metric="1" behindloadbalancer="no"/>
			<connection connectionid="writer_two" string="host=127.0.0.1;user=fbbin;password=binbin;db=master_two;port=3306;charset=utf8" metric="1" behindloadbalancer="no"/>
		</connections>
	</instance>

	<instance id="router" port="9090" socket="/tmp/router.socket" dbase="router" connections="3" maxconnections="10" maxqueuelength="5" growby="2" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="listener" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no">
		<users>
			<user user="fbbin" password="binbin"/>
		</users>
		<router>
			<route host="127.0.0.1" port="9092" socket="/tmp/writer.socket" user="fbbin" password="binbin">
				<query pattern="^\s*insert\s+into\s+"/>
				<query pattern="^\s*INSERT\s+INTO\s+"/>
                                <query pattern="^\s*update\s+"/>
				<query pattern="^\s*UPDATE\s+"/>
                                <query pattern="^\s*delete\s+from\s+"/>
				<query pattern="^\s*DELETE\s+FROM\s+"/>
                                <query pattern="^\s*drop\s+table\s+"/>
				<query pattern="^\s*DROP\s+TABLE\s+"/>
                                <query pattern="^\s*create\s+table\s+"/>
				<query pattern="^\s*CREATE\s+TABLE\s+"/>
                        </route>
                        <route host="127.0.0.1" port="9091" socket="/tmp/reader.socket" user="fbbin" password="binbin">
				<query pattern="^\s*select\s+.*\s+from\s+"/>
				<query pattern="^\s*SELECT\s+.*\s+FROM\s+"/>
				<query pattern=".*"/>
                        </route>    
                </router>
	</instance>

</instances>

这里呢就模拟实现了数据的读写分离,至于这里面的XML的tag所对应的是什么意思,那么就需要去看看文档,我也是研究半天才弄出来的,不容易啊 !其实能写到这里很多都是参考了网上的一些资料,虽然很多资料不全,很多资料吵来吵去的,哎,这就需要自己去整理了。

参考资料:
1、http://www.linuxsir.org/main/?q=node/144;
2、http://www.ydmsh.com/www/Blog/Show/id/152/;
3、http://www.ydmsh.com/www/Blog/Show/id/149/
4、更多的是看/usr/local/sqlrelay/share/doc下面的英文文档了

发表评论

电子邮件地址不会被公开。 必填项已用*标注


*

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>