Heim  >  Artikel  >  Datenbank  >  MySQL Proxy主从读写分离

MySQL Proxy主从读写分离

WBOY
WBOYOriginal
2016-06-07 16:32:12928Durchsuche

一、安装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
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn