sharding-proxy demo
【摘要】 部署sharding-proxy和mysql1. 下载sharding-proxy的example的代码， 进入sharding文件夹 (cd docker/sharding-proxy/sharding)[root@ymaster sharding]# lltotal 4drwxr-xr-x 2 root root 53 Nov 29 14:51 conf-rwxr-xr-x 1 ro...
部署sharding-proxy和mysql
1. 下载sharding-proxy的example的代码， 进入sharding文件夹 (cd docker/sharding-proxy/sharding)
[root@ymaster sharding]# ll total 4 drwxr-xr-x 2 root root 53 Nov 29 14:51 conf -rwxr-xr-x 1 root root 1574 Nov 18 17:23 docker-compose.yml
conf文件夹下是数据库分库分表的配置，详细配置见下面章节--分库分表配置介绍
docker-compose.yaml文件是编排mysql和sharding-proxy的例子，docker-compose文件详情见步骤2
2. 执行命令 (docker-compose up -d) 启动mysql和sharding-proxy
version: '3' services: mysql: ## mysql version, you could get more tags at here : https://hub.docker.com/_/mysql?tab=tags image: "mysql:5.7" ## default port is 3306, you could change to 33060 or any other port doesn't conflict MySQL on your OS ports: - "33060:3306" container_name: sharding-sphere-mysql ## launch mysql without password ## you could access the mysql in container by following command : ## docker exec -it sharding-sphere-mysql mysql -uroot environment: - MYSQL_ALLOW_EMPTY_PASSWORD=yes ## if you insist to use password in mysql, remove MYSQL_ALLOW_EMPTY_PASSWORD=yes and uncomment following args # - MYSQL_ROOT_PASSWORD=root volumes: - ../../../src/resources/manual_schema.sql:/docker-entrypoint-initdb.d/manual_schema.sql proxy: ## get more versions of proxy here : https://hub.docker.com/r/shardingsphere/sharding-proxy/tags image: "shardingsphere/sharding-proxy:3.1.0" container_name: sharding-sphere-proxy depends_on: - mysql ## wait-for-it.sh will make proxy entry point wait until mysql container 3306 port open entrypoint: > /bin/sh -c "/opt/wait-for-it.sh sharding-sphere-mysql:3306 --timeout=20 --strict -- && /opt/sharding-proxy/bin/start.sh 3308 && tail -f /opt/sharding-proxy/logs/stdout.log" ports: - "13308:3308" links: - "mysql:mysql" volumes: - ./conf/:/opt/sharding-proxy/conf - ../../tools/wait-for-it.sh:/opt/wait-for-it.sh environment: - JVM_OPTS="-Djava.awt.headless=true"
可见docker-compose.yaml文件启动了两个服务mysql和sharding -proxy ,mysql的启动脚本执行了manual_schema.sql文件初始化数据库，把分库分表都初始化好了。
manual_schema.sql如下：
DROP SCHEMA IF EXISTS demo_ds; DROP SCHEMA IF EXISTS demo_ds_0; DROP SCHEMA IF EXISTS demo_ds_1; DROP SCHEMA IF EXISTS demo_ds_master; DROP SCHEMA IF EXISTS demo_ds_slave_0; DROP SCHEMA IF EXISTS demo_ds_slave_1; DROP SCHEMA IF EXISTS demo_ds_master_0; DROP SCHEMA IF EXISTS demo_ds_master_0_slave_0; DROP SCHEMA IF EXISTS demo_ds_master_0_slave_1; DROP SCHEMA IF EXISTS demo_ds_master_1; DROP SCHEMA IF EXISTS demo_ds_master_1_slave_0; DROP SCHEMA IF EXISTS demo_ds_master_1_slave_1; CREATE SCHEMA IF NOT EXISTS demo_ds; CREATE SCHEMA IF NOT EXISTS demo_ds_0; CREATE SCHEMA IF NOT EXISTS demo_ds_1; CREATE SCHEMA IF NOT EXISTS demo_ds_master; CREATE SCHEMA IF NOT EXISTS demo_ds_slave_0; CREATE SCHEMA IF NOT EXISTS demo_ds_slave_1; CREATE SCHEMA IF NOT EXISTS demo_ds_master_0; CREATE SCHEMA IF NOT EXISTS demo_ds_master_0_slave_0; CREATE SCHEMA IF NOT EXISTS demo_ds_master_0_slave_1; CREATE SCHEMA IF NOT EXISTS demo_ds_master_1; CREATE SCHEMA IF NOT EXISTS demo_ds_master_1_slave_0; CREATE SCHEMA IF NOT EXISTS demo_ds_master_1_slave_1;
等mysql创建好了，直接连接mysql数据库可以看到数据库情况如下：
mysql> show databases; +--------------------------+ | Database | +--------------------------+ | information_schema | | demo_ds | | demo_ds_0 | | demo_ds_1 | | demo_ds_master | | demo_ds_master_0 | | demo_ds_master_0_slave_0 | | demo_ds_master_0_slave_1 | | demo_ds_master_1 | | demo_ds_master_1_slave_0 | | demo_ds_master_1_slave_1 | | demo_ds_slave_0 | | demo_ds_slave_1 | | mysql | | performance_schema | | sys | +--------------------------+ 16 rows in set (0.00 sec)
3. 执行命令连接sharding-proxy, 然后sharding-proxy会连接到mysql
命令 mysql -h<host-ip> -P13308 -proot -uroot
可以看到sharding-proxy连接之后只有sharding-db这一个库，看不到直连mysql查出来的12个库
# mysql -h x.x.x.x -uroot -proot -P13308 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL**. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.0-Sharding-Proxy 3.1.0 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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> show databases; +-------------+ | Database | +-------------+ | sharding_db | +-------------+ 1 row in set (0.00 sec)
4.连接sharding-proxy执行建表，插入数据，查询数据等操作
建表
mysql> CREATE TABLE IF NOT EXISTS demo_ds_0.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id)); Query OK, 0 rows affected (0.70 sec) mysql> CREATE TABLE IF NOT EXISTS demo_ds_1.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id)); Query OK, 0 rows affected (0.70 sec) mysql> CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id)); Query OK, 0 rows affected (0.93 sec) mysql> CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id)); Query OK, 0 rows affected (0.01 sec) mysql> select * from t_order; Empty set (0.02 sec)
插入数据
mysql> INSERT INTO t_order (user_id, status) VALUES (1, 'init'); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO t_order (user_id, status) VALUES (1, 'init'); Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO t_order (user_id, status) VALUES (2, 'init'); Query OK, 1 row affected (0.02 sec)
sharding proxy看到的
mysql> select * from t_order; +--------------------+---------+--------+ | order_id | user_id | status | +--------------------+---------+--------+ | 407052335232057344 | 2 | init | | 407051622900826112 | 1 | init | | 407051650910388225 | 1 | init | +--------------------+---------+--------+ 3 rows in set (0.01 sec)
mysql看到的
mysql> show tables; +---------------------+ | Tables_in_demo_ds_0 | +---------------------+ | t_order_0 | | t_order_1 | | t_order_item_0 | | t_order_item_1 | +---------------------+ 4 rows in set (0.00 sec) mysql> select * from t_order_0; +--------------------+---------+--------+ | order_id | user_id | status | +--------------------+---------+--------+ | 407052335232057344 | 2 | init | +--------------------+---------+--------+ 1 row in set (0.01 sec) mysql> select * from t_order_1; Empty set (0.00 sec) mysql> select * from t_order_item_0; Empty set (0.00 sec) mysql> select * from t_order_item_1; Empty set (0.00 sec) mysql> use demo_ds_1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t_order_0; +--------------------+---------+--------+ | order_id | user_id | status | +--------------------+---------+--------+ | 407051622900826112 | 1 | init | +--------------------+---------+--------+ 1 row in set (0.01 sec) mysql> select * from t_order_1; +--------------------+---------+--------+ | order_id | user_id | status | +--------------------+---------+--------+ | 407051650910388225 | 1 | init | +--------------------+---------+--------+ 1 row in set (0.00 sec)
更多例子可以参考 https://github.com/apache/incubator-shardingsphere-example/tree/dev/sharding-proxy-example
分库分表配置介绍
conf文件夹下有 两个文件
config-sharding.yaml
该文件是定义分片规则的文件
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://sharding-sphere-mysql:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://sharding-sphere-mysql:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password:
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}
keyGeneratorColumnName: 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}
keyGeneratorColumnName: order_item_id
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 2}
defaultTableStrategy:
none:
defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGeneratorschemaName字段: 表示在连接sharding-proxy的时候看到的db的name
mysql> show databases; +-------------+ | Database | +-------------+ | sharding_db | +-------------+
dataSource字段: 表示该schema下真实的库有哪些，并且给出每个db的url\user\pwd等连接方法
shardingRule: 分库分表的规则，上面例子的意思就是根据userid分库，根据orderid分表。 userid是双数，数据插入到ds0库中，userid是单数则数据插入到ds1库中。然后根据orderid确定写哪个表，orderid是单数，写到torder0表中，orderid是双数，写到torder_1表中
server.yaml
authentication: username: root password: root 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 sql.show: false
改配置主要是注册中心、认证信息以及公用属性等信息
更多其他配置可以参考官方文档： https://shardingsphere.apache.org/document/current/cn/manual/sharding-proxy/configuration/
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）