需要你准备四台mysql虚拟机,俩主俩从,以便测试。
本篇mysql服务地址
主1
:192.168.247.140:3000
主2
:192.168.247.140:3001
从1
:192.168.247.140:3002
从2
:192.168.247.140:3003
此时假设你的主1和从1与主2和从2已经配置好主从复制。好吧,就是需要你自己准备好,这里不赘述。你可能有为什么需要自己去设置的疑惑,因为mycat不负责主从复制,主从复制由mysql自主完成。
我这里在所有库都建了test表,里面有id和name字段。
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
打开server.xml,查看登录名和密码。
修改schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="test" primaryKey="id" dataNode="dn1,dn2" rule="auto-sharding-rang-mod" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost2" database="db2" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.247.140:3000" user="root" password="password">
<readHost host="hostS1" url="192.168.247.140:3002" user="root" password="password" />
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.247.140:3001" user="root" password="password">
<readHost host="hostS2" url="192.168.247.140:3003" user="root" password="password" />
</writeHost>
</dataHost>
</mycat:schema>
balance="3"
,所有的读操作都只发送到writeHost
里的readHost
上。
readHost
包在writeHost
里表示是其的从库。
命名的M1与S1,表示主(master)和从(Slave)。
修改rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>
<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
<property name="mapFile">partition-range-mod.txt</property>
<property name="defaultNode">0</property>
</function>
</mycat:rule>
采用范围取模分片。
修改partition-range-mod.txt
# range start-end ,data node group size
0-1M=1
1M1-2M=1
范围取模分片配置,表id字段0到10000进节点1,10001-20000进节点2。
启动或重启mycat
# ./mycat start
# ./mycat restart
登录mycat
# mysql -h 192.168.247.140 -uroot -p123456 -P8066
先测试主从有没有成功,mycat逻辑库为TESTDB
mysql> use TESTDB
mysql> INSERT INTO test (id, name)VALUES ('1', 'weikaixxxxxx');
mysql> INSERT INTO test (id, name)VALUES ('10001', 'weikaixxxxxx');
是成功的。这里因为分片的关系,所以id 1进主1了,id 10001进主2了。
查询
mysql> show variables like 'hostname';
mysql> SELECT * FROM test WHERE name like '%weikai%' limit 1;
然鹅,上面的方案是当主挂了,从就不能查询了。所以,下面的方案,是当主挂了,从还能查询。
修改schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="test" primaryKey="id" dataNode="dn1,dn2" rule="auto-sharding-rang-mod" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost2" database="db2" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.247.140:3000" user="root" password="password" />
<writeHost host="hostS1" url="192.168.247.140:3002" user="root" password="password" />
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.247.140:3001" user="root" password="password" />
<writeHost host="hostS2" url="192.168.247.140:3003" user="root" password="password" />
</dataHost>
</mycat:schema>
balance="1"
,全部的readHost 与stand by writeHost 参与select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2 都参与select 语句的负载均衡。
添加操作拒绝连接,查询可以。
通过测试,查询语句不会走主节点