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:
· 192.172.1.1 (Mysql Proxy Server’s IP)
· 192.172.1.2 (Mysql Slave)
· 192.172.1.3 (Mysql Slave)
· 192.172.1.4 (Mysql Slave)
· 192.172.1.5 (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)
#!/bin/bash
#!/bin/bash
BASE_DIR=/usr/local/
BIN_DIR=${BASE_DIR}/sbin
pkill -f mysql-proxy
sleep 1
${BIN_DIR}/mysql-proxy \
--proxy-backend-addresses=192.172.1.2:3306 \
--proxy-backend-addresses=192.172.1.3:3306 \
--proxy-backend-addresses=192.172.1.4:3306 \
--proxy-backend-addresses=192.172.1.5:3306 &
--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=192.172.1.1 --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'),
proxy.connection.server.thread_id,
query,
proxy.connection.client.address,
proxy.connection.client.username,
error_status))
fh:flush()
end
end
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 192.172.1.2 -d 192.172.1.1 -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
#!/bin/sh
#
# 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.
/var/lock/subsys/local
/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 0.0.0.0:4040 0.0.0.0:* LISTEN 3778/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 3778/mysql-proxy
