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

GitHub 代码：lane-mysql-42

单纯的 MySQL 实现

建库 lane1 和 lane2

建表 SQL

  2. CREATE TABLE `position` (
  3.   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  4.   `name` varchar(256) DEFAULT NULL,
  5.   `salary` varchar(50) DEFAULT NULL,
  6.   `city` varchar(256) DEFAULT NULL,
  7.   PRIMARY KEY (`Id`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  11. CREATE TABLE `position_detail` (
  12.   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  13.   `pid` bigint(11) NOT NULL DEFAULT '0',
  14.   `description` text DEFAULT NULL,
  15.   PRIMARY KEY (`Id`)
  16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

下载并配置 sharding porxy

下载解压后如图

Sharding Proxy 实战 - 图1

添加 mysql 驱动

在 lib 下添加 mysql 的 jar 如图

Sharding Proxy 实战 - 图2

修改 conf 下 server.yaml 如下

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

Sharding Proxy 实战 - 图3

修改 conf 下 sharding.yaml 如下

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

Sharding Proxy 实战 - 图4

启动 bin 下的 start.sh

  1. sh start.sh

出现错误

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

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

访问测试代理数据库

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

  1. mysql -uroot -h 127.0.0.1   -P 3307 -p
  2. mysql> show databases;
  3. +----------+
  4. | Database |
  5. +----------+
  6. | lane_db  |
  7. +----------+
  8. 1 row in set (0.02 sec)

可以看到把两个数据库的 position 的数据都查询出来了Sharding Proxy 实战 - 图5

执行下 insert 看下

  1. mysql> insert into position (name,salary,city) values ('tom',20000,'shanghai');
  2. Query OK, 1 row affected (0.02 sec)
  4. mysql> insert into position (name,salary,city) values ('lucy',21000,'hangzhou');
  5. Query OK, 1 row affected (0.02 sec)

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

Sharding Proxy 实战 - 图6

附加

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

Sharding Proxy + Spring Boot 实现

创建父项目作为依赖管理

mysql-example

添加依赖

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <modelVersion>4.0.0</modelVersion>
  7.     <groupId>com.galaxy</groupId>
  8.     <artifactId>mysql-example</artifactId>
  9.     <packaging>pom</packaging>
  10.     <version>1.0-SNAPSHOT</version>
  11.     <modules>
  13.         <module>sharding-proxy-example</module>
  14.     </modules>
  15.     <properties>
  16.         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  17.         <project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
  18.         <shardingsphere.version>4.1.0</shardingsphere.version>
  19.         <springboot.version>2.2.9.RELEASE</springboot.version>
  21.     </properties>
  22.     <dependencyManagement>
  23.     <dependencies>
  24.         <dependency>
  25.             <groupId>org.springframework.boot</groupId>
  26.             <artifactId>spring-boot-starter-jdbc</artifactId>
  27.             <version>${springboot.version}</version>
  28.         </dependency>
  29.         <dependency>
  30.             <groupId>org.springframework.boot</groupId>
  31.             <artifactId>spring-boot-starter-data-jpa</artifactId>
  32.             <version>${springboot.version}</version>
  33.         </dependency>
  34.         <dependency>
  35.             <groupId>org.springframework.boot</groupId>
  36.             <artifactId>spring-boot-starter-data-jpa</artifactId>
  37.             <version>2.1.6.RELEASE</version>
  38.         </dependency>
  39.         <dependency>
  40.             <groupId>org.springframework.boot</groupId>
  41.             <artifactId>spring-boot-starter-test</artifactId>
  42.             <version>${springboot.version}</version>
  43.             <scope>test</scope>
  44.         </dependency>
  45.         <dependency>
  46.             <groupId>mysql</groupId>
  47.             <artifactId>mysql-connector-java</artifactId>
  48.             <version>5.1.48</version>
  49.         </dependency>
  51.     </dependencies>
  52.     </dependencyManagement>
  53.     <build>
  54.             <plugins>
  55.                 <plugin>
  56.                     <groupId>org.apache.maven.plugins</groupId>
  57.                     <artifactId>maven-compiler-plugin</artifactId>
  58.                     <version>3.8.1</version>
  59.                     <configuration>
  60.                         <source>11</source>
  61.                         <target>11</target>
  62.                         <testSource>11</testSource>
  63.                         <testTarget>11</testTarget>
  64.                     </configuration>
  65.                 </plugin>
  66.             </plugins>
  67.     </build>
  68. </project>

创建模块项目

sharding-proxy-example

依赖

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <parent>
  6.         <artifactId>mysql-example</artifactId>
  7.         <groupId>com.galaxy</groupId>
  8.         <version>1.0-SNAPSHOT</version>
  9.     </parent>
  10.     <modelVersion>4.0.0</modelVersion>
  12.     <artifactId>sharding-proxy-example</artifactId>
  14.     <dependencies>
  15.         <dependency>
  16.             <groupId>org.springframework.boot</groupId>
  17.             <artifactId>spring-boot-starter-jdbc</artifactId>
  19.         </dependency>
  20.         <dependency>
  21.             <groupId>org.springframework.boot</groupId>
  22.             <artifactId>spring-boot-starter-data-jpa</artifactId>
  23.         </dependency>
  24.         <dependency>
  25.             <groupId>org.springframework.boot</groupId>
  26.             <artifactId>spring-boot-starter-test</artifactId>
  27.             <scope>test</scope>
  28.         </dependency>
  29.         <dependency>
  30.             <groupId>mysql</groupId>
  31.             <artifactId>mysql-connector-java</artifactId>
  32.         </dependency>
  33.     </dependencies>
  34. </project>

Java 基本代码

entity

  1. package com.galaxy.entity;
  3. import javax.persistence.*;
  4. import java.io.Serializable;
  6. /**
  7.  * @author lane
  8.  * @date 2021年07月23日 下午6:05
  9.  */
  10. @Entity
  11. @Table(name = "position")
  12. public class Position implements Serializable {
  14.     @Id
  15.     @Column(name = "id")
  16.     //使用sharding jdbc指定主键生成，不指定则是数据库默认auto_increment
  17.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  18.     private long id;
  20.     @Column(name = "name")
  21.     private String name;
  23.     @Column(name = "salary")
  24.     private String salary;
  26.     @Column(name = "city")
  27.     private String city;
  29.     public long getId() {
  30.         return id;
  31.     }
  33.     public void setId(long id) {
  34.         this.id = id;
  35.     }
  37.     public String getName() {
  38.         return name;
  39.     }
  41.     public void setName(String name) {
  42.         this.name = name;
  43.     }
  45.     public String getSalary() {
  46.         return salary;
  47.     }
  49.     public void setSalary(String salary) {
  50.         this.salary = salary;
  51.     }
  53.     public String getCity() {
  54.         return city;
  55.     }
  57.     public void setCity(String city) {
  58.         this.city = city;
  59.     }
  60. }

repository

  1. package com.galaxy.repository;
  3. import com.galaxy.entity.Position;
  4. import org.springframework.data.jpa.repository.JpaRepository;
  5. import org.springframework.data.jpa.repository.Query;
  6. import org.springframework.data.repository.query.Param;
  8. /**
  9.  * @author lane
  10.  * @date 2021年07月20日 下午7:36
  11.  */
  12. public interface PositionRepository extends JpaRepository<Position,Long> {
  15. }

启动类

  1. package com.galaxy;
  3. import org.springframework.boot.autoconfigure.SpringBootApplication;
  5. /**
  6.  * @author lane
  7.  * @date 2021年07月23日 下午6:10
  8.  */
  9. @SpringBootApplication
  10. public class ProxyApplication {
  11. }

配置信息

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

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

测试代码

  2. /**
  3.  * @author lane
  4.  * @date 2021年07月23日 下午6:16
  5.  */
  6. @SpringBootTest(classes = ProxyApplication.class)
  7. @RunWith(SpringRunner.class)
  8. public class TestProxy {
  10.     @Autowired
  11.     PositionRepository positionRepository;
  13.     @Test
  14.     public void test1(){
  16.         List<Position> positionList = positionRepository.findAll();
  17.         positionList.forEach((position)->{
  18.             System.out.println(position.getName());
  19.         });
  21.     }
  23.     @Test
  24.     @Repeat(2)
  25.     public void test2(){
  26.         Position position =new Position();
  27.         position.setName("sky"+new Random().nextInt(12));
  28.         position.setSalary("100000");
  29.         position.setCity("guangzhou");
  30.      positionRepository.save(position);
  32.     }
  33. }

测试结果

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

Sharding Proxy 实战 - 图7

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

Sharding Proxy 实战 - 图8