FreeBSD编译SQL Relay手记
FreeBSD/Linux/Server December 18th, 2008 4,129 Viewstar -zxvf rudiments-0.31.tar.gz cd rudiments-0.31 ./configure --prefix=/usr/local/rudiments gmake all gmake install gmake clean cd ../
tar -zxvf sqlrelay-0.39.4.tar.gz cd sqlrelay-0.39.4/ ./configure --prefix=/usr/local/sqlrelay --with-rudiments-prefix=/usr/local/rudiments --with-mysql-prefix=/usr/local/mysql --with-freetds-prefix=/usr/local/freetds --with-php-prefix=/usr/local/php gmake all gmake install gmake clean
这时SQL Relay就安装到了/usr/local/sqlrelay并在/usr/local/php/lib/php/extensions/no-debug-non-zts-20060613会生成sql_relay.so给PHP使用的扩展库。
修改一下/usr/local/php/etc/php.ini将
extension_dir = "./"
改为
extension_dir = "/usr/local/php/lib/php/extensions/no-debug-non-zts-20060613"
这里的no-debug-non-zts-20060613目录会根据你的PHP版本不同而变化，然后在php.ini中加入
extension=sql_relay.so
这里的改好以后我们在看phpinfo()里的Additional Modules一栏会多出一个Module Name为sql_relay说明我们安装配置成功。
接下来我们将实现PHP连接SQL Relay所配置的一个连接池。
新建建一个SQL Relay的配置文件sqlrelay.conf
vi /usr/local/sqlrelay/etc/sqlrelay.conf
输入如下内容：
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<!– This instance maintains connections to the "master" MySQL database
on the masterdb machine. This instance only listens on the
unix socket /tmp/master.socket and thus cannot be connected to
by clients from another machine. –>
<instance id="master" port="9002" socket="/tmp/master.socket" dbase="mysql" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="false">
<users>
<user user="masteruser" password="masterpassword"/>
</users>
<connections>
<connection connectionid="master165" string="user=mysql5;password=123456;db=test;host=192.168.0.165;port=3306;" metric="1" behindloadbalancer="no"/>
</connections>
</instance>
<!– This instance maintains connections to 4 "slave" MySQL databases
on 4 slave machines. This instance only listens on the unix
socket /tmp/slave.socket and thus cannot be connected to by
clients from another machine. –>
<instance id="slave" port="9003" socket="/tmp/slave.socket" dbase="mysql" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="false">
<users>
<user user="slaveuser" password="slavepassword"/>
</users>
<connections>
<connection connectionid="slave169" string="user=mysql5;password=123456;db=test;host=192.168.0.169;port=3306;" metric="1" behindloadbalancer="no"/>
</connections>
</instance>
<!– This instance sends DML (insert,update,delete) and
DDL (create/delete) queries to the "master" SQL Relay instance
which, in turn, sends them to the "master" database.
This instance sends any other queries to the "slave" SQL Relay
instance which, in turn, distributes them over the "slave"
databases. –>
<instance id="datarouter" port="9000" socket="/tmp/datarouter.socket" dbase="router" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="false">
<users>
<user user="routeruser" password="routerpassword"/>
</users>
<router>
<!– send all DML/DDL queries to "master" –>
<route host="" port="" socket="/tmp/master.socket" user="masteruser" password="masterpassword">
<query pattern="^\s*select\s+.*\s+from\s+"/>
<query pattern="^\s*insert\s+into\s+"/>
<query pattern="^\s*update\s+"/>
<query pattern="^\s*delete\s+from\s+"/>
<query pattern="^\s*drop\s+table\s+"/>
<query pattern="^\s*create\s+table\s+"/>
</route>
<!– send all other queries to "slave" –>
<route host="" port="" socket="/tmp/slave.socket" user="slaveuser" password="slavepassword">
<query pattern=".*"/>
</route>
</router>
</instance>
</instances>
上面的内容完成了一个简单的通过判断执行的SQL语句来判断SQL发送给哪一台数据库执行。
Recent Comments