sharding-proxy demo

部署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文件夾下是數(shù)據(jù)庫分庫分表的配置，詳細(xì)配置見下面章節(jié)--分庫分表配置介紹

docker-compose.yaml文件是編排mysql和sharding-proxy的例子，docker-compose文件詳情見步驟2

2. 執(zhí)行命令 (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文件啟動了兩個服務(wù)mysql和sharding -proxy ,mysql的啟動腳本執(zhí)行了manual_schema.sql文件初始化數(shù)據(jù)庫，把分庫分表都初始化好了。

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創(chuàng)建好了，直接連接mysql數(shù)據(jù)庫可以看到數(shù)據(jù)庫情況如下：

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. 執(zhí)行命令連接sharding-proxy, 然后sharding-proxy會連接到mysql

命令 ?mysql -h -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執(zhí)行建表，插入數(shù)據(jù)，查詢數(shù)據(jù)等操作

建表

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)

插入數(shù)據(jù)

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

該文件是定義分片規(guī)則的文件

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.DefaultKeyGenerator

schemaName字段:? ?表示在連接sharding-proxy的時候看到的db的name

mysql>?show?databases; +-------------+ |?Database????| +-------------+ |?sharding_db?| +-------------+

dataSource字段:? ?表示該schema下真實的庫有哪些，并且給出每個db的url\user\pwd等連接方法

shardingRule:? ? ? 分庫分表的規(guī)則，上面例子的意思就是根據(jù)userid分庫，根據(jù)orderid分表。 userid是雙數(shù)，數(shù)據(jù)插入到ds0庫中，userid是單數(shù)則數(shù)據(jù)插入到ds1庫中。然后根據(jù)orderid確定寫哪個表，orderid是單數(shù)，寫到torder0表中，orderid是雙數(shù)，寫到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

改配置主要是注冊中心、認(rèn)證信息以及公用屬性等信息

更多其他配置可以參考官方文檔：?https://shardingsphere.apache.org/document/current/cn/manual/sharding-proxy/configuration/

MySQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。