Sharding-JDBC is a non-invasive tool for MySQL database table sorting. All database table Settings need to be configured in the configuration file without any code changes.

This paper writes a Demo, which uses SpringBoot framework and conducts MySQL instance management through Docker. The structure of libraries and tables is shown as below. Meanwhile, all libraries are replicated by master and slave:

Master/slave database construction

Docker Project Structure:

├─ master │ ├─ ├─ data │ ├─ log │ ├─.log │ ├─ my.cnf │ ├─ mysql-files │ ├── slave ├── data ├─ log │ ├─ error.log ├─ my.cnf ├─ mysql-filesCopy the code

Compose File

version: '3'

networks:
  sharding-jdbc-demo:
    driver: bridge
    ipam:
      config:
        - subnet: 172.25.0.0/24

services:
  master:
    image: mysql
    container_name: sharding-jdbc-demo-master
    ports:
      - "3307:3306"
    volumes:
      - "./master/data:/var/lib/mysql"
      - "./master/mysql-files:/var/lib/mysql-files"         # MySQL8 for Windows, not for Linux
      - "./master/log/error.log:/var/log/mysql/error.log"
      - "./master/my.cnf:/etc/mysql/my.cnf"
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && chmod 644 /etc/mysql/my.cnf && exec /entrypoint.sh mysqld"
    restart: unless-stopped
    networks:
      sharding-jdbc-demo:
        ipv4_address: 172.25.0.101

  slave:
    image: mysql
    container_name: sharding-jdbc-demo-slave
    ports:
      - "3308:3306"
    volumes:
      - "./slave/data:/var/lib/mysql"
      - "./slave/mysql-files:/var/lib/mysql-files"
      - "./slave/log/error.log:/var/log/mysql/error.log"
      - "./slave/my.cnf:/etc/mysql/my.cnf"
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && chmod 644 /etc/mysql/my.cnf && exec /entrypoint.sh mysqld"
    restart: unless-stopped
    networks:
      sharding-jdbc-demo:
        ipv4_address: 172.25.0.102
Copy the code

The Master configuration

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = Log bind-address = 0.0.0.0 secure-file-priv = NULL max_connections = 16384 character-set-server = utf8mb4 collation-server = utf8mb4_general_ci init_connect ='SET NAMES utf8mb4' skip-name-resolve server_id = 1 log-bin = mysql-bin binlog-do-db = db_order_1 # duplicate db_order_1 binlog-do-db = db_order_2 # duplicate db_order_2 binlog-do-db = db_user # Copy db_user log-slave-updates sync_binlog = 1 AUTO_INCREment_offset = 1 auto_INCREment_INCREMENT = 1 expire_logs_days = 7 log_bin_trust_function_creators = 1 # Custom config should go here ! includedir /etc/mysql/conf.d/Copy the code

Slave configuration

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = Log bind-address = 0.0.0.0 secure-file-priv = NULL max_connections = 16384 character-set-server = utf8mb4 collation-server = utf8mb4_general_ci init_connect ='SET NAMES utf8mb4' skip-name-resolve skip-host-cache Server_id = 2 log-bin = mysql-bin log-slave-updates sync_binlog = 0 innodb_flush_log_AT_trx_commit = 0 # Commit policy Replicate -do-db = db_order_1 replicate-do-db = db_order_2 replicate-do-db = db_order_2 replicate-do-db = db_user # Replicate Db_user slave-net-timeout = 60 # Reconnect time log_bin_trust_function_creators = 1 # Custom config should go here! includedir /etc/mysql/conf.d/Copy the code

A master-slave configuration

  1. Start the containerdocker compose up -d;
  2. Log on to the MasterMysql -uroot -h 127.0.0.1 -p 3307 -p ;
  3. Check the master status. 
    mysql> show master status\G *************************** 1. row *************************** File: Mysql-bin.000004 # Remember bin log current file name Position: 156 # Remember bin log current offset Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: db_order_1,db_order_2,db_userCopy the code
  4. Login SlaveMysql -uroot -h 127.0.0.1 -p 3308 -p
  5. Set up the Master connection, notehost 与 portIs the Intranet address and port. 
    Mysql > change master to master_host='172.25.0.101', master_user='root', master_password='123456', master_port=3306, master_log_file='mysql-bin.000004', master_log_pos=156;Copy the code
  6. Start the synchronization 
    mysql> start slave;
Copy the code
  7. Check the Slave status. IfSlave_IO 与 Slave_SQLBoth are running forYESThat is success. 
    mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.25. 0101.
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin000004.
Read_Master_Log_Pos: 156
Relay_Log_File: d2a706a02933-relay-bin000002.
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin000004.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db_order_1,db_order_2,db_user
Copy the code

Create database sub-table

Log in to Master and create database:

CREATE DATABASE db_order_1;
CREATE DATABASE db_order_2;
CREATE DATABASE db_user;
Copy the code

If the secondary database is not created, the primary/secondary configuration fails.

At this point, the vertical and horizontal libraries have been completed. Next create the data table:

The Order of 1 library

First USE db_order_1; Create the t_dict global table, T_order_1, and T_order_2 horizontal tables, respectively.

DROP TABLE IF EXISTS `t_dict`;
CREATE TABLE `t_dict`
(
    `id`         int                                    NOT NULL AUTO_INCREMENT,
    `type`       int                                    NOT NULL,
    `enum_value` int                                    NOT NULL,
    `name`       varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ' '.PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 7
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;
LOCK TABLES `t_dict` WRITE;
INSERT INTO `t_dict` VALUES (1.1.0.'Undefined'), (2.1.1.'Unpaid'), (3.1.2.'Paid'), (4.1.3.'Refund in progress'), (5.1.4.'Refunded'), (6.1.5.'Done'), (7.2.0.'Undefined'), (8.2.1.'Created'), (9.2.2.'Verified'), (10.2.3.'Frozen'), (11.2.4.'Logged off'), (12.2.5.'Deleted');
UNLOCK TABLES;

DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1`
(
    `id`      bigint         NOT NULL,
    `user_id` bigint         NOT NULL,
    `price`   decimal(10.2) NOT NULL,
    `status`  int            NOT NULL DEFAULT '1'.PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2`
(
    `id`      bigint         NOT NULL,
    `user_id` bigint         NOT NULL,
    `price`   decimal(10.2) NOT NULL,
    `status`  int            NOT NULL DEFAULT '1'.PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;
Copy the code

The Order 2 library

First USE db_order_2; Create the t_dict global table, T_order_1, and T_order_2 horizontal tables, respectively. SQL executed is the same as db_order_1.

The User library

First USE db_user; T_dict global table t_user table t_dict global table t_dict global table T_user table Vertical subtable sharding- JDBC will not handle, because vertical subtable is different table heterogeneity, the implementation of Join operation is ok, or code for multiple query implementation.

DROP TABLE IF EXISTS `t_dict`;
CREATE TABLE `t_dict`
(
    `id`         int                                    NOT NULL AUTO_INCREMENT,
    `type`       int                                    NOT NULL,
    `enum_value` int                                    NOT NULL,
    `name`       varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ' '.PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 7
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;
LOCK TABLES `t_dict` WRITE;
INSERT INTO `t_dict` VALUES (1.1.0.'Undefined'), (2.1.1.'Unpaid'), (3.1.2.'Paid'), (4.1.3.'Refund in progress'), (5.1.4.'Refunded'), (6.1.5.'Done'), (7.2.0.'Undefined'), (8.2.1.'Created'), (9.2.2.'Verified'), (10.2.3.'Frozen'), (11.2.4.'Logged off'), (12.2.5.'Deleted');
UNLOCK TABLES;


DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`
(
    `id`   bigint                                  NOT NULL AUTO_INCREMENT,
    `name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ' ',
    `type` int                                     NOT NULL DEFAULT '1'.PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1426999086541635586
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;
Copy the code

Sharding – JDBC introduction

Sharding – JDBC maven:

<! -- Sharding-jdbc -->
<dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.1.1</version>
</dependency>
Copy the code

The other dependencies used by Demo are Junit test, Lombok, MyBatis Plus, Druid connection pool, MySQL driver, Java Faker data generator.

<dependencies>
        <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
        </dependency>
        <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.13.2</version>
                <scope>test</scope>
        </dependency>
        <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
        </dependency>
        <! -- MyBatis Plus -->
        <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.4.3.1</version>
        </dependency>
        <! -- Druid -->
        <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.2.6</version>
        </dependency>
        <! -- MySQL -->
        <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <! -- Sharding-jdbc -->
        <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>4.1.1</version>
        </dependency>
        <! -- Data Faker -->
        <dependency>
                <groupId>com.github.javafaker</groupId>
                <artifactId>javafaker</artifactId>
                <version>1.0.2</version>
        </dependency>
</dependencies>
Copy the code

Sharding – JDBC configuration

Optional configuration

  • Enable SQL printing:

    spring.shardingsphere.props.sql.show = true
Copy the code

Data source Configuration

In total, t_ORDER_1, T_ORDER_2, and T_user are three libraries, plus single-master, single-slave replication, so there are six databases, and six data sources need to be configured:

# Datasource Define
spring.shardingsphere.datasource.names = o1-master,o2-master,o1-slave,o2-slave,u-master,u-slave
# datasource o1-master
spring.shardingsphere.datasource.o1-master.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.o1-master.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.o1-master.url = jdbc:mysql://localhost:3307/db_order_1? useUnicode=true
spring.shardingsphere.datasource.o1-master.username = root
spring.shardingsphere.datasource.o1-master.password = 123456
# datasource o1-slave
spring.shardingsphere.datasource.o1-slave.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.o1-slave.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.o1-slave.url = jdbc:mysql://localhost:3308/db_order_1? useUnicode=true
spring.shardingsphere.datasource.o1-slave.username = root
spring.shardingsphere.datasource.o1-slave.password = 123456
# datasource o2-master
spring.shardingsphere.datasource.o2-master.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.o2-master.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.o2-master.url = jdbc:mysql://localhost:3307/db_order_2? useUnicode=true
spring.shardingsphere.datasource.o2-master.username = root
spring.shardingsphere.datasource.o2-master.password = 123456
# datasource o2-slave
spring.shardingsphere.datasource.o2-slave.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.o2-slave.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.o2-slave.url = jdbc:mysql://localhost:3308/db_order_2? useUnicode=true
spring.shardingsphere.datasource.o2-slave.username = root
spring.shardingsphere.datasource.o2-slave.password = 123456
# datasource u-master
spring.shardingsphere.datasource.u-master.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.u-master.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.u-master.url = jdbc:mysql://localhost:3307/db_user? useUnicode=true
spring.shardingsphere.datasource.u-master.username = root
spring.shardingsphere.datasource.u-master.password = 123456
# datasource u-slave
spring.shardingsphere.datasource.u-slave.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.u-slave.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.u-slave.url = jdbc:mysql://localhost:3308/db_user? useUnicode=true
spring.shardingsphere.datasource.u-slave.username = root
spring.shardingsphere.datasource.u-slave.password = 123456
Copy the code

Configure the primary/secondary replication

The primary/secondary configuration does not need to be declared. When defining, the primary/secondary configuration library in the key is automatically read as the logical library, as shown in db-order-1 below.

# Replication Define
spring.shardingsphere.sharding.master-slave-rules.db-order-1.master-data-source-name=o1-master
spring.shardingsphere.sharding.master-slave-rules.db-order-1.slave-data-source-names=o1-slave
spring.shardingsphere.sharding.master-slave-rules.db-order-2.master-data-source-name=o2-master
spring.shardingsphere.sharding.master-slave-rules.db-order-2.slave-data-source-names=o2-slave
spring.shardingsphere.sharding.master-slave-rules.db-user.master-data-source-name=u-master
spring.shardingsphere.sharding.master-slave-rules.db-user.slave-data-source-names=u-slave
Copy the code

Data Node Configuration

Data node refers to each data table, because there are different types of database, sub-table and global, so there are different types of data node. Note that since we are doing master-slave replication, the database here cannot be filled with the name of the data source directly. It should be filled with the name defined in the Key of the master-slave replication configuration, such as DB-user, rather than U-master or U-slave.

  • Global tables:

    # BroadCast Table
spring.shardingsphere.sharding.broadcast-tables = t_dict
Copy the code

  • Single library single table:

    Key-generator. column Sets the primary key column. Key-generator-type sets the primary key generation type. This uses the snowflake algorithm, which is not necessary because it is not subtable, but it defaults to this.

    # Data Node t_user
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db-user.t_user
spring.shardingsphere.sharding.tables.t_user.key-generator.column = id
spring.shardingsphere.sharding.tables.t_user.key-generator.type = SNOWFLAKE
Copy the code

  • Sub-database sub-table:

    Use groovy expressions in the actual-data-Nodes setting. Set the sharding mode in database-strategy. The specific self-check, no time to write.

    # Data Node t_order, If there is not master-salve-replication, use datasource name like "o$-master->{1.. 2}.t_order_$->{1.. 2}"
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db-order-$->{1.. 2}.t_order_$->{1.. 2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column = id
spring.shardingsphere.sharding.tables.t_order.key-generator.type = SNOWFLAKE
# database sharding strategy
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = db-order-$->{user_id % 2 + 1}
# table sharding strategy
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{id % 2 + 1}
Copy the code

The Demo program

See also: Zoharyips/Sharding-jdbc-demo (github.com)