Re: [Sqlrelay-discussion] MySQL PHP/Java Proxy

[David M. <dav...@fi...>]

This is actually quite complicated, and there are caveats, but it may be
possible :)

First, you need to set up SQL Relay on the local host, listening
on /var/lib/mysql/mysql.sock and talking to the database at sql:3306 and
using authtier="database" and a dummy user/password.

sqlrelay.conf:

...
<instances>
	<instance ... socket="/var/lib/mysql/mysql.sock" ...
authtier="database" ...>
		<users>
			<user user="dummy" password="dummy"/>
		</users>
		<connections>
			<connection connectionid="db"
string="user=...;password=...;db=...;host=sql;port=3306" metric="1"/>
		</connections>
	</instance>
</instances>

Verify that works by using sqlrsh to run test queries.

Next, you'll have to use the drop-in replacement library for MySQL on
the webserver.  See:
http://sqlrelay.sourceforge.net/sqlrelay/dropin/mysql.html
for details.

Basically, you need to edit the init script that starts apache and make
sure that the library is LD_PRELOAD'ed right before apache starts up.
Similar for Tomcat.

I've never used it with Tomcat before, so I'm not certain that it'll
work, but you can see if the library is loaded or not by getting the
process id of the running tomcat process and grepping for sqlr
in /proc/<pid>/maps.  You should see libmysql3sqlrelay or
libmysql40sqlrelay or libmysql41sqlrelay or libmysql5sqlrelay, depending
on which you loaded.  You can check apache the same way.

There are a few caveats to all of this:

PHP is often built with the code for MySQL compiled directly into the
PHP library itself.  So when PHP needs to talk to MySQL it doesn't load
the mysql client library, it just uses it's own built-in functions.  If
your PHP was built that way then the drop-in replacement library won't
work.  You'll need to rebuild and reinstall PHP so that it uses the
MySQL library rather than built-in code.

On the Tomcat side, the JDBC driver for MySQL may be pure java and may
not use the mysql client library either.  Or, it may be JNI and compiled
against the MySQL code just like PHP.  Again, if the JDBC driver doesn't
use libmysqlclient, then the drop-in library won't work.  You'll have to
find a JDBC driver that does use libmysqlclient and use it.

Also, the drop-in library doesn't support every mysql function.  So,
it's possible that your app may try to use a function that isn't
supported.  Most functions are supported and I haven't run into an app
that doesn't work yet, but your mileage may vary.


If you're socket-savvy, I'd think it would be simpler and more reliable
to write proxy program that listens on the socket and whenever it get's
a connection, it forks off a child which connects to mysql and ferries
data back and forth.  There may be programs out there that do it
already.  You may actually be able to do it with stunnel or something
like that.

Dave
dav...@fi...

On Wed, 2007-02-21 at 00:01 +0200, Max Korzhanoff wrote:
> Dear Community,
> 
>    I would like to setup "redirect" for mysql requests from local socket 
> (/var/lib/mysql/mysql.sock) to remote mysql server (sql:3306) with 
> authentication on remote server. It's for PHP(Apache) and Java(Tomcat) 
> applications that used local mysql server and now we moved mysql to 
> dedicated box. We can't touch applications and change hostname or 
> something like that. What would be best method and configuration to do 
> this? If it's possible please provide example.
> 
>    Thank you forward!
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion