dble分库分表:
1、修改三个配置文件server.xml schema.xml rule.xml
配置文件位置:/usr/local/dble/conf
1)schema.xml :
<?xml version="1.0"?>
<!DOCTYPE dble:schema SYSTEM "schema.dtd">
<dble:schema xmlns:dble="http://dble.cloud/" version="3.0">
<schema name="z_test">
<table name="z_test1,z_test3" dataNode="dn$0-7" rule="sharding-by-hash1"/>
<table name="z_test2" dataNode="dn8"/>
</schema>
<dataNode name="dn$0-3" dataHost="dataHost1" database="db_hbhs_mbs_$0-3"/>
<dataNode name="dn$4-7" dataHost="dataHost2" database="db_hbhs_mbs_$4-7"/>
<dataNode name="dn8" dataHost="dataHost2" database="db_hbhs_mbs_8"/>
<dataHost name="dataHost1" maxCon="1000" minCon="10" balance="0" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="***.***.***.***:3322" user="dble" password="123456">
</writeHost>
</dataHost>
<dataHost name="dataHost2" maxCon="1000" minCon="10" balance="0" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="***.***.***.***:3323" user="dble" password="123456">
</writeHost>
</dataHost>
</dble:schema>
2)rule.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dble:rule SYSTEM "rule.dtd">
<dble:rule xmlns:dble="http://dble.cloud/">
<tableRule name="sharding-by-hash1">
<rule>
<columns>psn_no</columns>
<algorithm>hashString1</algorithm>
</rule>
</tableRule>
<function name="hashString1" class="StringHash">
<property name="partitionCount">8</property>
<property name="partitionLength">256</property>
<property name="hashSlice">0:20</property>
</function>
</dble:rule>
3)server.xml:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:server SYSTEM "server.dtd">
<dble:server xmlns:dble="http://dble.cloud/">
<system>
<property name="processors">10</property>
<property name="backendProcessors">10</property>
<property name="processorExecutor">8</property>
<property name="backendProcessorExecutor">6</property>
<property name="sqlExecuteTimeout">3000000</property>
<property name="enableSlowLog">1</property>
<property name="slowLogBaseDir">/usr/local/dble/logs/slow.logs</property>
<property name="slowLogBaseName">slow-query</property>
<property name="flushSlowLogPeriod">1</property>
<property name="flushSlowLogSize">1000</property>
<property name="sqlSlowTime">1000</property>
<property name="txIsolation">2</property>
</system>
<user name="z_test">
<property name="password">123456</property>
<property name="schemas">z_test</property>
</user>
<user name="man">
<property name="password">123456</property>
<property name="manager">true</property>
</user>
</dble:server>
2、在各节点上新建库,在240服务器上,根据schema配置文件内容建库配置文件对应如下:
1)查看已有的数据库:
show databases;
2)创建新的数据库:
<writeHost host="hostM1" url="***.***.***.***:3322" 上新建库database="db_hbhs_mbs_$0-3"
<writeHost host="hostM2" url="***.***.***.***:3323"上新建库 database="db_hbhs_mbs_$4-8")
在mysql服务器240上,以mysql -uroot -p -S /usr/local/mysql/tmp/mysql3322.sock 登录,新建db_hbhs_mbs_0,db_hbhs_mbs_1,db_hbhs_mbs_2,db_hbhs_mbs_3
eg:create database db_hbhs_mbs_0;
在mysql服务器240上,以mysql -uroot -p -S /usr/local/mysql/tmp/mysql3323.sock 登录,新建db_hbhs_mbs_4,db_hbhs_mbs_5,db_hbhs_mbs_6,db_hbhs_mbs_7,db_hbhs_mbs_8
eg:create database db_hbhs_mbs_4;
3、重启dble :
到/usr/local/dble/bin路径下(241服务器上)执行:
./dble start
重启
./dble restart
查看 vim /usr/local/dble/logs/wrapper.log 的启动日志
tail -n 100 wrapper.log
4、启动后使用dble连接:
mysql -u用户名 -p密码 -P端口 -h ip,
eg:[root@jmenv bin]# /usr/local/mysql/bin/mysql -u z_test -p -P 8066 -h ***.***.***.xxx
进入之后可以看到schmea.xml 配置文件中的 <schema name="z_test">对应z_test数据库,
进入数据库,新建表(只能建schema.xml中配置的表名,表字段必须有rule.xml里配置的<columns>psn_no</columns>字段)
登录之后,也已使用建表语句进行表创建
6、添加表名修改配置文件后可用命令(reload @@config;)刷新 或重启dble使之生效。
#############补充说明################3
server.xml 中
<user name="z_test">
<property name="password">123456</property>
<property name="schemas">z_test</property>
</user>
是配置连接dble指定库的用户名和密码,可以将多个用户写到root里
dble分库分表, 注意wrapper需要替换
文档放在data目录下
9066 管理端
8066 用户端