MySQL的读写分离的几种选择

3、Mysql Proxy选项说明

1. # Mysql Proxy help-all 

管理功能选项:

1. admin-address=host:port 指定一个mysqo-proxy的管理端口, 缺省是4041;  
2. admin-username=<string> username to allow to log in  
3. admin-password=<string> password to allow to log in  
4. admin-lua-script=<filename> script to execute by the admin plugin 

代理功能选项:

1. -P, proxy-address=<host:port> 是Mysql Proxy 服务器端的监听端口, 缺省是4040;  
2. -r, proxy-read-only-backend-addresses=<host:port> 只读Slave的地址和端口, 缺省为不设置;  
3. -b, proxy-backend-addresses=<host:port> 远程Master地址和端口, 可设置多个做failover和load balance, 缺省是127.0.0.1:3306;  
4. proxy-skip-profiling 关闭查询分析功能, 缺省是打开的;  
5. proxy-fix-bug-25371 修正 mysql的libmysql版本大于5.1.12的一个#25371号bug;  
6. -s, proxy-lua-script=<file> 指定一个Lua脚本来控制Mysql Proxy的运行和设置, 这个脚本在每次新建连接和脚本发生修改的的时候将重新调用; 

其他选项:

1. defaults-file=<file>配置文件, 可以把Mysql Proxy的参数信息置入一个配置文件里;  
2. daemon Mysql Proxy以守护进程方式运行  
3. pid-file=file 设置Mysql Proxy的存储PID文件的路径  
4. keepalive try to restart the proxy if it crashed, 保持连接启动进程会有2个, 一号进程用来监视二号进程, 如果二号进程死掉自动重启proxy. 

4、数据库准备工作

（1）安装半同步补丁(建议)

读写分离不能回避的问题之一就是延迟, 可以考虑Google提供的SemiSyncReplication补丁.

（2）给用户授权

在Master/Slave建立一个测试用户, 因为以后客户端发送的SQL都是通过Mysql Proxy服务器来转发, 所以要确保可以从Mysql Proxy服务器上登录MySQL主从库.

1. mysql> grant all privileges on *.* to ‘u_test‘@‘192.168.41.203‘ identified by ‘xxx‘ with grant option; 

（3）在Master建立测试表

1. mysql> create table db_test.t_test (col varchar(10));  
2. mysql> insert into db_test.t_test values (‘testA‘);  
3. mysql> select * from db_test.t_test;  
4. +-+  
5. | col   |  
6. +-+  
7. | testA |  
8. +-+ 

5、Mysql Proxy启动

（1）修改读写分离lua脚本

默认最小4个最大8个以上的客户端连接才会实现读写分离, 现改为最小1个最大2个:

1. # vi +40 /usr/local/Mysql Proxy/share/doc/Mysql Proxy/rw-splitting.lua  
2.  connection pool  
3. if not proxy.global.config.rwsplit then  
4.         proxy.global.config.rwsplit = {  
5.                 min_idle_connections = 1,  
6.                 max_idle_connections = 2,  
7.                 is_debug = true   
8.         }         
9. end 

这是因为Mysql Proxy会检测客户端连接, 当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上.

（2）启动Mysql Proxy

建议使用配置文件的形式启动, 注意配置文件必须是660权限, 否则无法启动. 如果有多个Slave的话, proxy-read-only-backend-addresses参数可以配置多个以逗号分隔的IP:Port从库列表.

1. # killall Mysql Proxy   
2. # vi /etc/Mysql Proxy.cnf  
3. [Mysql Proxy]  
4. admin-username=wangnc 
5. admin-password=iamwangnc 
6. admin-lua-script=/usr/local/Mysql Proxy/lib/Mysql Proxy/lua/admin.lua  
7. proxy-backend-addresses=192.168.41.196:3351  
8. proxy-read-only-backend-addresses=192.168.41.197:3351  
9. proxy-lua-script=/usr/local/Mysql Proxy/share/doc/Mysql Proxy/rw-splitting.lua  
10. log-file=/var/tmp/Mysql Proxy.log  
11. log-level=debug 
12. daemon=true 
13. keepalive=true 
14. # chmod 660 /etc/Mysql Proxy.cnf  
15. # Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  
16. # ps -ef | grep Mysql Proxy | grep -v grep  
17. root      1869     1  0 18:16 ?        00:00:00 /usr/local/Mysql Proxy/libexec/Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  
18. root      1870  1869  0 18:16 ?        00:00:00 /usr/local/Mysql Proxy/libexec/Mysql Proxy defaults-file=/etc/Mysql Proxy.cnf  
19. # tail -50f /var/tmp/Mysql Proxy.log 

6、客户端连接测试

（1）先停止Slave的复制进程

1. mysql> stop slave; 

（2）连接Proxy端口, 插入数据

1. # mysql -uu_test -pxxx -h192.168.41.203 -P4040 -Ddb_test  
2. mysql> insert into db_test.t_test values (‘testB‘);  
3. mysql> select * from db_test.t_test;  
4. +-+  
5. | col   |  
6. +-+  
7. | testA |  
8. | testB |  
9. +-+ 

（3）多开几个客户端, 连接Proxy端口, 查询数据

1. # mysql -uu_test -pxxx -h192.168.41.203 -P4040 -Ddb_test  
2. mysql> select * from db_test.t_test;  
3. +-+  
4. | col   |  
5. +-+  
6. | testA |  
7. +-+ 

如果查询不到上步新插入的数据, 说明连接到了Slave, 读写分离成功. 在同一线程再插入数据并验证:

1. mysql> insert into db_test.t_test values (‘testC‘);  
2. mysql> select * from db_test.t_test;  
3. +-+  
4. | col   |  
5. +-+  
6. | testA |  
7. +-+ 

发现insert操作成功, 但是select不出刚插入的数据, 说明同一线程也读写分离成功. 从日志中可以验证:

1. # tail -50f /var/tmp/Mysql Proxy.log  
2. ...  
3. [read_query] 192.168.41.203:45481  
4.   current backend   = 0 
5.   client default db = db_test 
6.   client username   = u_test 
7.   query             = select * from db_test.t_test  
8.   sending to backend : 192.168.41.197:3351  
9.     is_slave         : true  
10.     server default db: db_test  
11.     server username  : u_test  
12.     in_trans        : false  
13.     in_calc_found   : false  
14.     COM_QUERY       : true  
15. [read_query] 192.168.41.203:45481  
16.   current backend   = 0 
17.   client default db = db_test 
18.   client username   = u_test 
19.   query             = insert into db_test.t_test values (‘testC‘)  
20.   sending to backend : 192.168.41.196:3351  
21.     is_slave         : false  
22.     server default db: db_test  
23.     server username  : u_test  
24.     in_trans        : false  
25.     in_calc_found   : false  
26.     COM_QUERY       : true 

（4）测试完毕后, 启动Slave的复制进程

1. mysql> start slave; 

7、正式环境说明

1、Mysql Proxy当前还只是个测试版, MySQL官方还不建议用到生产环境中;

2、Mysql Proxy的rw-splitting.lua脚本在网上有很多版本, 但是最准确无误的版本仍然是源码包中所附带的rw-splitting.lua脚本, 如果有lua脚本编程基础的话, 可以在这个脚本的基础上再进行优化；

3、Mysql Proxy实际上非常不稳定, 在高并发或有错误连接的情况下, 进程很容易自动关闭, 因此打开keepalive参数让进程自动恢复是个比较好的办法, 但还是不能从根本上解决问题, 因此通常最稳妥的做法是在每个从服务器上安装一个Mysql Proxy供自身使用, 虽然比较低效但却能保证稳定性；

4、Amoeba for MySQL是一款优秀的中间件软件, 同样可以实现读写分离, 负载均衡等功能, 并且稳定性要大大超过Mysql Proxy, 建议大家用来替代Mysql Proxy, 甚至MySQL-Cluster.

mysql的读写分离amoeba

原址如下：

http://freeze.blog.51cto.com/1846439/860111

此文凝聚笔者不少心血请尊重笔者劳动，转载请注明出处http://freeze.blog.51cto.com/ 

  一、关于读写分离

1. 读写分离（Read/Write Splitting），基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。 

二、同类产品比较 

1. 虽然大多数都是从程序里直接实现读写分离的，但对于分布式的部署和水平和垂直分割，一些代理的类似中间件的软件还是挺实用的，Amoeba for Mysql 与MySQL Proxy比较 在MySQL proxy 6.0版本 上面如果想要读写分离并且 读集群、写集群 机器比较多情况下，用mysql proxy 需要相当大的工作量，目前mysql proxy没有现成的 lua脚本。mysql proxy根本没有配置文件， lua脚本就是它的全部，当然lua是相当方便的。那么同样这种东西需要编写大量的脚本才能完成一 个复杂的配置。而Amoeba for Mysql只需要进行相关的配置就可以满足需求。 

1. Amoeba(变形虫)项目,该开源框架于2008年 开始发布一款 Amoeba for Mysql软件。这个软件致力于MySQL的分布式数据库前端代理层，它主要在应用层访问MySQL的 时候充当SQL路由功能，专注于分布式数据库代理层（Database Proxy）开发。座落与 Client、DB Server(s)之间,对客户端透明。具有负载均衡、高可用性、SQL 过滤、读写分离、可路由相关的到目标数据库、可并发请求多台数据库合并结果。 通过Amoeba你能够完成多数据源的高可用、负载均衡、数据切片的功能，目前Amoeba已在很多 企业的生产线上面使用。 

四、Amoeba的安装

4.1下载 

1. wget http://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz 

4.2安装amoeba 

1. mkdir /usr/local/amoeba 
2. #mv amoeba-mysql-binary-2.1.0-RC5.tar.gz /usr/local/amoeba 
3. #tar  xvf amoeba-mysql-binary-2.1.0-RC5.tar.gz  

4.3安装JDK 

1. 因为Amoeba是java开发的，需要JDK支持。 Amoeba框架是基于Java SE1.5开发的，建议使用Java SE 1.5版本。 
2.  
3. % java -version 
4. java version "1.6.0_18" 
5. Java(TM) SE Runtime Environment (build 1.6.0_18-b07) 
6. Java HotSpot(TM) Client VM (build 16.0-b13, mixed mode, sharing) 
7. 目前Amoeba经验证在JavaTM SE 1.5和Java SE 1.6能正常运行，（可能包括其他未经验证的版本）。 如果你的机器上没有安装JavaTM环境，可以访问http://www.oracle.com/technetwork/java/javase/downloads/index.html进行下载。可以根据你的操作系统等详情安装JavaTM环境。 
8.  
9. 去oracle官网下载jdk安装包后，安装jdk 
10.  
11. # chmod 755 jdk-6u25-linux-i586.bin 
12. # ./jdk-6u25-linux-i586.bin 
13. # mv jdk1.6.0_25/ /usr/local/jdk 
14. 声明路径，修改/etc/profile，在末尾加上以下代码 
15.  
16. export AMOEBA_HOME=/usr/local/amoeba 
17. export JAVA_HOME=/usr/local/jdk 
18. export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$AMOEBA_HOME/bin 

 五、Amoeba配置

1. cd /usr/local/amoeba/conf 主要配置以下2个配置文件：  
2. dbServers.xml  #定义连接数据库信息 
3. amoeba.xml     #定义读写分离节点管理信息 

5.1 配置dbServers.xml

1. <?xml version="1.0" encoding="gbk"?> 
2.  
3. <!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd"> 
4. <amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/"> 
5.  
6.                 <!--  
7.                         Each dbServer needs to be configured into a Pool, 
8.                          such as ‘multiPool‘ dbServer    
9.                 --> 
10.  
11.         <dbServer name="abstractServer" abstractive="true"> 
12.                 <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"> 
13.                         <property name="manager">${defaultManager}</property> 
14.                         <property name="sendBufferSize">64</property> 
15.                         <property name="receiveBufferSize">128</property> 
16.  
17.                         <!-- mysql port --> 
18.                         <property name="port">3306</property>         __ ** ##后端数据库端口**__ 
19.  
20.                         <!-- mysql schema --> 
21.                         <property name="schema">test</property>        __ ** ##后端数据库默认库**__ 
22.                         <!--  mysql password 
23.                         <property name="password">password</property> 
24.                         --> 
25.                 </factoryConfig> 
26.  
27.                 <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool"> 
28.                         <property name="maxActive">500</property> 
29.                         <property name="maxIdle">500</property> 
30.                         <property name="minIdle">10</property> 
31.                         <property name="minEvictableIdleTimeMillis">600000</property> 
32.                         <property name="timeBetweenEvictionRunsMillis">600000</property> 
33.                         <property name="testOnBorrow">true</property> 
34.                         <property name="testWhileIdle">true</property> 
35.                 </poolConfig> 
36.         </dbServer> 
37.   
38.         <dbServer name="master"  parent="abstractServer">                          __ ** ##定义主的写的节点**__ 
39.                 <factoryConfig> 
40.                         <property name="ipAddress">192.168.1.1</property>      __ ** ##主masterIP**__ 
41.                         <property name="user">test1</property>                 __ ** ##与主mysql通信，连接数据库的帐号，以下是密码**__ 
42.                         <property name="password">test1</property> 
43.                 </factoryConfig> 
44.         </dbServer> 
45.  
46.         <dbServer name="slave"  parent="abstractServer"> 
47.                 <factoryConfig> 
48.                         <property name="ipAddress">192.168.1.2</property> 
49.                         <property name="user">test2</property>                 __ ** ##与从mysql通信，连接数据库的帐号，以下是密码**__ 
50.                         <property name="password">test2</property> 
51.                 </factoryConfig> 
52.         </dbServer> 
53.  
54.         <dbServer name="server1" virtual="true"> 
55.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">    __ ** ##定义写的池，把master节点加入**__ 
56.                         <property name="loadbalance">1</property> 
57.                         <property name="poolNames">master</property> 
58.                 </poolConfig> 
59.         </dbServer> 
60.  
61.         <dbServer name="server2" virtual="true"> 
62.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">    __ ** ##定义读的池，把slave节点加入**__ 
63.                         <property name="loadbalance">1</property> 
64.                         <property name="poolNames">slave</property> 
65.                 </poolConfig> 
66.         </dbServer> 
67. </amoeba:dbServers> 

5.2 配置amoeba.xml

1. <?xml version="1.0" encoding="gbk"?> 
2. <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/"> 
3.  
4.         <proxy> 
5.  
6.                 <!-- service class must implements com.meidusa.amoeba.service.Service --> 
7.                         <!-- port --> 
8.                          <property name="port">6666</property>                   __ ** ##定义amoeba读写分离proxy对外代理的端口**__ 
9.                         <!-- bind ipAddress --> 
10.                         <!--  
11.                         <property name="ipAddress">127.0.0.1</property> 
12.                          --> 
13.  
14.                         <property name="manager">${clientConnectioneManager}</property> 
15.  
16.                         <property name="connectionFactory"> 
17.                                         <property name="sendBufferSize">128</property> 
18.                                         <property name="receiveBufferSize">64</property> 
19.                                 </bean> 
20.                         </property> 
21.  
22.                         <property name="authenticator"> 
23.                                          <property name="user">dbproxy</property>     __ ** ##定义proxy的管理帐号密码，客户端和程序只需要连接proxy的帐号密码即可，相当于中间接封装**__ 
24.                                         <property name="password">123456</property> 
25.  
26.                                         <property name="filter"> 
27.                                                 </bean> 
28.                                         </property> 
29.                                 </bean> 
30.                         </property> 
31.  
32.                 </service> 
33.  
34.                 <!-- server class must implements com.meidusa.amoeba.service.Service --> 
35.                         <!-- port --> 
36.                         <!--  default value: random number 
37.                         <property name="port">9066</property> 
38.                         --> 
39.                         <!-- bind ipAddress --> 
40.                         <property name="ipAddress">127.0.0.1</property> 
41.                         <property name="daemon">true</property> 
42.                         <property name="manager">${clientConnectioneManager}</property> 
43.                         <property name="connectionFactory"> 
44.                 <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext"> 
45.                         <!-- proxy server net IO Read thread size --> 
46.                         <property name="readThreadPoolSize">20</property> 
47.  
48.                         <!-- proxy server client process thread size --> 
49.  
50.                         <!-- per connection cache prepared statement size  --> 
51.                         <property name="statementCacheSize">500</property> 
52.  
53.                         <!-- query timeout( default: 60 second , TimeUnit:second) --> 
54.                         <property name="queryTimeout">60</property> 
55.                 </runtime> 
56.  
57.         </proxy> 
58.  
59.         <!--  
60.                 Each ConnectionManager will start as thread 
61.                 manager responsible for the Connection IO read , Death Detection 
62.         --> 
63.         <connectionManagerList> 
64.                         <!--  
65.                           default value is avaliable Processors  
66.                         <property name="processors">5</property> 
67.                          --> 
68.                 </connectionManager> 
69.  
70.                         <!--  
71.                           default value is avaliable Processors  
72.                         <property name="processors">5</property> 
73.                          --> 
74.                 </connectionManager> 
75.         </connectionManagerList> 
76.  
77.                 <!-- default using file loader --> 
78.         <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader"> 
79.                 <property name="configFile">${amoeba.home}/conf/dbServers.xml</property> 
80.         </dbServerLoader> 
81.  
82.         <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"> 
83.                 <property name="ruleLoader"> 
84.                         <bean class="com.meidusa.amoeba.route.TableRuleFileLoader"> 
85.                                 <property name="ruleFile">${amoeba.home}/conf/rule.xml</property> 
86.                                 <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property> 
87.                         </bean> 
88.                 </property> 
89.                 <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property> 
90.                 <property name="LRUMapSize">1500</property> 
91.                 <property name="defaultPool">server1</property>                   __ ** ##定义默认的池，一些除了SELECT\UPDATE\INSERT\DELETE的语句都会在defaultPool执行。**__ 
92.                 <property name="writePool">server1</property>                      __ ** ##定义写的池**__ 
93.                 <property name="readPool">server2</prope