环境安装

jdk

# 安装jdk1.7以上
yum search jdk | grep openjdk
yum install -y java-1.8.0-openjdk-devel.x86_64

# 配置javahome
vi /etc/profile

# 添加
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.282.b08-1.el7_9.x86_64/

# 立即生效
source /etc/profile

mycat

# 安装mycat
# 下载地址 http://dl.mycat.org.cn/

# 这里为了省事本地下载好上传到服务器
scp -r /Users/qvbilam/Downloads/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz  root@192.168.128.120:/data/pack

# 解压
tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

# 移动目录
mv ./mycat /usr/local/mycat

# 添加全局命令
ln -s /usr/local/mycat/bin/mycat /usr/bin

配置说明

schma.xml

schema.xml文件定义MyCat的逻辑库,逻辑表,分片规则,DataNode以及DataSource.

schema标签

table标签

定义MyCat逻辑表

childTable标签

定义子表,通过标签上的属性与父表进行关联.子表数据会与父表数据在同一个节点中.首先获取joinkey的值,再通过parentKey属性的列名查询,通过执行查询获得父表分片,将子表写入该分片中.

dataNode标签

定义mycat数据节点,即数据分片.一个dataNode就是一个独立的分片,

dataHost标签

定义具体数据库,读写分离,心跳语句

heartbeat标签

定义数据库心跳检测语句

writeHost,readHost标签

集群搭建

集群说明

配置

配置服务

配置虚拟mysql服务

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

<!-- 修改服务端口号为3306,默认8066 -->
<property name="serverPort">3306</property> <property name="managerPort">9066</property>

<!-- 修改user标签对应的账号密码与访问的user数据库 -->

<user name="root" defaultAccount="true">
    <property name="password">root</property>
    <property name="schemas">blog</property>
    <property name="defaultSchema">blog</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>

配置逻辑库

vi /usr/local/mycat/conf/schema.xm

<!-- 数据库名为blog, 表名为xxx,xxx分片到dn1与dn2, 使用rule.xml中的mod-long-->
<schema name="blog" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
    <table name="blog_contents" dataNode="dn1,dn2" rule="mod-long" splitTableNames ="true"/>
    <table name="blog_user" dataNode="dn1,dn2" rule="mod-long" splitTableNames ="true"/>
</schema>

<!--分片名称 -->
<dataNode name="dn1" dataHost="pxc1" database="blog" />
<dataNode name="dn2" dataHost="pxc2" database="blog" />

<!-- pxc1连接设置 -->
<dataHost name="pxc1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select 1</heartbeat>
    <writeHost host="hostM1" url="192.168.128.125:3306" user="root" password="123456">
        <readHost host="hostR1" url="192.168.128.126:3306" user="root" password="Kenan123!@#qwe"></readHost>
        <readHost host="hostR2" url="192.168.128.127:3306" user="root" password="Kenan123!@#qwe"></readHost>
     </writeHost>
</dataHost>


<!-- pxc2连接设置 -->
<dataHost name="pxc2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select 1</heartbeat>
    <writeHost host="hostM2" url="192.168.128.128:3306" user="root" password="123456">
        <readHost host="hostM2R1" url="192.168.128.129:3306" user="root" password="Kenan123!@#qwe"></readHost>
        <readHost host="hostM2R2" url="192.168.128.130:3306" user="root" password="Kenan123!@#qwe"></readHost>
    </writeHost>
</dataHost>

配置算法

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

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
      <!-- n个节点就是. 对主键取模分配到对应的节点 例如0分配到第一个节点,1分配到第二个节点 -->
    <property name="count">2</property>
</function>

启动

关闭防火墙

# 关闭防火墙
systemctl stop firewalld
# 查看
systemctl status firewalld

# 临时关闭
setenforce 0
# 查看
getenforce

开放端口

# 添加规则
# vim  /etc/sysconfig/iptables

-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 8066 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 9066 -j ACCEPT


# 保存退出使配置生效
service iptables restart

启动

# 进入mycatmulu
cd /usr/local/mycat
# 启动
./bin/startup_nowrap.sh

测试

连接

# 通过配置虚拟的mysql启动,server.xml设置的账户密码以及端口号(默认8066)

mysql -uroot -proot -h192.168.128.120

创建库与表

在schema.xm中配置了虚拟库名为blog,表名为blog_user与blog_contents

# 在两个节点125,128创建库与表.
# 因为pxc集群所以库与表在125～130中都存在
create database blog;
use blog;

# 创建表
CREATE TABLE `blog_contents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(150) DEFAULT '',
  `text` longtext,
    `image` varchar(255) DEFAULT '',
  `order` int(10) unsigned DEFAULT 0,
    `is_delete` tinyint(1) DEFAULT 0,
  `create_time` int(11) DEFAULT 0,
    `update_time` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建表
CREATE TABLE `blog_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT '',
  `password` varchar(64) DEFAULT '',
  `mail` varchar(150) DEFAULT '',
  `url` varchar(150) DEFAULT '',
  `nickName` varchar(32) DEFAULT '',
  `activated` int(10) unsigned DEFAULT 0,
  `create_time` int(11) DEFAULT 0,
    `update_time` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `mail` (`mail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

在mycat 插入数据测试

# mycat 中插入数据
insert into blog_user(id,name,password,nickname) values (1,'qvbilam',123456,'qvbilam');
insert into blog_user(id,name,password,nickname) values (2,'534511019',123456,'羡仙');
insert into blog_user(id,name,password,mail,nickname) values (3,'534511020',123456,concat(RAND() * 1000),'打王者吗');
insert into blog_user(id,name,password,mail,nickname) values (4,'534511021',123456,concat(RAND() * 1000),'玩fgo吗');

在mycat 查看数据

mysql> select * from blog_user;
+----+-----------+----------+--------------------+------+--------------+-----------+-------------+-------------+
| id | name      | password | mail               | url  | nickName     | activated | create_time | update_time |
+----+-----------+----------+--------------------+------+--------------+-----------+-------------+-------------+
|  2 | 534511019 | 123456   |                    |      | 羡仙         |         0 |           0 |           0 |
|  4 | 534511021 | 123456   | 192.49191432492054 |      | 玩fgo吗      |         0 |           0 |           0 |
|  1 | qvbilam   | 123456   |                    |      | qvbilam      |         0 |           0 |           0 |
|  3 | 534511020 | 123456   | 339.7693730329809  |      | 打王者吗     |         0 |           0 |           0 |
+----+-----------+----------+--------------------+------+--------------+-----------+-------------+-------------+

在pxc1集群查看数据

mysql> select * from blog_user;
+----+-----------+----------+--------------------+------+-----------+-----------+-------------+-------------+
| id | name      | password | mail               | url  | nickName  | activated | create_time | update_time |
+----+-----------+----------+--------------------+------+-----------+-----------+-------------+-------------+
|  2 | 534511019 | 123456   |                    |      | 羡仙      |         0 |           0 |           0 |
|  4 | 534511021 | 123456   | 192.49191432492054 |      | 玩fgo吗   |         0 |           0 |           0 |
+----+-----------+----------+--------------------+------+-----------+-----------+-------------+-------------+
2 rows in set (0.00 sec)

在pxc2集群查看数据

mysql> select * from blog_user;
+----+-----------+----------+-------------------+------+--------------+-----------+-------------+-------------+
| id | name      | password | mail              | url  | nickName     | activated | create_time | update_time |
+----+-----------+----------+-------------------+------+--------------+-----------+-------------+-------------+
|  1 | qvbilam   | 123456   |                   |      | qvbilam      |         0 |           0 |           0 |
|  3 | 534511020 | 123456   | 339.7693730329809 |      | 打王者吗     |         0 |           0 |           0 |
+----+-----------+----------+-------------------+------+--------------+-----------+-------------+-------------+
2 rows in set (0.00 sec)