springboot+mybatis+druid实现mysql主从读写分离

jdbc动态数据源实现操作数据库读写分离

以下一、二缺一不可

一.mysql服务器配置一主多从

mysql主从复制配置，这里以两台虚拟机为例进行配置：

 

环境准备

1.克隆一个centos

网络配置

2.mysql主从复制配置：

2.1主节点

2.1.1创建用户

create user 'xiaxin'@'192.168.237.%' identified by '123456';

2.1.2赋予权限

grant replication slave on *.* to 'xiaxin'@'192.168.237.%' identified by '123456';

如果创建要通用权限用户

create user 'xiaxin'  identified by '123456';
grant all  on *.* to 'xiaxin'@'%' identified by '123456';        

2.1.3启动binlog日志（my.cnf配置文件中加入）

server-id=1    //随便指定一个id  不能与其他主机冲突
log-bin=/var/lib/mysql/mysql-bin

2.1.4重启

service mysqld restart

2.2从节点

2.2.1my.cnf配置文件中加入

server-id=2
relay-log=/var/lib/mysql/relay-bin
relay-log-index=/var/lib/mysql/relay-bin.index

2.2.2登录mysql执行（建立关系）

change master to master_host='192.168.204.101',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0;

注：master_log_file='mysql-bin.000001',master_log_pos=0;这两个值有时需要根据master的信息写

查看命令：

show master status

2.2.3开始复制

start slave; 

2.2.4查看状态

show slave status\G  

搭建注意点：

1.关闭防火墙或开放端口

2.修改 /var/lib/mysql/auto.cnf文件  将uuid随便修改一下（如果是克隆虚拟机的话，会出现要UUID一致的情况）

3.修改配置文件重启

二.代码层面实现读写分离

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.xiaxin</groupId>
    <artifactId>master-slave</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.14</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>
</project>

1.启动类

@SpringBootApplication
//@EnableAspectJAutoProxy(proxyTargetClass = true)
@MapperScan("com.xiaxin.mapper")
public class MybatisMasterSlaveApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisMasterSlaveApplication.class);
    }
}

2.数据源配置

@Configuration
public class DruidConfig {

    public final static String MAPPER_XML_PATH = "classpath:mybatis/mapper/*.xml";

    @ConfigurationProperties(prefix = "master.datasource")
    @Bean(name = "masterDataSource")
    public DataSource masterDataSource() {
        return new DruidDataSource();
    }


    @Bean
    public PlatformTransactionManager txManager(DataSource dynamicDataSource) {
        return new DataSourceTransactionManager(dynamicDataSource);
    }


    @ConfigurationProperties(prefix = "slave.datasource")
    @Bean
    public DataSource slaveDataSource(){
        return  new DruidDataSource();
    }


    @Bean
    public DynamicDataSource dynamicDataSource(){
        DynamicDataSource dynamicDataSource=new DynamicDataSource();
        Map<Object,Object> map=new HashMap<>();
        map.put(DbUtil.master,masterDataSource());
        map.put(DbUtil.slave,slaveDataSource());
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource());
        dynamicDataSource.setTargetDataSources(map);
        return dynamicDataSource;
    }

    @Bean
    public SqlSessionFactoryBean sqlSessionFactoryBean(DataSource dynamicDataSource) throws IOException {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dynamicDataSource);
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_XML_PATH));
        return sqlSessionFactory;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactoryBean sqlSessionFactoryBean) throws Exception {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactoryBean.getObject());
        return sqlSessionTemplate;
    }
}

3.动态数据源配置

/**
 * spring的jdbc提供了动态数据源的入口
 * 继承AbstractRoutingDataSource覆盖determineCurrentLookupKey()方法 返回当前使用数据库
 */
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        log.info("当前使用数据库：{}",DbUtil.getDb());
        return DbUtil.getDb();
    }
}

 工具类用来设置和获取当前使用数据源

public class DbUtil {
    public static String master="master";
    public static String slave="slave";

    private static ThreadLocal<String> threadLocal=new ThreadLocal();


    public static void setDb(String db){
        threadLocal.set(db);
    }

    public static String getDb(){
        return threadLocal.get();
    }

}

问题：这个数据源类型不能写死，那么在哪里设置当前的数据库操作需要访问哪个数据源呢？可以使用注解和aop

4.设置当前访问操作数据库的数据源类型

自定义注解：

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

aop：

@Aspect
@Component
public class DatabaseAOP {
    @Pointcut(value = "execution(* com.xiaxin.mapper..*.*(..))")
    public void pointCut() {

    }

    @Before("pointCut()")
    public void before(JoinPoint joinPoint) {
        MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
        Method method = methodSignature.getMethod();
        boolean isExist = method.isAnnotationPresent(MasterDataSource.class);
        if (!isExist) {
            DbUtil.setDb(DbUtil.slave);
            return;
        }
        DbUtil.setDb(DbUtil.master);
    }
}

注：如果访问方法上加了注解，那么就使用主库用来写入数据

5.业务代码：

controller层：

@RestController
@RequestMapping("/city")
public class CityController {

    @Autowired
    private CityService cityService;

    @GetMapping("/add")
    public String addCity(String cityName) {
        cityService.addCity(new City().setCityName(cityName));
        return "success";
    }
    @GetMapping("/getCityByCityName")
    public List<City> getCityByCityName(String cityName) {
        return cityService.getCityByCityName(cityName);
    }
}

service层：

//接口
public interface CityService {
    void addCity(City city);

    List<City> getCityByCityName(String cityName);
}

//实现类
@Service
public class CityServiceImpl implements CityService {
    @Autowired
    CityMapper cityMapper;
    @Override
    public void addCity(City city) {
       cityMapper.insertCity(city);
    }

    @Override
    public List<City> getCityByCityName(String cityName) {


        return cityMapper.selectByName(cityName);
    }
}

dao层：

@Mapper
@Component
public interface CityMapper {

//    @Insert("INSERT into city (id,city_name) VALUES (#{id,jdbcType=INTEGER}, #{cityName,jdbcType=VARCHAR});")
    @MasterDataSource
    void insertCity(City city);
    /**
     * 根据城市名称，查询城市信息
     *
     * @param cityName 城市名
     */
    @Select("select * from city where city_name like CONCAT('%', #{cityName},'%')")
    List<City> selectByName(@Param("cityName") String cityName);
}

mapper配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiaxin.mapper.CityMapper">
  <resultMap id="BaseResultMap" type="com.xiaxin.entity.City">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="city_name" jdbcType="VARCHAR" property="cityName" />
  </resultMap>
  <sql id="Base_Column_List">
    id,city_name
  </sql>
  <insert id="insertCity" parameterType="com.xiaxin.entity.City">
    INSERT into city (id,city_name) VALUES (#{id,jdbcType=INTEGER}, #{cityName,jdbcType=VARCHAR});
  </insert>
  <!--<select id="selectByName" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from city
    where city_name like CONCAT('%', #{cityName},'%')
  </select>-->
</mapper>

entity实体类：

@Data
@Accessors(chain = true)
public class City {
 
    private Long id;

    private String cityName;

}

查询和写入分别显示如下结果：

至此演示完毕

 

项目源码：

https://github.com/xiaxin07/master-slave.git