项目中使用mysql 主从复制,但是用程序实现的读写分离,代码片段如下:
![](https://i-blog.csdnimg.cn/blog_migrate/1fa987a29c6482f53d401256f96355eb.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/ca75c07623e1b494fee67e8f316fc310.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/9b8a8a44dd1c74ae49c20a7cd451974e.gif)
2
![](https://i-blog.csdnimg.cn/blog_migrate/97e794c86028c5f5b5461ae5ef440a4c.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/3c6cafce68eb941a00f1998f1d3d3aa6.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/9b8a8a44dd1c74ae49c20a7cd451974e.gif)
3
![](https://i-blog.csdnimg.cn/blog_migrate/d18c02628675d0a2c816449d98bda930.gif)
4
![](https://i-blog.csdnimg.cn/blog_migrate/97e794c86028c5f5b5461ae5ef440a4c.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/3c6cafce68eb941a00f1998f1d3d3aa6.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/9b8a8a44dd1c74ae49c20a7cd451974e.gif)
5
![](https://i-blog.csdnimg.cn/blog_migrate/d18c02628675d0a2c816449d98bda930.gif)
6
![](https://i-blog.csdnimg.cn/blog_migrate/ecedf933ec37d714bd4c2545da43add2.gif)
7
![](https://i-blog.csdnimg.cn/blog_migrate/8f1ba5b45633e9678d1db480c16cae3f.gif)
获取数据源,首先你要确定MethodType 类型,一个是读,一个是写
![](https://i-blog.csdnimg.cn/blog_migrate/1fa987a29c6482f53d401256f96355eb.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/ca75c07623e1b494fee67e8f316fc310.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/9b8a8a44dd1c74ae49c20a7cd451974e.gif)
2
![](https://i-blog.csdnimg.cn/blog_migrate/d18c02628675d0a2c816449d98bda930.gif)
3
![](https://i-blog.csdnimg.cn/blog_migrate/8f1ba5b45633e9678d1db480c16cae3f.gif)
读是获取从库数据源,写是获取主库数据源。
这样,就需要在jdbc配置配置两个数据源(一主一从)
还有另一种实现方式,不用程序来控制。mysql 驱动包提供相应的实现 com.mysql.jdbc.ReplicationDriver.
我写了一个简单的例子:
1 package com.howard.loadbalance;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7 import java.util.Properties;
8 import java.util.concurrent.ExecutorService;
9 import java.util.concurrent.Executors;
10
11 import org.slf4j.Logger;
12 import org.slf4j.LoggerFactory;
13
14 import com.mysql.jdbc.ReplicationDriver;
15
16 public class MysqlTest
{
17
18 public static class QueryRunnable implements Runnable
{
19
20 protected final static Logger logger = LoggerFactory
21 .getLogger(QueryRunnable. class );
22
23 @Override
24 public void run()
{
25 logger.info( " user size: " + this .query());
26 }
27
28 public int query()
{
29 int count = 0 ;
30 try
{
31 ReplicationDriver driver = new ReplicationDriver();
32 Properties props = new Properties();
33 props.put( " roundRobinLoadBalance " , " true " );
34 props.put( " autoReconnect " , " true " );
35 props.put( " user " , " core " );
36 props.put( " password " , " core " );
37 Connection conn = null ;
38 Statement stat = null ;
39 ResultSet res = null ;
40 try
{
41 conn = driver
42 .connect(
43 // 注意url串中间不要有空格,因为mysql源码对多个地址split时没有trim.
44 " jdbc:mysql:replication://127.0.0.1:3309,127.0.0.1:3306/core " ,
45 props);
46 // 读写分离标记
47 // 当设置true时,只会在从库查询数据
48 // 当设置false时,会在主库做更新删除操作
49 // conn.setReadOnly(true);
50 stat = conn.createStatement();
51 res = stat.executeQuery( " select count(1) from c_user " );
52 while (res.next())
53 count = res.getInt( 1 );
54 } finally
{
55 if (res != null )
56 res.close();
57 if (stat != null )
58 stat.close();
59 if (conn != null )
60 conn.close();
61 }
62 } catch (SQLException e)
{
63 e.printStackTrace();
64 }
65 return count;
66 }
67 }
68
69 public static void main(String[] args)
{
70 // 创建线程池测试负载军衡
71 ExecutorService service = Executors.newCachedThreadPool();
72 for ( int i = 0 ; i < 10 ; i ++ )
{
73 service.execute( new QueryRunnable());
74 }
75 service.shutdown();
76 }
77
78 }