※ MySQL이 static으로 설치된 경우 Drop-in Replacement Library는 사용할 수 없다.
※ MySQL의 wait_timeout이 28800(8시간)으로 기본값이 되어 있어 sqlrelay가 이 시간동안 유휴상태일 경우 죽는 문제가 있다고 함. MySQL에 주기적인 Ping Query를 요청하므로써 해결 가능함.
0. Current Directory
1. Source Download
# wget http://prdownloads.sourceforge.net/sqlrelay/sqlrelay-0.39.4.tar.gz?download
2. Extract Archive
# tar zxvf sqlrelay-0.39.2.tar.gz
3. Install rudiments
# ./configure
# make
# make install
# cd ..
4. Install sqlrelay
# ./configure --enable-small-code \
--enable-mysql-rpath \
--enable-freetds-rpath \ #freetds가 설치된 경우에만 입력(mssql)
--enable-php-rpath \
--with-php-ext-dir=/usr/local/apache/libexec
아래 사항을 확인해보자
***** Summary ***********************************************
Version : 0.39.2
APIs : C/C++ yes Perl yes
Python yes Ruby no
PHP yes Java no
TCL no Zope no
Connections : Oracle8 no MySQL dynamic
PostgreSQL dynamic SQLite no
FreeTDS dynamic Sybase no
ODBC no DB2 no
Firebird no MDB Tools no
*************************************************************
# make install
5. Configure sqlrelay
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<!-- Regular SQL Relay Instance -->
<instance id="biz-relay" port="9000" socket="/tmp/test-relay.socket"
dbase="mysql" connections="3" maxconnections="15" maxqueuelength="0"
growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
runasuser="root" runasgroup="root" cursors="5" authtier="listener"
handoff="pass" deniedips="" allowedips="" debug="listener_and_connection"
maxquerysize="65536" maxstringbindvaluelength="4000"
maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
listenertimeout="0" reloginatstart="false">
<users>
<user user="test" password="1234"/>
</users>
<connections>
<connection connectionid="testdb"
string="host=localhost;user=test;password=1234;db=test;"
metric="1" behindloadbalancer="no"/>
</connections>
</instance>
</instances>※ debug : none(남기지 않음), connection(접속로그만), listener_and_connection(쿼리까지 남음)
6.Edit bash_profile
# vi .bash_profile
PATH에 '/usr/local/firstworks/bin' 추가
7. Start&Stop sqlrelay
# sqlr-stop [biz-relay]
※ sqlr-stop시 instance-id를 입력하지 않으면 모든 instance가 종료된다.
8. Edit extension_dir & Add php module
extesion_dir="/usr/local/apache/libexec" (수정)
extension=sql_relay.so (추가)
9. Add MySQL drop-in replacement library to bash script 'apachectl' (Optional)
LD_PRELOAD='/usr/local/firstworks/lib/libmysql50sqlrelay.so'
export LD_PRELOAD
※ 이 부분은 PHP의 확장모듈 함수를 쓰지 않고 MySQL 함수인 mysql_connect를 사용했을 경우 자동으로 sqlrelay로 모든 쿼리를 던져준다. 이 부분을 설정할 경우 직접 MySQL에 접속이 불가능하기 때문에 되도록이면 권장하지 않는다.
10. Restart apache
11. Test Source
<?PHP
/**
* Drop-in Replacement Library
*/
echo 'Using Drop-in Replacement Library<br><hr>';
$dbconn = mysql_connect('localhost:9000', 'test', '1234');
if(!is_resource($dbconn)) {
echo 'no dbconn';
exit;
}
mysql_select_db('BIZ', $dbconn);
$rs = mysql_query("SELECT id, name FROM members", $dbconn);
echo '<xmp>';
while($data = mysql_fetch_assoc($rs)) {
print_r($data);
}
echo '</xmp>';
mysql_close($dbconn);
?>
<?PHP
/**
* sql_relay module
*/
echo 'Using sql_relay module function<br><hr>';
if(!extension_loaded('sql_relay.so')) dl("sql_relay.so");
$con = sqlrcon_alloc('localhost', 9000, '', 'test', '1234', 0, 1);
$cur = sqlrcur_alloc($con);
if(!sqlrcur_sendQuery($cur, "SELECT id, name FROM members")) {
echo sqlrcur_errorMessage($cur);
echo "\n";
}
sqlrcon_endSession($con);
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);
?>
댓글