Installation configuration of Mysql Proxy detailed tutorial

8.12.1.1 Installation of Mysql proxy

Install the required base components, and the base system can meet the component version requirements of LUA

Install LUA (determine if installation is required)
Yum-y install gcc gcc-c++ autoconf automake zlib libxml ncurses-devel libmc rypt libt Ool Flex
Pkgconfig glib* Readline-devel Libevent-devel

? Installing glib
http://ftp.gnome.org/pub/gnome/sources/glib/

? Installing LUA
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
Tar zvfx lua-5.1.4.tar.gz

Attention
Mkdir-p/usr/local/lua
CD Lua-5.1.4;vi Makefile
Sed-i ' s#install_top=. #INSTALL_TOP =/usr/local/lua#g ' Makefile installation path
If the 64-bit, you need to change the following
Sed-i ' s#^cflags=. #CFLAGS =-o2-fpic-wall $ (mycflags) #g ' Src/makefile

Make Linux && make install

Mkdir-p/usr/local/lua-5.1.4/lib/pkgconfig
CP./etc/lua.pc/usr/local/lua-5.1.4/lib/pkgconfig
Sed-i ' s#prefix=.* #prefix =/usr/local/lua-5.1.4/#g '/usr/local/lua-5.1.4/lib/pkgconfig/lua.pc
Export Pkg_config_path= $PKG _config_path:/usr/local/glib/lib/pkgconfig/:/usr/local/lua-5.1.4/lib/pkgconfig

? Installing Mysql-proxy

http://mirrors.sohu.com/mysql/MySQL-Proxy/

SOURCE Installation
wget http://launchpad.net/mysql-proxy/0.8/0.8.0/+download/mysql-proxy-0.8.0.tar.gz
TAR-ZXVF mysql-proxy-0.8.0.tar.gz
CD mysql-proxy-0.8.0/
./configure--pre--with-lua=/usr/local/lua-5.1.4--with-mysql=/usr/lib64/mysql/mysql_config
Make
Make install

Verify that:
Mysql-proxy-v

Binary Source Decompression Installation
Cd/usr/local/software
TAR-ZXVF mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
MV Mysql-proxy-0.8.5-linux-el6-x86-64bit/usr/local/mysql-proxy
Useradd mysql-proxy-s/sbin/nologin
Chmod-r 775/usr/local/mysql-proxy
Chown-r Mysql-proxy. /usr/local/mysql-proxy

? Configuring Environment variables
Vim/etc/profile
Lua_path= "/usr/local/mysql-proxy/share/doc/mysql-proxy/?" Lua
Export Lua_path
Export path= $PATH:/usr/local/mysql-proxy/bin
After saving and exiting, execute the following command
Source/etc/profile//Make variables effective immediately

8.12.1.2 mysql-proxy Configuration

Modify the configuration of the read-write separation script for MySQL proxy

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua默认最小4个(最大8个)以上的客户端连接才会实现读写分离, 现改为最小1个最大2个，便于读写分离的测试-- connection poolif not proxy.global.config.rwsplit then        proxy.global.config.rwsplit = {                min_idle_connections = 1,  // 改为1                max_idle_connections = 2,  // 改为2                is_debug = false

}
End
This is because Mysql-proxy detects client connections, and when the connection does not exceed the Min_idle_connections preset value,
There is no read-write separation, that is, the query operation takes place on master.

8.12.1.3 mysql-proxy Firewall configuration

-A input-m state--state new-m tcp-p TCP--dport 4040-j ACCEPT

8.12.1.4 Mysql Master-Slave Authorization

Grant Select,insert,update,delete on . to ' test ' @ ' 192.168.1.202 ' identified by ' 321321 '; Create a signed-in user and authorize

8.12.1.5 Mysql-proxy Start

Vim/etc/init.d/mysql-proxy//Create a new shell file and add the following:
#!/bin/sh
Lua_path=/usr/local/mysql-proxy/share/doc/mysql-proxy/?. lua:/usr/local/mysql-proxy/lib/mysql-proxy/lua/?. Lua
Mode=$1
If [-Z "$mode"]; Then
Mode= "Start"
Fi

Case $mode in
' Start ')
Mysql-proxy--daemon \
--log-level=debug \
--user=mysql-proxy \
--keepalive \
--log-file=/var/log/mysql-proxy.log \
--plugins= "proxy" \
--proxy-backend-addresses=10.204.3.9:3306 \
--proxy-read-only-backend-addresses=10.204.3.10:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua \
--plugins= "admin" \
--admin-username= "admin" \
--admin-password= "admin" \
--admin-lua-script= "/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua";;

' Stop ')
Killall Mysql-proxy
;;

' Restart ')
if $ stop; Then
$ start
Else
echo "Retart failed!!!"
Exit 1
Fi
;;
Esac
Exit 0

Save and exit
chmod 775/etc/init.d/mysql-proxy//Grant EXECUTE permission
./mysql-proxy.sh//Start Mysql-proxy

Detailed configuration parameters
--daemon Start with daemon mode
--admin-address=:4401 Specify the management port of MySQL proxy, no parameters, default 4041
--PROXY-ADDRESS=:3307 Specifies the listening port of the MySQL proxy, no default 4040 can also be represented by 127.0.0.1:3307
--PROXY-BACKEND-ADDRESSES=192.168.1.3:3306 specifies the port of master-written MySQL host
--proxy-read-only-backend-addresses=192.168.1.1:3306 specifying a read-only MySQL host port
--proxy-read-only-backend-addresses=192.168.1.2:3306 specify another read-only MySQL host port
--PROXY-LUA-SCRIPT=/USR/LOCAL/SHARE/MYSQL-PROXY/RW-SPLITTING.LUA Specifies the Lua script, where the rw-splitting script is used for read-write separation
--keepalive: Attempts to restart the mysql-proxy when it crashes;
--plugins=plugin: Plug-in loaded at mysql-proxy startup;
--user=user_name: The user running the mysql-proxy process;
--defaults-file=/path/to/conf_file_name: The configuration file path used by default, and its configuration section using [Mysql-proxy] identification;
--proxy-skip-profiling: Disable profile;
--pid-file=/path/to/pid_file_name: Process file name;

--plugins=proxy-b 192.168.1.219:3306-r 192.168.1.177:3306

8.12.1.6 Mysql-proxy Log

Monitoring the Boot log

Tail-f/var/log/mysql-proxy.log

8.12.1.7 Mysql-proxy Test Connection
Mysql-uadmin-padmin-h 10.204.3.8-p4040 # Admin mode for querying status
Mysql-uadmin-padmin-h 10.204.3.8-p4041 # Monitoring mode for connection operation

By default, only the following actions
mysql> show databases;
ERROR 1105 (07000): Use ' SELECT * from help ' to see the supported commands

Mysql> select * from Backends;
+-------------+------------------+---------+------+------+-------------------+
| Backend_ndx | Address | State | Type | UUID | connected_clients |
+-------------+------------------+---------+------+------+-------------------+
| 1 | 10.204.3.9:3306 | Unknown | RW | NULL | 0 |
| 2 | 10.204.3.10:3306 | Unknown | Ro | NULL | 0 |
+-------------+------------------+---------+------+------+-------------------+
2 rows in Set (0.00 sec)
Note: Since the Rw-splitting.lua script default has 4 links to enable separation, so open several terminals, multiple tests, you can also modify the relevant values inside;

8.12.1.8 mysql-proxy pressure test
Pressure test with Sysbench
[@s1. yejr.com ~]# sysbench--test=oltp--mysql-table-engine=innodb--oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock--mysql-user=root--mysql-db=test Prepare

[@s1. yejr.com ~]# sysbench--test=oltp--mysql-table-engine=innodb--oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock--mysql-user=root--mysql-db=test Run

8.12.1.9 Mysql-proxy Error

GCC-O2-FPIC-WALL-DLUA_USE_LINUX-C-O lua.o lua.c
In a file that contains self-lua.h:16,
From lua.c:15:
LUACONF.H:275:31: Error: readline/readline.h: no file or directory
LUACONF.H:276:30: Error: Readline/history.h: no file or directory

Workaround: Yum install-y readline-devel

Checking for LUA ... no
Checking for LUA ... configure:error:Package requirements (lua5.1 >= 5.1) were not met:
No package ' lua5.1 ' found

Workaround: Vi/etc/profile
Export lua_cflags= "-i/usr/local/lua-5.1.4/include/"
Export lua_libs= "-l/usr/local/lua-5.1.4/lib/-LLUA-LDL"

Checking for Event_init in-levent ... no
Configure:error:libevent is required

Workaround: Yum Install Libevent-devel

Installation configuration of Mysql Proxy detailed tutorial