43. mycat垂直分表

1. 含义

#比如world库中，有city表、country表
将city表放到sh1分片，将country放到sh2分片

2. 备份原来的配置文件

mv schema.xml schema.xml.ha

3. 编写垂直分表配置文件

vim 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" dataNode="sh1">
    <table name="city" dataNode="sh1"/>
    <table name="country" dataNode="sh2"/>
</schema>

    <dataNode name="sh1" dataHost="oldguo1" database="world" />
    <dataNode name="sh2" dataHost="oldguo2" database="world" />

    <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.178.161:3307" user="root" password="123">
        <readHost host="db2" url="192.168.178.161:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.178.162:3307" user="root" password="123">
        <readHost host="db4" url="192.168.178.162:3309" user="root" password="123" />
    </writeHost>
    </dataHost>

    <dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.178.161:3308" user="root" password="123">
        <readHost host="db2" url="192.168.178.161:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.178.162:3308" user="root" password="123">
        <readHost host="db4" url="192.168.178.162:3310" user="root" password="123" />
    </writeHost>
    </dataHost>

</mycat:schema>

4. 处理后端数据

db01:
mysql -S /data/3307/mysql.sock
use world;
#由于有外键，必须要重新建表
create table city_new like city;
insert into city_new select * from city;
#按顺序删
drop table city;
drop table country language;
drop table country;


mysql -S /data/3308/mysql.sock
use world;
create table country_new like country;
insert into country_new select * from country;
drop table city;
drop table country language;
drop table country;

5. 修改配置文件中的表

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
    <table name="city_new" dataNode="sh1"/>
    <table name="country_new" dataNode="sh2"/>
</schema>

6. 重启mycat

mycat restart

7. 登录mycat

mysql -uroot -p123 -h 127.0.0.1 -P 8066

8. 查看表

use TESTDB
show tables;

9. 单独访问表

select * from city_new;
#应该可以看到只能查出100条，sqlMaxLimit参数在此处生效了

---

最后更新: 2022-02-20 08:44:07