做MySQL读写分离实验之前,先要搭建MySQL主从复制,可以参考我之前的两篇文章:

MySQL主从配置:http://msiyuetian.blog.51cto.com/8637744/1697288

部署搭建分层次LAMP架构(最后一章节):http://msiyuetian.blog.51cto.com/8637744/1740236

实现了主从复制后,下面我们进行读写分离的安装与配置。环境说明:

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安装

[root@proxy ~]# yum install -y gcc gcc-c++ autoconf mysql-devel libtool pkgconfig ncurses ncurses-devel


2、安装libevent-2.0.22

[root@proxy ~]# cd /usr/local/src/

[root@proxy src]# wget https://github.com/libevent/libevent/releases/download/release-2.0.22-stable/libevent-2.0.22-stable.tar.gz

[root@proxy src]# tar xvf libevent-2.0.22-stable.tar.gz

[root@proxy src]# cd libevent-2.0.22-stable

[root@proxy libevent-2.0.22-stable]# ./configure

[root@proxy libevent-2.0.22-stable]# make

[root@proxy libevent-2.0.22-stable]# make install


3、安装glib-2.20.4

[root@proxy src]# wget http://ftp.gnome.org/pub/gnome/sources/glib/2.20/glib-2.20.4.tar.gz

[root@proxy src]# tar xvf glib-2.20.4.tar.gz

[root@proxy src]# cd glib-2.20.4

[root@proxy glib-2.20.4]# ./configure

[root@proxy glib-2.20.4]# make

[root@proxy glib-2.20.4]# make install


4、安装readline-6.1

[root@proxy src]# wget http://ftp.gnu.org/gnu/readline/readline-6.1.tar.gz

[root@proxy src]# tar xvf readline-6.1.tar.gz

[root@proxy src]# cd readline-6.1

[root@proxy readline-6.1]# ./configure

[root@proxy readline-6.1]# make

[root@proxy readline-6.1]# make install

[root@proxy readline-6.1]# ldconfig -v          //让动态链接库为系统所共享


5、安装 lua-5.1.4

[root@proxy src]# wget http://www.lua.org/ftp/lua-5.1.4.tar.gz

[root@proxy src]# tar xvf lua-5.1.4.tar.gz

[root@proxy src]# cd lua-5.1.4

[root@proxy lua-5.1.4]# vim src/Makefile        //64位系统需加上 -fPIC

CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS)

[root@proxy lua-5.1.4]# make linux

[root@proxy lua-5.1.4]# make install

验证是否安装成功

[root@proxy lua-5.1.4]# /usr/local/lua/bin/lua

Lua 5.1.4  Copyright (C) 1994-2008 Lua.org, PUC-Rio

>


6、更改环境变量

[root@proxy lua-5.1.4]# cp etc/lua.pc /usr/local/lib/pkgconfig/

[root@proxy lua-5.1.4]# export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/local/lib/pkgconfig

若版本为5.2以上则通过下面方式修改变量:

[root@proxy lua-5.2.3]# 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"

[root@proxy lua-5.2.3]# source /etc/profile


7、安装 mysql-proxy

[root@proxy src]# wget http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4.tar.gz

[root@proxy src]# tar xvf mysql-proxy-0.8.4.tar.gz

[root@proxy src]# cd mysql-proxy-0.8.4

[root@proxy mysql-proxy-0.8.4]# ./configure -prefix=/usr/local/mysql-proxy

[root@proxy mysql-proxy-0.8.4]# make

[root@proxy mysql-proxy-0.8.4]# make install

[root@proxy mysql-proxy-0.8.4]# cp lib/rw-splitting.lua /usr/local/lib/

[root@proxy mysql-proxy-0.8.4]# cp lib/admin.lua /usr/local/lib/


8、新建配置文件

[root@proxy src]# vim /etc/mysql-proxy.cnf

[mysql-proxy]

admin-username = root

admin-password = 123456

admin-lua-script = /usr/local/lib/admin.lua

proxy-read-only-backend-addresses = 192.168.0.110

proxy-backend-addresses = 192.168.0.109

proxy-lua-script = /usr/local/lib/rw-splitting.lua

log-file = /var/log/mysql-proxy.log

log-level = debug

daemon = true

keepalive = true

[root@proxy src]# chmod 660 /etc/mysql-proxy.cnf

说明:

admin-username :指定登入的用户名

admin-password :指定登入的密码

admin-lua-script :指定由admin插件执行的脚本

proxy-address = <ip:port> :mysql-proxy服务端的监听端口,默认是4040,建议改成3306

proxy-read-only-backend-addresses = <ip:port> :指定只读slave的地址和端口,若有多个slave,以逗号分隔

proxy-backend-addresses = <ip:port> :指定远程master的地址和端口,若不指定默认是127.0.0.1:3306

proxy-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、编辑读写分离脚本

[root@proxy src]# 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

[root@proxy src]# /usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.0.104:3306 --defaults-file=/etc/mysql-proxy.cnf


12、测试读写分离

[root@proxy src]# netstat -lnp                                 //查看已启动 mysql-proxy 服务

wKioL1cPRbTjUlFdAABBKVPkTIs375.png


[root@proxy src]# tail /var/log/mysql-proxy.log    //查看日志文件,如下表示成功

2016-04-14 22:32:11: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=56545 alive

2016-04-14 22:32:11: (debug) chassis-unix-daemon.c:157: waiting for 56545

2016-04-14 22:32:11: (debug) chassis-unix-daemon.c:121: we are the child: 56545

2016-04-14 22:32:11: (critical) plugin proxy 0.8.4 started

2016-04-14 22:32:11: (debug) max open file-descriptors = 1024

2016-04-14 22:32:11: (message) proxy listening on port 192.168.0.104:3306

2016-04-14 22:32:11: (message) added read/write backend: 192.168.0.109

2016-04-14 22:32:11: (message) added read-only backend: 192.168.0.110

wKioL1cPRfLxa7nBAABLlzuGk3s953.png


注意:MySQL Proxy服务启动后,我们可以看到网站页面全是乱码,所以要将主从数据库的配置文件都要加上如下代码以避免这个问题

# vim /etc/my.cnf

[mysqld]

skip-character-set-client-handshake init-connect = 'SET NAMES utf8'

default-character-set = utf8