今日内容
mycat的测试案例
1.高可用故障转移
重新上传修改过的schema.xml【删除原schema.xml】
实现了故障转移,重启的mysql不能更新另一个mysql的数据,另一个mysql可以写也可以读
给两个连接新建表格
添加第二个dataNode dn2
<dataNode name="dn2" dataHost="localhost2" database="mstest" />
绑定第二个dataHost
dataHost添加一个localhost2
修改schema.xml
原schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="mstest" checkSQLschema="true" sqlMaxLimit="100">
<table name="user" primaryKey="ID" dataNode="dn1"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="mstest" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.129:3306" user="root"
password="root">
</writeHost>
<writeHost host="hostM2" url="192.168.253.130:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
新的schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="mstest" checkSQLschema="true" sqlMaxLimit="100">
<table name="user" primaryKey="ID" dataNode="dn1"/>
<table name="student" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="mstest" />
<dataNode name="dn2" dataHost="localhost2" database="mstest" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.129:3306" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.130:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="mstest" checkSQLschema="true" sqlMaxLimit="100">
<table name="user" primaryKey="ID" dataNode="dn1"/>
<table name="student" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-murmur"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="mstest" />
<dataNode name="dn2" dataHost="localhost2" database="mstest" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.129:3306" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.130:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
SELECT * from t_cart;
SELECT * from t_product;
SELECT * from t_product LEFT JOIN t_cart ON t_cart.id=t_product.c_id;
use mstest;
CREATE TABLE `order_test` (
`id` int(11) NOT NULL,
`receiver` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `order_item_test` (
`id` int(11) NOT NULL,
`title` varchar(100) DEFAULT NULL,
`o_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
schema.xml
<table name="order_test" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long">
<childTable name="order_item_test" primaryKey="ID" joinKey="o_id" parentKey="id"/>
</table>
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="mstest" checkSQLschema="true" sqlMaxLimit="100">
<table name="user" primaryKey="ID" dataNode="dn1"/>
<table name="student" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long"/>
<table name="t_cart" primaryKey="ID" dataNode="dn1"/>
<table name="t_product" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long"/>
<table name="order_test" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long">
<childTable name="order_item_test" primaryKey="ID" joinKey="o_id" parentKey="id"/>
</table>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="mstest" />
<dataNode name="dn2" dataHost="localhost2" database="mstest" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.129:3306" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.130:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
mycat构建一个订单系统需要的逻辑库和表
1.订单表格结构
order_item
2.mycat中schema配置对应的逻辑表
schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="mstest" checkSQLschema="true" sqlMaxLimit="100">
<table name="user" primaryKey="ID" dataNode="dn1"/>
<table name="student" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long"/>
<table name="t_cart" primaryKey="ID" dataNode="dn1"/>
<table name="t_product" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long"/>
<table name="order_test" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long">
<childTable name="order_item_test" primaryKey="ID" joinKey="o_id" parentKey="id"/>
</table>
<table name="t_order" primaryKey="order_id" dataNode="dn1,dn2" rule="easymall-order">
<childTable name="t_order_item" primaryKey="ID" joinKey="order_id" parentKey="order_id"/>
</table>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="mstest" />
<dataNode name="dn2" dataHost="localhost2" database="mstest" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.129:3306" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.253.130:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
rule.xml【如果不设置easy_order将使用原来的murmur,默认是id,不是order_id】
<tableRule name="easymall-order">
<rule>
<columns>order_id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
在mycat客户端中使用语句执行【填表可能会出错----编码问题】