mysql-proxy如何实现数据库读写分离
本文主要给大家介绍mysql-proxy如何实现数据库读写分离，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在行业资讯里面关注我的更新文章的。
机器：192.168.21.139
完成实战之前的必要条件：主从复制环境搭建好。
1 MySQL Proxy
MySQL Proxy是一个处于你的Client端和MySQL server端之间的简单程序，它可以监测、分析或改变它们的通信。它使用灵活，没有限制，常见的用途包括：负载平衡，故障、查询分析，查询过滤和修改等等。
MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQLProxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用云服务器的连接池配置中配置到多个proxy的连接参数即可。
MySQL Proxy更强大的一项功能是实现“读写分离”，基本原理是让主数据库处理事务性查询，让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库。
2 读写分离架构
3 部署
主机名 | IP地址 | 系统版本 |
mysql-proxy | 192.168.21.139 | CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64 |
mysql-master | 192.168.21.135 | CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64 |
mysql-slave | 192.168.21.135 | CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64 |
4安装软件
4.1安装基础依赖包
yum -y install gcc gcc-c++autoconf libevent-devel pkgconfig libtool mysql-devel
4.2其他软件包
[root@zyl tools]# wget http://ftp.gnome.org/pub/gnome/sources/glib/2.22/glib-2.22.5.tar.gz
tar xf glib-2.22.5.tar.gz
cd glib-2.22.5
./configure--prefix=/usr/local/glib-2.22.5
make && make install
echo $?
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar xf lua-5.1.4.tar.gz
cd lua-5.1.4
sed -i's#^INSTALL_TOP=.*#INSTALL_TOP= /usr/local/lua-5.1.4#gi' ./Makefile
sed -i 's#^CFLAGS=.*#CFLAGS= -02-fPIC -Wall $(MYCFLAGS)#gi' ./src/Makefile
make linux install
echo $?
4.3安装mysql-proxy
tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
cdmysql-proxy-0.8.5-linux-glibc2.3-x86-64bit
cd ..
cp -amysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy
mv mysql-proxy /application/
cd /application/
ls
pkill mysql
netstat -lnput|grep 330
cd mysql
cd ../mysql-proxy/
ls
mkdir conf
mkdir logs
ls
cd ./conf/
ls
#添加到PATH环境变量
echo 'exportPATH=$PATH:/application/mysql-proxy/bin/' >>/etc/profile
tail -1 /etc/profile
source /etc/profile
which mysql-proxy
/application/mysql-proxy/bin/mysql-proxy--help
vim ./mysql-proxy.cnf
#############################################
简单一个配置(192.168.21.135：单机多实例)
[mysql-proxy]
plugins=admin,proxy
admin-username=admin
admin-password=admin
admin-lua-script=/application/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-backend-addresses=192.168.21.135:3306 èmaster
proxy-read-only-backend-addresses=192.168.21.135:3308èslave
proxy-lua-script=/application/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-level=debug 日志的级别
keepalive=true 在mysql-proxy崩溃时尝试重启之；
daemon=true 以守护进程模式启动mysql-proxy；
log-file=/application/mysql-proxy/logs/proxy.log
##########################
chmod 0660 mysql-proxy.cnf
启动：
/application/mysql-proxy/bin/mysql-proxy--defaults-file=/application/mysql-proxy/conf/mysql-proxy.cnf
cat logs/proxy.log
[root@zyl tools]# netstat -lnpt|grep mysql-proxy
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 4261/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 4261/mysql-proxy
4040是proxy端口；4041是admin端口，也就是管理。
5 mysql-master端操作
mysqlgrant all on *.* to 'zyl'@'192.168.21.139' identified by '888666';
mysql> flush privileges
##########################
多开几个终端，测试连接：
[root@zyl -master ]# mysql -u zyl -p -h 192.168.21.139 --port 4040
[root@zyl mysql-proxy]# mysql-uadmin -P4041 -h 192.168.21.139 -padmin
mysql> select * from help;
+------------------------+------------------------------------+
| command | description |
+------------------------+------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | liststhe backends and their state |
+------------------------+------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.21.135:3306 | unknown | rw |NULL | 0 |
| 2 | 192.168.21.135:3308 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
最终结果：
mysql> select * frombackends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid |connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 |192.168.21.135:3306 | up | rw | NULL | 0 |
| 2 |192.168.21.135:3308 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
##出现两个up,那么分离成功了。
第二章遇见的问题总结
1.
[root@zyl lua-5.1.4]# make linuxinstall
cd src && make linux
make[1]: Entering directory`/home/tools/lua-5.1.4/src'
make allMYCFLAGS=-DLUA_USE_LINUX MYLIBS="-Wl,-E -ldl -lreadline -lhistory-lncurses"
make[2]: Entering directory`/home/tools/lua-5.1.4/src'
gcc -O2 -Wall -fPIC -DLUA_USE_LINUX -c -o lua.o lua.c
在包含自 lua.h：16 的文件中，
从 lua.c：15:
luaconf.h:275:31: 错误：readline/readline.h：没有那个文件或目录
luaconf.h:276:30: 错误：readline/history.h：没有那个文件或目录
lua.c: 在函数‘pushline’中:
lua.c:182: 警告：隐式声明函数‘readline’
lua.c:182: 警告：赋值时将整数赋给指针，未作类型转换
lua.c: 在函数‘loadline’中:
lua.c:210: 警告：隐式声明函数‘add_history’
make[2]: *** [lua.o] 错误 1
make[2]: Leaving directory`/home/tools/lua-5.1.4/src'
make[1]: *** [linux] 错误 2
make[1]: Leaving directory`/home/tools/lua-5.1.4/src'
make: *** [linux] 错误 2
解决：
yum installlibtermcap-devel ncurses-devel libevent-devel readline-devel
-------------------------------------------------------------------------------------------
2.
[root@zyl tools]# mysql -u zyl -p -h 192.168.21.139 --port 4040
ERROR 1105 (HY000): #07000MySQLProxy Lua script failed to load. Check the error log.
解决：
[root@zyl lua]# pkillmysql-proxy
[root@zyl lua]# cp./mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua/opt/mysql-proxy/lib/mysql-proxy/lua/
[root@zyl lua]# vim/opt/mysql-proxy/init.d/mysql-proxy
更改脚本：
检查这个脚本：--proxy-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua"
-------------------------------------------------------------------------------------------
3.
[root@zyl lua]# mysql -u zyl -p -h 192.168.21.139 --port 4040
注：因为rw-splitting.lua脚本默认有4个链接才启用分离；所以多开启几个终端；多测试几下；你也可以去修改里面的相关值；
改rw-splitting.lua读写分离脚本如下参数：
4.编译glib时报错
configure: error:
*** You must have either havegettext support in your C library, or use the
*** GNU gettext library.(http://www.gnu.org/software/gettext/gettext.html
解决：
[root@zyl glib-2.22.5]# yum install -y gettext
-------------------------------------------------------------------------------------------
5.
[root@zyl conf]#/application/mysql-proxy/bin/mysql-proxy--defaults-file=/application/mysql-proxy/conf/mysql-proxy.cnf
2016-09-02 22:28:37: (critical)mysql-proxy-cli.c:326: loading config from'/application/mysql-proxy/conf/mysql-proxy.cnf' failed: permissions of/application/mysql-proxy/conf/mysql-proxy.cnf aren't secure (0660 or stricter required)
2016-09-02 22:28:37: (message)Initiating shutdown, requested from mysql-proxy-cli.c:328
2016-09-02 22:28:37: (message) shuttingdown normally, exit code is: 1
解决：
[root@zyl mysql-proxy]# chmod0660 mysql-proxy.cnf
看了以上关于mysql-proxy如何实现数据库读写分离，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。