windows 下配置 mysql 一主多从 + springboot ,shardingjdbc实现单库分表 读写分离

1.环境

操作系统：win10 64位

mysql版本：5.7.30。下载地址：https://dev.mysql.com/downloads/mysql/
网速慢的看这里 ---->百度网盘：链接：https://pan.baidu.com/s/1EfW_a16S5TtBWEqs9skElQ
提取码：np8t

基于这个demo编写了一个仿简书的博客，目前还没写完，有感兴趣的可以去看看gitee地址

安装mysql

1. 在D盘创建文件夹 ：
2. D:\mysql-5.7.30-winx64
3. D:\mysql-5.7.30-winx64-slave
4. D:\mysql-5.7.30-winx64-slave1
5. 分别在目录下创建my.ini文件
6. D:\mysql-5.7.30-winx64 内容如下

[mysqld]
#skip-grant-tables
default-storage-engine=INNODB
basedir=D:/mysql-5.7.30-winx64
datadir=D:/mysql-5.7.30-winx64/data
bind-address=127.0.0.1
#表示同步的库
binlog-do-db=master_ds_0
#表示不同步的库
binlog-ignore-db=mysql 
port=3306
server-id=1
log-bin=master-bin
log-bin-index=master-bin-index

6. D:\mysql-5.7.30-winx64-slave 内容如下

[mysqld]
#skip-grant-tables
default-storage-engine=INNODB
basedir=D:/mysql-5.7.30-winx64-slave
datadir=D:/mysql-5.7.30-winx64-slave/data
bind-address=127.0.0.1
port=3307
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

7. D:\mysql-5.7.30-winx64-slave1 内容如下

[mysqld]
#skip-grant-tables
default-storage-engine=INNODB
basedir=D:/mysql-5.7.30-winx64-slave1
datadir=D:/mysql-5.7.30-winx64-slave1/data
bind-address=127.0.0.1
port=3308
server-id=3
relay-log=slave1-relay-bin
relay-log-index=slave1-relay-bin.index

目录下分别执行安装：
打开cmd
进入到目录：D:\mysql-5.7.30-winx64\bin>
执行以下命令
mysqld install mysql

mysqld --initialize

进入到目录：D:\mysql-5.7.30-winx64-slave\bin>
执行以下命令
mysqld install mysql-slave

mysqld --initialize

进入到目录：D:\mysql-5.7.30-winx64-slave1\bin>
执行以下命令
mysqld install mysql-slave0

mysqld --initialize

---

会发现目录下新增了data目录，并为root用户分配了默认密码（见D:\mysql-5.7.30-winx64\data$主机名.err日志）

到这里mysql就安装完了

启动三个实例：

net start mysql

net start mysql-slave

net start mysql-slave0

3.配置主从

配置之前 分别进入三个库 ，无论进入那个库都要 初始化密码 并刷新权限

执行命令

mysql -u root -P 3306 -p（大写的P代表端口号，小写的p代表密码）

ALTER USER USER() IDENTIFIED BY ‘root’;

use mysql;

flush privileges;

执行完毕后如下图所示：

至此，主、从数据库的安装及配置就完成了。

进入Windows的服务管理（进入服务管理方法，快键键Win+R打开运行框窗口，输入services.msc），即可看到主从MySQL，以及它们的状态

4.关联主库（mysql）与从库（mysql-slave，mysql-slave0）

上面我们已经把mysql和mysql-slave，mysql-slave相关配置都已配置完成，并分别启动了，现在我们在cmd下分别登录到主库和从库中。

注意：登录主库，需要先进入到主库的bin文件夹下，再登录MySQL，然后，执行命令 show master status 查看master的状态

下图中的 File 和Position 记录下来，在从库配置中会用到 （注意：每次重启主数据库 File 和 Position 参数都会变），如下图所示：

从库，也需要先进入到从库的bin文件夹下，再登录MySQL，然后，执行命令 show slave status 查看slave的状态

我们可以发现 ，master的状态下，生成了一个二进制的日志文件，而slave下是空的，所以我们现在就要把主库与从库关联起来。只需要让从库（slave）知道主库（master）的地址就可以了。

首先我们需要在主库（master）中创建一个用户用于与从库同步的用户名和密码（这里我创建一个test用户，密码为mysql），并给test用户授权，以用于主库操作从库。

注意：是在主库中建立一个用户（专门用给从库连接的，可别迷迷糊糊的到从库的命令界面敲）：

具体如下：

相关命令：

create user test;

grant replication slave on . to ‘从库用户名(test)’@'从库主机地址(127.0.0.1)'identified by ‘密码(mysql)’;

flush privileges; # 刷新权限

现在我们切到从库，把主库与从库联系起来。

执行以下命令：

change master to master_host=‘127.0.0.1’,master_port=3306,master_user=‘test’,master_password=‘mysql’,master_log_file=‘master-bin.000002’,master_log_pos=414;

然后执行命令 start slave 开启主从同步，如下图所示：

执行下列命令查看状态

show slave status \G（注意：\G后面没有分号的）

如下图所示：

下面如果都是yes 就没问题了

另一个从库 也是一样设置 这里就不赘述了。

5、验证主从同步

使用navicat 连接 主库

创建一个新的库

master_ds_0
在新建库右键 运行sql文件

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50721
 Source Host           : localhost:3306
 Source Schema         : master_ds_0

 Target Server Type    : MySQL
 Target Server Version : 50721
 File Encoding         : 65001

 Date: 28/07/2020 00:33:33
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_address
-- ----------------------------
DROP TABLE IF EXISTS `t_address`;
CREATE TABLE `t_address`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '编码',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `pid` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '父id',
  `type` int(11) NULL DEFAULT NULL COMMENT '1国家2省3市4县区',
  `lit` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_user0
-- ----------------------------
DROP TABLE IF EXISTS `t_user0`;
CREATE TABLE `t_user0`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `city_id` int(12) NULL DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) NULL DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电话',
  `email` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `password` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_user1
-- ----------------------------
DROP TABLE IF EXISTS `t_user1`;
CREATE TABLE `t_user1`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `city_id` int(12) NULL DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) NULL DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电话',
  `email` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `password` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

运行完成 查看主库和从库 是否同步
如果配置都没有问题 现在从库 是这样的：

在模拟插入一条数据 试试

好了 现在从库 已经同步过来了

我亲试没问题。 简直是傻瓜教程！！！！！！！！

--------------------------分割线，现在mysql主从配置完了，开始搭建springboot+shardingjdbc----------------------------

springboot + shardingjdbc 环境

springboot + shardingjdbc 分表我参考了别人的文章 附上链接传送门
在这里我就不赘述了，mapper entity cotroller 什么的 大家都懂，在文章最后面我也会附上源码

操作系统： win10
Jdk：1.8
Maven:3.6.0
Mysql:5.7.30

先来一张工程结构截图

pom.xml配置

    <dependencies>
        <!--Springboot 核心依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <!--Boot Web依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--实体类插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--数据源连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.3</version>
        </dependency>
        <!--Mysql 驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>
        <!--Mybatis orm 框架-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
        <!--Json格式化工具类-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.53</version>
        </dependency>
        <!--分库分表-->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <!-- aop 切面 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
            <version>2.2.0.RELEASE</version>
        </dependency>
    </dependencies>

实体类 Address

package org.sharding.demo.entity;

import lombok.Data;

@Data
public class Address {
    private Long id;
    private String code;
    private String name;
    private String pid;
    private Integer type;
    private Integer lit;

    public Address(){

    }
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public Integer getLit() {
        return lit;
    }

    public void setLit(Integer lit) {
        this.lit = lit;
    }
}

实体类 User

package org.sharding.demo.entity;

import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private String phone;
    private String email;
    private String password;
    private Integer cityId;
    private Date createTime;
    private Integer sex;

    public User (){

    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getCityId() {
        return cityId;
    }

    public void setCityId(Integer cityId) {
        this.cityId = cityId;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }
}

mapper AddressMapper

package org.sharding.demo.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.sharding.demo.entity.Address;

import java.util.List;

@Mapper
public interface AddressMapper {
    /**
     * 保存
     */
    @Insert(" INSERT INTO t_address(code,name,pid,type,lit) VALUES (#{code},#{name},#{pid},#{type},#{lit}) ")
    int save(Address address);

    /**
     * 查询
     * @param id
     * @return
     */
    @Select("select * from t_address where id = #{id}")
    Address get(@Param("id") Long id);

    @Select("select * from t_address")
    List<Address> getList();
}

Mapper UserMapper

package org.sharding.demo.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.sharding.demo.entity.User;

import java.util.List;

@Mapper
public interface UserMapper {
    /**
     * 保存
     */
    @Insert(" INSERT INTO t_user(name,phone,email,city_id,sex,password) VALUES ( #{name},#{phone},#{email},#{cityId},#{sex},#{password} ) ")
    int save(User user);

    /**
     * 查询
     * @param id
     * @return
     */
    @Select("select * from t_user where id = #{id}")
    User get(Long id);


    @Select("select * from t_user")
    List<User> getList();
}

Service 和 Impl 我就不贴了 大家都知道怎么写

Cotroller AddressController

package org.sharding.demo.controller;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.sharding.demo.entity.Address;
import org.sharding.demo.servce.impl.AddressServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Slf4j
@RestController
public class AddressController {

    @Autowired
    private AddressServiceImpl addressMapper;

    @RequestMapping("/address/save")
    public String save() {
        for (int i = 0; i <10 ; i++) {
            Address address=new Address();
            address.setCode("code_"+i);
            address.setName("name_"+i);
            address.setPid(i+"");
            address.setType(0);
            address.setLit(i%2==0?1:2);
            addressMapper.save(address);
        }

        return "success";
    }

    @RequestMapping("/address/get/{id}")
    public Object get(@PathVariable Long id) {
        Address address = addressMapper.get(id);
        return address;
    }


    @RequestMapping("/addressList")
    public Object getList(){
        return addressMapper.getList();
    }
}

UserController

package org.sharding.demo.controller;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.sharding.demo.entity.User;
import org.sharding.demo.servce.impl.UserServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Date;

/**
 * <p>
 *  前端控制器
 * </p>
 * @author mazhq123
 * @since 2019-08-20
 */
@Slf4j
@RestController
public class UserController {

    @Autowired
    private UserServiceImpl userMapper;

    @RequestMapping("/user/save")
    public String save() {
        for (int i = 0; i <10 ; i++) {
            User user = new User();
            user.setName("test"+i);
            user.setCityId(1%2==0?1:2);
            user.setCreateTime(new Date());
            user.setSex(i%2==0?1:2);
            user.setPhone("11111111"+i);
            user.setEmail("xxxxx");
            user.setCreateTime(new Date());
            user.setPassword("eeeeeeeeeeee");
            userMapper.save(user);
        }

        return "success";
    }

    @RequestMapping("/user/get/{id}")
    public User get(@PathVariable Long id) {
        User user =  userMapper.get(id);
        return user;
    }

    @RequestMapping("/UserList")
    public Object getList(){
        return userMapper.getList();
    }
}

application.yml

server:
  port: 9000
spring:
  profiles:
    #加载指定yml文件 { Sub-table 分表不开库 + 读写分离 (一主俩从) }如果引用多个文件中间用逗号隔开
    include:
      Sub-table
  application:
    name: shatding-springboot-mybatis

mybatis:
  type-aliases-package: org.sharding.demo.entity
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true #忽略下划线





#日记配置
logging:
  pattern:
    #输出到控制台
    console: "%clr(%d{${LOG_DATEFORMAT_PATTERN:yyyy-MM-dd HH:mm:ss}}){magenta} %clr(${LOG_LEVEL_PATTERN:-%5p}) %clr(%25.25logger{25}){cyan} %clr(:){faint} %m%n${LOG_EXCEPTION_CONVERSION_WORD:%wEx}"
    #指定文件中日志输出的格式
    file: "%d{yyyy-MM-dd} === [%thread] === %-5level === %logger{50} ==== %msg%n"
  level:
    #dao层日志输出控制台
    org.sharding.demo.mapper: debug
    # 在当前磁盘的根路径下创建文件夹
  #file:
    #path: "/logs/"

application-Sub-table.yml

sharding:
  jdbc:
    datasource:
      #druid相关配置
      druid:
        #配置初始化大小/最小/最大
        initial-size: 1
        min-idle: 1
        max-active: 50
        #获取连接等待超时时间
        max-wait: 60000
        #间隔多久进行一次检测，检测需要关闭的空闲连接
        time-between-eviction-runs-millis: 60000
        #一个连接在池中最小生存的时间
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 'x'
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        #打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
        pool-prepared-statements: false
        max-pool-prepared-statement-per-connection-size: 20
      # 多数据源 {ds0}
      names: ds0,ds1,ds2
      #主库
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/master_ds_0?useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password: root
      #从库1
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3307/master_ds_0?useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password: root
      #从库2
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3308/master_ds_0?useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password: root
    config:
      masterslave:
        # 配置从库选择策略，提供轮询与随机，这里选择用轮询
        load-balance-algorithm-type: round_robin
        #这个名字自定义就行了
        name: boot-sharding
        master-data-source-name: ds0
        slave-data-source-names: ds1,ds2
      sharding:
        props:
          sql.show: true
        tables:
          t_user:  #t_user表
            key-generator-column-name: id  #主键  默认使用雪花算计生成唯一Id
            actual-data-nodes: ds0.t_user${0..1}    #数据节点,均匀分布
            table-strategy:  #分表策略
              inline: #行表达式
                sharding-column: sex
                algorithm-expression: t_user${sex % 2}  #按模运算分配