7. SpringBoot 整合MySQL高可用数据库
📕 SpringBoot 实现MySQL读写分离(二)
application.properties
#配置文件
spring.profiles.active=master
#thymeleaf 后缀
spring.freemarker.suffix=.html
# 处理SpringBoot2.6.x与Swagger2 3.0.0版本冲突
spring.mvc.pathmatch.matching-strategy=ant_path_matcher
#log4j2的配置文件地址
logging.config=classpath:log4j2.xml
#mybatis的配置信息
mybatis.mapper-locations=classpath:mapper/*/*.xml
mybatis.type-aliases-package=com.yjren.yjrenweb.model
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
application-master.properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.150.15:3306/rzleyou?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root@1234
application-slave.properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.150.17:3306/rzleyou?useSSL=false&serverTimezone=UTC
spring.datasource.username=rzleyou2
spring.datasource.password=root@1234
t_coun_user.sql
CREATE TABLE `t_coun_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) NOT NULL COMMENT '姓名',
`phone` varchar(11) DEFAULT NULL COMMENT '手机号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`operator_id` varchar(32) NOT NULL DEFAULT '' COMMENT '操作人id',
`operator_name` varchar(32) NOT NULL DEFAULT '' COMMENT '操作人名称',
`is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='用户表'
mybatis 逆序文件配置t_coun_user信息
<table tableName="t_coun_user" domainObjectName="UserEntity"
enableInsert="true" enableSelectByPrimaryKey="true"
enableSelectByExample="true" enableUpdateByPrimaryKey="true"
enableDeleteByPrimaryKey="true" enableDeleteByExample="true"
enableCountByExample="true" enableUpdateByExample="true"
selectByExampleQueryId="true">
<property name="runtimeTableName" value="t_coun_user"/>
<generatedKey column="id" sqlStatement="Mysql" identity="true"
type="post"/>
</table>
Junit5 测试
@SpringBootTest
@AutoConfigureMockMvc
public class TestApplication {
@Resource
private UserEntityMapper userEntityMapper;
@Test
public void insertMaster(){
//spring.profiles.active=master,主服务器进行插入数据
UserEntity userEntity = new UserEntity();
userEntity.setName("张家口");
userEntity.setPhone("15835125802");
userEntity.setOperatorId("1001");
userEntity.setOperatorName("任雨杰");
userEntityMapper.insertSelective(userEntity);
}
@Test
public void selectMaster(){
//spring.profiles.active=master,判断是否插入成功
UserEntityExample example = new UserEntityExample();
example.createCriteria().andIsDeletedEqualTo((byte) 0);
List<UserEntity> userEntities = userEntityMapper.selectByExample(example);
System.out.println(userEntities);
}
@Test
public void selectSlave(){
//spring.profiles.active=slave,从服务器进行查询，是否数据同步
UserEntityExample example = new UserEntityExample();
example.createCriteria().andIsDeletedEqualTo((byte) 0);
List<UserEntity> userEntities = userEntityMapper.selectByExample(example);
System.out.println(userEntities);
}
@Test
public void deleteSlave(){
//spring.profiles.active=slave,限制从服务器用户只读，执行该方法报错代表只读权限成功
UserEntityExample example = new UserEntityExample();
example.createCriteria().andIsDeletedEqualTo((byte) 0);
userEntityMapper.deleteByExample(example);
}
/*
create user 'rzleyou2'@'%' identified by 'root@1234'; // 创建普通用户，可以远程连接
grant select on *.* to 'rzleyou2'@'%'; //授权所有库,只能查询操作
FLUSH PRIVILEGES;
*/
}
