Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions. MVCC means that, when a row is updated, the database stores both the old and new versions of the row.


Tuesday, November 1, 2011

Load Balance Mysql Slaves Using MySQLProxy

I am sure you all have read about MySQL replication, like Master-Master, Single Master - Multiple Slaves and their topologies.
Here I am going to describe how you can load balance several MySQL Slaves and query logging.

Required packages:
·         MysqlProxy

·         Lua

·         Yum (readline readline-devel libtermcap-devel ncurses-devel  libevent  libevent-devel  mysql-devel openssl krb5-libs glib2-devel)

Our Servers:

· (Mysql Proxy Server’s IP)

·  (Mysql Slave)

·  (Mysql Slave)

·  (Mysql Slave)

·  (Mysql Slave)

Install Mysql Proxy:
I am using MysqlProxy Ver.  0.6.1, download this version from your own way or use the below lines.

#   cd /mnt/download

#   tar zxvf mysql-proxy-0.6.1.tar.gz

 Download Lua, if you want logging:

#   tar xzf  lua-5.1.3.tar.gz

#   cd lua-5.1.3

#   yum install readline readline-devel libtermcap-devel ncurses-devel  libevent  libevent-devel

#   make linux

#   make install

Lua Done

Now go to mysql-proxy under download directory

#   cd /mnt/download/ mysql-proxy-0.6.1

#  yum install mysql-devel openssl krb5-libs glib2-devel

#   ./configure --prefix=/opt/mysql-proxy --with-lua LDFLAGS="-lm -ldl" LUA_CFLAGS="-I/usr/lib64" LUA_LIBS=/usr/lib64/liblua5.1.a

#   make

#   make install

MysqlProxy Installation Done:

Now we have to create a Bash File for automating task:

#   vi  /scripts/runproxy.sh (I put all my script under /scripts/, so change accordingly)




pkill -f mysql-proxy

sleep 1

${BIN_DIR}/mysql-proxy \

--proxy-backend-addresses= \

--proxy-backend-addresses= \

--proxy-backend-addresses= \

--proxy-backend-addresses= &

--proxy-lua-script=/scripts/simple-log.lua  &

sleep 1

echo "Testing Query: select * FROM speed limit 1;"

echo "select * FROM speed limit 1;" |  /usr/local/mysql/bin/mysql --host= --port=4040 --user=root --password=mysql-password test || { echo "${sqlStatement}: failed (is that server up?)"; exit 1; }

#   vi /scripts/simple-log.lua

local log_file = '/var/log/mysql-proxy/mysql.log'

local fh = io.open(log_file, "a+")

function read_query( packet )

 if string.byte(packet) == proxy.COM_QUERY then

   local query = string.sub(packet, 2)

   fh:write( string.format("%s %6d -- %s :IP %s :USER: %s\n",

   os.date('%Y-%m-%d %H:%M:%S'),









Now time to execute the script, but still some work before running the script.

Have to edit IPTABLES :

#   vi /etc/sysconfig/iptables  (On MysqlProxy Server)

-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 4040 -j ACCEPT

-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 4041 -j ACCEPT

#   vi /etc/sysconfig/iptables  (On Mysql Slave Servers)

-A RH-Firewall-1-INPUT -s -d -p tcp -m state --state NEW,ESTABLISHED,RELATED -m tcp --dport 4040 -j ACCEPT

Apply IPTABLES Rules, restart IPtables

Now run the script:

#   chmod 755 /scripts/runproxy.sh

#   /bin/sh /scripts/runproxy.sh

Or Just add this file to :  /etc/rc.local

# vi /etc/rc.local



# This script will be executed *after* all the other init scripts.

# You can put your own initialization stuff in here if you don't

# want to do the full Sys V style init stuff.


/bin/sh  /scripts/runproxy.sh

Check MysqlProxy is running:

# netstat -tnlp

Active Internet connections (only servers)

Proto     Recv-Q  Send-Q Local Address               Foreign Address             State       PID/Program name

tcp        0             0          *                   LISTEN      3778/mysql-proxy

tcp        0             0          *                   LISTEN      3778/mysql-proxy

No comments:

Post a Comment