社区 学习 工具库 休闲
搜索
简体中文
首页 > 数据库 > mysql教程 > 正文

MySQL Proxy主从读写分离

WBOY
发布： 2016-06-07 16:32:12
原创
936 人浏览过

一、安装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
登录后复制

原文地址：MySQL Proxy主从读写分离, 感谢原作者分享。

相关标签：
mysql proxy 分离 安装 读写
来源：php.cn
上一篇：静态编译TCMalloc到MySQL 下一篇：用mysqlslap进行MySQL压力测试
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
作者最新文章
最新问题
如何从结果集中仅显示最低值（MYSQL） 我有以下声明：selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommessagewherefacilityi...
来自于 2024-04-06 21:44:19
0
1
603
使用MySQL SQL查询计算另一张表中字段的总和 我有一个这样的模式：具有属性“user_id”和“username”的用户表以及具有属性“customer_id”（user_id的FK）和“finalPrice”的订单表数据库架...
来自于 2024-04-06 19:39:29
0
1
441
MySQL：'从......中选择文本作为<此处变量或子查询>” 我有下表，其中包含以下数据：id文本语言1德语文本德语2英文文本英语我想要的是获得以下格式的结果：german="deutschertext"english=&...
来自于 2024-04-06 19:25:54
0
2
529
MySQL中如何进行分组和计数？ 我正在尝试编写一个查询，提取发送给朋友的未删除消息的总数。这是表结构的屏幕截图：我想要实现的输出结果如下：idmessages_count---------------------...
来自于 2024-04-06 18:30:17
0
1
353
MySQL从多个表中获取数据 我有一个eg_design表，其中包含以下几列：和eg_domains表，其中包含以下列：和eg_fonts表，其中包含以下列：现在，基于$domain_id我想获取eg_desi...
来自于 2024-04-06 18:42:44
0
2
479
相关专题
更多>
热门推荐
热门教程
更多>
相关教程
热门推荐
最新课程
最新下载
更多>
网站特效
网站源码
网站素材
前端模板