springmvc配置mysql数据库读写分离（基于注解aop）加上（搭建mysql主从同步）

来源：互联网发布：windows live id格式编辑：程序博客网时间：2024/04/27 23:11

1.先配置数据库的主从

以windows系统为主数据库，以虚拟机的linux系统为从数据库

(1)主数据库配置(windows系统)

1.在mysql文件my.ini里面加入

在[mysqld]中添加：

server-id = 1

log_bin = master-bin

log_bin_index = master-bin.index

binlog_do_db = my_data

binlog_ignore_db = mysql

2.登录主服务器MySQL创建从服务器用到的账户和权限：

grant replication slave on *.* to 'masterbackup' @'192.168.1.%' identified by 'masterbackup';

3.重启MySQL，命令：

/etc/init.d/mysql restart

4.登录MySQL状态下，查看主服务器状态：

show master status;

(2)从数据库配置(linux系统)

1.编辑my.cnf（命令查找文件位置：find / -name my.cnf）

vi /etc/mysql/my.cnf

在[mysqld]中添加：

server-id = 2

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

这个[mysqld]要自己加千万注意了！！！

2.重启Slave从服务器MySQL，命令：

/etc/init.d/mysql restart

3.登录Slave从服务器，连接Master主服务器：

change master to master_host='192.168.1.103',master_port=3306,master_user='masterbackup',master_password='masterbackup',master_log_file='master-bin.000001',master_log_pos=2403;

最好主服务器ip固定

4.登录MySQL状态下，启动Slave数据同步。

start slave;

5.登录MySQL状态下，查看Slave信息：

show slave status\G;

这样就成功了。。！！

接下来开始在springmvc里面配置读写分离

1.在spring-dao.xml里面配置数据库连接池和事务管理

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"       xmlns:context="http://www.springframework.org/schema/context"       xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">    <context:component-scan base-package="com.example">        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>    </context:component-scan>    <!-- 配置整合mybatis过程 -->    <!-- 1.配置数据库相关参数properties的属性：${url} -->    <!-- 使用数据库配置文件解耦 -->    <aop:aspectj-autoproxy/>    <context:property-placeholder location="classpath:jdbc.properties"/>    <!-- 下面的druid配置都是基本配置,具体优化设置可以上网查询,也可以去github上面直接搜索druid -->    <!-- dataSource 配置 -->    <!-- 主数据库-->    <bean name="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">        <property name="url" value="${jdbc.master.url}" />        <property name="username" value="${jdbc.master.username}" />        <property name="password" value="${jdbc.master.password}" />        <property name="initialSize" value="${jdbc.initialSize}" />        <property name="minIdle" value="${jdbc.minIdle}" />        <property name="maxActive" value="${jdbc.maxActive}" />        <property name="maxWait" value="${jdbc.maxWait}" />        <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />        <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" />        <property name="validationQuery" value="${jdbc.validationQuery}" />        <property name="testWhileIdle" value="${jdbc.testWhileIdle}" />        <property name="testOnBorrow" value="${jdbc.testOnBorrow}" />        <property name="testOnReturn" value="${jdbc.testOnReturn}" />        <property name="removeAbandoned" value="${jdbc.removeAbandoned}" />        <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" />        <property name="filters" value="${jdbc.filters}" />        <!-- 关闭abanded连接时输出错误日志 -->        <property name="logAbandoned" value="true" />    </bean>    <!--从数据库-->    <bean name="slaveDataSource1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">        <property name="url" value="${jdbc.slave.url}" />        <property name="username" value="${jdbc.slave.username}" />        <property name="password" value="${jdbc.slave.password}" />        <property name="initialSize" value="${jdbc.initialSize}" />        <property name="minIdle" value="${jdbc.minIdle}" />        <property name="maxActive" value="${jdbc.maxActive}" />        <property name="maxWait" value="${jdbc.maxWait}" />        <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />        <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" />        <property name="validationQuery" value="${jdbc.validationQuery}" />        <property name="testWhileIdle" value="${jdbc.testWhileIdle}" />        <property name="testOnBorrow" value="${jdbc.testOnBorrow}" />        <property name="testOnReturn" value="${jdbc.testOnReturn}" />        <property name="removeAbandoned" value="${jdbc.removeAbandoned}" />        <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" />        <property name="filters" value="${jdbc.filters}" />        <!-- 关闭abanded连接时输出错误日志 -->        <property name="logAbandoned" value="true" />    </bean>    <!-- 动态数据源 -->    <bean id="dynamicDataSource" class="com.example.data.ReadAndWriteDataSource">        <!-- 通过key-value关联数据源 -->        <property name="targetDataSources">            <map>                <entry value-ref="masterDataSource" key="master"></entry>                <entry value-ref="slaveDataSource1" key="slave"></entry>            </map>        </property>        <property name="defaultTargetDataSource" ref="masterDataSource" />    </bean>    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">        <property name="dataSource" ref="dynamicDataSource"/>    </bean>    <!--mybatis与Spring整合 开始 -->    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">        <property name="dataSource" ref="dynamicDataSource" />        <property name="mapperLocations">            <list>                <!--<value>classpath:cc/alino/business/authc/mapper/*.xml</value>-->                <value>classpath:mapper/*.xml</value>            </list>        </property>        <property name="configLocation" value="classpath:mybatis-config.xml" />    </bean>    <!-- 4.配置扫描Dao接口包，动态实现Dao接口，注入到spring容器中 -->    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">        <!-- 注入sqlSessionFactory -->        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>        <!-- 给出需要扫描Dao接口包 -->        <property name="basePackage" value="com.example.dao"        />    </bean>    <!-- 上面的druid的配置 -->    <bean id="logFilter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter">        <property name="statementExecutableSqlLogEnable" value="false" />    </bean>    <!--事务-->    <tx:advice id="txAdvice" transaction-manager="transactionManager">        <tx:attributes>            <tx:method name="publish*" />            <tx:method name="save*" />            <tx:method name="add*" />            <tx:method name="update*" />            <tx:method name="insert*" />            <tx:method name="create*" />            <tx:method name="del*" />            <tx:method name="load*" />            <tx:method name="init*" />            <tx:method name="*"  read-only="true"/>        </tx:attributes>    </tx:advice>    <!--aop--><bean id="dynamicDataSourceAspect" class="com.example.aspect.DatasourceAspect"></bean><aop:config>    <aop:pointcut id="myPointcut" expression="execution(public * com.example.service.serviceImp.*.*(..))" />    <aop:advisor advice-ref="txAdvice" pointcut-ref="myPointcut"  />        <aop:aspect id="c" ref="dynamicDataSourceAspect" order="1">        <aop:pointcut id="tx" expression="execution(public * com.example.service.serviceImp.*.*(..))" />        <aop:before pointcut-ref="tx"  method="before" />    </aop:aspect></aop:config>
</beans>

2.jdbc.properties配置

jdbc.master.url=jdbc:mysql://localhost:3306/testangular?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNulljdbc.master.username=rootjdbc.master.password=rootjdbc.slave.url=jdbc:mysql://192.168.60.128:3306/testangular?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNulljdbc.slave.username=rootjdbc.slave.password=poidsajdbc.initialSize=3jdbc.minIdle=2jdbc.maxActive=60jdbc.maxWait=60000jdbc.timeBetweenEvictionRunsMillis=60000jdbc.minEvictableIdleTimeMillis=30000jdbc.validationQuery=SELECT 'x'jdbc.testWhileIdle=truejdbc.testOnBorrow=falsejdbc.testOnReturn=falsejdbc.poolPreparedStatements=truejdbc.maxPoolPreparedStatementPerConnectionSize=20jdbc.removeAbandoned=truejdbc.removeAbandonedTimeout=120jdbc.logAbandoned=falsejdbc.filters=stat

3.@DataSource自定义注解

@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.METHOD)public @interface DataSource {  String value() default "master";}

5.Aop的切面类DatasourceAspect

public class DatasourceAspect {    public void before(JoinPoint joinpoint) throws Exception{        String methodname=joinpoint.getSignature().getName();        Object target=joinpoint.getTarget();        Class<?>[] classzz=target.getClass().getInterfaces();        Class<?>[] paramesType=((MethodSignature)joinpoint.getSignature()).getMethod().getParameterTypes();        java.lang.reflect.Method method=classzz[0].getMethod(methodname,paramesType);        DataSource dataSource=null;        try {            if(method!=null&&method.isAnnotationPresent(DataSource.class)){                dataSource= method.getAnnotation(DataSource.class);                DataContextHolder.putDataType(dataSource.value());            }        }        catch (Exception e){            System.out.println("数据源选择错误");        }    }}

6.使用方式在mybatis的service接口内使用

public interface LeftTabService {    @DataSource("slave")    List<LeftTabTable> list();    @DataSource("master")    int insertLeftTab(LeftTabTable leftTabTable);}

7.这个类加上很重要的数据库初始化类

public class ReadAndWriteDataSource extends AbstractRoutingDataSource{    @Override    protected Object determineCurrentLookupKey() {        return DataContextHolder.getDataType();    }}

主库做写入操作，从库做读取操作

阅读全文

1 0