1. software env
---------------
- Linux debian on both 2.6.18-5-686 and 2.6.18-5-amd64
- sqlrelay-0.39.4
- rudiments-0.31
- mysql-5.0.67
2. software installation
------------------------
$ apt-get install gcc zliblg-dev libxml2 libxml2-dev g++ libncurses5-dev php5-sqlrelay
$ aptitude search socket
$ make distclean //if get any error during software compile
2.1 rudiments-0.31
$ ./configure --prefix=/usr/local/app/rudiments-0.31
2.2 mysql-5.0.67
error:
checking for tgetent in -ltermcap... no
checking for termcap functions library... configure: error: No curses/termcap library found
need to install curses/termcap
$ apt-cache search curses | grep lib
$ apt-get install libncurses5-dev
$ ./configure --prefix=/usr/local/app/mysql-5.0.67
2.3 php-5.2.6
$ apt-get install php5-sqlrelay
$ ./configure --prefix=/usr/local/app/php5
PEAR config: /usr/local/app/php5/etc/pear.conf
Add: /usr/local/app/php5/lib/php to your php.ini include_path
PDO headers: /usr/local/app/php5/include/php/ext/pdo/
2.4 sqlrelay-0.39.4
./configure --prefix=/usr/local/app/sqlrelay-0.39.4 --with-rudiments-prefix=/usr/local/app/rudiments-0.31 --with-php-prefix=/usr/local/app/php5 --enable-small-code --with-mysql-prefix=/usr/local/app/mysql-5.0.67 --with-pear-db-dir=/var/www/etpass/app/lib/PEAR/DB
// it will generate:
1. sqlrelay.php under /var/www/etpass/app/lib/PEAR/DB
2. sqlrelay.so under /usr/local/app/php5/lib/php/extensions/no-debug-non-zts-20060613
$ cp sqlrelay.conf.example sqlrelay.conf
$ more /usr/local/app/sqlrelay-0.39.4/etc/sqlrelay.conf
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<!-- Regular SQL Relay Instance -->
<instance id="master" port="9000" socket="/tmp/master.socket" dbase="mysql" connections="5" maxconnections="15" maxqueuelength="5" growby="1" ttl="60\" endofsession="commit" sessiontimeout="600" unasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" axlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="1" listenertimeout="0" reloginatstart="false">
<users>
<user user="root" password="kooxoo"/>
</users>
<connections>
<connection connectionid="master" string="user=root;password=kooxoo;host=10.0.1.243;db=ETPASS" metric="1" behindloadbalancer="no"/>
</connections>
</instance>
<instance id="slave" port="9001" socket="/tmp/slave.socket" dbase="mysql" connections="5" 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="root" password="etpass"/>
</users>
<connections>
<connection connectionid="slave" string="user=root;password=etpass;host=10.0.1.204;db=ETPASS" metric="1" behindloadbalancer="no"/>
</connections>
</instance>
<!-- Query Router/Filter -->
<instance id="router" port="9002" socket="/tmp/router.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="user1" password="password1"/>
</users>
<router>
<!-- send all queries for table1 to host1 -->
<route host="" port="" socket="/tmp/master.socket" user="root" password="kooxoo">
<query pattern="^\s*insert\s+into\s+table1"/>
<query pattern="^\s*update\s+table1"/>
<query pattern="^\s*delete\s+from\s+table1"/>
<query pattern="^\s*drop\s+table\s+table1"/>
<query pattern="^\s*create\s+table\s+table1"/>
</route>
<!-- send any other queries to host2 -->
<route host="" port="" socket="/tmp/slave.socket" user="root" password="etpass">
<query pattern=".*"/>
</route>
</router>
</instance>
</instances>
|
3. configuration
$ cd php-5.2.6
$ cp php.ini-dist /usr/local/app/php5/etc/php.ini
$ e /usr/local/app/php5/etc/php.ini
extension_dir = "./:/usr/local/app/php5/lib/php/extensions/no-debug-non-zts-20060613"
$ file /usr/local/app/php5/lib/php/extensions/no-debug-non-zts-20060613/sqlrelay.so
$ export PATH=$PATH:/usr/local/app/sqlrelay-0.39.4/bin
or
$ e /etc/profile
#add below
export PATH=$PATH:/usr/local/app/sqlrelay-0.39.4/bin
$ ./sqlr-start -id master -config /usr/local/app/sqlrelay-0.39.4/etc/sqlrelay.conf
$ ./sqlr-start -id slave -config /usr/local/app/sqlrelay-0.39.4/etc/sqlrelay.conf
$ ./sqlr-start -id router -config /usr/local/app/sqlrelay-0.39.4/etc/sqlrelay.conf
4. monitor
$ netstat -na | more
$ ping localhost
$ telnet localhost 9000
$ telnet localhost 9001
$ telnet localhost 9002
5. testing
$ e /root/db_conn.php
<?
dl("sql_relay.so");
$con=sqlrcon_alloc("10.0.1.204",9002,"/tmp/router.socket","user1","password1",0,1);
$cur=sqlrcur_alloc($con);
sqlrcur_sendQuery($cur,"select * from tmp_order limit 1");
for($row=0;$row<sqlrcur_rowCount($cur);$row++) {
for ($col=0; $col<sqlrcur_colCount($cur); $col++) {
echo sqlrcur_getField($cur,$row,$col);
echo ",";
}
echo "
\n";
}
sqlrcur_free($cur);
sqlrcon_free($con);
?>
|
<?php
require_once 'DB.php';
echo "0";
echo "0.5";
$db = DB::connect("sqlrelay://user1:password1@10.0.1.204:9002/ETPASS");
echo "1";
if (DB::isError($db)) {
die ($db->getMessage());
}
echo "2";
$db->query("select * from tmp_order limit 1");
echo "3";
$db->disconnect();
?>
|
$ cd /usr/local/app/php5/bin
$ ./php -c ../etc/php.ini /root/db_conn.php
$ ./php -c ../etc/php.ini /root/pear_conn.php
6. Ref:
http://sqlrelay.sourceforge.net/
