mysql读写分离

前言

随着业务的不断扩展，数据量的不断增大,由单台Mysql作为独立的数据库是完全不能满足实际需求的。读和写又存在一定的差异例如："库的引擎"，Innodb支持更好的事物，但Myisam确有更高的性能。不同的业务应该有不同的库来支持，在安全性，高可用性以及高并发等各个方面进行考虑。
mysql的master/slave方案可以解决以上问题，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力优点详情。

• 主从复制 参考

  image.png
  Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制在复制过程中一个服务器充当主服务器更新写入二进制日志文件，而一个或多个其它服务器充当从服务器。进行一主多从数据的同步

• 读写分离 参考

  image.png
  读写分离，基本的实现是让主数据库处理事务性增、改、删操作，而从数据库处理查询操作。数据库复制被用来把事务性操作导致的变更（主库）同步到集群中的从数据库。
  读写分离实现大致有两种
  1）基于程序代码内部实现
  2）使用中间代理层 mycat, mysql_proxy 或 amoeba.它们是介于数据库与应用之间，进行数据处理与交互的中间服务。对数据进行分片处理之后，从原有的一个库，被切分为多个分片数据库，所有的分片数据库集群构成了整个完整的数据库存储。

主从配置

• 配置主从参考
  已经安装至少两个数据库，也可多实例进行主从复制。分别在主库创建用户并授权 - 开启二进制文件 - 从库登陆主库，就可完成简单的主从同步。

#grant replication slave on . '用户名'@'%' inentified by '密码' 主库创建用户分配slave访问权限 image.png

#vi my.cnf 主库添加添加二进制日志文件，依靠此日志文件完成数据同步日志 image.png

#show master status; 查看主库的状态(需要重启服务)记住file,设置从库同步就是根据这个二进制文件 image.png

#vi my.cnf 修改从库的配置的文件后重启，添加日志 image.png

重启后登陆需要登陆从库写入如下，从库根据此MASTER_LOG_FILE指定的二进制文件进行同步 image.png

重启slave库

登录 ， #start slave; 开启从库

#SHOW SLAVE STATUS\G 大写 image.png

测试

主库，建库，建表，添加数据 image.png
从库查询： image.png

到此主从配置完成；

• 读写分离的两种实现

1）基于代码实现需要先配置主从，利用spirng 的AbstractRoutingDataSource抽象类对数据源路由在通过aop标记然后动态切换数据源。缺点不能动态添加数据源。

定义代理类

public class DataSourceProxy {

    private static ThreadLocal<DataSourceEnum> dataSourceThreadLocal = new ThreadLocal<>();

    private ConnectionFactory connectionFactory;

    //主从库
    public enum DataSourceEnum{
        MASTER,
        SLAVE
    }

    //设置当前线程的数据库，如果不使用aop的话，可以在dao层进行手动指明主从库
    public static void setDataSource(DataSourceEnum dataSourceEnum){
        dataSourceThreadLocal.set(dataSourceEnum);
    }
    //获取线程当前的库
    public static DataSourceEnum getDataSource(){
        System.out.println("当前数据库为" +dataSourceThreadLocal.get());
        return dataSourceThreadLocal.get();
    }
    //设置为主库
    public static void setMaster(){
        setDataSource(DataSourceEnum.MASTER);
    }
    //获取从库
    public static void setSlave(){
        setDataSource(DataSourceEnum.SLAVE);
    }
}

路由

/**
 * Created by: jun on 2018/1/15.
 * description:数据源的路由，继承AbstractRoutingDataSource实现。将数据源写入到AbstractRoutingDataSource中的map中，
 * 根据aop的标记读取map中的key,从而实现数据源动态切换
 */
public class OptionalDataSource extends AbstractRoutingDataSource{

    //获取数据源路由的key
    private String masterDataSource = "masterDataSource";
    private String[] slaveDataSource = {"slaveDataSource1"};

    @Override
    //访问数据库时，都会调用此方法来获取数据源map中的key，从而得到对应的数据源。
    protected Object determineCurrentLookupKey() {

        DataSourceProxy.DataSourceEnum dataSourceEnum = DataSourceProxy.getDataSource();
        if (dataSourceEnum == DataSourceProxy.DataSourceEnum.SLAVE){
            double random = Math.random();
            int index = (int) (random * slaveDataSource.length);
            return slaveDataSource[index];
        }else{
            return masterDataSource;
        }
    }
}

切面,标记数据源

public class DataSourceAspect {

    public void before(JoinPoint joinPoint){
        //获取目标和方法
        Object target = joinPoint.getTarget();
        MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();

        String method = methodSignature.getName();
        //查询类做主库的标记
        if (isSalve(method)){
            DataSourceProxy.setSlave();
        }else DataSourceProxy.setMaster();
    }

    /**
     * 判断是否为读库 ， 判断依据为方法开头例如select, query, get 查询就走从库。
    **/
    private Boolean isSalve(String methodName){
        String[] args = {"query","find","get"};
        return StringUtils.startsWithAny(methodName,args);
    }

}

db.properties

#从库
slave1.driver=com.mysql.jdbc.Driver
slave1.url=jdbc:mysql://192.168.1.200:3306/test_master
slave1.user=test200
slave1.pwd=1234qwer

slave2.driver=com.mysql.jdbc.Driver
slave2.url=jdbc:mysql://192.168.1.200:3307/test_master
slave2.user=test3307
slave2.pwd=1234qwer

数据源部分

    <!--jdbc-->
    <context:property-placeholder location="classpath:db.properties" />

    <!--配置mybatis-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="classpath*:mapping/*.xml"/>   <!--如果有配置文件，指定扫描路径-->
        <property name="typeAliasesPackage" value="com.tianzhuan.web.entity"/></bean>

    <!--配置主库-->
    <bean id="master" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="${slave1.url}"/>
        <property name="username" value="${slave1.user}"/>
        <property name="password" value="${slave1.pwd}"/>
    </bean>
    <!--配置从库-->
    <bean id="slave1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="${slave2.url}"/>
        <property name="username" value="${slave2.user}"/>
        <property name="password" value="${slave2.pwd}"/>
    </bean>
<!--路由-->
    <bean id="dataSource" class="com.tianzhuan.common.database.OptionalDataSource">
        <!--通过key-value的形式关联shujuyuan-->
        <property name="targetDataSources">
            <map>
                <entry key="masterDataSource" value-ref="master"/>
                <entry key="slaveDataSource1" value-ref="slave1"/>
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="master"/>
    </bean>

aop部分

    <bean id="queryAspect" class="com.tianzhuan.common.database.DataSourceAspect"/>
    <aop:config>
        <aop:aspect id="master_slave" ref="queryAspect">
            <aop:pointcut id="cutMethod" expression="execution(* com.tianzhuan.web.*.*.*.*(..))"/>
            <aop:before method="before" pointcut-ref="cutMethod"/>
        </aop:aspect>
    </aop:config>

到此基于spring AbstractRoutingDataSource 分离完成。

2）使用中间件的代理mycat实现官方文档

image.png
如上图所表示，数据被分到多个分片数据库后，应用如果需要读取数据，就要需要处理多个数据源的数据。如果没有数据库中间件，那么应用将直接面对分片集群，数据源切换、事务处理、数据聚合都需要应用直接处理，原本该是专注于业务的应用，将会花大量的工作来处理分片后的问题，最重要的是每个应用处理将是完全的重复造轮子。 所以有了数据库中间件，应用只需要集中与业务处理，大量的通用的数据聚合，事务，数据源切换都由中间件来处理，中间件的性能与处理能力将直接决定应用的读写性能，所以一款好的数据库中间件至关重要。
安装服务

• 解压到相关目录 #cd /mycat_home/conf

mycat主要配置 schema.xml ，rule.xml，server.xml 3 个文件就可实现简单的负载均衡，读写分离，分库分表

• #vi schema.xml //添加配置如下 image.png

schema.xml 主要有<schema>、<dataNode>、<dataHost>3个主要的节点

1. <schema>定义mycat实例中的逻辑库（托管mysql程序连接的虚拟库）同mysql的database的概念，name属性就是jdbc连接mycat的库名 和<server>中定义的用户，一起构成了数据源的信息。
2. <dataNode> 标签定义了 MyCat中的数据节点，也就是我们通常说所的数据分片。一个dataNode 标签就是
   一个独立的数据分片。 截图部分意为：使用名字为mater_slaver_1数据库实例上的test_master物理数据库(需要手动创建)，这就组成一个数据分片，最后，我们使用名字dn1标识这个分片。
3. <datahost> 直接定义了具体的数据库实例、读写分离配置和心跳语句。
   maxCOn/minCon属性定义初始化连接池的大小。
   balance属性 均衡类型，0为不开启读写分离，1为主从全部参与select的均衡，2所有的读操作在主从随机分发
   switchType属性主从的切换状态，-1为不切换，1为默认值自动切换，2为根据mysql的主从同步状态决定
   <heartbeat>show slave status<heartbeat>定义心跳。

• #vi server.xml 添加用户 image.png

• #./bin/mycat startup_nowrap.sh 启动服务或者 mycat console|start来启动

• 如出现以下错误检测账号授权，重启系统 image.png

• #./mysql -uroot -p1234qwer -h192.168.1.200 -P8806 //使用mysql客户端登陆mycat image.png

测试

• vi /conf/log4j2.xml //修改日志记录级别为debug image.png
• 连接mycat 后分别进行插入和查询。 image.png
• 查看日志文件 image.png
  image.png
• 使用工具进行登陆 image.png
• 使用jdbc连接

db.properties

#mycat
mycat.url=jdbc:mysql://192.168.1.200:8066/TESTDB  //schema的name
mycat.user=root
mycat.pwd=1234qwer

连接池

 <!--mycat-->
    <bean id="mycat" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="${mycat.url}"/>
        <property name="username" value="${mycat.user}"/>
        <property name="password" value="${mycat.pwd}"/>
    </bean>
    <!--配置mybatis-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="mycat"/>
        <property name="mapperLocations" value="classpath*:mapping/*.xml"/>   <!--如果有配置文件，指定扫描路径-->
        <property name="typeAliasesPackage" value="com.tianzhuan.web.entity"/></bean>

image.png

基于mycat简单的读写分离到此结束