Sharding Proxy 实战

理论参考：MySQL 高级框架实战理论笔记

GitHub 代码：lane-mysql-42

单纯的 MySQL 实现

建库 lane1 和 lane2

建表 SQL

CREATE TABLE `position` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `salary` varchar(50) DEFAULT NULL,
  `city` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `position_detail` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `pid` bigint(11) NOT NULL DEFAULT '0',
  `description` text DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

下载并配置 sharding porxy

下载解压后如图

添加 mysql 驱动

在 lib 下添加 mysql 的 jar 如图

修改 conf 下 server.yaml 如下

基本默认即可，指定自己逻辑代理数据库名 lane_db

修改 conf 下 sharding.yaml 如下

修改逻辑库名，database 信息，配置下分库规则

启动 bin 下的 start.sh

sh start.sh

出现错误

Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
Unrecognized VM option 'UseFastAccessorMethods'

看意思是 在 jdk 未来的版本将会 移除 UseFastAccessorMethods，而我使用的是 jdk11 超过了 9，那么应该是移除了，故而无法识别 UseFastAccessorMethods ，下载的 sharding proxy 有点老了，那就修改下 start.sh 删掉 UseFastAccessorMethods，重新启动成功

访问测试代理数据库

在终端输入，注意默认端口号是 3307

mysql -uroot -h 127.0.0.1   -P 3307 -p
mysql> show databases;
+----------+
| Database |
+----------+
| lane_db  |
+----------+
1 row in set (0.02 sec)

可以看到把两个数据库的 position 的数据都查询出来了

执行下 insert 看下

mysql> insert into position (name,salary,city) values ('tom',20000,'shanghai');
Query OK, 1 row affected (0.02 sec)

mysql> insert into position (name,salary,city) values ('lucy',21000,'hangzhou');
Query OK, 1 row affected (0.02 sec)

可以看到成功实现了分库插入数据

附加

可以配置其他配置文件，实现读写分离，脱敏等功能

Sharding Proxy + Spring Boot 实现

创建父项目作为依赖管理

mysql-example

添加依赖

<?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.galaxy</groupId>
    <artifactId>mysql-example</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
    
        <module>sharding-proxy-example</module>
    </modules>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
        <shardingsphere.version>4.1.0</shardingsphere.version>
        <springboot.version>2.2.9.RELEASE</springboot.version>

    </properties>
    <dependencyManagement>
    <dependencies>
        <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-data-jpa</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.1.6.RELEASE</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.48</version>
        </dependency>
 
    </dependencies>
    </dependencyManagement>
    <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.1</version>
                    <configuration>
                        <source>11</source>
                        <target>11</target>
                        <testSource>11</testSource>
                        <testTarget>11</testTarget>
                    </configuration>
                </plugin>
            </plugins>
    </build>
</project>

创建模块项目

sharding-proxy-example

依赖

<?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">
    <parent>
        <artifactId>mysql-example</artifactId>
        <groupId>com.galaxy</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>sharding-proxy-example</artifactId>

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

        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>
</project>

Java 基本代码

entity

package com.galaxy.entity;

import javax.persistence.*;
import java.io.Serializable;

/**
 * @author lane
 * @date 2021年07月23日 下午6:05
 */
@Entity
@Table(name = "position")
public class Position implements Serializable {

    @Id
    @Column(name = "id")
    //使用sharding jdbc指定主键生成，不指定则是数据库默认auto_increment
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "salary")
    private String salary;

    @Column(name = "city")
    private String city;

    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 getSalary() {
        return salary;
    }

    public void setSalary(String salary) {
        this.salary = salary;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }
}

repository

package com.galaxy.repository;

import com.galaxy.entity.Position;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * @author lane
 * @date 2021年07月20日 下午7:36
 */
public interface PositionRepository extends JpaRepository<Position,Long> {


}

启动类

package com.galaxy;

import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @author lane
 * @date 2021年07月23日 下午6:10
 */
@SpringBootApplication
public class ProxyApplication {
}

配置信息

注意库名字是代理库 lane_db 端口号是 3307

#datasource
spring.datasource.url=jdbc:mysql://127.0.0.1:3307/lane_db?useUnicode=true&characterEncoding=utf8
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.zaxxer.hikari.HikariDataSource

测试代码

/**
 * @author lane
 * @date 2021年07月23日 下午6:16
 */
@SpringBootTest(classes = ProxyApplication.class)
@RunWith(SpringRunner.class)
public class TestProxy {

    @Autowired
    PositionRepository positionRepository;

    @Test
    public void test1(){

        List<Position> positionList = positionRepository.findAll();
        positionList.forEach((position)->{
            System.out.println(position.getName());
        });

    }

    @Test
    @Repeat(2)
    public void test2(){
        Position position =new Position();
        position.setName("sky"+new Random().nextInt(12));
        position.setSalary("100000");
        position.setCity("guangzhou");
 	positionRepository.save(position);

    }
}

测试结果

查询好似是一个库，实际是两个库的数据

新增按照规则实现分库 lane1 和 lane2 各添加了一条数据 1