笔记整理-mycat分库分表

1.创建数据#在上次创建数据的基础上做的实验因为有主从同步所以创建数据只在主数据库操作就可以了

CREATE DATABASE grace CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> use grace;
 
CREATE TABLE heihei(
id INT NOT NULL AUTO_INCREMENT,
city varchar(50) NOT NULL,
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

 
mysql> insert into grace.heihei values(1,"beijing");
mysql> show tables;

select * from grace.heihei;


use ha;
CREATE TABLE heihei(
id INT NOT NULL AUTO_INCREMENT,
city varchar(50) NOT NULL,
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into ha.heihei values(1,"shanghai");
select * from ha.heihei;

2.修改配置文件

vi /usr/local/mycat/conf/server.xml#通用不配置

cp /usr/local/mycat/conf/rule.xml /usr/local/mycat/conf/rule.xml.bak#先备份

vi /usr/local/mycat/conf/rule.xml

<mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="mycat-rule"> #分片规则schema.xml要引用
                <rule>
                        <columns>id</columns>#分片的列
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>#分几片这里是2片
    </function>
</mycat:rule>

cp /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml.old#备份上次的实验配置

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="ha" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
        <table name="test" primaryKey="id"  dataNode="dn1" /> #加入的部分
        <table name="heihei" primaryKey="id" dataNode="dn1,grace_db" rule="mycat-rule" />#加入的部分引用了分片规则
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="ha" />
        <dataNode name="grace_db" dataHost="localhost1" database="grace" />#加入的部分添加了新的库节点
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="node2" url="192.168.1.64:3306" user="mycat"
                                   password="123456">
                </writeHost>
                <writeHost host="node1" url="192.168.1.62:3306" user="mycat"
                                   password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>

测试数据

mysql -h 192.168.1.65 -uroot -p -P 8066#登录mycat数据库插入数据

insert into test values(10,"wangbiao");
insert into test values(11,"zhangcaiyi");
insert into heihei(id,city) values(3,"chognqing");
insert into heihei(id,city) values(20,"chognqing");
insert into heihei(id,city) values(22,"xianggang");
insert into heihei(id,city) values(28,"chengdu");
insert into heihei(id,city) values(23,"huoqiu");
insert into heihei(id,city) values(24,"haikou");
insert into heihei(id,city) values(25,"anqing");
insert into heihei(id,city) values(26,"tianqing");
insert into heihei(id,city) values(27,"hangzhou");

insert into heihei(id,city) values(28,"chengdu");#重复值会报错

explain select * from heihei where id=2;#查看插入的节点是哪

在节点数据库查询分库情况

select * from grace.heihei;
select * from ha.heihei;

tail -f /usr/local/mycat/macat.log #操作记录查看

select * from heihei order by id asc;#排序如果不加上排序命令按节点然后依次排序-在mycat上操作

注意：实验中发现开始在mycat插入数据正常但是查询不到，但是数据正常插入备数据主机。主数据主机查不到信息，可能是node数据库host用户名写反 了不能随意写