SQL Relay Setup

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;$rowsqlrcur_rowCount($cur);$row++) {
for ($col=0; $colsqlrcur_colCount($cur); $col++) {
echo sqlrcur_getField($cur,$row,$col);
echo ",";
}
echo "
\n";
}
sqlrcur_free($cur);
sqlrcon_free($con);
?>

$ e /root/pear_conn.php

?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/