通过Mycat分库分表

2024-02-15 09:11:46

前言

实验环境：
系统：centos7
Mysql版本： 5.7
Mycat版本： 1.6
Mycat主机ip地址： 10.8.161.75
主机1地址： 10.8.161.185
主机2地址： 10.8.161.186

实验目标：

主1、主2实现分库分表
主1创建database1、创建table1；主2创建database2 、创建table1、table2
通过mycat实现分库分表

备注：本文略过主1和主2互为主从的搭建步骤，需要看请移步
<互为主从搭建步骤 />

一. 创建所需的库和表

CREATE DATABASE databse1CHARACTER SET utf8 COLLATE utf8_general_ci;  #创建database1库

use database1；      #进入 database1库  

CREATE TABLE table1 (              # 创建 table1表
id INT NOT NULL AUTO_INCREMENT,
city varchar(50) NOT NULL,
PRIMARY KEY (id)
 )AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into database1.table1 values(1,"beijing"); # 在table1插入数据


CREATE DATABASE databse2 CHARACTER SET utf8 COLLATE utf8_general_ci;
use databse2；                             # 创建 databse2 库

use  databse2                             # 进入  databse2 库
create table if not exists table2 (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);   # 创建 table2 表


CREATE TABLE table1 (                       # 同样创建 table1表
id INT NOT NULL AUTO_INCREMENT,
city varchar(50) NOT NULL,
PRIMARY KEY (id)
 )AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into databse2.table1 values(1,"shanghai");   #插入 table1表数据

二. 设置Mycat配置文件

这里设置配置文件之前记得先备份

1. 设置 server.xml

 <user name="mycat">
<property name="password">aA12345678.</property>
 <property name="schemas">mycat</property>

2. 设置 rule.xml

直接清空，导入


<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
    - you may not use this file except in compliance with the License. - You
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
    - - Unless required by applicable law or agreed to in writing, software -
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
    License for the specific language governing permissions and - limitations
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
  
<tableRule name="mycat-rule">                <!-- 表分片的规则名，这里定义为mycat-rule，这个需要在schema.xml文件中引用 -->
<rule>
<columns>id</columns>                  <!--biao1表的分片列 -->
<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>        <!-- count值与分片个数相同，这里table1表分片到库database1和database2库里，共2份。 -->
</function>
</mycat:rule>

3. 设置 schema.xml

要将rule.xml里定义的分片模型写进去，由于这里的table1表配置了主键，所以primaryKey="id"这个也写进去，其他情况看表结构，也可以不写）：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  
<schema name="mycat" checkSQLschema="false" sqlMaxLimit="100">
<table name="biao2" primaryKey="id"  dataNode="dn1" />
<table name="table1" primaryKey="id" dataNode="dn1,dn2" rule="mycat-rule" />
</schema>
          
<dataNode name="dn1" dataHost="Mycat-node" database="databse2" />
<dataNode name="dn2" dataHost="Mycat-node" database="ku1" />
  
<dataHost name="Mycat-node" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="Mysql-node1" url="10.8.161.185:3306" user="root" password="aA12345678.">
<readHost host="Mysql-node2" url="10.8.161.186:3306" user="root" password="aA12345678.">
</readHost>
</writeHost>
<writeHost host="Mysql-node1" url="10.8.161.185:3306" user="root" password="aA12345678.">
</writeHost>
</dataHost>
  
</mycat:schema>

4.启动myact，登陆查看

mysql -umycat -h 10.8.161.75 -p'aA707767695.' -P 8066  #用server.xml  里设置的账号密码登陆

mysql> show databases;
+----------+
| DATABASE |
+----------+
| mycat    |
+----------+
1 row in set (0.00 sec)


mysql> use mycat;
Database changed
mysql> show tables;
+-----------------+
| Tables in mycat |
+-----------------+
| table1           |
| table2           |
+-----------------+
2 rows in set (0.00 sec)


mysql> desc table1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| city  | varchar(50) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

======================================================================================================================================================================================================================================================================================================
辛苦浏览观看，如果对你有帮助，请顺手点个赞吧 (σﾟ∀ﾟ)σ…:*☆

码农公寓

前言