MySQL垂直分库（基于MyCat）

参考资料：

参考视频

参考博客

Mycat基本部署

视频参考资料：链接: https://pan.baidu.com/s/1xT_WokN_xlRv0h06b6F3yg 提取码: aag3 

---

概要：

        本文的垂直分库，全部是基于前文部署的基本架构进行的

---

垂直分库：

        垂直分库相对来说比较简单

• 只需要指定指定表去到哪台服务器即可
• 不需要分片规则

        我们将进行如下分库

1. 修改schema.xml

        利用Notepad++，将schema.xml的内容替换为如下内容，根据个人进行修改

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
		<table name="tb_goods_item" dataNode="dn1" primaryKey="id" />
	 
		<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
		<table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
		<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />
	 
		<table name="tb_user" dataNode="dn3" primaryKey="id" />
		<table name="tb_user_address" dataNode="dn3" primaryKey="id" />
		<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
		<table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
		<table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
	</schema>
	 
	<dataNode name="dn1" dataHost="dhost1" database="shopping" />
	<dataNode name="dn2" dataHost="dhost2" database="shopping" />
	<dataNode name="dn3" dataHost="dhost3" database="shopping" />
	 
	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="jdbc:mysql://192.168.154.142:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123888" />
	</dataHost>
	 
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="jdbc:mysql://192.168.154.143:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123888" />
	</dataHost>
	 
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="jdbc:mysql://192.168.154.144:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123888" />
	</dataHost>
</mycat:schema>

2. 创建目标数据库

        根据schema.xml中的规定，在每台服务器上创建目标数据库

3. 修改server.xml

        修改原先的server.xml的目标数据库

	<user name="root" defaultAccount="true">
		<property name="password">123888</property>
		<property name="schemas">SHOPPING</property>
		<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">SHOPPING</property>
		<property name="readOnly">true</property>
	</user>

4. 重启Mycat

        切换到mycat的安装目录

cd /usr/local/mycat/

        先停止 ，然后启动

#停止
bin/mycat stop

#开始
bin/mycat statr

5. 查看启动日志

tail -100f logs/wrapper.log 

 

6. 上传sql脚本

        可以根据上述网盘地址找到数据库脚本

• shopping-table.sql
• shopping-insert.sql

上传至一个目录下

7.登录MyCat

mysql -h 192.168.154.135 -P 8066 -uroot -p123888

查看逻辑数据库，逻辑表（注意是逻辑上存在的，并不是实际存在的）

show databases;
use SHOPPING;
show tables;

8. 执行上传的脚本

source /usr/local/mydir/shopping-table.sql

source /usr/local/mydir/shopping-insert.sql

9. 检验结果

        可以看到，数据已经分流到了各个数据库

---

全局表：

        上述已经完成了数据库的垂直分库，但是面临一个问题，跨服务器进行联表查询的时候会报错，如下SQL

SELECT order_id, payment, receiver, province, city, area FROM tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r WHERE
o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND
o.receiver_region = r.areaid;

 

 

1. 概述 

•  首先，在Mycat进行跨服务器进行联表查询是不可能的
• 唯一的办法就是将该服务器用到的数据表，放到该服务器上，或者将每一个服务器都会用到的数据表做成全局表

2. 全局表步骤

        假如数据表tb_areas_provinces，tb_areas_city，tb_areas_region每个服务器的数据库都会用到，那么我们就需要把他们做成全局表

（1） 停止Mycat

bin/mycat stop

(2) 清除各个服务器上shopping数据库中的表

 

（3）修改schema.xml

<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>

(4) 启动mycat

bin/mycat start

查看启动日志

tail -100f logs/wrapper.log

（5）登录Mycat，运行脚本

mysql -h 192.168.154.135 -P 8066 -uroot -p123888

选择数据库

 

use SHOPPING;

 

运行脚本 

source /usr/local/mydir/shopping-table.sql

source /usr/local/mydir/shopping-insert.sql

可以看到，每一个服务器都有了这三张表

（6）执行联表查询

        然后再执行联表查询

SELECT order_id, payment, receiver, province, city, area FROM tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r WHERE
o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND
o.receiver_region = r.areaid;

(7) 全局表的修改

        对于全局表的增删改，同样也会同步到每一个数据库上