03 水平分库，思想4 id主键范围 一致性hash

改my.cnf

     

图解：备库read_only=1 这个参数没改，存储过程参数本来就有，改了：lower_case_table_names=1 不区分大小写，和autocommit=1 自动提交

配置文件 添加lower_case_table_names=1  ，不管导入的是大写的还是小写的数据库或者表，数据库和表全部小写，此时你不管用大写还是小写，都是一个库，不做区分

导表的话，不提交，重启后，数据全部丢失

 

lower_case_table_names=1

autocommit = 1

log_bin_trust_function_creators = 1

 

 

 

 

1.  id分库

数据不是平均分配

 

 

192.168.0.51 itpuxdb1:

create database itpuxdb01 default character set utf8;

create database itpuxdb02 default character set utf8;

create database itpuxdb03 default character set utf8;

 

192.168.0.52    itpuxdb2:

 

create database itpuxdb13 default character set utf8;

create database itpuxdb14 default character set utf8;

create database itpuxdb15 default character set utf8;

 

192.168.0.53    itpuxdb3:

 

create database itpuxdb25 default character set utf8;

create database itpuxdb26 default character set utf8;

create database itpuxdb27 default character set utf8;

 

vi schema.xml

 

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

 

<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100">

<table    name="itpux_member" dataNode="dnitpuxdb$1-3,dnitpuxdb$13-15,dnitpuxdb$25-27" rule="auto-sharding-long"></table>

</schema>

 

 

 

<!-- 设定数据结点 dnitpuxdb1-dnitpuxdb12 对应的 192.168.0.51 服务 以及对应的物理 schema    -->

<dataNode name="dnitpuxdb1" dataHost="192.168.0.51" database="itpuxdb01"/>

<dataNode name="dnitpuxdb2" dataHost="192.168.0.51" database="itpuxdb02"/>

<dataNode name="dnitpuxdb3" dataHost="192.168.0.51" database="itpuxdb03"/>

 

<!-- 设定数据结点 dnitpuxdb13-dnitpuxdb24 对应的 192.168.0.52 服务 以及对应的物理 schema    -->

<dataNode    name="dnitpuxdb13"    dataHost="192.168.0.52" database="itpuxdb13" />

<dataNode    name="dnitpuxdb14"    dataHost="192.168.0.52" database="itpuxdb14" />

<dataNode    name="dnitpuxdb15"    dataHost="192.168.0.52" database="itpuxdb15" />

 

<!-- 设定数据结点 dnitpuxdb25-dnitpuxdb35 对应的 192.168.0.53 服务 以及对应的物理 schema    -->

<dataNode    name="dnitpuxdb25"    dataHost="192.168.0.53" database="itpuxdb25" />

<dataNode    name="dnitpuxdb26"    dataHost="192.168.0.53" database="itpuxdb26" />

<dataNode    name="dnitpuxdb27"    dataHost="192.168.0.53" database="itpuxdb27" />

 

<dataHost name="192.168.0.51" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost  host="192.168.0.51"  url="192.168.0.51:3306"  user="root" password="root" />

</dataHost>

 

<dataHost name="192.168.0.52" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost  host="192.168.0.52"  url="192.168.0.52:3306"  user="root" password="root" />

</dataHost>

 

<dataHost name="192.168.0.53" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost  host="192.168.0.53"  url="192.168.0.53:3306"  user="root" password="root" />

</dataHost>

 

</mycat:schema>

 

 

 

vi rule.xml

<!DOCTYPE mycat:rule SYSTEM "rule.dtd">

<mycat:rule xmlns:mycat="http://io.mycat/">

 

<tableRule name="auto-sharding-long">

<rule>

<columns>id</columns>

<algorithm>rang-long</algorithm>

</rule>

</tableRule>

 

<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">

<property name="mapFile">autopartition-long-itpux_member.txt</property>

<property name="defaultNode">0</property>

</function>

 

</mycat:rule>

 

 

 

 

vi autopartition-long-itpux_member.txt

20000000-20005000=0

20005001-20010000=1

20010001-20015000=2

20015001-20020000=3

20020001-20025000=4

20025001-20030000=5

20030001-20035000=6

20035001-20040000=7

20040001-20050000=8

 

 

修改  server.xml

<user name="root">

    <property name="password">root</property>

    <property name="schemas">mycatdb</property>

</user>

 

<user name="user">

    <property name="password">user</property>

    <property name="schemas">mycatdb</property>

    <property name="readOnly">true</property>

</user> 

 

[root@itpuxdb1 soft]# mysql -uroot -proot -h192.168.0.71 -P8066 mycatdb< itpux_member_5w.sql

[root@itpuxm01 ~]# mysql -uroot -proot -P8066 -h192.168.0.71 -Dmycatdb

 

 

mysql -uroot -proot -h192.168.0.51

select count(*) from itpuxdb01.itpux_member;

select count(*) from itpuxdb02.itpux_member;

select count(*) from itpuxdb03.itpux_member;

exit;

 

mysql -uroot -proot -h192.168.0.52

select count(*) from itpuxdb13.itpux_member;

select count(*) from itpuxdb14.itpux_member;

select count(*) from itpuxdb15.itpux_member;

exit;

 

mysql -uroot -proot -h192.168.0.53

select count(*) from itpuxdb25.itpux_member;

select count(*) from itpuxdb26.itpux_member;

select count(*) from itpuxdb27.itpux_member;

 exit;

 

 

 

 

mysql -uroot -proot -h192.168.0.71 -P8066 mycatdb

truncate table itpux_member;

drop table itpux_member;

 

 

 

 

1. 一致性hash

 

 

一致性 hash：sharding-by-murmur

优点：可以实现横向扩展数据库  ，现在是9个库，扩展到12个库或者更多的话，只需要改变配置文件，数据直接打散分配到12个库中

数据不是平均分配

 

192.168.0.51 itpuxdb1:

mysql -uroot -proot

create database itpuxdb01 default character set utf8;

create database itpuxdb02 default character set utf8;

create database itpuxdb03 default character set utf8;

 

192.168.0.52itpuxdb2:

mysql -uroot -proot

create database itpuxdb13 default character set utf8;

create database itpuxdb14 default character set utf8;

create database itpuxdb15 default character set utf8;

 

192.168.0.53itpuxdb3:

mysql -uroot -proot

create database itpuxdb25 default character set utf8;

create database itpuxdb26 default character set utf8;

create database itpuxdb27 default character set utf8;

 

vi schema.xml

 

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

 

<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100">

<table    name="itpux_member" dataNode="dnitpuxdb$1-3,dnitpuxdb$13-15,dnitpuxdb$25-27" rule="sharding-by-murmur-id"></table>

</schema>

 

<!-- 设定数据结点 dnitpuxdb1-dnitpuxdb12 对应的 192.168.0.51 服务 以及对应的物理 schema    -->

<dataNode name="dnitpuxdb1" dataHost="192.168.0.51" database="itpuxdb01"

/>

<dataNode name="dnitpuxdb2" dataHost="192.168.0.51" database="itpuxdb02"

/>

<dataNode name="dnitpuxdb3" dataHost="192.168.0.51" database="itpuxdb03"

/>

 

<!-- 设定数据结点 dnitpuxdb13-dnitpuxdb24 对应的 192.168.0.52 服务 以及对应的物理 schema    -->

<dataNode    name="dnitpuxdb13"    dataHost="192.168.0.52" database="itpuxdb13" />

<dataNode    name="dnitpuxdb14"    dataHost="192.168.0.52" database="itpuxdb14" />

<dataNode    name="dnitpuxdb15"    dataHost="192.168.0.52" database="itpuxdb15" />

 

<!-- 设定数据结点 dnitpuxdb25-dnitpuxdb35 对应的 192.168.0.53 服务 以及对应的物理 schema    -->

<dataNode    name="dnitpuxdb25"    dataHost="192.168.0.53" database="itpuxdb25" />

<dataNode    name="dnitpuxdb26"    dataHost="192.168.0.53" database="itpuxdb26" />

<dataNode    name="dnitpuxdb27"    dataHost="192.168.0.53" database="itpuxdb27" />

 

 

 

<dataHost name="192.168.0.51" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost  host="192.168.0.51"  url="192.168.0.51:3306"  user="root" password="root" />

</dataHost>

 

<dataHost name="192.168.0.52" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost  host="192.168.0.52"  url="192.168.0.52:3306"  user="root" password="root" />

</dataHost>

 

<dataHost name="192.168.0.53" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost  host="192.168.0.53"  url="192.168.0.53:3306"  user="root" password="root" />

</dataHost>

 

</mycat:schema>

 

 

[root@itpuxm01 conf]# vi rule.xml

<!DOCTYPE mycat:rule SYSTEM "rule.dtd">

<mycat:rule xmlns:mycat="http://io.mycat/">

 

<tableRule name="sharding-by-murmur-id">

<rule>

<columns>id</columns>

<algorithm>murmur-id</algorithm>

</rule>

</tableRule>

 

<function    name="murmur-id"

class="io.mycat.route.function.PartitionByMurmurHash">

<property name="seed">0</property><!-- 默认是 0 -->

<property name="type">0</property><!-- 默认是 0, 表示 integer,非 0 表示 string-->

<property name="count">9</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->

<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是 160 倍，也就是虚拟节点数是物理节点数的 160 倍 -->

<!--    <propertyname="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是 1。以 properties 文件的格式填写，以从 0 开始到 count-1 的整数值也就是节点索引为 key，以节点权重值为值。>所有权重值必须是正整数，否则以 1 代替 -->

<property name="bucketMapPath">/mysql/app/mycat/logs/bucketMapPath-murmur-id</pr operty>

<!-- 用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的 murmur hash 值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->

</function>

 

</mycat:rule>

 

[root@itpuxdb1 soft]# mysql -uroot -proot -h192.168.0.71 -P8066 mycatdb< itpux_member.sql

 

[root@itpuxm01 ~]# mysql -uroot -proot -P8066 -h192.168.0.71 -Dmycatdb

mysql -uroot -proot -h192.168.0.51

select count(*) from itpuxdb01.itpux_member;

select count(*) from itpuxdb02.itpux_member;

select count(*) from itpuxdb03.itpux_member; exit;

 

mysql -uroot -proot -h192.168.0.52

select count(*) from itpuxdb13.itpux_member;

select count(*) from itpuxdb14.itpux_member;

select count(*) from itpuxdb15.itpux_member; exit;

 

mysql -uroot -proot -h192.168.0.53

select count(*) from itpuxdb25.itpux_member;

select count(*) from itpuxdb26.itpux_member;

select count(*) from itpuxdb27.itpux_member; exit;

 

truncate table itpux_member;

drop table itpux_member;