ShardingProxy：简介与搭建

简介

定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。目前提供 MySQL 和 PostgreSQL 版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端 (如：MySQL Command Client, MySQL Workbench, Navicat 等) 操作数据，对 DBA 更加友好。

向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用。
适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。

环境搭建

官网下载地址：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/downloads/

我这里下载的是apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin

我这里把压缩包解压到/opt/apache-shardingsphere下面

把mysql的连接驱动放到/opt/apache-shardingsphere/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/lib/下面

linux环境前提必须要现有jdk环境。

此外，修改lib目录下所有jar包名为.jar结尾。

配置

cat server.yml

authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db
props:
  executor.size: 16  # Infinite by default.
  sql.show: true

配置分库分表：

vi config-sharding.yml

schemaName: sharding_db
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.36:3307/demo_ds_0?serverTimezone=GMT%2B8&useSSL=false
    username: root
    password: 1234
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://192.168.1.37:3307/demo_ds_1?serverTimezone=GMT%2B8&useSSL=false
    username: root
    password: 1234
    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:

配置读写分离：

vi config-master_slave.yml

schemaName: sharding_db_1
dataSources:
  master_0_ds:
    url: jdbc:mysql://192.168.1.36:3307/demo_ds_0?serverTimezone=GMT%2B8&useSSL=false
    username: root
    password: 1234
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_0:
    url: jdbc:mysql://192.168.1.36:3317/demo_ds_0?serverTimezone=GMT%2B8&useSSL=false
    username: root
    password: 1234
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
masterSlaveRule:
  name: ms_ds
  masterDataSourceName: master_0_ds
  slaveDataSourceNames:
    - slave_ds_0

vi config-master_slave_2.yml

schemaName: sharding_db_2
dataSources:
  master_1_ds:
    url: jdbc:mysql://192.168.1.36:3307/demo_ds_1?serverTimezone=GMT%2B8&useSSL=false
    username: root
    password: 1234
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_1:
    url: jdbc:mysql://192.168.1.36:3317/demo_ds_1?serverTimezone=GMT%2B8&useSSL=false
    username: root
    password: 1234
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
masterSlaveRule:
  name: ms_ds_1
  masterDataSourceName: master_1_ds
  slaveDataSourceNames:
    - slave_ds_1

mysql主从策略配置文件中需要配置上demo_ds_0和demo_ds_1数据库。

MySQL创建demo_ds_0和demo_ds_1数据库

启动ShardingProxy:

#指定3388端口启动
sh bin/start.sh 3388

观察日志输出

tail -200f /opt/apache-shardingsphere/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/logs/stdout.log

测试连接

使用Sqlyog连接sharding proxy(这里不知道为啥navicat显示有问题)

账号密码都是root，端口3388

后面操作数据库，我们只需要操作sharding proxy暴露出来的sharding_db数据库就行了。

在sharding_db数据库中创建t_order和t_order_item表

CREATE TABLE t_order (
ORDER_id BIGINT(20) NOT NULL AUTO_INCREMENT,
user_id INT(11) NOT NULL,
STATUS VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (order_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
 
CREATE TABLE t_order_item(
  order_item_id BIGINT(20) NOT NULL,
  order_id BIGINT(20) NOT NULL,
  user_id INT(11) NOT NULL,
  content VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
  STATUS VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (order_item_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

创建完成后：

sharding_db_1和sharding_db_2自动创建一些表

测试插入：

INSERT INTO t_order (user_id, STATUS) VALUES(1,1)
INSERT INTO t_order (user_id, STATUS) VALUES(2,1)
INSERT INTO t_order (user_id, STATUS) VALUES(2,2)

我们再去真实的数据库看，看看数据落到哪个库的哪个表中：