Sharding-Proxy实战[新人创作礼]

1、下载Sharding-Proxy

下载 Sharding-Proxy

2、引入依赖

如果后端连接MySQL数据库，需要下载MySQL Connector/J， 或者使用国内的镜像源sohu MySQL Connector/J解压缩后，将mysql-connector-java-5.1.47.jar拷贝到${sharding-proxy}\lib目录。

)

将下载的jar包拷贝到上边下载的Sharding-Proxy lib目录下:

3、规则配置

编辑%SHARDING_PROXY_HOME%\conf\server.yaml。详情请参见配置手册。

Sharding-Proxy使用conf/server.yaml配置注册中心、认证信息以及公用属性。

权限验证: 用于执行登录Sharding Proxy的权限验证。配置用户名、密码、可访问的数据库后，必须使用正确的用户名、密码才可登录Proxy。 authentication: users: root: # 自定义用户名 password: root # 自定义用户名 sharding: # 自定义用户名 password: sharding # 自定义用户名 authorizedSchemas: sharding_db, masterslave_db # 该用户授权可访问的数据库，多个用逗号分隔。缺省将拥有root权限，可访问全部数据库。 认证信息配置： conf/server.yaml

#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
​
######################################################################################################
# 
# If you want to configure orchestration, authorization and proxy properties, please refer to this file.
# 
######################################################################################################
#
#orchestration:
#  orchestration_ds:
#    orchestrationType: registry_center,config_center,distributed_lock_manager
#    instanceType: zookeeper
#    serverLists: localhost:2181
#    namespace: orchestration
#    props:
#      overwrite: false
#      retryIntervalMilliseconds: 500
#      timeToLiveSeconds: 60
#      maxRetries: 3
#      operationTimeoutMilliseconds: 500
#
authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db
props:
#  max.connections.size.per.query: 1
#  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
#  proxy.frontend.flush.threshold: 128  # The default value is 128.
#    # LOCAL: Proxy will run with LOCAL transaction.
#    # XA: Proxy will run with XA transaction.
#    # BASE: Proxy will run with B.A.S.E transaction.
#  proxy.transaction.type: LOCAL
#  proxy.opentracing.enabled: false
#  proxy.hint.enabled: false
#  query.with.cipher.column: true
  sql.show: true
#  allow.range.query.with.inline.sharding: false

编辑%SHARDING_PROXY_HOME%\conf\config-xxx.yaml。详情请参见配置手册。

分库分表配置： conf/config-sharding.yaml

#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
​
######################################################################################################
# 
# Here you can configure the rules for the proxy.
# This example is configuration of sharding rule.
#   
# If you want to use sharding, please refer to this file; 
# if you want to use master-slave, please refer to the config-master_slave.yaml.
# 
######################################################################################################
#
#schemaName: sharding_db
#
#dataSources:
#  ds_0:
#    url: jdbc:postgresql://127.0.0.1:5432/demo_ds_0?serverTimezone=UTC&useSSL=false
#    username: postgres
#    password: postgres
#    connectionTimeoutMilliseconds: 30000
#    idleTimeoutMilliseconds: 60000
#    maxLifetimeMilliseconds: 1800000
#    maxPoolSize: 50
#  ds_1:
#    url: jdbc:postgresql://127.0.0.1:5432/demo_ds_1?serverTimezone=UTC&useSSL=false
#    username: postgres
#    password: postgres
#    connectionTimeoutMilliseconds: 30000
#    idleTimeoutMilliseconds: 60000
#    maxLifetimeMilliseconds: 1800000
#    maxPoolSize: 50
#
#shardingRule:
#  tables:
#    t_order:
#      actualDataNodes: ds_${0..1}.t_order_${0..1}
#      tableStrategy:
#        inline:
#          shardingColumn: order_id
#          algorithmExpression: t_order_${order_id % 2}
#      keyGenerator:
#        type: SNOWFLAKE
#        column: order_id
#    t_order_item:
#      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
#      tableStrategy:
#        inline:
#          shardingColumn: order_id
#          algorithmExpression: t_order_item_${order_id % 2}
#      keyGenerator:
#        type: SNOWFLAKE
#        column: order_item_id
#  bindingTables:
#    - t_order,t_order_item
#  defaultDatabaseStrategy:
#    inline:
#      shardingColumn: user_id
#      algorithmExpression: ds_${user_id % 2}
#  defaultTableStrategy:
#    none:
​
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
​
schemaName: sharding_db
​
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.10.10:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://192.168.10.10:3307/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
#
shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_item_id
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:

读写分离配置： 第一个数据库的读写分离配置： conf/config-master_slave.yaml

######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
​
########################
#
# 第一个数据库的读写分离
# 读从库，写更新删除在主库
# 
########################
​
schemaName: sharding_db_1
#
dataSources:
  master_0_ds:
    url: jdbc:mysql://192.168.10.10:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_0:
    url: jdbc:mysql://192.168.10.10:3317/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
​
masterSlaveRule:
  name: ms_ds
  masterDataSourceName: master_0_ds
  slaveDataSourceNames:
    - slave_ds_0
#    - slave_ds_1
  loadBalanceAlgorithmType: ROUND_ROBIN

第二个数据库的读写分离配置: conf/config-master_slave_2.yaml

全部配置完之后，然后启动服务：

######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
​
########################
#
# 第2个数据库的读写分离
# 读从库，写更新删除在主库
# 
########################
​
schemaName: sharding_db_2
#
dataSources:
  master_1_ds:
    url: jdbc:mysql://192.168.10.10:3307/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_1:
    url: jdbc:mysql://192.168.10.10:3317/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
​
masterSlaveRule:
  name: ms_ds
  masterDataSourceName: master_1_ds
  slaveDataSourceNames:
    - slave_ds_1
#    - slave_ds_1
  loadBalanceAlgorithmType: ROUND_ROBIN

4、修改主从同步配置

1、先停止主库和从库

[root@localhost master]# docker stop  mysql-master mysql-slaver-01
mysql-master
mysql-slaver-01

2、修改主库同步库 vi /mydata/mysql/master/conf/my.cnf

[client]
default-character-set=utf8
​
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
​
#master-slaver repication
server_id=1
log-bin=mysql-bin
read-only=0
binlog-do-db=gulimall_ums
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_oms
binlog-do-db=gulimall_wms
binlog-do-db=gulimall_admin
# new add
binlog-do-db=demo_ds_0
binlog-do-db=demo_ds_1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

3、修改从库同步库 vi /mydata/mysql/slaver/conf/my.cnf

[client]
default-character-set=utf8
​
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
​
#master-slaver repication
server_id=2
log-bin=mysql-bin
read-only=1
binlog-do-db=gulimall_ums
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_oms
binlog-do-db=gulimall_wms
binlog-do-db=gulimall_admin
# new add
binlog-do-db=demo_ds_0
binlog-do-db=demo_ds_1
​
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

4、重启主从库

[root@localhost conf]# docker start mysql-master mysql-slaver-01
mysql-master
mysql-slaver-01

5、创建库 手动在master库创建 demo_ds_0, demo_ds_1 测试库，可以看到我们的从库也同步了刚创建的这两个库。

6、启动sharing-proxy 启动sharing-proxy，并且指定端口为 3388

cd /Users/kaiyiwang/Web/sharding-proxy/bin
➜  bin ./start.sh 3388
Starting the Sharding-Proxy ...
The port is 3388
The classpath is /Users/kaiyiwang/Web/sharding-proxy/conf:.:..:/Users/kaiyiwang/Web/sharding-proxy/lib/*:/Users/kaiyiwang/Web/sharding-proxy/lib/*:/Users/kaiyiwang/Web/sharding-proxy/ext-lib/*
Please check the STDOUT file: /Users/kaiyiwang/Web/sharding-proxy/logs/stdout.log

日志打印：

➜  cat /Users/kaiyiwang/Web/sharding-proxy/logs/stdout.log 
...
[INFO ] 13:02:02.701 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x17cbeb2f] REGISTERED
[INFO ] 13:02:02.703 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x17cbeb2f] BIND: 0.0.0.0/0.0.0.0:3388
[INFO ] 13:02:02.707 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x17cbeb2f, L:/0.0.0.0:3388] ACTIVE

可以看到sharding-proxy 已经启动成功了。 然后本地连接，可以看到连接成功了

在这里我们可以看到三个库，其实是我们在上边配置文件时写的名字，schemaName，以后我们只操作 sharding-db 库。

创建测试表：

CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id))
​
CREATE TABLE IF NOT EXISTS t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (order_item_id))
​
INSERT INTO t_order (user_id, status) VALUES (10, 'INIT')
INSERT INTO t_order_item (order_id, user_id) VALUES (%d, 10) # %d，1-10，可以多执行几次
​
INSERT INTO t_order (user_id, status) VALUES (11, 'INIT')
INSERT INTO t_order_item (order_id, user_id) VALUES (%d, 11)  # %d，1-10，可以多执行几次

我们可以看到，按照订单的用户ID进行分库，再按照订单ID进行分表，sharding-sphere非常强大，不需要修改代码即可完成分库分表操作，并且有对应的逻辑表，非常方便我们整体查询。

小结

sharding-sphere是一天非常强大的分布式数据库中间件解决方法。

有简单易懂的行表达式用于配置数据节点和数据分片算法。

有自己的诸多大杀器，比如强制路由等。

官方文档齐全，实例代码项目case较全，能够在较短时间完成分库分表。