使用MyCat实现MySQL数据库的读写分离、分库和水平分表

于 2025-08-04 12:00:00 发布
阅读量421 收藏 13
点赞数 23
CC 4.0 BY-SA版权
文章标签： 数据库 mysql 运维 linux mycat db
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2402_88627342/article/details/149575982

实验环境

安装好MySQL

一、配置双主双从架构

1、修改MySQL配置文件

MySQL主1：

vim /etc/my.cnf

添加：

server-id=1

log-bin=mysql-bin

binlog_format=ROW

sync_binlog=1

relay-log=mysql-relay-bin

log-slave-updates=ON

MySQL主2：

vim /etc/my.cnf

添加：

server-id=2

log-bin=mysql-bin

binlog_format=ROW

sync_binlog=1

relay-log=mysql-relay-bin

log-slave-updates=ON

MySQL从1：

vim /etc/my.cnf

添加：

server-id=3

relay-log=mysql-relay-bin

read-only=ON

MySQL从2：

vim /etc/my.cnf

添加：

server-id=4

relay-log=mysql-relay-bin

read-only=ON

2、重启MySQL服务（MySQL主1、MySQL主2、MySQL从1、MySQL从2）

systemctl restart mysqld

3、创建复制专用用户（MySQL主1、MySQL主2）

create user 'repl'@'192.168.10.%' identified by '123456';

grant replication slave on *.* to 'repl'@'192.168.10.%';

flush privileges;

4、配置主主复制连接

MySQL主1：

show master status;

MySQL主2：

change master to

master_host='192.168.10.41',

master_user='repl',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=769;

启动复制

start slave;

查看复制状态

show slave status\G

show master status;

MySQL主1：

change master to

master_host='192.168.10.42',

master_user='repl',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=769;

启动复制

start slave;

查看复制状态

show slave status\G

5、配置主从复制连接

（1）将MySQL从1成为MySQL主1的从

MySQL主1：

show master status;

MySQL从1：

change master to

master_host='192.168.10.41',

master_user='repl',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=769;

启动复制

start slave;

查看复制状态

show slave status\G

（2）将MySQL从2成为MySQL主2的从

MySQL主2：

show master status;

MySQL从2：

change master to

master_host='192.168.10.42',

master_user='repl',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=769;

启动复制

start slave;

查看复制状态

show slave status\G

6、验证主从复制

MySQL主1：

create database mydb charset utf8mb4;

use mydb;

create table tb1 (id int primary key, name varchar(20));

insert into tb1 values(1,'zs'),(2,'ls');

select * from tb1;

MySQL主2：

use mydb;

insert into tb1 values(2,'ls');

select * from tb1;

MySQL从1：

select * from mydb.tb1;

MySQL从2：

select * from mydb.tb1;

二、MyCat安装配置

1、安装MyCat（mycat）

（1）安装jdk环境

rocky自带jdk1.8

dnf -y install java-1.8.0-openjdk-devel

java -version

（2）上传Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz包到/root目录并解压

tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local/

（3）查看mycat目录结构

ll /usr/local/mycat/

bin：可执行命令

conf：配置文件

lib：存放jar包

logs：日志信息

version.txt：版本信息

（4）配置环境变量

echo 'export MYCAT_HOME=/usr/local/mycat' >> /etc/profile

echo 'export PATH=$MYCAT_HOME/bin:$PATH' >> /etc/profile

加载环境变量

source /etc/profile

2、配置mycat实现读写分离（mycat）

（1）配置server.xml

定义连接mycat的用户和密码

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

修改：

（2）配置schema.xml

定义逻辑数据库、数据节点和数据主机的映射关系

cp /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml.bak

vim /usr/local/mycat/conf/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="dn1">

  </schema>

  <dataNode name="dn1" dataHost="host1" database="mydb" />

  <dataHost name="host1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">

    <heartbeat>select user()</heartbeat>

    <writeHost host="hostM1" url="jdbc:mysql://192.168.10.41:3306" user="root" password="123456">

      <readHost host="hostS1" url="jdbc:mysql://192.168.10.43:3306" user="root" password="123456"/>

    </writeHost>

    <writeHost host="hostM2" url="jdbc:mysql://192.168.10.42:3306" user="root" password="123456">

      <readHost host="hostS2" url="jdbc:mysql://192.168.10.44:3306" user="root" password="123456"/>

    </writeHost>

  </dataHost>

</mycat:schema>

核心参数解析：

balance="3" - 读写分离模式

作用：控制读流量的分配策略

可选值：

        0：不开启读写分离，所有请求发往 writeHost

        1：读请求随机分配到所有 readHost + writeHost

        2：读请求随机分配到任意 readHost

        3：所有读请求只发往 readHost（适合严格读写分离）

writeType="0" - 写节点选择模式

作用：主库故障时的写操作处理方式

可选值：

        0：自动切换到第一个可用的 writeHost

        1：随机选择可用的 writeHost

switchType="1" - 故障切换策略

作用：定义主库故障时的切换逻辑

可选值：

        1：自动切换（基于心跳检测）

        2：基于 MySQL 主从状态自动切换（需配合 show slave status 心跳）

        3：手动切换

slaveThreshold="100" - 从库延迟阈值（秒）

作用：当从库复制延迟超过该值时，跳过该从库

生产建议：通常设置为 60（1分钟）以下

（3）启动mycat

mycat start

ss -anplt | grep java

3、授权root用户允许远程登录（MySQL主1）

grant all on *.* to root@'%' identified by '123456';

4、验证

（1）安装mysql客户端（mycat）

dnf -y install mysql

（2）连接mycat查看数据（mycat）

mysql -umycat -p123456 -h 192.168.10.40 -P 8066
show databases;

use TESTDB

show tables;

select * from tb1;

（3）验证读写分离

mycat：

通过查看MySQL的id测试读操作所在服务器

select @@server_id;

读操作会被调度到两台从节点中

mycat：

插入数据验证写操作

insert into tb1 values(3,'ww');

select * from tb1;

MySQL主1、MySQL主2、MySQL从1、MySQL2：

select * from mydb.tb1;

因为writeType="0"参数，写操作会被调度到MySQL主1服务器上，再通过主主、主从复制到所有服务器

三、使用MyCat进行分库、水平分表

1、修改MySQL架构（MySQL主1、MySQL主2）

（1）停止复制

stop slave;

（2）重置复制配置

reset slave all;

（3）验证复制状态

show slave status\G

2、分库（mycat）

将表分布到不同的数据库实例上

（1）配置schema.xml

将users表分配到dataNode2（MySQL主2、MySQL从2）上

vim /usr/local/mycat/conf/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="dn1">

    <table name="users" dataNode="dn2" />

  </schema>

  <dataNode name="dn1" dataHost="host1" database="mydb" />

  <dataNode name="dn2" dataHost="host2" database="mydb" />

  <dataHost name="host1" maxCon="500" minCon="20" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="60">

    <heartbeat>select user()</heartbeat>

    <writeHost host="hostM1" url="jdbc:mysql://192.168.10.41:3306" user="root" password="123456">

      <readHost host="hostS1" url="jdbc:mysql://192.168.10.43:3306" user="root" password="123456"/>

    </writeHost>

  </dataHost>

  <dataHost name="host2" maxCon="500" minCon="20" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="60">

    <heartbeat>select user()</heartbeat>

    <writeHost host="hostM2" url="jdbc:mysql://192.168.10.42:3306" user="root" password="123456">

      <readHost host="hostS2" url="jdbc:mysql://192.168.10.44:3306" user="root" password="123456"/>

    </writeHost>

  </dataHost>

</mycat:schema>

（2）重启mycat

mycat restart

（3）验证

mycat：

连接mycat

mysql -umycat -p123456 -h 192.168.10.40 -P 8066

创建users表并插入数据

use TESTDB

CREATE TABLE users (

user_id INT PRIMARY KEY,

username VARCHAR(50));

insert into users values(1,'test1');

select * from users;

MySQL主2：

查看数据表

show tables;

select * from users;

users表被分到dataNode2（MySQL主2）上

MySQL从2：

查看数据表

show tables;

select * from users;

users表通主从复制同步到MySQL从2上

3、水平分表（mycat）

将同一个表的数据按行分散到多个结构相同的表中

（1）配置schema.xml

将orders表数据分别分配到dataNode1（MySQL主1、MySQL从1）和dataNode2（MySQL主2、MySQL从2）上

vim /usr/local/mycat/conf/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="dn1">

    <table name="users" dataNode="dn2" />

    <table name="orders" primaryKey="order_id" dataNode="dn1,dn2" rule="mod-long" />

  </schema>

  <dataNode name="dn1" dataHost="host1" database="mydb" />

  <dataNode name="dn2" dataHost="host2" database="mydb" />

  <dataHost name="host1" maxCon="500" minCon="20" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="60">

    <heartbeat>select user()</heartbeat>

    <writeHost host="hostM1" url="jdbc:mysql://192.168.10.41:3306" user="root" password="123456">

      <readHost host="hostS1" url="jdbc:mysql://192.168.10.43:3306" user="root" password="123456"/>

    </writeHost>

  </dataHost>

  <dataHost name="host2" maxCon="500" minCon="20" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="60">

    <heartbeat>select user()</heartbeat>

    <writeHost host="hostM2" url="jdbc:mysql://192.168.10.42:3306" user="root" password="123456">

      <readHost host="hostS2" url="jdbc:mysql://192.168.10.44:3306" user="root" password="123456"/>

    </writeHost>

  </dataHost>

</mycat:schema>

（2）配置rule.xml

配置数据分片规则

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

修改：

（3）重启mycat

mycat restart

（4）验证

mycat：

连接mycat

mysql -umycat -p123456 -h 192.168.10.40 -P 8066

创建users表并插入数据

use TESTDB

CREATE TABLE mydb.orders (

order_id BIGINT PRIMARY KEY,

user_id INT,

amount DECIMAL(10,2));

insert into orders(order_id,user_id,amount) values(1,1,100.00);

insert into orders(order_id,user_id,amount) values(2,2,200.00);

insert into orders(order_id,user_id,amount) values(3,3,300.00);

insert into orders(order_id,user_id,amount) values(4,4,400.00);

select * from orders;

MySQL主1：

select * from mydb.orders;

MySQL主2：

select * from orders;

数据按照order_id的值取模分片存储

四、MyCat安全设置

1、权限配置（mycat）

（1）查看用户权限配置

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

（2）验证用户权限

使用user用户连接mycat

mysql -uuser -puser -h 192.168.10.40 -P 8066
show databases;

use TESTDB

show tables;

select * from tb1;

insert into tb1 values(4,'cl');

delete from tb1 where id = 3;

update tb1 set name = 'cl' where id = 1;

user用户只有读取权限

2、表级 DML 权限（SELECT/INSERT/UPDATE/DELETE）设置（mycat）

权限优先级：表级 > 库级 > 全局。（表级权限优先级最高）

权限位与 MyCat 配置对照表：

权限位   INSERT   UPDATE  SELECT   DELETE   MyCat 实现方式

0000         ❌             ❌         ❌           ❌         只读用户 + 防火墙全禁用

0010         ❌             ❌         ✔️           ❌         只读用户 (readOnly=true)

1110          ✔️             ❌         ❌           ❌         读写用户 + 防火墙限制

1111          ✔️              ✔️         ✔️           ✔️         读写用户 (readOnly=false)

（1）设置权限

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

添加：

    <privileges check="true">

      <schema name="TESTDB" dml="1110" >

        <table name="tb1" dml="0110"></table>

      </schema>

    </privileges>

（2）重启mycat服务

mycat restart

（3）验证

使用user用户连接mycat

mysql -uuser -puser -h 192.168.10.40 -P 8066
use TESTDB

insert into tb1 values(4,'cl');

delete from tb1 where id = 3;

update tb1 set name = 'cl' where id = 1;

select * from tb1;

delete from users where id = 1;

update users set username = 'aa' where user_id = 1;

insert into users values(2,'test2');

select * from users;

user用户对tb1表禁止INSERT、DELETE操作，允许UPDATE、SELECT操作。

对TESTDB其他表禁止DELETE操作，允许INSERT、UPDATE、SELECT操作。

3、SQL 拦截

firewall 标签用来定义防火墙； firewall 下 whitehost 标签用来定义 IP 白名单 ， blacklist 用来定义SQL 黑名单。

（1）白名单设置（mycat）

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

添加：

  <firewall>

     <whitehost>

        <host host="192.168.10.40" user="user"/>

     </whitehost>

  </firewall>

（2）重启mycat服务（mycat）

mycat restart

（3）验证

mycat：

使用user用户连接mycat

mysql -uuser -puser -h 192.168.10.40 -P 8066

MySQL主1：

mysql -uuser -puser -h 192.168.10.40 -P 8066

仅允许192.168.10.40的客户端，使用user用户连接mycat，其他IP无法连接。

（4）黑名单设置（mycat）

优先级：黑名单的拦截优先级高于用户权限（<privileges>），即使账号有 DELETE 权限也会被拦截。

常用拦截属性

属性名

说明

典型应用场景

selelctAllow

是否允许执行 SELECT 语句

deleteAllow

是否允许执行 DELETE 语句

updateAllow

是否允许执行 UPDATE 语句

insertAllow

是否允许执行 INSERT 语句

dropAllow

是否允许执行 DROP TABLE 或 DROP DATABASE

防止误删表或数据库

createTableAllow

是否允许创建表

限制创建新表或数据库

setAllow

是否允许执行 SET 语句

防止全局配置被篡改

alterAllow

是否允许执行 Alter 语句

防止表结构被修改

renameAllow

是否允许执行RENAME TABLE

防止表被重命名

selectIntoAllow

是否允许执行SELECT INTO OUTFILE

防止数据导出到文件

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

添加：

       <blacklist check="true">

       <property name="deleteAllow">false</property>

       </blacklist>

（2）重启mycat服务（mycat）

mycat restart

（3）验证

mycat：

使用user用户连接mycat

mysql -uuser -puser -h 192.168.10.40 -P 8066
use TESTDB

delete from tb1 where id = 1;

delete from users where user_id = 1;

<property name="deleteAllow">false</property>：明确禁止所有 DELETE 类型的 SQL 语句（无论操作哪张表）

LKAI.
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值