1、首先看我前两天发的文章实现主从复制
mysql主从复制https://blog.csdn.net/Dyoungwhite/article/details/127428477?spm=1001.2014.3001.5501
2、将mysql主库中的data文档复制到从库里面(将从库的服务先关掉)
3、将主库和从库的my.ini配置文件中节点数据库名称对应上
4、到idea中修改配置文件,把数据库分为主从两个
server.port=7010 spring.application.name=shuipingfenku-server #bean覆盖 spring.main.allow-bean-definition-overriding=true #mtbatis mybatis.mapper-locations=classpath:/mappers/*.xml #设置数据源名称,多数据源以逗号隔开 spring.shardingsphere.datasource.names=m1,m2,s1,s2 #数据库连接池类名称(主) spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource #数据库驱动类名 spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver #数据库url连接 spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/product_db1?serverTimezone=Asia/Shanghai #数据库用户名 spring.shardingsphere.datasource.m1.username=root #数据库密码 spring.shardingsphere.datasource.m1.password=123456 #数据库连接池类名称 spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource #数据库驱动类名 spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver #数据库url连接 spring.shardingsphere.datasource.m2.url=jdbc:mysql://127.0.0.1:3306/product_db2?serverTimezone=Asia/Shanghai #数据库用户名 spring.shardingsphere.datasource.m2.username=root #数据库密码 spring.shardingsphere.datasource.m2.password=123456 #设置主从分组(dbs0) #spring.shardingsphere.sharding.master-slave-rules.dbs0.masterDataSourceName=m1 #spring.shardingsphere.sharding.master-slave-rules.dbs0.slaveDataSourceNames=m2 #数据库连接池类名称(从) spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSource #数据库驱动类名 spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver #数据库url连接 spring.shardingsphere.datasource.s1.url=jdbc:mysql://127.0.0.1:3308/product_db1?serverTimezone=Asia/Shanghai #数据库用户名 spring.shardingsphere.datasource.s1.username=root #数据库密码 spring.shardingsphere.datasource.s1.password=123456 #数据库连接池类名称 spring.shardingsphere.datasource.s2.type=com.alibaba.druid.pool.DruidDataSource #数据库驱动类名 spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.cj.jdbc.Driver #数据库url连接 spring.shardingsphere.datasource.s2.url=jdbc:mysql://127.0.0.1:3308/product_db2?serverTimezone=Asia/Shanghai #数据库用户名 spring.shardingsphere.datasource.s2.username=root #数据库密码 spring.shardingsphere.datasource.s2.password=123456 #设置主从分组 spring.shardingsphere.sharding.master-slave-rules.m1.masterDataSourceName=m1 spring.shardingsphere.sharding.master-slave-rules.m1.slaveDataSourceNames=s1 spring.shardingsphere.sharding.master-slave-rules.m2.masterDataSourceName=m2 spring.shardingsphere.sharding.master-slave-rules.m2.slaveDataSourceNames=s2 #设置公共表 spring.shardingsphere.sharding.broadcast-tables=shop #节点有几个(分表) spring.shardingsphere.sharding.tables.shop.actualDataNodes=m$->{1..2}.shop #根据id进行表的划分 spring.shardingsphere.sharding.tables.shop.keyGenerator.column=id #根据雪花算法生成表的id spring.shardingsphere.sharding.tables.shop.keyGenerator.type=SNOWFLAKE #根据id进行表的划分 spring.shardingsphere.sharding.tables.product.keyGenerator.column=id #根据雪花算法生成表的id spring.shardingsphere.sharding.tables.product.keyGenerator.type=SNOWFLAKE #节点有几个(分表) spring.shardingsphere.sharding.tables.product.actualDataNodes=m$->{1..2}.product_$->{1..2} #根据id进行计算 spring.shardingsphere.sharding.tables.product.tableStrategy.inline.shardingColumn=id # spring.shardingsphere.sharding.tables.product.tableStrategy.inline.algorithmExpression=product_$->{id % 2 + 1} #指定分片策略,shopid值偶数添加到product_db1表,shopid值奇数添加到product_db2表 spring.shardingsphere.sharding.tables.product.databaseStrategy.inline.shardingColumn=shopId spring.shardingsphere.sharding.tables.product.databaseStrategy.inline.algorithmExpression=m$->{shopId % 2 +1} spring.shardingsphere.props.sql.show=true logging.level.root = info logging.level.org.springframework.web = info logging.level.com.itheima.dbsharding = debug logging.level.druid.sql = debug
上面是整个配置文件,下面是读写分离的主要修改配置
#设置数据源名称,多数据源以逗号隔开
spring.shardingsphere.datasource.names=m1,m2,s1,s2
#数据库连接池类名称(主)
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
#数据库驱动类名
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/product_db1?serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.m1.username=root
#数据库密码
spring.shardingsphere.datasource.m1.password=123456
#数据库连接池类名称
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
#数据库驱动类名
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.m2.url=jdbc:mysql://127.0.0.1:3306/product_db2?serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.m2.username=root
#数据库密码
spring.shardingsphere.datasource.m2.password=123456
#数据库连接池类名称(从)
spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSource
#数据库驱动类名
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.s1.url=jdbc:mysql://127.0.0.1:3308/product_db1?serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.s1.username=root
#数据库密码
spring.shardingsphere.datasource.s1.password=123456
#数据库连接池类名称
spring.shardingsphere.datasource.s2.type=com.alibaba.druid.pool.DruidDataSource
#数据库驱动类名
spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.s2.url=jdbc:mysql://127.0.0.1:3308/product_db2?serverTimezone=Asia/Shanghai
#数据库用户名
spring.shardingsphere.datasource.s2.username=root
#数据库密码
spring.shardingsphere.datasource.s2.password=123456
#设置主从分组
spring.shardingsphere.sharding.master-slave-rules.m1.masterDataSourceName=m1
spring.shardingsphere.sharding.master-slave-rules.m1.slaveDataSourceNames=s1
spring.shardingsphere.sharding.master-slave-rules.m2.masterDataSourceName=m2
spring.shardingsphere.sharding.master-slave-rules.m2.slaveDataSourceNames=s2
5、上面步骤操作完成,接下来就该测试了,我用的是postman
select获取id,看是否是从库中查询的
从日志中可以看到s1,因为在前面配置文件中,主库是m1和m2,从库是s1和s2,这里显示是从s1和s2中查的,也就是说实现了读写分离。
非常简单,希望可以帮到你