第二讲—MySQL读写分离实战

一、为什么需要读写分离

    1.提高应用整体性能

    2.数据备份

    3.数据库可以水平扩展

 

二、MySQL读写分离原理

    1.基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。

    

三、如何实现读写分离

    1.Amoeba百科：Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）。

Amoeba相当于一个SQL请求的 路由器， 目的是为负载均衡、读写分离、高可用性提供机制，而不是完全实现它们。用户需要结合使用MySQL的 Replication等机制来实现副本同步等功能。amoeba对底层数据库连接管理和路由实现也采用了可插拨的机制，第三方可以开发更高级的策略类来 替代作者的实现。这个程序总体上比较符合 KISS原则的思想。

    2.Amoeba优势：

        a). 数据切分后复杂数据源整合

        b). 提供数据切分规则并降低数据切分规则给数据库带来的影响

        c). 降低数据库与 客户端连接

        d). 读写分离路由

     3.Amoeba不足：

        a)、目前还不支持事务b)、暂时不支持存储过程（近期会支持）

        c)、不适合从amoeba导数据的场景或者对 大数据量查询的query并不合适（ 比如一次请求返回10w以上甚至更多数据的场合）

        d)、暂时不支持分库分表，amoeba目前只做到分数据库实例，每个被切分的节点需要保持库表结构一致

     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第一讲。

 

        2.1给Mysql的Master，Slave服务器创建一个Amoeba使用的账号密码：

 

GRANT ALL PRIVILEGES ON *.* TO 'amoeba' @ '%' IDENTIFIED BY 'amoeba' ; FLUSH PRIVILEGES;

 

          创建后使用该账号在本地登录一下，看能否登录成功：

           

mysql -uamoeba -pamoeba

 

          我在使用的时候这里报错了：Access denied for user 'amoeba'@'localhost' (using password: YES)

          解决方案：

 

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 >

 

    2.4简单的读写分离，只要进行上述的配置即可

         进入amoeba服务器，重启amoeba。

         使用amoeba登录客户端： mysql -uroot -p -h172.16.151.131 --port 8066   #-h后面跟的是amoeba服务器IP地址

         开始验证之前，可以打开Master和Slave的数据库日志，tail -1000f  ${MYSQL_HOME}/data/mysql.log

         在登录客户端时，我们会发现Master和Slave的日志都出现以下日志信息：

         Connect amoeba@172.16.151.131 on test    （172.16.151.131的IP使用amoeba登录，并使用test数据库）

         接下来开始验证读和写：

             2.4.1 创建一个表：

            create table syn_amoeba

            (

                id int(10) primary key,

                name varchar(20)

             );

           发现Master和Slave的log都创建了这张表，因为主库创建完会同步到从库，那怎么样验证amoeba只是在Master里创建了表呢，而不是同时往两个库创建呢，继续往下看。

       

            2.4.2 插入一条数据 

            insert into syn_amoeba values (1,'Minutch');

            Master和Salve也都插入了数据，原因与创建表相同。

    

            2.4.3 查询一条数据

            select * from syn_amoeba;

            我们发现，仅仅是Slave库出现了查询日志，在Master并没有查询，也就是说，amoeba并不是同时在两个库里进行SQL操作。

问题一只：当使用join做关联查询时，直接访问master了，和我想象的不一样，可能是有规则配置的问题（等研究amoeba的SQL路由配置时再看这歌问题）。

 

             <本节完>