Adding Proxy Server to MySQL Cluster

It’s not uncommon that one of two Application Servers breaks and you need to manually edit config on the Application Client to point to the other Application Server.

To enable automatic failover switching we will setup a Proxy Server. In later tests we will examine how Proxy Server affects the delay.

Start out by downloading a MySQL Proxy.

// Install needed libraries

mgm1: #apt-get install libevent-dev pkg-config  liblua5.1-dev libglib2.0-0 libglib2.0-dev libmysqlclient-dev

// Extract mysql-proxy source

mgm1: # tar xvfz mysql-proxyl-0.8.3.tar.gz -C /usr/local/src
mgm1: # chown -R root.root /usr/local/src/mysql-proxy-0.8.3
mgm1: # cd /usr/local/src/mysql-proxy-0.8.3

// Configure MySQL Proxy

mgm1: /usr/local/src/mysql-proxy-0.8.3# ./configure

// Install MySQL Proxy

mgm1: /usr/local/src/mysql-cluster-gpl-7.3.2# make clean install

I installed the Proxy Server on Management Server 1 (mgm1) as it doesn’t have much load anyway.
// Config setup of proxy server
// Create mysql-proxy.conf in /usr/local/mysql

mgm1: /usr/local/mysql-proxy# vi mysql-proxy.conf
 
# mysql-proxy.conf
 
[mysql-proxy]
daemon = true
proxy-address = mgm1:3306
proxy-skip-profiling = true
keepalive = true
max-open-files = 1024
event-threads = 50
log-level = debug
proxy-backend-addresses = app1:3306,app2:3306
proxy-lua-scripts=/usr/local/mysql-proxy/lib/mysql-proxy/lua/proxy/balance.lua
 
admin-username = "name"
admin-password= "password"
admin-lua-script= /usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua

// Init-scripts (automatic startup at boot) for mysql-proxy

mgm1: /usr/local/mysql-proxy# vi mysql-proxy_daemon
 
#! /bin/bash
#
 
PROXY_PATH=/usr/local/mysql-proxy
DAEMON=mysql-proxy
 
OPTIONS="--defaults-file=$PROXY_PATH/mysql-proxy.conf"
PIDFILE=$PROXY_PATH/mysql-proxy.pid
LOGFILE=$PROXY_PATH/mysql-proxy.log
 
if ! test -x $PROXY_PATH/bin/$DAEMON; then
    echo "Can't find execute $PROXY_PATH/bin/$DAEMON file."
    exit 1
fi
 
start() {
    pids=`ps aux | grep -iv "grep" | grep -iv "$0" | grep -i "$DAEMON" | wc -l`
    if [ $pids -eq 0 ]; then
    	$PROXY_PATH/bin/$DAEMON $OPTIONS --pid-file $PIDFILE --log-file $LOGFILE
 
    	echo "$DAEMON daemon started."
    else
    	echo "$DAEMON daemon is already running."
    fi
}
 
stop() {
    pids=`ps aux | grep -iv "grep" | grep -iv "$0" | grep -i "$DAEMON" | wc -l`
    if [ $pids -ne 0 ]; then
 
    	pids=`ps aux | grep -iv "grep" | grep -iv "$0" | grep -i "$DAEMON" | awk '{ print $2 }'`
    	for pid in $(echo $pids); do
            kill $pid 2> /dev/null
    	done
 
    	sleep 2
 
    	pids=`ps aux | grep -iv "grep" | grep -iv "$0" | grep -i "$DAEMON" | wc -l`
    	if [ $pids -eq 0 ]; then
            echo "$DAEMON daemon stopped."
            rm -f $PIDFILE
    	else
            echo "Could not stop $DAEMON daemon."
    	fi
    else
    	echo "$DAEMON daemon is not running."
    fi
}
 
case "$1" in
    start)
    	start
    	;;
    stop)
    	stop
    	;;
    restart)
    	stop
    	sleep 3
    	start
    	;;
    *)
        echo $"Usage: $0 {start|stop|restart}" >&2
    	;;
esac
exit 0

// Launch mysql-proxy on mgm1

mgm1:/usr/local/mysql-proxy# ./mysql-proxy_daemon start
mysql-proxy daemon started.

// testing cluster with mysql-proxy (mgm1 == proxy1)

ubuntu:/usr/local/mysql# bin/mysql -u vasil -p -h proxy1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.11-ndb-7.3.2-cluster-gpl MySQL Cluster Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use clusterdb
Database changed
 
mysql> show tables;
+---------------------+
| Tables_in_clusterdb |
+---------------------+
| bigdata             |
| testcluster         |
+---------------------+
2 rows in set (0.00 sec)
 
mysql> select * from testcluster;
+----+----------------------------------------+
| id | message                                |
+----+----------------------------------------+
|  1 | first test our cluster!                |
|  2 | add new application server to cluster! |
+----+----------------------------------------+
2 rows in set (0.00 sec)
 
mysql> insert into testcluster(message) values('Test mysql-proxy');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from testcluster;
+----+----------------------------------------+
| id | message                                |
+----+----------------------------------------+
|  1 | first test our cluster!                |
|  2 | add new application server to cluster! |
|  3 | Test mysql-proxy                       |
+----+----------------------------------------+
3 rows in set (0.03 sec)
 
mysql>

// All Done!

, August 13, 2013. Category: MySQL Cluster.

About rupic

View all posts by rupic
←