四、mycat垂直分库
系列导航
一、实验环境
垂直分库
实验环境 服务内容 dataNode
192.168.0.1 mycat dn162
192.168.0.2 mysql dn163
192.168.0.3 mysql dn164
二、实验内容
1、数据库环境及用户名密码见mycat实验数据
2、配置 schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="demo_db" checkSQLschema="false" sqlMaxLimit="100">
<table name="order_master" primaryKey="order_id" dataNode="dn163" />
<table name="customer_inf" primaryKey="customer_inf_id" dataNode="dn164" />
</schema>
<dataNode name="dn163" dataHost="mysql163" database="order_db" />
<dataNode name="dn164" dataHost="mysql164" database="customer_db" />
<dataHost name="mysql163" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="192.168.0.2" url="192.168.0.2:3306" user="im_mycat" password="123456">
</writeHost>
</dataHost>
<dataHost name="mysql164" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="192.168.0.3" url="192.168.0.3:3306" user="im_mycat" password="123456">
</writeHost>
</dataHost>
</mycat:schema>
3、配置service.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
<property name="bindIp">0.0.0.0</property>
<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
<property name="charset">utf8</property>
<property name="txIolation">2</property>
<property name="processors">8</property>
<property name="idleTimeout">300000</property>
<property name="sqlExecuteTimeout">300</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
<property name="defaultMaxLimit">100</property>
<property name="maxPacketSize">104857600</property>
</system>
<user name="mycat_user" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">demo_db</property>
</user>
</mycat:server>
4、启动mycat看是否正确
[root@zhu mysql]# su mycat
[mycat@zhu mysql]$ mycat start
Starting Mycat-server...
192.168.0.1上使用root和mycat用户都可以
>mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456
mysql> show databases;
+----------+
| DATABASE |
+----------+
| demo_db |
+----------+
mysql> use demo_db
Database changed
mysql> show tables;
+-------------------+
| Tables in demo_db |
+-------------------+
| customer_inf |
| order_master |
+-------------------+
插入数据
先清空192.168.0.2上order_master，192.168.0.3上customer_inf表的数据；
在192.168.0.1上插入数据
insert into order_master(order_id,order_sn,customer_id,shipping_user)values(1,'20190845125425', 1 ,'张三');
insert into order_master(order_id,order_sn,customer_id,shipping_user)values(2,'20190845125426', 2 ,'李四');
commit;
insert into customer_inf(customer_inf_id ,customer_id,customer_name)values('3','5','王五');
insert into customer_inf(customer_inf_id ,customer_id,customer_name)values('4','6','杨六');
commit;
插入的数据在192.168.0.1上都可以看到
order_master的数据在163上可以看到
customer_inf的数据在164上可以看到
说明垂直分库成功
5、配置全局表
之前region_info表在192.168.0.2，192.168.0.3上已经建立好了
配置 schema.xml
添加
<table name="region_info" primaryKey="region_id" dataNode="dn163,dn164" type="global" />
重启mycat
mycat stop
mycat start
192.168.0.1上登录mycat 插入数据
[mycat@zhu ~]$ mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456
mysql> use demo_db;
Database changed
mysql> show tables;
+-------------------+
| Tables in demo_db |
+-------------------+
| customer_inf |
| order_master |
| region_info |
+-------------------+
3 rows in set (0.01 sec)
insert into region_info(region_name )values('乌鲁木齐');
insert into region_info(region_name )values('昌吉');
commit;
之后在192.168.0.1，192.168.0.2，192.168.0.3都能查到数据说明配置成功
