Mysql数据库读写分离简单配置_MySQL

bitsCN.com

Mysql数据库读写分离简单配置

 

环境：

 

Master：192.168.71.128      mysql-sql-node1Slave：192.168.71.140       mysql-data-node1Mysql-Proxy：192.168.71.138 mysql-mgm-noderpm -q libevent glib2 pkgconfig libtool mysql-devel

查看系统是否已安装以上包，如未安装，使用yum install 安装。

yum install -y libtermcap-devel ncurses-devel libevent-devel readline-devel gcc-c++

一、下载程序：

wget http://www.lua.org/ftp/lua-5.1.4.tar.gzwget http://gd.tuwien.ac.at/db/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.6.0.tar.gzwget http://www.monkey.org/~provos/libevent-1.4.13-stable.tar.gz

二、安装lua：

[root@mailtest /software]# tar zxvf lua-5.1.4.tar.gz[root@mailtest lua-5.1.4]#  cd lua-5.1.4[root@mailtest lua-5.1.4]# # vi Makefile修改：INSTALL_TOP= /usr/local/lua[root@mailtest lua-5.1.4]#  make && make linux && make install设置环境变量[root@mailtest lua-5.1.4]# export LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl" LDFLAGS="-L/usr/local/libevent/lib -lm"[root@mailtest lua-5.1.4]# export CPPFLAGS="-I/usr/local/libevent/include"[root@mailtest lua-5.1.4]# export CFLAGS="-I/usr/local/libevent/include"

三、安装 libevent：

tar zvxf libevent-1.4.13-stable.tar.gzcd libevent-1.4.13-stable./configure --prefix=/usr/local/libeventmake && make install

四、安装mysql-proxy：

./configure --prefix=/usr/local/mysql-proxy --with-mysql=/usr/local/mysql --with-luamake && make install启动：/usr/local/mysql-proxy/sbin/mysql-proxy --proxy-read-only-backend-addresses=192.168.71.140:3306 --proxy-backend-addresses=192.168.71.128:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua &查看进程：netstat -ntlp | grep mysql编辑启动脚本：vi /etc/init.d/mysql-proxy#!/bin/bashPRODIR=/usr/local/mysql-proxyLUA_PATH=$PRODIR/share/mysql-proxystart(){$PRODIR/sbin/mysql-proxy --proxy-read-only-backend-addresses=192.168.71.140:3306 --proxy-backend-addresses=192.168.71.128:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua &>>$PRODIR/mysql-proxy.log &}stop(){kill $(pidof mysql-proxy)if [ $? -ne 0 ];thenkill -9 $(pidof mysql-proxy)fi}case "$1" instart)start;;stop)stop;;restart)stopstart;;*)echo $"Usage: $0 {start|stop|restart}"esac修改权限：chmod +x /etc/init.d/mysql-proxy/etc/init.d/mysql-proxy start测试：在MySQL-Master 上创建测试数据库和用户。mysql> create database unixhot;mysql> GRANT ALL PRIVILEGES ON king.* to proxytest@'%' identified by 'bobo365';连接测试[root@MySQL-Proxy ~]# mysql -h 192.168.71.138 -P 4040 -u proxytest -p输入密码即可登录。mysql> show processlist;+----+-----------+----------------------+------+---------+------+-------+------------------+| Id | User      | Host                 | db   | Command | Time | State | Info             |+----+-----------+----------------------+------+---------+------+-------+------------------+| 14 | proxytest | 192.168.71.138:17350 | NULL | Sleep   |    2 |       | NULL             || 15 | proxytest | 192.168.71.138:17355 | NULL | Query   |    0 | NULL  | show processlist || 16 | proxytest | 192.168.71.138:17358 | NULL | Sleep   |    2 |       | NULL             |+----+-----------+----------------------+------+---------+------+-------+------------------+3 rows in set (0.01 sec)

 

bitsCN.com