mysql proxy 主从_MySQL Proxy主从读写分离

一、安装MySQL，主机：192.168.1.222/223/224 # wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.9.tar.gz/from/http://mirror.services.wisc.edu/mysql/# tar zxf mysql-5.5.9.tar.gz# cd mysql-5.5.9# useradd mysql# wget http://www.cmake

一、安装MySQL，主机：192.168.1.222/223/224

# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.9.tar.gz/from/http://mirror.services.wisc.edu/mysql/

# tar zxf mysql-5.5.9.tar.gz

# cd mysql-5.5.9

# useradd mysql

# wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz

# tar xzf cmake-2.8.4.tar.gz

# cd cmake-2.8.4

# ./configure && make && make install

# cmake . \

-DCMAKE_INSTALL_PREFIX=/opt/module/mysql/ \

-DMYSQL_DATADIR=/opt/data/mysql/ \

-DMYSQL_UNIX_ADDR=/opt/data/mysql/mysql.sock \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_TCP_PORT=3306 \

-DEXTRA_CHARSETS=gb2312,gbk,big5,utf8,ascii,latin1 \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DMYSQL_USER=mysql \

-DWITH_DEBUG=0

# make && make install

# cp support-files/my-medium.cnf /etc/my.cnf

# cp support-files/mysql.server /etc/init.d/mysqld

# chmod 755 /etc/init.d/mysqld

# chkconfig --add mysqld

# chkconfig mysqld on

# chown mysql:mysql /etc/my.cnf

# chown -R mysql:mysql /opt/module/mysql/ /opt/data/mysql/

# vi /etc/my.cnf

==========

[mysqld]

datadir = /opt/data/mysql

==========

# su – mysql

$ cd /opt/module/mysql

$ cp scripts/mysql_install_db .

$ ./mysql_install_db –user=mysql

# service mysqld start

二、安装Lua

# wget http://www.lua.org/ftp/lua-5.1.4.tar.gz

# tar xzf lua-5.1.4.tar.gz

# cd lua-5.1.4

# make

# make linux INSTALL_TOP= /usr/local/lua

# make install

三、安装MySQL Proxy，主机：192.168.1.224(注：将MySQL Proxy安装在单独的机器上更好)

a) glib

# wget http://ftp.gnome.org/pub/gnome/sources/glib/2.28/glib-2.28.4.tar.bz2

# tar jxf glib-2.28.4.tar.bz2

# cd glib-2.28.4

# ./configure --prefix=/opt/module/glib2

# make && make install

# echo "/opt/module/glib2/lib" >> /etc/ld.so.conf

# ldconfig

b) pkg-config

# wget http://pkg-config.freedesktop.org/releases/pkgconfig-0.18.tar.gz

# tar xzf pkgconfig-0.18.tar.gz

# cd pkgconfig-0.18

# ./configure && make && make install

c) libevent

# wget http://monkey.org/%7Eprovos/libevent-2.0.10-stable.tar.gz

# tar zxf libevent-2.0.10-stable.tar.gz

# cd libevent-2.0.10-stable

# ./configure --prefix=/opt/module/libevent

# make; make install

d) mysql-proxy

# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz/from/http://mysql.he.net/

# tar xzf mysql-proxy-0.8.1.tar.gz

# cd mysql-proxy-0.8.1

# export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm"

export PKG_CONFIG_PATH="/usr/lib/pkgconfig/"

export GLIB_CFLAGS="-I/opt/module/glib2/include/glib-2.0 -I/opt/module/glib2/lib/glib-2.0/include" GLIB_LIBS="-L/opt/module/glib2/lib -lglib-2.0"

export GMODULE_CFLAGS="-I/opt/module/glib2/include" GMODULE_LIBS="-L/opt/module/glib2/lib"

export GTHREAD_CFLAGS="-I/opt/module/glib2/include"

export GTHREAD_LIBS="-L/opt/module/glib2/lib"

export CPPFLAGS="$CPPFLAGS -I/opt/module/libevent/include"

export CFLAGS="$CFLAGS -I/opt/module/libevent/include"

export LDFLAGS="$LDFLAGS -L/opt/module/libevent/lib -lm"

export LIBS="/opt/module/glib2/lib/libgthread-2.0.so /opt/module/glib2/lib/libgmodule-2.0.so"

# ./configure --with-mysql="/opt/module/mysql" --prefix="/opt/module/mysql-proxy"

查看是否安装成功：

# /opt/module/mysql-proxy/bin/mysql-proxy --help-all | less

配置：

# vi /etc/mysql-proxy.cnf

===================

[mysql-proxy]

admin-username = mysqlproxyadm 主从数据库都需要的数据库用户

admin-password = 123456

daemon = true

keepalive = true

log-file = /opt/module/mysql-proxy/log

log-level = debug

proxy-address = 192.168.1.224:4040

proxy-backend-addresses = 192.168.1.223:3306 主

proxy-read-only-backend-addresses = 192.168.1.222:3306,192.168.1.224:3306 从

proxy-lua-script = /opt/module/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua

admin-lua-script = /opt/module/mysql-proxy/lib/mysql-proxy/lua/admin.lua

===================

# chmod 0660 /etc/mysql-proxy.cnf

制作启动脚本：

# vi /etc/init.d/mysql-proxy

===================

#!/bin/sh

export LUA_PATH="/opt/module/mysql-proxy/lib/mysql-proxy/lua/?.lua"

mode=$1;

if [ -z $mode ] ; then

mode="start"

fi

case $mode in

start)

/opt/module/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf>/opt/module/mysql-proxy/log &

;;

stop)

killall -9 mysql-proxy

;;

*)

echo "Usage: $0 (start|stop)"

exit 1

;;

esac

exit 0;

=====================

# chmod +x /etc/init.d/mysql-proxy

#cp x/mysql-proxy-0.8.1/lib/rw-splitting.lua /opt/module/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua

修改rw-splitting.lua

# vi /opt/module/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua

===========

min_idle_connections = 1,

max_idle_connections = 2

===========

启动mysql-proxy

# /etc/init.d/mysql-proxy start

四、配置主从数据库(master库A在192.168.1.223上，slave库B在192.168.1.222上，slave库C在192.168.1.224)

1、登录master，增加slave用的用户

GRANT REPLICATION SLAVE ON *.* TO 'slave_a'@'192.168.1.222' IDENTIFIED BY '123456'

GRANT REPLICATION SLAVE ON *.* TO 'slave_b'@'192.168.1.224' IDENTIFIED BY '123456'

注：开放master的端口，允许slave远程连接

# vi /etc/sysconfig/iptables

============================

在icmp-host-prohibited前增加：-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

============================

# service iptables restart

2、修改MySQL配置文件

a)master

# vi /etc/my.cnf

=============

[mysqld]

server-id = 1

log_bin = mysql-bin

read-only = 0

binlog-do-db = test # 多个写多行

binlog-ignore-db = mysql #多个写多行

binlog-ignore-db = information_schema

=============

b)slave，配置每个slave

# vi /etc/my.cnf

[mysqld]

server-id = 2

log_bin = mysql-bin

read-only = 1

replicate-do-db = test

replicate-ignore-db = mysql

replicate-ignore-db = information_schema

=============

3、启动slave后执行(各台slave操作类似)：

CHANGE MASTER TO MASTER_HOST = '192.168.1.223', MASTER_USER = 'slave_a', MASTER_PASSWORD = '123456', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107; 通过在master上SHOW MASTER STATUS 来得到MASTER_LOG_FILE和MASTER_LOG_POS的值

SLAVE START;

SHOW SLAVE STATUS \G; 查看是否同步上

4、登录master，增删改数据后看下各slave是否正常同步到

五、测试

1、简单测试

在主从库上都建立账号mysqlproxyadm

# GRANT SELECT ON *.* TO 'mysqlproxyadm'@'192.168.1.224' IDENTIFIED BY '123456'

# GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON *.* TO 'mysqlproxyadm'@'192.168.1.224' IDENTIFIED BY '123456'

# GRANT SELECT ON *.* TO 'mysqlproxyadm'@'192.168.1.224' IDENTIFIED BY '123456'

执行读写操作简单测试是否读写分离(观察主从库日志文件，需要打开my.cnf的log = log-filename)

./mysql -h192.168.1.224 -umysqlproxyadm -p123456 -P4040