MicroBot - 微服务后端开发基础框架

组件概览

快速开发框架：Spring Boot
服务治理框架：Spring Cloud
负载均衡：OpenFeign
API网关：Gateway
注册中心：Nacos
配置中心：Nacos
流量防护：Sentinel
链路追踪：Sleuth、Zipkin
消息队列：RocketMQ
缓存：Redis
搜索：Elasticsearch
鉴权：JWT
数据库：MySQL、ShardingSphere
分布式事务：Seata

版本依赖关系

Spring Cloud Version	Spring Cloud Alibaba Version	Spring Boot Version
Spring Cloud 2020.0.1	2021.1	2.4.2
Spring Cloud Hoxton.SR9	2.2.6.RELEASE	2.3.2.RELEASE

组件版本关系

Spring Cloud Alibaba Version	Sentinel Version	Nacos Version	RocketMQ Version	Seata Version
2021.1 2.2.5.RELEASE 2.1.4.RELEASE 2.0.4.RELEASE	1.8.0	1.4.1	4.4.0	1.3.0
2.2.6.RELEASE	1.8.1	1.4.2	4.4.0	1.3.0

注册中心：Nacos

wget -c https://github.com/alibaba/nacos/releases/download/2.0.3/nacos-server-2.0.3.tar.gz
tar -zxvf nacos-server-2.0.3.tar.gz
cd nacos
sh bin/startup.sh -m standalone

流量防护：Sentinel-Dashboard

# clone下来整个项目，进入到sentinel-dashboard项目根目录，打包项目
git clone https://github.com/alibaba/Sentinel.git
cd /sentinel/sentinel-dashboard
mvn clean package
# 守护进程启动服务
nohup java -Dserver.port=8080 -Dcsp.sentinel.dashboard.server=localhost:8080 -Dproject.name=sentinel-dashboard -jar sentinel-dashboard.jar &
# 控制台URL：localhost:8080
# 用户名和密码都是：sentinel
# 参考文档：https://github.com/alibaba/Sentinel/tree/master/sentinel-dashboard
# 参考文档：https://github.com/alibaba/spring-cloud-alibaba/wiki/Sentinel

链路跟踪：Zipkin

curl -sSL https://zipkin.io/quickstart.sh | bash -s
nohup java -jar zipkin.jar &
# 控制台URL：localhost:9411
# 参考文档：https://zipkin.io/pages/quickstart
# 持久化存储到MySQL：
# 参考文档：https://github.com/openzipkin/zipkin/blob/master/zipkin-storage/mysql-v1/src/main/resources/mysql.sql
nohup java -jar zipkin.jar --STORAGE_TYPE=mysql --MYSQL_HOST=192.168.0.104 --MYSQL_TCP_PORT=3306 --MYSQL_DB=zipkin-log --MYSQL_USER=root --MYSQL_PASS=root &

分布式事务：Seata

Seata-Server配置

创建seata数据库并创建以下表

-- -------------------------------- The script used when storeMode is 'db' --------------------------------
CREATE DATABASE `seata` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- the table to store GlobalSession data
CREATE TABLE IF NOT EXISTS `global_table`
(
    `xid`                       VARCHAR(128) NOT NULL,
    `transaction_id`            BIGINT,
    `status`                    TINYINT      NOT NULL,
    `application_id`            VARCHAR(32),
    `transaction_service_group` VARCHAR(32),
    `transaction_name`          VARCHAR(128),
    `timeout`                   INT,
    `begin_time`                BIGINT,
    `application_data`          VARCHAR(2000),
    `gmt_create`                DATETIME,
    `gmt_modified`              DATETIME,
    PRIMARY KEY (`xid`),
    KEY `idx_gmt_modified_status` (`gmt_modified`, `status`),
    KEY `idx_transaction_id` (`transaction_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- the table to store BranchSession data
CREATE TABLE IF NOT EXISTS `branch_table`
(
    `branch_id`         BIGINT       NOT NULL,
    `xid`               VARCHAR(128) NOT NULL,
    `transaction_id`    BIGINT,
    `resource_group_id` VARCHAR(32),
    `resource_id`       VARCHAR(256),
    `branch_type`       VARCHAR(8),
    `status`            TINYINT,
    `client_id`         VARCHAR(64),
    `application_data`  VARCHAR(2000),
    `gmt_create`        DATETIME(6),
    `gmt_modified`      DATETIME(6),
    PRIMARY KEY (`branch_id`),
    KEY `idx_xid` (`xid`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

-- the table to store lock data
CREATE TABLE IF NOT EXISTS `lock_table`
(
    `row_key`        VARCHAR(128) NOT NULL,
    `xid`            VARCHAR(128),
    `transaction_id` BIGINT,
    `branch_id`      BIGINT       NOT NULL,
    `resource_id`    VARCHAR(256),
    `table_name`     VARCHAR(32),
    `pk`             VARCHAR(36),
    `gmt_create`     DATETIME,
    `gmt_modified`   DATETIME,
    PRIMARY KEY (`row_key`),
    KEY `idx_branch_id` (`branch_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE IF NOT EXISTS `distributed_lock`
(
    `lock_key`       CHAR(20) NOT NULL,
    `lock_value`     VARCHAR(20) NOT NULL,
    `expire`         BIGINT,
    primary key (`lock_key`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('AsyncCommitting', ' ', 0);
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('RetryCommitting', ' ', 0);
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('RetryRollbacking', ' ', 0);
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('TxTimeoutCheck', ' ', 0);

# 参考文档：https://github.com/seata/seata/blob/develop/script/server/db/mysql.sql

Nacos创建一个新的Seata专用的namespace：例如：seata
修改file.conf

## transaction log store, only used in seata-server
store {
  mode = "db"
  db {
    datasource = "druid"
    dbType = "mysql"
    driverClassName = "com.mysql.jdbc.Driver"
    url = "jdbc:mysql://127.0.0.1:3306/seata"
    user = "root"
    password = "root"
    minConn = 5
    maxConn = 30
    globalTable = "global_table"
    branchTable = "branch_table"
    lockTable = "lock_table"
    queryLimit = 100
    maxWait = 5000
  }
}

修改registry.conf

registry { 
  type = "nacos"
  nacos {
    application = "seata-server"
    serverAddr = "172.16.213.129:8848"
    group = "SEATA_GROUP"
    namespace = "afe3ca53-758e-487b-8945-42d39cbd5685"
    cluster = "default"
    username = "nacos"
    password = "nacos"
  }
}

config { 
  type = "nacos"
  nacos {
    serverAddr = "172.16.213.129:8848"
    namespace = "afe3ca53-758e-487b-8945-42d39cbd5685"
    group = "SEATA_GROUP"
    username = "nacos"
    password = "nacos"
  } 
}

修改config.txt

store.mode=db
store.file.dir=file_store/data
store.file.maxBranchSessionSize=16384
store.file.maxGlobalSessionSize=512
store.file.fileWriteBufferCacheSize=16384
store.file.flushDiskMode=async
store.file.sessionReloadReadSize=100
store.db.datasource=druid
store.db.dbType=mysql
store.db.driverClassName=com.mysql.cj.jdbc.Driver
store.db.url=jdbc:mysql://127.0.0.1:3306/seata?useUnicode=true
store.db.user=root
store.db.password=root
store.db.minConn=5
store.db.maxConn=30
store.db.globalTable=global_table
store.db.branchTable=branch_table
store.db.queryLimit=100
store.db.lockTable=lock_table
store.db.maxWait=5000

这里列出config.txt所有内容供其参考，我们使用db存储，更改store.db下对应配置，其他保持默认

transport.type=TCP
transport.server=NIO
transport.heartbeat=true
transport.enableClientBatchSendRequest=false
transport.threadFactory.bossThreadPrefix=NettyBoss
transport.threadFactory.workerThreadPrefix=NettyServerNIOWorker
transport.threadFactory.serverExecutorThreadPrefix=NettyServerBizHandler
transport.threadFactory.shareBossWorker=false
transport.threadFactory.clientSelectorThreadPrefix=NettyClientSelector
transport.threadFactory.clientSelectorThreadSize=1
transport.threadFactory.clientWorkerThreadPrefix=NettyClientWorkerThread
transport.threadFactory.bossThreadSize=1
transport.threadFactory.workerThreadSize=default
transport.shutdown.wait=3
service.vgroupMapping.beijing=default
service.default.grouplist=127.0.0.1:8091
service.enableDegrade=false
service.disableGlobalTransaction=false
client.rm.asyncCommitBufferLimit=10000
client.rm.lock.retryInterval=10
client.rm.lock.retryTimes=30
client.rm.lock.retryPolicyBranchRollbackOnConflict=true
client.rm.reportRetryCount=5
client.rm.tableMetaCheckEnable=false
client.rm.sqlParserType=druid
client.rm.reportSuccessEnable=false
client.rm.sagaBranchRegisterEnable=false
client.tm.commitRetryCount=5
client.tm.rollbackRetryCount=5
client.tm.degradeCheck=false
client.tm.degradeCheckAllowTimes=10
client.tm.degradeCheckPeriod=2000
store.mode=db
store.file.dir=file_store/data
store.file.maxBranchSessionSize=16384
store.file.maxGlobalSessionSize=512
store.file.fileWriteBufferCacheSize=16384
store.file.flushDiskMode=async
store.file.sessionReloadReadSize=100
store.db.datasource=druid
store.db.dbType=mysql
store.db.driverClassName=com.mysql.cj.jdbc.Driver
store.db.url=jdbc:mysql://127.0.0.1:3306/seata?useUnicode=true
store.db.user=root
store.db.password=root
store.db.minConn=5
store.db.maxConn=30
store.db.globalTable=global_table
store.db.branchTable=branch_table
store.db.queryLimit=100
store.db.lockTable=lock_table
store.db.maxWait=5000
store.redis.host=127.0.0.1
store.redis.port=6379
store.redis.maxConn=10
store.redis.minConn=1
store.redis.database=0
store.redis.password=null
store.redis.queryLimit=100
server.recovery.committingRetryPeriod=1000
server.recovery.asynCommittingRetryPeriod=1000
server.recovery.rollbackingRetryPeriod=1000
server.recovery.timeoutRetryPeriod=1000
server.maxCommitRetryTimeout=-1
server.maxRollbackRetryTimeout=-1
server.rollbackRetryTimeoutUnlockEnable=false
client.undo.dataValidation=true
client.undo.logSerialization=kryo
client.undo.onlyCareUpdateColumns=true
server.undo.logSaveDays=7
server.undo.logDeletePeriod=86400000
client.undo.logTable=undo_log
client.log.exceptionRate=100
transport.serialization=seata
transport.compressor=none
metrics.enabled=false
metrics.registryType=compact
metrics.exporterList=prometheus
metrics.exporterPrometheusPort=9898

配置文件同步到Nacos

cd seata/script/config-center/nacos
sh nacos-config.sh -h 172.16.213.129 -p 8848 -g SEATA_GROUP -t afe3ca53-758e-487b-8945-42d39cbd5685
# -h ip
# -p port
# -g group
# -t namespace

启动seata-server

cd /seata/bin
sh seata-server.sh -p 8091 -h 192.168.0.101 -n 1
# 如果启动不成功，提示找不到日志文件，则需要创建seta/logs/seata_gc.log
# seata-server默认使用的内网ip注册到注册中心，服务启动最好明确指定IP
# 集群化部署：sh seata-server.sh -p 8092 -h 192.168.0.101 -n 2
# -h：指定在注册中心注册的IP，不指定获取当前的IP，部署在云端或容器中建议指定IP
# -p：端口，默认为8091
# -m：事务日志存储方式，支持file，db、redis，默认file，redis需要seata-server 1.3版本及以上才支持
# -n：指定seata-server节点ID，如1，2，3，默认为1
# -e：指定seata-server运行环境，如dev，prod等，服务启动时会使用registry-dev.conf这样的配置
# 如果使用的是JDK11的话，seata-server.sh里面的启动参数-Xloggc:"$BASEDIR"/logs/seata_gc.log需要修改为：-Xlog:gc:"$BASEDIR"/logs/seata_gc.log，因为-Xloggc在JDK11中已经被弃用

Seata-Client配置

涉及到分布式事务的数据库都要创建undo_log表

-- for AT mode you must to init this sql for you business database. the seata server not need it.
CREATE TABLE IF NOT EXISTS `undo_log`
(
    `branch_id`     BIGINT(20)   NOT NULL COMMENT 'branch transaction id',
    `xid`           VARCHAR(100) NOT NULL COMMENT 'global transaction id',
    `context`       VARCHAR(128) NOT NULL COMMENT 'undo_log context,such as serialization',
    `rollback_info` LONGBLOB     NOT NULL COMMENT 'rollback info',
    `log_status`    INT(11)      NOT NULL COMMENT '0:normal status,1:defense status',
    `log_created`   DATETIME(6)  NOT NULL COMMENT 'create datetime',
    `log_modified`  DATETIME(6)  NOT NULL COMMENT 'modify datetime',
    UNIQUE KEY `ux_undo_log` (`xid`, `branch_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8 COMMENT ='AT transaction mode undo table';

注意事项：

OpenFeign调用报错：Caused by: java.lang.IllegalStateException: Method has too many Body parameters: public abstract com.chhliu.springboot.restful.vo.User com.chhliu.springboot.restful.feignclient.UserFeignClient.updateStock(java.lang.String,java.lang.String)
如果发送的是get请求，那么需要在请求参数前加上@RequestParam注解修饰，Controller里面可以不加该注解修饰。feign中可以有多个@RequestParam，但只能有一个@RequestBody。

Parameter 'num' not found. Available parameters are [arg1, arg0, param1, param2]
如果service层参数这么写:int deductStockBySkuId(Integer skuId, Integer num);dao层也这么写：int deductStockBySkuId(Integer skuId, Integer num);就会报错
正确写法如下：
@Update("update t_stock set stock = (stock - #{num}) where sku_id = #{skuId}")
    int deductStockBySkuId(@Param("skuId") Integer skuId, @Param("num") Integer num);

seata-server启报错：com.alibaba.druid.pool.DruidDataSource   : create connection SQLException, url: jdbc:mysql://127.0.0.1:3306/seata?useUnicode=true, errorCode 0, state 08001
==>com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
如果MySQL数据使用的是5.7版本及以上，这里使用MySQL8.0.18版本，driverClassName更新成了com.mysql.cj.jdbc.Driver

如果分支事务执行的SQL包含日期格式Date，Datetime等，分支有可能会回滚失败，建议把字段类型改成timestamp

序列化异常：更改seata配置文件：client.undo.logSerialization=kryo，默认是jackson

Nacos配置中心配置

MicroBot-Order-Service服务配置

Data ID:MicroBot-Order-Service-dev.yaml

server:
  port: 8000
spring:
  application:
    name: MicroBot-Order-Service
  zipkin:
    base-url: http://172.16.213.129:9411/
    discovery-client-enabled: false
  sleuth:
    sampler:
      probability: 1.0
  cloud:
    nacos:
      discovery:
        server-addr: 172.16.213.129:8848
    alibaba:
      seata:
        tx-service-group: beijing
    sentinel:
      transport:
        dashboard: 172.16.213.129:8080
        port: 9999
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/microbot-order?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: root
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
feign:
  sentinel:
    enabled: true

seata:
  registry:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      application: seata-server
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: afe3ca53-758e-487b-8945-42d39cbd5685
  config:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: afe3ca53-758e-487b-8945-42d39cbd5685

MicroBot-Video-Service服务配置

Data ID:MicroBot-Video-Service-dev.yaml

server:
  port: 9000
spring:
  application:
    name: MicroBot-Video-Service
  zipkin:
    base-url: http://172.16.213.129:9411/
    discovery-client-enabled: false
  sleuth:
    sampler:
      probability: 1.0
  cloud:
    nacos:
      discovery:
        server-addr: 172.16.213.129:8848
    alibaba:
      seata:
        tx-service-group: beijing
    sentinel:
      transport:
        dashboard: 172.16.213.129:8080
        port: 9998
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/microbot-video?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: root
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
feign:
  sentinel:
    enabled: true

seata:
  registry:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      application: seata-server
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: afe3ca53-758e-487b-8945-42d39cbd5685
  config:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: afe3ca53-758e-487b-8945-42d39cbd5685

MicroBot-User-Service服务配置

Data ID:MicroBot-User-Service-dev.yaml

server:
  port: 7000
spring:
  application:
    name: MicroBot-User-Service
  zipkin:
    base-url: http://172.16.213.129:9411/
    discovery-client-enabled: false
  sleuth:
    sampler:
      probability: 1.0
  cloud:
    nacos:
      discovery:
        server-addr: 172.16.213.129:8848
    sentinel:
      transport:
        dashboard: 172.16.213.129:8080
        port: 9997
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/microbot-video?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: root
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
feign:
  sentinel:
    enabled: true

MicroBot-Gateway服务配置

Data ID:MicroBot-Gateway-dev.yaml

server:
  port: 8888
spring:
  application:
    name: MicroBot-Gateway
  zipkin:
    base-url: http://172.16.213.129:9411/
    discovery-client-enabled: false
  sleuth:
    sampler:
      probability: 1.0
  cloud:
    nacos:
      discovery:
        server-addr: http://172.16.213.129:8848
    alibaba:
      seata:
        tx-service-group: beijing
    gateway:
      discovery:
        locator:
          enabled: true
          lower-case-service-id: true
      routes:
        - id: MicroBot-Order-Service
          uri: lb://MicroBot-Order-Service
          order: 1
          predicates:
            - Path=/order-service/**
          filters:
            - StripPrefix=1
        - id: MicroBot-Video-Service
          uri: lb://MicroBot-Video-Service
          order: 1
          predicates:
            - Path=/video-service/**
          filters:
            - StripPrefix=1
        - id: MicroBot-User-Service
          uri: lb://MicroBot-User-Service
          order: 1
          predicates:
            - Path=/user-service/**
          filters:
            - StripPrefix=1

seata:
  registry:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      application: seata-server
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: afe3ca53-758e-487b-8945-42d39cbd5685
  config:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: afe3ca53-758e-487b-8945-42d39cbd5685

MySQL主从复制

数据库安装
CentOS7安装MySQL8.0.18教程：http://39.107.136.250:8080/view/40
主库IP：172.16.213.128
从库IP：172.16.213.130

MySQL-Master配置

首先修改主库配置： vi /etc/my.cnf，增加以下内容：

# 开启Binlog日志
log_bin=master_log
# MySQL唯一标识，数字类型，主库server_id必须小于从库server_id
server_id=1

配置完重启MySQL服务

systemctl restart mysqld

增加一个远程访问的用户

mysql -uroot -proot
mysql> CREATE USER 'slave'@'172.16.213.130' IDENTIFIED BY 'MicroBot@123';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'slave'@'172.16.213.130';
mysql> grant system_user on *.* to 'root';
mysql>  ALTER USER 'slave'@'172.16.213.130' IDENTIFIED WITH mysql_native_password BY 'MicroBot@123';
mysql> flush privileges;

mysql> use mysql;
mysql> select Host,User,Plugin from user;
+----------------+------------------+-----------------------+
| Host           | User             | Plugin                |
+----------------+------------------+-----------------------+
| %              | root             | caching_sha2_password |
| 172.16.213.130 | slave            | mysql_native_password |
| localhost      | mysql.infoschema | caching_sha2_password |
| localhost      | mysql.session    | caching_sha2_password |
| localhost      | mysql.sys        | caching_sha2_password |
+----------------+------------------+-----------------------+
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master_log.000001 |      155 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

进入到MySQL安装根目录，可以看到master_log.000001这个文件

[root@172 mysql]# cd /var/lib/mysql
[root@172 mysql]# ll
total 168008
-rw-r-----. 1 mysql mysql       56 Nov 19 22:51  auto.cnf
-rw-r-----. 1 mysql mysql      178 Nov 19 23:25  binlog.000001
-rw-r-----. 1 mysql mysql       16 Nov 19 22:51  binlog.index
-rw-------. 1 mysql mysql     1680 Nov 19 22:51  ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Nov 19 22:51  ca.pem
-rw-r--r--. 1 mysql mysql     1112 Nov 19 22:51  client-cert.pem
-rw-------. 1 mysql mysql     1676 Nov 19 22:51  client-key.pem
-rw-r-----. 1 mysql mysql     3343 Nov 20 16:02  ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Nov 20 16:02  ibdata1
-rw-r-----. 1 mysql mysql 50331648 Nov 20 16:02  ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Nov 19 22:51  ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Nov 20 16:02  ibtmp1
drwxr-x---. 2 mysql mysql      187 Nov 20 16:02 '#innodb_temp'
-rw-r-----. 1 mysql mysql      155 Nov 20 16:02  master_log.000001
-rw-r-----. 1 mysql mysql       20 Nov 20 16:02  master_log.index
drwxr-x---. 2 mysql mysql      143 Nov 19 22:51  mysql
-rw-r-----. 1 mysql mysql 25165824 Nov 20 16:02  mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 Nov 20 16:02  mysql.sock
-rw-------. 1 mysql mysql        6 Nov 20 16:02  mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 Nov 19 22:51  performance_schema
-rw-------. 1 mysql mysql     1680 Nov 19 22:51  private_key.pem
-rw-r--r--. 1 mysql mysql      452 Nov 19 22:51  public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Nov 19 22:51  server-cert.pem
-rw-------. 1 mysql mysql     1676 Nov 19 22:51  server-key.pem
drwxr-x---. 2 mysql mysql       28 Nov 19 22:51  sys
-rw-r-----. 1 mysql mysql 10485760 Nov 20 16:02  undo_001
-rw-r-----. 1 mysql mysql 10485760 Nov 20 16:02  undo_002

注意：

# 如果是新安装的数据库，没有任何数据，看到有好几个master_log.000001，master_log.000002等等，可以将其全部删除。重新生成。
# 如果是老的数据库，不要改动任何数据，只需要刷新日志即可。使用新生成的log和pos配置即可。
[root@172 mysql]# rm -rf master_log.000001 master_log.000002 master_log.index
mysql> flush logs;

# 如果MySQL服务是虚拟机clone出来的主从服务，MySQL服务的UUID是一样的，slave启动的时候也会报错，通过修改master的UUID即可解决，长度和格式不要变，更改不一样即可。
vi /var/lib/mysql/auto.cnf

MySQL-Salve配置

修改从库配置： vi /etc/my.cnf，增加以下内容：

# MySQL唯一标识，数字类型，主库server_id必须小于从库server_id
server_id=2

配置完重启MySQL服务

systemctl restart mysqld

在slave上配置主库相关信息

[root@172 ~]# mysql -uroot -proot
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to master_host='172.16.213.128',master_user='slave',master_password='MicroBot@123',master_log_file='master_log.000001',master_log_pos=155;
Query OK, 0 rows affected, 1 warnings (0.10 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
# 看看有没有ERROR信息，没有ERROR就成功了。
mysql> show slave status \G;

测试主从同步：在主库上新建数据库，新建表，插入数据，更改数据，删除数据，删除表，删除库，每一步操作完就去从库看下变没变。

数据库中间件：ShardingSphere

参考文档：https://shardingsphere.apache.org/document/current/en/overview/
参考文档：https://github.com/apache/shardingsphere

Order服务分库分表配置

MicroBot-Order-Service-dev.yaml

server:
  port: 8000
spring:
  application:
    name: MicroBot-Order-Service
  zipkin:
    base-url: http://172.16.213.129:9411/
    discovery-client-enabled: false
  sleuth:
    sampler:
      probability: 1.0
  cloud:
    nacos:
      discovery:
        server-addr: 172.16.213.129:8848
    alibaba:
      seata:
        tx-service-group: beijing
    sentinel:
      transport:
        dashboard: 172.16.213.129:8080
        port: 9999
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: m1,m2
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        # HiKariCP需要使用:jdbc-url: jdbc:mysql://172.16.213.128:3306/microbot-order
        url: jdbc:mysql://172.16.213.128:3306/microbot-order?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: MicroBot@123
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.16.213.128:3306/microbot-order2?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: MicroBot@123
    sharding:
      tables:
        # 虚拟表
        t_order:
          # 真实表
          actual-data-nodes: m$->{1..2}.t_order_$->{1..2}
          # 分片键
          key-generator:
            column: order_id
            # 分片算法
            type: SNOWFLAKE
            props:
              worker:
                id: 1
          table-strategy:
            #inline:
              #sharding-column: order_id
              #algorithm-expression: t_order_$->{order_id%2+1}
            standard:
              sharding-column: order_id
              range-algorithm-class-name: com.comet.config.TableRangeShardingAlgorithm
              precise-algorithm-class-name: com.comet.config.TablePreciseShardingAlgorithm
          database-strategy:
            #inline:
              #sharding-column: order_id
              #algorithm-expression: m$->{order_id%2+1}
            standard:
              sharding-column: order_id
              range-algorithm-class-name: com.comet.config.DBRangeShardingAlgorithm
              precise-algorithm-class-name: com.comet.config.DBPreciseShardingAlgorithm
  main:
    allow-bean-definition-overriding: true

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
feign:
  sentinel:
    enabled: true
  compression:
    request:
      enabled: true
      mime-types: text/html,application/xml,application/json
      min-request-size: 512
    response:
      enabled: true
      useGzipDecoder: true
seata:
  registry:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      application: seata-server
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: ab4e52b2-3b98-4773-8e64-0f48737e9992
  config:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: ab4e52b2-3b98-4773-8e64-0f48737e9992

Order服务分库分表、主从同步、读写分离配置

MicroBot-Order-Service-dev.yaml

server:
  port: 8000
spring:
  application:
    name: MicroBot-Order-Service
  zipkin:
    base-url: http://172.16.213.129:9411/
    discovery-client-enabled: false
  sleuth:
    sampler:
      probability: 1.0
  cloud:
    nacos:
      discovery:
        server-addr: 172.16.213.129:8848
    alibaba:
      seata:
        tx-service-group: beijing
    sentinel:
      transport:
        dashboard: 172.16.213.129:8080
        port: 9999
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: m1,m2,m3,m4
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.16.213.128:3306/microbot-order?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: MicroBot@123
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.16.213.128:3306/microbot-order2?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: MicroBot@123
      m3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.16.213.130:3306/microbot-order?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: MicroBot@123
      m4:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://172.16.213.130:3306/microbot-order2?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: MicroBot@123
    sharding:
      tables:
        # 虚拟表
        t_order:
          # 真实表
          actual-data-nodes: ds$->{1..2}.t_order_$->{1..2}
          # 分片键
          key-generator:
            column: order_id
            # 分片算法
            type: SNOWFLAKE
            props:
              worker:
                id: 1
          table-strategy:
            standard:
              sharding-column: order_id
              range-algorithm-class-name: com.comet.config.TableRangeShardingAlgorithm
              precise-algorithm-class-name: com.comet.config.TablePreciseShardingAlgorithm
          database-strategy:
            standard:
              sharding-column: order_id
              range-algorithm-class-name: com.comet.config.DBRangeShardingAlgorithm
              precise-algorithm-class-name: com.comet.config.DBPreciseShardingAlgorithm
      master-slave-rules:
        ds1:
          master-data-source-name: m1
          slave-data-source-names:
            - m3
        ds2:
          master-data-source-name: m2
          slave-data-source-names: 
            - m4
  main:
    allow-bean-definition-overriding: true

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
feign:
  sentinel:
    enabled: true
  compression:
    request:
      enabled: true
      mime-types: text/html,application/xml,application/json
      min-request-size: 512
    response:
      enabled: true
      useGzipDecoder: true
seata:
  registry:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      application: seata-server
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: ab4e52b2-3b98-4773-8e64-0f48737e9992
  config:
    type: nacos
    nacos:
      server-addr: 172.16.213.129:8848
      username: nacos
      password: nacos
      group: SEATA_GROUP
      namespace: ab4e52b2-3b98-4773-8e64-0f48737e9992

数据库文件

microbot-course数据库

/*
 Navicat Premium Data Transfer

 Source Server         : ShardingSphere_Master
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : 172.16.213.128:3306
 Source Schema         : microbot-course

 Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001

 Date: 27/11/2021 23:28:39
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_course
-- ----------------------------
DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
  `course_id` bigint(18) NOT NULL,
  `course_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `course_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `price` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `logo_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table structure for t_course_1
-- ----------------------------
DROP TABLE IF EXISTS `t_course_1`;
CREATE TABLE `t_course_1` (
  `course_id` bigint(18) NOT NULL,
  `course_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `course_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `price` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `logo_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table structure for t_course_2
-- ----------------------------
DROP TABLE IF EXISTS `t_course_2`;
CREATE TABLE `t_course_2` (
  `course_id` bigint(18) NOT NULL,
  `course_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `course_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `price` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `logo_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `category` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table structure for undo_log
-- ----------------------------
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log` (
  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',
  `xid` varchar(100) NOT NULL COMMENT 'global transaction id',
  `context` varchar(128) NOT NULL COMMENT 'undo_log context,such as serialization',
  `rollback_info` longblob NOT NULL COMMENT 'rollback info',
  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',
  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',
  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',
  UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='AT transaction mode undo table';

SET FOREIGN_KEY_CHECKS = 1;

microbot-course2数据库

microbot-course2和microbot-course一样，只有数据库名称不同

microbot-order数据库

/*
 Navicat Premium Data Transfer

 Source Server         : ShardingSphere_Master
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : 172.16.213.128:3306
 Source Schema         : microbot-order

 Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001

 Date: 27/11/2021 23:33:32
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_order
-- ----------------------------
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `order_id` bigint(18) NOT NULL,
  `user_id` bigint(18) NOT NULL,
  `course_id` bigint(18) NOT NULL,
  `course_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `course_desc` varchar(18) COLLATE utf8mb4_general_ci NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `logo_url` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `create_time` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `remark` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
  `order_id` bigint(18) NOT NULL,
  `user_id` bigint(18) NOT NULL,
  `course_id` bigint(18) NOT NULL,
  `course_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `course_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `logo_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `create_time` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table structure for t_order_2
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
  `order_id` bigint(18) NOT NULL,
  `user_id` bigint(18) NOT NULL,
  `course_id` bigint(18) NOT NULL,
  `course_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `course_desc` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `logo_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `create_time` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table structure for undo_log
-- ----------------------------
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log` (
  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',
  `xid` varchar(100) NOT NULL COMMENT 'global transaction id',
  `context` varchar(128) NOT NULL COMMENT 'undo_log context,such as serialization',
  `rollback_info` longblob NOT NULL COMMENT 'rollback info',
  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',
  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',
  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',
  UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='AT transaction mode undo table';

SET FOREIGN_KEY_CHECKS = 1;

microbot-course2数据库

microbot-order2和microbot-order一样，只有数据库名称不同

seata数据库

/*
 Navicat Premium Data Transfer

 Source Server         : MySQL_Local
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : localhost:3306
 Source Schema         : seata

 Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001

 Date: 29/11/2021 08:41:49
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for branch_table
-- ----------------------------
DROP TABLE IF EXISTS `branch_table`;
CREATE TABLE `branch_table` (
  `branch_id` bigint(20) NOT NULL,
  `xid` varchar(128) NOT NULL,
  `transaction_id` bigint(20) DEFAULT NULL,
  `resource_group_id` varchar(32) DEFAULT NULL,
  `resource_id` varchar(256) DEFAULT NULL,
  `branch_type` varchar(8) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `client_id` varchar(64) DEFAULT NULL,
  `application_data` varchar(2000) DEFAULT NULL,
  `gmt_create` datetime(6) DEFAULT NULL,
  `gmt_modified` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`branch_id`),
  KEY `idx_xid` (`xid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for global_table
-- ----------------------------
DROP TABLE IF EXISTS `global_table`;
CREATE TABLE `global_table` (
  `xid` varchar(128) NOT NULL,
  `transaction_id` bigint(20) DEFAULT NULL,
  `status` tinyint(4) NOT NULL,
  `application_id` varchar(32) DEFAULT NULL,
  `transaction_service_group` varchar(32) DEFAULT NULL,
  `transaction_name` varchar(128) DEFAULT NULL,
  `timeout` int(11) DEFAULT NULL,
  `begin_time` bigint(20) DEFAULT NULL,
  `application_data` varchar(2000) DEFAULT NULL,
  `gmt_create` datetime DEFAULT NULL,
  `gmt_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`xid`),
  KEY `idx_gmt_modified_status` (`gmt_modified`,`status`),
  KEY `idx_transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for lock_table
-- ----------------------------
DROP TABLE IF EXISTS `lock_table`;
CREATE TABLE `lock_table` (
  `row_key` varchar(128) NOT NULL,
  `xid` varchar(96) DEFAULT NULL,
  `transaction_id` bigint(20) DEFAULT NULL,
  `branch_id` bigint(20) NOT NULL,
  `resource_id` varchar(256) DEFAULT NULL,
  `table_name` varchar(32) DEFAULT NULL,
  `pk` varchar(36) DEFAULT NULL,
  `gmt_create` datetime DEFAULT NULL,
  `gmt_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`row_key`),
  KEY `idx_branch_id` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

songjianghu / MicroBot

MicroBot - 微服务后端开发基础框架

组件概览

版本依赖关系

组件版本关系

注册中心：Nacos

流量防护：Sentinel-Dashboard

链路跟踪：Zipkin

分布式事务：Seata

Seata-Server配置

Seata-Client配置

Nacos配置中心配置

MicroBot-Order-Service服务配置

MicroBot-Video-Service服务配置

MicroBot-User-Service服务配置

MicroBot-Gateway服务配置

MySQL主从复制

MySQL-Master配置

MySQL-Salve配置

数据库中间件：ShardingSphere

Order服务分库分表配置

Order服务分库分表、主从同步、读写分离配置

数据库文件

microbot-course数据库

microbot-course2数据库

microbot-order数据库

microbot-course2数据库

seata数据库

k8s容器化生产部署

性能优化

简介

发行版

贡献者

近期动态

songjianghu / MicroBot .gitee-modal { width: 500px !important; }

MicroBot - 微服务后端开发基础框架

组件概览

版本依赖关系

组件版本关系

注册中心：Nacos

流量防护：Sentinel-Dashboard

链路跟踪：Zipkin

分布式事务：Seata

Seata-Server配置

Seata-Client配置

Nacos配置中心配置

MicroBot-Order-Service服务配置

MicroBot-Video-Service服务配置

MicroBot-User-Service服务配置

MicroBot-Gateway服务配置

MySQL主从复制

MySQL-Master配置

MySQL-Salve配置

数据库中间件：ShardingSphere

Order服务分库分表配置

Order服务分库分表、主从同步、读写分离配置

数据库文件

microbot-course数据库

microbot-course2数据库

microbot-order数据库

microbot-course2数据库

seata数据库

k8s容器化生产部署

性能优化

简介

发行版

贡献者

近期动态

搜索帮助

songjianghu / MicroBot