一、为什么需要读写分离
1.提高应用整体性能
2.数据备份
3.数据库可以水平扩展
二、MySQL读写分离原理
1.基本的原理是让主数据库处理事务性查询,而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。
三、如何实现读写分离
1.Amoeba百科:Amoeba是一个以MySQL为底层数据存储,并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求,依据用户事先设置的规则,将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比,作者强调的是amoeba配置的方便(基于XML的配置文件,用SQLJEP语法书写规则,比基于lua脚本的MySQL Proxy简单)。
3.Amoeba不足:
a)、目前还不支持事务b)、暂时不支持存储过程(近期会支持)
4.官方文档:http://docs.hexnova.com/amoeba/index.html
四、Amoeba安装
1.下载Amoba,解压至目录,执行 ${Amoeba_Home}/bin/amoeba
2.如果报以下错误:Error: JAVA_HOME environment variable is not set.
明明设置了JAVA_HOME的,他还是会报这个错误,所以我门就直接修改启动的脚本,vi ${Amoeba_Home}/bin/amoeba
JAVA_HOME=
/Library/Java/JavaVirtualMachines/jdk1
.7.0_51.jdk
/Contents/Home
#加上这一行,相当于在启动脚本里加上JAVA_HOME的配置
noJavaHome=
false
if
[ -z
"$JAVA_HOME"
] ;
then
noJavaHome=
true
fi
if
$cygwin ;
then
[ -n
"$JAVA_HOME"
] &&
JAVA_HOME=`cygpath -u
"$JAVA_HOME"
`
fi
if
[ ! -e
"$JAVA_HOME/bin/java"
] ;
then
noJavaHome=
true
fi
if
$noJavaHome ;
then
echo
"Error: JAVA_HOME environment variable is not set."
exit
1
fi
|
继续启动,可能会报一个错误:the stack size specified is too small, Specify at least 160k Error: Could not create the Java Virtual Machine.继续修改启动脚本
DEFAULT_OPTS=
"-server -Xms256m -Xmx512m -Xss512k"
#加上这句
#DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k" #注释掉这句
# DEFAULT_OPTS="$DEFAULT_OPTS -XX:+HeapDumpOnOutOfMemoryError -XX:+AggressiveOpts -XX:+UseParallelGC -XX:+UseBiasedLocking -XX:NewSize=64m"
DEFAULT_OPTS=
"$DEFAULT_OPTS -Damoeba.home=\"$AMOEBA_HOME\""
DEFAULT_OPTS=
"$DEFAULT_OPTS -Dclassworlds.conf=\"$AMOEBA_HOME/bin/amoeba.classworlds\""
|
继续启动,执行 ${Amoeba_Home}/bin/amoeba start , 启动成功,显示:
log4j:WARN log4j config load completed from
file
:
/usr/programe/amoeba/conf/log4j
.xml
2015-04-17 08:37:52,689 INFO context.MysqlRuntimeContext - Amoeba
for
Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from
file
:
/usr/programe/amoeba/conf/access_list
.conf
2015-04-17 08:37:53,036 INFO net.ServerableConnectionManager - Amoeba
for
Mysql listening on 0.0.0.0
/0
.0.0.0:8066.
2015-04-17 08:37:53,038 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on
/127
.0.0.1:43757.
|
五、使用Amoeba做代理实现读写分离实战
1.环境介绍
1.1 IP:172.16.151.1 (MySQL Master服务器)
操作系统:Mac OS
软件:JDK,MySQL
1.1 IP:172.16.151.130 (MySQL Slave服务器)
操作系统:Centos 6.5
软件:JDK,MySQL
1.1 IP:172.16.151.131 (Amoeba服务器)
操作系统:Centos 6.5
软件:JDK,MySQL,Amoeba
2.Amoeba配置
首先搭建好MySQL主从复制的环境,详情参见MySQL第一讲。
GRANT ALL PRIVILEGES ON *.* TO
'amoeba'
@
'%'
IDENTIFIED BY
'amoeba'
;
FLUSH PRIVILEGES;
|
mysql -uamoeba -pamoeba
|
delete from user where user is null;
delete from user where user=
''
;
FLUSH PRIVILEGES;
|
重新登录,成功!
2.2配置amoeba.xml文件,该文件配置amoeba服务器相关配置信息。
<?
xml
version
=
"1.0"
encoding
=
"gbk"
?>
<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<
amoeba:configuration
xmlns:amoeba
=
"<a href="http://amoeba.meidusa.com/" "="">http://amoeba.meidusa.com/"
>
<
proxy
>
<!-- service class must implements com.meidusa.amoeba.service.Service -->
<
service
name
=
"Amoeba for Mysql"
class
=
"com.meidusa.amoeba.net.ServerableConnectionManager"
>
<!-- port amoeba代理的端口号-->
<
property
name
=
"port"
>8066</
property
>
<!-- bind ipAddress 绑定IP地址 -->
<!--<property name="ipAddress">127.0.0.1</property>-->
<
property
name
=
"manager"
>${clientConnectioneManager}</
property
>
<
property
name
=
"connectionFactory"
>
<
bean
class
=
"com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory"
>
<
property
name
=
"sendBufferSize"
>128</
property
>
<
property
name
=
"receiveBufferSize"
>64</
property
>
</
bean
>
</
property
>
<!— amoeba客户端的mysql账号 -->
<
property
name
=
"authenticator"
>
<
bean
class
=
"com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"
>
<
property
name
=
"user"
>root</
property
>
<
property
name
=
"password"
></
property
>
<
property
name
=
"filter"
>
<
bean
class
=
"com.meidusa.amoeba.server.IPAccessController"
>
<
property
name
=
"ipFile"
>${amoeba.home}/conf/access_list.conf</
property
>
</
bean
>
</
property
>
</
bean
>
</
property
>
</
service
>
<!-- server class must implements com.meidusa.amoeba.service.Service -->
<
service
name
=
"Amoeba Monitor Server"
class
=
"com.meidusa.amoeba.monitor.MonitorServer"
>
<!-- port -->
<!-- default value: random number <property name="port">9066</property>-->
<!-- bind ipAddress -->
<
property
name
=
"ipAddress"
>127.0.0.1</
property
>
<
property
name
=
"daemon"
>true</
property
>
<
property
name
=
"manager"
>${clientConnectioneManager}</
property
>
<
property
name
=
"connectionFactory"
>
<
bean
class
=
"com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"
></
bean
>
</
property
>
</
service
>
<
runtime
class
=
"com.meidusa.amoeba.mysql.context.MysqlRuntimeContext"
>
<!-- proxy server net IO Read thread size -->
<
property
name
=
"readThreadPoolSize"
>20</
property
>
<!-- proxy server client process thread size -->
<
property
name
=
"clientSideThreadPoolSize"
>30</
property
>
<!-- mysql server data packet process thread size -->
<
property
name
=
"serverSideThreadPoolSize"
>30</
property
>
<!-- per connection cache prepared statement size -->
<
property
name
=
"statementCacheSize"
>500</
property
>
<!-- query timeout( default: 60 second , TimeUnit:second) -->
<
property
name
=
"queryTimeout"
>60</
property
>
</
runtime
>
</
proxy
>
<!--
Each ConnectionManager will start as thread
manager responsible for the Connection IO read , Death Detection
-->
<
connectionManagerList
>
<
connectionManager
name
=
"clientConnectioneManager"
class
=
"com.meidusa.amoeba.net.MultiConnectionManagerWrapper"
>
<
property
name
=
"subManagerClassName"
>com.meidusa.amoeba.net.ConnectionManager</
property
>
<!--
default value is avaliable Processors
<property name="processors">5</property>
-->
</
connectionManager
>
<
connectionManager
name
=
"defaultManager"
class
=
"com.meidusa.amoeba.net.MultiConnectionManagerWrapper"
>
<
property
name
=
"subManagerClassName"
>com.meidusa.amoeba.net.AuthingableConnectionManager</
property
>
<!--
default value is avaliable Processors
<property name="processors">5</property>
-->
</
connectionManager
>
</
connectionManagerList
>
<!-- default using file loader -->
<
dbServerLoader
class
=
"com.meidusa.amoeba.context.DBServerConfigFileLoader"
>
<
property
name
=
"configFile"
>${amoeba.home}/conf/dbServers.xml</
property
>
</
dbServerLoader
>
<
queryRouter
class
=
"com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"
>
<
property
name
=
"ruleLoader"
>
<
bean
class
=
"com.meidusa.amoeba.route.TableRuleFileLoader"
>
<
property
name
=
"ruleFile"
>${amoeba.home}/conf/rule.xml</
property
>
<
property
name
=
"functionFile"
>${amoeba.home}/conf/ruleFunctionMap.xml</
property
>
</
bean
>
</
property
>
<
property
name
=
"sqlFunctionFile"
>${amoeba.home}/conf/functionMap.xml</
property
>
<
property
name
=
"LRUMapSize"
>1500</
property
>
<!— 默认使用的数据库服务器 配置信息在dbServer.xml -->
<
property
name
=
"defaultPool"
>server1</
property
>
<!— 写入的数据库服务器 配置信息在dbServer.xml-->
<
property
name
=
"writePool"
>server1</
property
>
<!— 读取的数据库服务器 配置信息在dbServer.xml-->
<
property
name
=
"readPool"
>multiPool</
property
>
<
property
name
=
"needParse"
>true</
property
>
</
queryRouter
>
</
amoeba:configuration
>
|
2.3 配置dbServer.xml,该文件配置实际的MySQL服务器相关信息,以及读写的服务器,访问策略等
<?
xml
version
=
"1.0"
encoding
=
"gbk"
?>
<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<
amoeba:dbServers
xmlns:amoeba
=
"<a href="http://amoeba.meidusa.com/" "="">http://amoeba.meidusa.com/"
>
<!--
Each dbServer needs to be configured into a Pool,
If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
such as 'multiPool' dbServer
-->
<
dbServer
name
=
"abstractServer"
abstractive
=
"true"
>
<
factoryConfig
class
=
"com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"
>
<
property
name
=
"manager"
>${defaultManager}</
property
>
<
property
name
=
"sendBufferSize"
>64</
property
>
<
property
name
=
"receiveBufferSize"
>128</
property
>
<!-- mysql 服务器的端口,Schema,账号,密码 -->
<
property
name
=
"port"
>3306</
property
>
<
property
name
=
"schema"
>test</
property
>
<
property
name
=
"user"
>amoeba</
property
>
<
property
name
=
"password"
>amoeba</
property
>
</
factoryConfig
>
<
poolConfig
class
=
"com.meidusa.amoeba.net.poolable.PoolableObjectPool"
>
<
property
name
=
"maxActive"
>500</
property
>
<
property
name
=
"maxIdle"
>500</
property
>
<
property
name
=
"minIdle"
>10</
property
>
<
property
name
=
"minEvictableIdleTimeMillis"
>600000</
property
>
<
property
name
=
"timeBetweenEvictionRunsMillis"
>600000</
property
>
<
property
name
=
"testOnBorrow"
>true</
property
>
<
property
name
=
"testOnReturn"
>true</
property
>
<
property
name
=
"testWhileIdle"
>true</
property
>
</
poolConfig
>
</
dbServer
>
<!— server1 为Master -->
<
dbServer
name
=
"server1"
parent
=
"abstractServer"
>
<
factoryConfig
>
<
property
name
=
"ipAddress"
>172.16.151.1</
property
>
</
factoryConfig
>
</
dbServer
>
<!— server2 为Slave -->
<
dbServer
name
=
"server2"
parent
=
"abstractServer"
>
<
factoryConfig
>
<
property
name
=
"ipAddress"
>172.16.151.130</
property
>
</
factoryConfig
>
</
dbServer
>
<
dbServer
name
=
"multiPool"
virtual
=
"true"
>
<
poolConfig
class
=
"com.meidusa.amoeba.server.MultipleServerPool"
>
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<
property
name
=
"loadbalance"
>1</
property
>
<!-- Separated by commas,such as: server1,server2,server1 -->
<!— 这里是配置相当于访问策略的信息
1.如配置:server1,server2则会按相同权重随机访问其中一个服务器
2.如配置:server2则只会访问server2服务器
3.如配置:server1,server2,server2则会访问server1,server2(权重较大,访问到的几率更高)
-->
<
property
name
=
"poolNames"
>server2</
property
>
</
poolConfig
>
</
dbServer
>
</
amoeba:dbServers
>
|
<本节完>