Archive for the 'MySQL' Category

Running MySQL Proxy as a daemon

August 06th, 2008 | Category: Computers, Linux, MySQL
I had to figure out how to setup mySQL Proxy to run as a daemon (system service).
The original information came from this page and has been slightly modified.
It is assumed that mySQL Proxy has been installed and the mysql-proxy executable is located at /usr/local/sbin/mysql-proxy.
The first file is the init.d launch script:

/etc/init.d/mysql-proxy

#!/bin/sh
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql

# Source function library.
. /etc/rc.d/init.d/functions

PROXY_PATH="/usr/local/sbin"

prog="mysql-proxy"

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0

# Set default mysql-proxy configuration.
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid

# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ] ; then
        . /etc/sysconfig/mysql-proxy
fi

PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

# By default it's all good
RETVAL=0

# See how we were called.
case "$1" in
  start)
        # Start daemon.
        echo -n $"Starting $prog: "
        daemon $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --pid-file $PROXY_PID
        RETVAL=$?
        echo
        if [ $RETVAL = 0 ]; then
                touch /var/lock/subsys/mysql-proxy
        fi
        ;;
  stop)
        # Stop daemons.
        echo -n $"Stopping $prog: "
        killproc $prog
        RETVAL=$?
        echo
        if [ $RETVAL = 0 ]; then
                rm -f /var/lock/subsys/mysql-proxy
                rm -f $PROXY_PID
        fi
        ;;
  restart)
        $0 stop
        sleep 3
        $0 start
        ;;
  condrestart)
       [ -e /var/lock/subsys/mysql-proxy ] && $0 restart
       ;;
  status)
        status mysql-proxy
        RETVAL=$?
        ;;
  *)
        echo "Usage: $0 {start|stop|restart|status|condrestart}"
        RETVAL=1
        ;;
esac

exit $RETVAL
The launch script will either use the default configuration or read it from the file /etc/sysconfig/mysql-proxy if it exists.
To set mysql-proxy parameters create:

/etc/sysconfig/mysql-proxy

# Options to mysql-proxy
# do not remove --daemon
PROXY_OPTIONS="
 --daemon
 --proxy-backend-addresses=127.0.0.1:3306
 --proxy-backend-addresses=127.0.0.1:3307
 --proxy-lua-script=load-balancing.lua"
No comments

(partial) MySQL Proxy API Doc

May 13th, 2008 | Category: Computers, Linux, MySQL

I have been working a lot with MySQL proxy lately and trying to figure out how it works is a major PITA. There is virtually no documentation for it and the best way to understand how it actually works is to read the source code.

This is a list of API functions discovered by reading plugin.c in the mysql_proxy SVN source. The list may be incomplete or not entirely accurate.

Bear with me as I try to get better <pre> tag stylization, right now this is the best I can do.

[i] = integer based index

[s] = string based index

[proxy]

proxy.global - data structure shared amongst all lua state machines

proxy.global.config - sub structure (possibly added for esthetical reasons only)

proxy.backends - array of backends (shared)

proxy.connection - mysql connection object

[proxy.backends[i]]

proxy.backends[i].connected_clients - connected clients

proxy.backends[i].address - server Address

proxy.backends[i].state - status {BACKEND_STATE_UNKNOWN, BACKEND_STATE_UP, BACKEND_STATE_DOWN}

proxy.backends[i].type - type {BACKEND_TYPE_UNKNOWN, BACKEND_TYPE_RW, BACKEND_TYPE_RO}

proxy.backends[i].pool - connection pool object

[proxy.backends[i].pool]

proxy.backends[i].pool.max_idle_connections - max connections

proxy.backends[i].pool.min_idle_connections - min connections

proxy.backends[i].pool.users - hash table containing sockets hashed by username

[proxy.backends[i].pool.users[s]]

proxy.backends[i].pool.users[s].cur_idle_connections - number of sockets currently in the pool (meaning they are idle)

[proxy.connection]

proxy.connection.backend_ndx - id of active backend (magic value, setting it can trigger server socket changes, i.e: setting it to 0 returns the socket into the connection pool)

proxy.connection.server - server socket object

proxy.connection.client - client socket object

[proxy.connection.client]

proxy.connection.client.default_db - database

proxy.connection.client.username - user

proxy.connection.client.address - ip address (?)

proxy.connection.client.scrambled_password - password as it was sent from the client

[proxy.connection.server]

proxy.connection.server.default_db - database

proxy.connection.server.username - user

proxy.connection.server.address - ip address (?)

proxy.connection.server.scrambled_password - password as it was sent from the client

proxy.connection.server.mysqld_version - mysql version

proxy.connection.server.thread_id - connection id as set during the server handshake

proxy.connection.server.scramble_buffer - password hash (?)
2 comments

Add a self-generated SSL certificate to the list of trusted certificates

May 13th, 2008 | Category: Java, Linux, Mac OS X, MySQL

Usually Java only accepts SSL certificates that can be validated with one of the CA providers in JRE’s internal cacerts keystore.

The cacerts keystore is a file located at $JAVA_HOME/lib/security/cacerts

How to import a self generated SSL certificate

First, export the self-generated key 'mywebsite.com' to a file called mywebsite.com.cert on the server
keytool -export -keystore ~/mywebsite.com.keystore -alias mywebsite.com -file mywebsite.com.cert

Then download the cert file with FTP or SFTP to your local computer.

Finally, import the certificate 'mywebsite.com.cert' into a local cacerts keystore:
keytool -import -keystore $JAVA_HOME/lib/security/cacerts -storetype jks -alias mywebsite.com -file ./mywebsite.com.cert

References

keytool export command ˆ
keytool import command ˆ

Read more

No comments