Mysql读写分离
实现了主从复制后，下面我们进行读写分离的安装与配置。环境说明：Linux发行版本：centos 6.7 x86_64 （三台）
Hostname
IP备注
mysql-master
192.168.0.109主MySQL
mysql-slaver192.168.0.110从MySQL
mysql-proxy192.168.0.104调度服务器
以下所有操若未特殊说明，都是在 mysql-proxy 机器上操作。
1、安装所需软件包由于mysql-proxy实际上并不需要在本机上运行MySQL实例，所以这里用yum安装# yum install -y gcc gcc-c++ autoconf mysql-devel libtool pkgconfig ncurses ncurses-devel
2、安装libevent-2.0.22# cd /usr/local/src/# wget https://github.com/libevent/libevent/releases/download/release-2.0.22-stable/libevent-2.0.22-stable.tar.gz# tar xvf libevent-2.0.22-stable.tar.gz# cd libevent-2.0.22-stable# ./configure# make# make install
3、安装glib-2.20.4
# wget http://ftp.gnome.org/pub/gnome/sources/glib/2.20/glib-2.20.4.tar.gz# tar xvf glib-2.20.4.tar.gz
# cd glib-2.20.4# ./configure# make# make install
4、安装readline-6.1# wget http://ftp.gnu.org/gnu/readline/readline-6.1.tar.gz# tar xvf readline-6.1.tar.gz
# cd readline-6.1# ./configure# make# make install# ldconfig -v //让动态链接库为系统所共享
5、安装 lua-5.1.4# wget http://www.lua.org/ftp/lua-5.1.4.tar.gz# tar xvf lua-5.1.4.tar.gz
# cd lua-5.1.4# vim src/Makefile //64位系统需加上 -fPICCFLAGS= -O2 -Wall -fPIC $(MYCFLAGS)# make linux# make install验证是否安装成功
# /usr/local/lua/bin/luaLua 5.1.4Copyright (C) 1994-2008 Lua.org, PUC-Rio>
6、更改环境变量# cp etc/lua.pc /usr/local/lib/pkgconfig/# export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/local/lib/pkgconfig若版本为5.2以上则通过下面方式修改变量：
# vim /etc/profile //添加如下
export LUA_CFLAGS="-I/usr/local/lua/include"export LUA_LIBS="-L/usr/local/lua/lib-llua -ldl"export LDFLAGS="-L/usr/local/libevent/lib -lm"export CPPFLAGS="-I/usr/local/libevent/include"export CFLAGS="-I/usr/local/libevent/include"
# source /etc/profile
7、安装 mysql-proxy# wget http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4.tar.gz# tar xvf mysql-proxy-0.8.4.tar.gz
# cd mysql-proxy-0.8.4# ./configure -prefix=/usr/local/mysql-proxy# make# make install# cp lib/rw-splitting.lua /usr/local/lib/# cp lib/admin.lua /usr/local/lib/
8、新建配置文件# vim /etc/mysql-proxy.cnf
admin-username = rootadmin-password = 123456admin-lua-script = /usr/local/lib/admin.luaproxy-read-only-backend-addresses = 192.168.0.110proxy-backend-addresses = 192.168.0.109proxy-lua-script = /usr/local/lib/rw-splitting.lualog-file = /var/log/mysql-proxy.loglog-level = debugdaemon = truekeepalive = true
# chmod 660 /etc/mysql-proxy.cnf
说明：
admin-username ：指定登入的用户名admin-password ：指定登入的密码admin-lua-script ：指定由admin插件执行的脚本proxy-address = <ip:port> ：mysql-proxy服务端的监听端口，默认是4040，建议改成3306proxy-read-only-backend-addresses = <ip:port> ：指定只读slave的地址和端口，若有多个slave，以逗号分隔proxy-backend-addresses = <ip:port> ：指定远程master的地址和端口，若不指定默认是127.0.0.1:3306proxy-lua-script ：指定一个Lua脚本来控制mysql-proxy的运行和配置defaults-file ：指定mysql-proxy配置文件log-file ：指定日志文件log-level ：指定日志级别daemon = true ：指定mysql-proxy以守护进程方式运行keepalive = true ：开启该功能修正以前mysql-proxy容易死掉的bug
9、给用户授权在主MySQL执行：
mysql> grant all privileges on *.* to 'proxyuser'@'192.168.0.104' identified by '123456' with grant option;
在从MySQL执行：
mysql> grant all privileges on *.* to 'proxyuser'@'192.168.0.104' identified by '123456' with grant option;
10、编辑读写分离脚本# vim /usr/local/lib/rw-splitting.lua
找到：min_idle_connections = 4,max_idle_connections = 8,改为：min_idle_connections = 1,max_idle_connections = 2,
说明：修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离。
11、启动mysql-proxy# /usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.0.104:3306 --defaults-file=/etc/mysql-proxy.cnf
12、测试读写分离# netstat -lnp //查看已启动 mysql-proxy 服务
# tail /var/log/mysql-proxy.log //查看日志文件，如下表示成功
2016-04-14 22:32:11: (message) chassis-unix-daemon.c:136: we try to keep PID=56545 alive2016-04-14 22:32:11: (debug) chassis-unix-daemon.c:157: waiting for 565452016-04-14 22:32:11: (debug) chassis-unix-daemon.c:121: we are the child: 565452016-04-14 22:32:11: (critical) plugin proxy 0.8.4 started2016-04-14 22:32:11: (debug) max open file-descriptors = 10242016-04-14 22:32:11: (message) proxy listening on port 192.168.0.104:33062016-04-14 22:32:11: (message) added read/write backend: 192.168.0.1092016-04-14 22:32:11: (message) added read-only backend: 192.168.0.110
注意：MySQL Proxy服务启动后，我们可以看到网站页面全是乱码，所以要将主从数据库的配置文件都要加上如下代码以避免这个问题# vim /etc/my.cnfskip-character-set-client-handshake init-connect = 'SET NAMES utf8'default-character-set = utf8
页:
[1]