Apache ShardingSphere-Proxy使用

* 测试环境已安装jdk 

yum install java

一、创建目录

mkdir -p /software/mysql1/logs
mkdir -p /software/mysql1/data
mkdir -p /software/mysql1/conf
mkdir -p /sofrware/mysql1/mysql-files

mkdir -p /software/mysql2/logs
mkdir -p /software/mysql2/data
mkdir -p /software/mysql2/conf
mkdir -p /sofrware/mysql2/mysql-files

二、使用docker创建2个服务

 docker run -p 13301:3306 --name mysql1 
  -v /software/mysql1/conf:/etc/mysql 
  -v /software/mysql1/logs:/var/log/mysql 
  -v /software/mysql1/data:/var/lib/mysql 
  -v /software/mysql1/mysql-files:/var/lib/mysql-files 
  -e MYSQL_ROOT_PASSWORD=123456 
   -d mysql 
  --lower_case_table_names=1 


  docker run -p 13302:3306 --name mysql2 
  -v /software/mysql2/conf:/etc/mysql 
  -v /software/mysql2/logs:/var/log/mysql 
  -v /software/mysql2/data:/var/lib/mysql 
  -v /software/mysql2/mysql-files:/var/lib/mysql-files 
  -e MYSQL_ROOT_PASSWORD=123456 
  -d mysql 
  --lower_case_table_names=1

查看mysql端口

[root@test]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql1
172.17.0.2
[root@test]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql2
172.17.0.3

 2个节点分别创建数据库demo_ds_0、demo_ds_1

三、下载mysql驱动

wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar

四、下载Apache ShardingSphere

wget https://downloads.apache.org/shardingsphere/5.0.0-alpha/apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin.tar.gz

 解压

tar zxvf  apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin.tar.gz

重命名文件夹

mv apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin shardingsphere-proxy

 移动mysql驱动到 lib目录下

mv /software/mysql-connector-java-5.1.47.jar /software/shardingsphere-proxy/lib/

修改配置文件 路径 /software/shardingsphere-proxy/lib/conf

server.yaml

authentication:
  users:
    root:
      password: root
    sharding:
      password: 12345 
      authorizedSchemas: ds

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
  check-table-metadata-enabled: false

config-sharding.yaml

schemaName: ds

dataSources:
   ds_0:
     url: jdbc:mysql://172.17.0.2:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
     username: root
     password: 123456
     connectionTimeoutMilliseconds: 30000
     idleTimeoutMilliseconds: 60000
     maxLifetimeMilliseconds: 1800000
     maxPoolSize: 50
     minPoolSize: 1
     maintenanceIntervalMilliseconds: 30000
   ds_1:
     url: jdbc:mysql://172.17.0.3:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
     username: root
     password: 123456
     connectionTimeoutMilliseconds: 30000
     idleTimeoutMilliseconds: 60000
     maxLifetimeMilliseconds: 1800000
     maxPoolSize: 50
     minPoolSize: 1
     maintenanceIntervalMilliseconds: 30000
     
rules:
- !SHARDING
  tables: 
     ds_table:
        actualDataNodes: ds_${0..1}.ds_table_${0..1}
        databaseStrategy:   
           standard:
              shardingColumn: user_id
              shardingAlgorithmName: database_inline
        tableStrategy:  
           standard:
              shardingColumn: order_id
              shardingAlgorithmName: table_inline
  shardingAlgorithms:       
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    table_inline:
      type: INLINE
      props:
        algorithm-expression: ds_table_${order_id % 2}

五、启动，使用13333端口

sh ./shardingsphere-proxy/bin/start.sh 13333

 六、登陆

 

 七、创建一张表

CREATE TABLE `NewTable` (
`user_id`  varchar(32) NULL ,
`order_id`  varchar(32) NULL ,
`number`  int(20) NULL ,
`updatetime`  timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP 
);

虚拟主库

 注：newtable、t1111 两张表未设置分表分库

 实际数据

 在虚拟库上执行，创建索引，4个实体表会自动创建如下索引

ALTER TABLE `ds_table`
ADD INDEX `user_id_index` (`user_id`) ,
ADD INDEX `order_id_index` (`order_id`) ;

 

 插入数据

INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (1, 1, 3);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (2, 2, 4);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (3, 3, 5);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (4, 1, 6);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (5, 2, 7);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (6, 3, 8);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (7, 1, 9);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (8, 2, 10);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (9, 3, 11);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (10, 1, 12);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (11, 2, 11);
INSERT INTO `ds_table` (`order_id`, `user_id`, `number`) VALUES (12, 3, 12);

 

完