ShardingJDBC实现分库以及读写分离架构搭建

• 一、环境软件版本
• 二、环境架构介绍
• 三、MySQL安装(一主两从)
• • 1. 下载mysql5.7.78
  • 2. 移除linux自带的mariadb，避免安装时会产生冲突
  • 3. 安装mysql
  • 4. 初始化用户并查看密码
  • 5. 启动mysql并设置开机启动
  • 6. 登录mysql并修改密码
  • 7.登录Mysql，授权其他机器访问
• 四、主从同步配置
• • 1.主库配置
  • 2.从库配置
  • 3. 建表SQL
• 五、java工程
• • 1. maven依赖
  • 2. 配置文件（分片规则）
  • 3. 映射类以及操作类
  • • 3.1 实体类Order
    • 3.2 mapper类
  • 4. 测试类
  • 5. 测试结果
  • • 5.1 运行testAdd方法
    • 5.2 运行testSelect方法

一、环境软件版本

环境&软件	版本
虚拟机&VMware Fusion	12.1
服务器&CentOS	7.8
数据库&Mysql	5.7.28
远程连接&SSH Shell	16.03

二、环境架构介绍

机器名称	IP	角色
Mysql_Master1	192.168.91.	可读写、主库
Mysql_Slave1	192.168.91.135	只读、Master1从库
Mysql_Slave2	192.168.91.136	只读、Master1从库
Mysql_Master0	192.168.91.134	可读写、主库
Mysql_Slave3	192.168.91.135	只读、Master0从库
Mysql_Slave4	192.168.91.136	只读、Master0从库

三、MySQL安装(一主两从)

1. 下载mysql5.7.78

可以通过wget下载：
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
已下载可通过scp传输：
scp -r mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar root@192.168.91.135:/usr/local/software

2. 移除linux自带的mariadb，避免安装时会产生冲突

rpm -qa|grep mariadb
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps 

3. 安装mysql

tar xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar 
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm --nodeps 
rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm 

4. 初始化用户并查看密码

mysqld --initialize --user=mysql
cat /var/log/mysqld.log | grep password

5. 启动mysql并设置开机启动

systemctl start mysqld
systemctl enable mysqld

6. 登录mysql并修改密码

mysql -uroot -p{cat查看的初始密码}
set password for root@localhost = password('123456');

7.登录Mysql，授权其他机器访问

grant all privileges on *.* to 'root'@'%' identified by 'root';
flush privileges;

四、主从同步配置

1.主库配置

1. 修改配置文件
vim /etc/my.cnf

-----------------------------------追加配置内容：
#bin_log配置
log_bin=mysql-bin
server-id=1
sync-binlog=1
# 忽略database库设置
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0
#gtid配置
#开启gtid
#gtid_mode=on
#enforce_gtid_consistency=1
-----------------------------------配置文件完成

2. 重启mysql
systemctl restart mysqld 

3. 授权同步
grant replication slave on *.* to 'root'@'%' identified by 'root';
flush privileges;

4. 查看主库状态
show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |     1398 |              | information_schema,mysql,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+

2.从库配置

1. 修改配置文件：
vim /etc/my.cnf

-----------------------------------追加配置内容：
#bin_log配置
log_bin=mysql-bin
#服务器ID,从库1是2,从库2是3server-id=2
server-id=2

sync-binlog=1
# 忽略database库设置
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

#relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0
read_only=1

#gtid配置
#开启gtid
#gtid_mode=on
#enforce_gtid_consistency=1
-----------------------------------配置文件完成

2. 重启mysql：systemctl restart mysqld 

3. 设置连接的主库信息并开启slave

change master to master_host='192.168.91.134',master_port=3306,master_user='root',master_password='123456'
,master_log_file='mysql-bin.000001',master_log_pos=1398;

start slave;

此时主从复制已完成，主库的增删改可以同步到从库

3. 建表SQL

CREATE TABLE `c_order`  (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) NULL,
  `company_id` bigint(20) NULL,
  `position_id` bigint(20) NULL,
  `resume_type` int(11) NULL,
  `status` tinyint(1) NULL,
  `create_time` datetime(0) NULL,
  `update_time` datetime(0) NULL,
  `description` varchar(255) NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

五、java工程

1. maven依赖

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter</artifactId>
</dependency>

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
	<version>${springboot.version}</version>
</dependency>

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-test</artifactId>
	<version>${springboot.version}</version>
	<scope>test</scope>
</dependency>

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.35</version>
</dependency>
<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>${shardingsphere.version}</version>
</dependency>

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
	<version>${springboot.version}</version>
</dependency>

2. 配置文件（分片规则）

# 显示执行SQL
spring.shardingsphere.props.sql.show=true
# 显示依赖
spring.shardingsphere.props.sql.show=true
#datasource
spring.shardingsphere.datasource.names=master0,master1,slave1,slave2,slave3,slave4

spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://192.168.91.3:3306/dayu
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=123456

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.91.5:3306/dayu
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=123456


spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.91.2:3306/dayu
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.91.4:3306/dayu
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=123456

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.91.6:3306/dayu
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=123456

spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave4.jdbc-url=jdbc:mysql://192.168.91.7:3306/dayu
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=123456

#sharding-database默认配置
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=master$->{id % 2}

#sharding-tables
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{user_id%2}
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
        
#主从配置
spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave3,slave4
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1,slave2

3. 映射类以及操作类

3.1 实体类Order

package com.dayu.shardingsphere.entity;


import javax.persistence.*;

@Entity
@Table(name = "c_order")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "user_id")
    private Long userId;
    @Column(name = "company_id")
    private Long companyId;
    @Column(name = "position_id")
    private Long positionId;
    @Column(name = "description")
    private String description;

    public Long getId() {
        return id;
    }

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

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public Long getCompanyId() {
        return companyId;
    }

    public void setCompanyId(Long companyId) {
        this.companyId = companyId;
    }

    public Long getPositionId() {
        return positionId;
    }

    public void setPositionId(Long positionId) {
        this.positionId = positionId;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", userId=" + userId +
                ", companyId=" + companyId +
                ", positionId=" + positionId +
                ", description='" + description + '\'' +
                '}';
    }
}

3.2 mapper类

package com.dayu.shardingsphere.repository;

import com.dayu.shardingsphere.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<Order,Long> {
}

4. 测试类

package com.dayu.shardingsphere;

import com.dayu.shardingsphere.entity.Order;
import com.dayu.shardingsphere.repository.OrderRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.List;
import java.util.Random;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingsphereApplication.class)
public class TestShardingDatabase {

    @Resource
    private OrderRepository orderRepository;

    @Test
    public void testAdd() {
        Random random = new Random();
        for (int i = 1; i <= 20; i++) {
            Order order = new Order();
            order.setCompanyId(random.nextLong());
            order.setPositionId(Math.abs(random.nextLong()));
            order.setUserId(Math.abs(random.nextLong()));
            order.setDescription("this is " + i + " record");
            orderRepository.save(order);
        }
    }

    @Test
    public void testSelect() {
        List<Order> all = orderRepository.findAll();
        all.forEach(System.out::println);
    }

}

5. 测试结果

5.1 运行testAdd方法

可以看到实际执行的sql根据user_id取模后存入到对应的库中

5.2 运行testSelect方法

可以看到实际执行的sql分发到从库中执行