采用sharding-jdbc分库分表

（一）安装Mysql8.0

1、下载安装包

[root@VM-24-15-centos ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
警告：mysql80-community-release-el7-3.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql80-community-release-el7-3  ################################# [100%]
[root@VM-24-15-centos ~]#  yum repolist enabled | grep mysql
mysql-connectors-community           MySQL Connectors Community
mysql-tools-community                MySQL Tools Community
mysql80-community                    MySQL 8.0 Community Server

2、rpm安装依赖包

[root@VM-24-15-centos ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
警告：mysql80-community-release-el7-3.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql80-community-release-el7-3  ################################# [100%]

3、查看yum仓库中Mysql服务

[root@VM-24-15-centos ~]#  yum repolist enabled | grep mysql
mysql-connectors-community           MySQL Connectors Community
mysql-tools-community                MySQL Tools Community
mysql80-community                    MySQL 8.0 Community Server

4、关闭默认mysql模块

[root@VM-24-15-centos ~]# yum module disable mysql

5、yum安装服务

[root@VM-24-15-centos ~]# yum install -y  mysql-community-server 
完毕！
## 看到success表示安装成功！

6、修改etc/my.cnf

[root@VM-24-15-centos ~]# vim /etc/my.cnf
## 添加以下配置
[mysqld]
#配置文件根据环境自行配置，或者保持默认
#例如添加下面几行，设置默认引擎编码和排序规则(根据情况设置合适的)
default-storage-engine=INNODB
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
skip-character-set-client-handshake
secure_file_priv=''

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

utf8mb4和utf8的区别：

utf8mb4是utf8的超集，utf8mb4能够存储4个字节，utf8只能存储3个字节。

7、启动mysql服务

[root@VM-24-15-centos ~]# systemctl start mysqld
## 查看状态
[root@VM-24-15-centos ~]# systemctl status mysqld
## 设置开机启动
[root@VM-24-15-centos ~]# systemctl enable mysqld
## 查看监听端口，默认 3306 
[root@VM-24-15-centos ~]# ss -natl |grep 3306

8、查看默认密码并且修改

[root@VM-24-15-centos ~]# grep 'temporary password' /var/log/mysqld.log
2022-01-17T03:03:23.091948Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: C&,WHskkn6zV
## 默认密码：C&,WHskkn6zV

登录mysql

[root@VM-24-15-centos ~]# mysql -uroot -p'C&,WHskkn6zV'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.27

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> 

修改密码：

mysql> alter user   root@localhost   identified  by  'xxx';
Query OK, 0 rows affected (0.01 sec)
## xxx表示自己的密码

9、开放3306端口和云服务器入站规则

查看防火墙状态

[root@VM-24-15-centos ~]# systemctl status firewalld.service 
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
## dead说明防火墙是关闭的

开启防火墙

[root@VM-24-15-centos ~]# systemctl start firewalld.service 
[root@VM-24-15-centos ~]# systemctl status firewalld.service 
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: active (running) since Mon 2022-01-17 11:24:46 CST; 1s ago
     Docs: man:firewalld(1)
 Main PID: 259131 (firewalld)
    Tasks: 2 (limit: 11426)
   Memory: 32.6M
   CGroup: /system.slice/firewalld.service
           └─259131 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid
## Active: active (running)表示防火墙已经开启

开放防火墙端口

[root@VM-24-15-centos ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@VM-24-15-centos ~]# firewall-cmd --reload
success

查看防火墙已经开放的端口

[root@VM-24-15-centos ~]# firewall-cmd --zone=public --list-ports 
3306/tcp
## 3306端口已经开放

开放云服务器的入站规则（具体参照云服务器提供商操作手册）

Navcat连接问题：

（1）host is not allowed to connect to this mysql server

​ 解决方案：

use mysql;
update user set host = '%' where user ='root';
flush privileges;

（2）1251 client does not support authentication protocol requested by server;consider upgrading Mysql client

​ 解决方案：

USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxx';
## xxx表示自己的密码

（二）搭建Mysql主从同步

1、建Mysql主从

主从复制：

Master节点将DB操作写入Binary log(二进制文件)，Slave节点通过I/O读取二进制文件写入中继文件（Relay log）,然后将Realy log内容刷入到DB中。

注意：

主从复制需要保证DB的版本一致

修改Master节点 /etc/my.cnf配置

[root@hecs-36968 ~]# vim /etc/my.cnf
## 加入以下配置
server-id=100
## 开启二进制文件
log-bin=mysql-bin
## 主从复制忽略mysql数据库
binlog-ignore-db=mysql
## binlog的内存大小
binlog_cache_size=1M
## 主从复制策略
binlog_format=mixed

修改Slave节点 /etc/my.cnf配置

server-id=102
log-bin=mysql-slave-bin
## slave节点二进制文件
relay_log=edu-mysql-relay-bin
binlog-ignore-db=mysql
## binlog的内存大小
binlog_cache_size=1M
## 主从复制策略
binlog_format=mixed
## 排除主键引发的问题报错
slave_skip_errors=1062

重启Master节点数据库使配置生效

[root@hecs-36968 ~]# systemctl restart mysqld

Master节点连接Mysql，查看状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      156 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

重启Slave节点数据库使配置生效

[root@hecs-36968 ~]# systemctl restart mysqld

Slave节点配置连接Master节点

mysql> change master to master_host='xxx', master_user='root', master_password='xxx', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 156, master_connect_retry=30;

Query OK, 0 rows affected, 10 warnings (0.03 sec)

## master_host ：Master的地址
## master_port：Master的端口号
## master_user：用于数据同步的用户
## master_password：用于同步的用户的密码
## master_log_file：指定 Slave 从哪个日志文件开始复制数据
## master_log_pos：从哪个 Position 开始读
## master_connect_retry：如果连接失败，重试的时间间隔

Slave节点启动主从复制

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Slave节点查看主从状态

mysql> show slave status G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 119.3.176.65
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 156
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 156
              Relay_Log_Space: 537
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: b0441377-75ab-11ec-8a87-fa163e61614f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

## Slave_IO_Running: Yes，Slave_SQL_Running: Yes
## 这两个状态表示主从配置完成！

Salve节点主从启动失败常见错误：

（1）Slave_IO_Running: Connecting

​ 解决思路：网络不通、ip、端口、用户名密码不对、pos不对

（2）Slave_SQL_Running: No

​ 解决方案 1：

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
mysql> show slave statusG;

​ 解决方案 2：

## 查看Master状态
mysql> show master status;
## Slave节点重新绑定
mysql> stop slave;
mysql> change master to master_host='119.3.176.65', master_user='root', master_password='xxx', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 156, master_connect_retry=30;
mysql> start slave;

测试主从复制：

Master节点创建sharding_order数据库

mysql> create database sharding_order;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| febs_nacos         |
| geekbang_coupon_db |
| information_schema |
| mysql              |
| performance_schema |
| sharding_order     |
| sys                |
| xxl_job            |
+--------------------+
8 rows in set (0.00 sec)

查看Slave节点

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sharding_order     |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

测试结果表示主从复制搭建成功！

2、JAVA版Sharding-JDBC DEMO

主从pom文件

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.9-SNAPSHOT</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.xuexiangban</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <mybatis.plus.version>3.3.2</mybatis.plus.version>
        <druid.version>1.1.9</druid.version>
        <sharding.jdbc.version>4.1.0</sharding.jdbc.version>
    </properties>
    <dependencies>
        <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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--mybatis-plus依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis.plus.version}</version>
        </dependency>

        <!--druid依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <!--sharding-jdbc依赖-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding.jdbc.version}</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
        <repository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <releases>
                <enabled>false</enabled>
            </releases>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>
        <pluginRepository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <releases>
                <enabled>false</enabled>
            </releases>
        </pluginRepository>
    </pluginRepositories>

</project>

(1)读写分离yaml文件配置

server:
  port: 8080
spring:
  main:
    allow-bean-definition-overriding: true
  ## 打印执行sql
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8
        username: root
        password: xxx
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8
        username: root
        password: xxx
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      ## 默认数据源，用于写
      default-data-source-name: ds0
    masterslave:
      ## 主从配置名称，随便写
      name: ms
      ## 主节点名称，只用于写
      master-data-source-name: ds0
      ## 从节点名称，只用于读，多个用逗号分隔
      slave-data-source-names: ds1
      ## 负载均衡类型 random、round_robin
      load-balance-algorithm-type: round_robin

(2)分库分表yaml配置

server:
  port: 8080
spring:
  main:
    allow-bean-definition-overriding: true
  ## 打印执行sql
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8
        username: root
        password: xxx
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8
        username: root
        password: xxx
        maxPoolSize: 100
        minPoolSize: 5
    sharding:

      ## 用于分库分表
      tables:
        ## 逻辑表名
        user_order:
          ## 主键生成策略
          key-generator:
            column: id
            type: SNOWFLAKE
          ## 配置要分的库和表
          actual-data-nodes: ds$->{0..1}.user_order$->{0..2}
          ## 分库策略
          database-strategy:
            ## 标准分片
            standard:
              sharding-column: birthday
              precise-algorithm-class-name: com.xuexiangban.sharding.conf.BirthdayPreciseShardingAlgorithm
          ## 分表策略
          table-strategy:
            inline:
              sharding-column: sex
              algorithm-expression: user_order$->{sex % 2}

(3)读写分离+分库分表yaml配置

server:
  port: 8080
spring:
  main:
    allow-bean-definition-overriding: true
  ## 打印执行sql
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: m0,s0
      m0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8
        username: root
        password: xxx
        maxPoolSize: 100
        minPoolSize: 5
      s0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8
        username: root
        password: xxx
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      master-slave-rules:
        ds0:
          ## Master节点
          master-data-source-name: m0
          ## Slave节点
          slave-data-source-names: s0
          ## 负载均衡策略
          load-balance-algorithm-type: ROUND_ROBIN
      ## 用于分库分表
      tables:
        ## 逻辑表名
        user_order:
          ## 配置要分的库和表
          actual-data-nodes: ds0.user_order$->{0..2}
          ## 主键生成策略
          key-generator:
            column: id
            type: SNOWFLAKE
          ## 分库策略
          database-strategy:
            ## 标准分片
            standard:
              sharding-column: birthday
              precise-algorithm-class-name: com.xuexiangban.sharding.conf.BirthdayPreciseShardingAlgorithm  
          ## 分表策略
          table-strategy:
            inline:
              sharding-column: sex
              algorithm-expression: user_order$->{sex % 2}

配置雪花算法主键注意点：

1、long类型

2、主键不能自增

分片方式:

（1）inline : 行表达式分片

inline: # 行表达式分片
      sharding-column: test_id # 分片列名称
      algorithm-expression: master_test_${test_id % 2} # 分片表达式,根据指定的表达式计算得到需要路由

（2）standard : 标准分片

    standard: # 单列sharidng算法,需要配合对应的preciseShardingAlgorithm,rangeShardingAlgorithm接口的实现使用,目前无生产可用实现
      shardingColumn: # 列名,允许单列
      preciseShardingAlgorithm: # preciseShardingAlgorithm接口的实现类
      rangeShardingAlgorithm: # rangeShardingAlgorithm接口的实现类

（3）hint : 标记分片**(不推荐)**

hint: #基于标记的sharding分片
      shardingAlgorithm: # 需要是HintShardingAlgorithm接口的实现,目前代码中,仅有为测试目的实现的OrderDatabaseHintShardingAlgorithm,没有生产环境可用的实现

（4）complex : 多列分片**(不推荐)**

    complex: # 支持多列的shariding,目前无生产可用实现
      shardingColumns: # 逗号切割的列
      shardingAlgorithm: # ComplexKeysShardingAlgorithm接口的实现类

官网链接：

https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/configuration/config-yaml/

3、事务配置

（1）添加Maven依赖

## sharding-transaction-spring-boot-starter
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-transaction-spring-boot-starter</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>

（2）业务逻辑通过注解方式开启XA事务

@ShardingTransactionType(TransactionType.XA)
@Transactional
public void demo(){}

最后

以上就是幸福人生为你收集整理的采用sharding-jdbc分库分表的全部内容，希望文章能够帮你解决采用sharding-jdbc分库分表所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。