MySQL集群读写分离方案C-(MySQL+GTID+MyCat+Keepalive)之3/5：MyCat读写分离

2024年1月30日 937点热度 0人点赞 0条评论

一、配置 MyCat读写分离
二、启动mycat服务并测试
三、读写分离场景之主从切换。
四、存在问题

一、配置 MyCat读写分离

1、配置schema.xml

（1）备份原schema.xml

cd /mysql/app/mycat/conf/
cp schema.xml schema.xml.source

（2）编辑schema.xml（全部替换）--【待更新】

vi /mysql/app/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  
    <!-- 逻辑库配置 mycatdb1,mycatdb2,mycatdb3是逻辑库并不是真的数据库 -->
    <schema name="llldb1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb1"/>
    <schema name="llldb2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb2"/>
    <schema name="llldb3" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb3"/>

    <!-- 其中checkSQLschema 表明是否检查并过滤 SQL 中包含 schema 的情况，如逻辑库为mycatdb1，则可能写为 select*from mycatdb1.testbak11，此时会自动过滤 mycatdb1，SQL变为 select * from testbak11，若不会出现上述写法，则可以关闭属性为 false -->
    <!-- sqlMaxLimit默认返回的最大记录数限制，MyCat1.4版本里面，用户的 Limit参数会覆盖掉 MyCat的 sqlMaxLimit默认设置-->
     
    <!-- 表分片配置在这里 -->
    <!--</schema> -->
    <!-- 定义 MyCat的数据节点，节点配置 mysqldb1,mysqldb2,mysqldb3才是真正的数据库， dataNode 中的 name 数据表示节点名称， dataHost表示数据主机名称， database表示该节点要路由的数据库的名称 -->
    <dataNode name="dnmycatdb1" dataHost="mycat176" database="llldb1"/>   
    <dataNode name="dnmycatdb2" dataHost="mycat176" database="llldb2"/>
    <dataNode name="dnmycatdb3" dataHost="mycat176" database="llldb3"/>

    <!-- 读写分离的配置 -->
    <!-- dataHost配置的是实际的后端数据库集群（当然，也可以是非集群） -->
    <!-- 注意：schema 中的每一个 dataHost 中的 host 属性值必须唯一，否则会出现主从在所有dataHost中全部切换的现象 -->
    <!-- 定义数据主机 dtHost1，只连接到 MySQL读写分离集群中的 Master节点，不使用MyCat托管 MySQL主从切换 -->
    <!-- <dataHost name="dtHost1" maxCon="500" minCon="20" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.21.161:3306" user="root" password="rootroot" />
    </dataHost> -->
    <!-- 使用 MyCat托管 MySQL主从切换 -->
    <!-- 定义数据主机 mysqlm01，连接到 MySQL读写分离集群，并配置了读写分离和主从切换 -->
    <dataHost name="mycat176" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- <heartbeat>show slave status</heartbeat> -->
        <writeHost host="192.168.21.161" url="192.168.21.161:3306" user="root" password="rootroot">
          <!-- 可以在这配置它对应的多个读库-->
          <readHost host="192.168.21.162" url="192.168.21.162:3306" user="root" password="rootroot" />
          <readHost host="192.168.21.163" url="192.168.21.163:3306" user="root" password="rootroot" />
          <readHost host="192.168.21.164" url="192.168.21.164:3306" user="root" password="rootroot" />
          <readHost host="192.168.21.165" url="192.168.21.165:3306" user="root" password="rootroot" />
        </writeHost>
        <!-- 主故障，顶替写节点,主正常是分担读压力，建议用 MHA的第二个主-->
        <!--此处已经注释掉，如果要做主从切换，此处需要开启-->
        <!-- <writeHost host="192.168.21.162" url="192.168.21.162:3306" user="root" password="rootroot" > </writeHost> -->
    </dataHost>
</mycat:schema>

2、配置解释

这里面，有两个参数需要注意，balance和 switchType。

（1）balance指的负载均衡类型，目前的取值有 4种：

--balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost上。
--balance="1"，全部的 readHost与 stand by writeHost参与 select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1与 M2互为主备)，正常情况下，M2,S1,S2都参与 select语句的负载均衡。
--balance="2"，所有读操作都随机的在 writeHost、readhost上分发。
--balance="3"，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost不负担读压力

因此，该配置文件中的 balance="1"意味着作为 stand by writeHost的 hostS1和 hostS2将参与 select语句的负载均衡，这就实现了主从的读写分离，
switchType='-1'意味着当主挂掉的时候，不进行自动切换，即 hostS1和 hostS2并不会被提升为主，仍只提供读的功能。
这就避免了将数据写进 slave的可能性，毕竟，单纯的 MySQL主从集群并不允许将数据读进 slave中，除非配置的是双 master。

（2）switchType指的是切换的模式，目前的取值也有 4种：

--switchType='-1' 表示不自动切换
--switchType='1' 默认值，表示自动切换
--switchType='2' 基于 MySQL 主从同步的状态决定是否切换,心跳语句为 show slave status
                 MyCat1.4开始支持 MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：
                 MyCat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性：switchType="2" 与 slaveThreshold="100"，此时意味着开启 MySQL 主从复制状态绑定的读写分离与切换机制，MyCat 心跳机制通过检测show slave status 中的"Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running"三个字段来确定当前主从同步的状态以及 Seconds_Behind_Master主从复制时延，当 Seconds_Behind_Master 大于 slaveThreshold时，读写分离筛选器会过滤掉此 Slave机器，防 止 读 到 很 久 之 前 的 旧 数 据 ， 而 当 主 节 点 宕 机 后 ， 切 换 逻 辑 会 检 查 Slave 上 的Seconds_Behind_Master是否为 0，为 0时则表示主从同步，可以安全切换，否则不会切换。
--switchType='3'基于 MySQL galary cluster/PXC/mgr 的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'。

3、配置 MyCat的 server.xml（部分配置）

（1）备份原server.xml

cd /mysql/app/mycat/conf/
cp server.xml server.xml.source

（2）编辑server.xml（只需要修改如下信息）

vi server.xml

<!-- root权限用户 test/root，对应的 MyCat逻辑库连接到的数据节点对应的主机为主从复制集群，并通过 MyCat实现了读写分离 -->
        <user name="test">
                <property name="password">test123</property>
                <property name="schemas">llldb1,llldb2,llldb3</property>
                <!-- 表级 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，只读权限-->
        <user name="test1">
                <property name="password">test123</property>
                <property name="schemas">llldb1,mycatdb2,mycatdb3</property>
                <property name="readOnly">true</property>
        </user>

注意:后面可以使用test进行登录，进行数据的读写分离。

参数            说明
user            用户配置节点
--name          登录的用户名，也就是连接Mycat的用户名
--password      登录的密码，也就是连接Mycat的密码
--schemas       数据库名，这里会和schema. xml中的配置关联，多个用逗号分开，例如需要这个用户需要管理两个
--privileges    配置用户针对表的增删改查的权限，具体见官方文档吧

4、配置增加log4j.properties

[root@mycat176 conf]# vi log4j.properties
#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/kuang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
~

5、防火墙中打开8066和9066端口

MyCat的默认数据端口为8066，mycat通过这个端口接收数据库客户端的访问请求。
管理端口为9066，用来接收mycat监控命令、查询mycat运行状况、重新加载配置文件等。

6、修改日志级别

（1）更改日志的输出模式

vi log4j2.xml
修改<asyncRoot level="info" includeLocation="true"> 为 <asyncRoot level="debug" includeLocation="true">

info >> debug，然后去查询去添加数据在/logs/mycat.log日志文件里查看sql被路由到了哪个服务器上。

二、启动mycat服务并测试

1、启动mycat

（1）方法1 ：

在mycat的lib包下面丢入mysql的驱动包，然后在mycat\bin目录下找到./startup_nowrap. sh运行。双击没有一闪而退，就启动成功了。如果启动不成功，仔细检查配置信息。

cd /mysql/app/mycat/bin
./startup_nowrap.sh

（2）方法2：

mycat console
mycat stop
mycat start
ss -lntup|grep java
netstat -tnlp

（3）方法3：

systemctl restart mycat
systemctl status mycat

2、登录

mysql -utest -ptest123 -P9066 -h192.168.21.176

3、查看数据库

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| mycatdb1 |
| mycatdb2 |
| mycatdb3 |
+----------+
3 rows in set (0.01 sec)

4、查看状态

（1）进入管理口，查看相关管理信息

mysql --utest -ptest123 -P9066 -h192.168.21.176
show @@help;
show @@heartbeat;
show @@datasource;

（2）进入数据口，查看相关数据信息

mysql --utest -ptest123 -P8066 -h192.168.21.176
show databases;

5、测试：

mysql --utest -ptest123 -P8066 -h192.168.21.176

use mycatdb1;           ---进入mycatdb1，其实是进入了mysqldb1这个库。
show tables;
select * from test;
select * from test;
select * from mysqldb2.test2;
select * from mysqldb3.test3;
use mycatdb1
insert into mysqldb1.test values (7,'LLL117');
insert into mysqldb1.test values (7,'LLL117');
commit;

6、关机测试

（1）测试21.176

mysql -utest -ptest123 -P8066 -h192.168.21.176 -e "use llldb1;select @@hostname";

（2）关掉其中一台 mysql，继续测试

systemctl stop mysqld
mysql -utest -ptest123 -P8066 -h192.168.21.176 -e "use llldb1;select @@hostname";
systemctl start mysqld
mysql -utest -ptest123 -P8066 -h192.168.21.176 -e "use llldb1;select @@hostname";

三、读写分离场景之主从切换。

1、配置schema.xml

[root@mysqlm01 conf]# vi schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库配置 mycatdb1,mycatdb2,mycatdb3是逻辑库并不是真的数据库-->
<schema name="mycatdb1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb1"/>
<schema name="mycatdb2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb2"/>
<schema name="mycatdb3" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb3"/>
<!-- 表分片配置在这些 -->
<!--</schema> -->
<!-- 节点配置 testdb1,testdb2,testdb3才是真正的数据库 -->
<dataNode name="dnmycatdb1" dataHost="mysqlm01" database="mysqldb1"/>
<dataNode name="dnmycatdb2" dataHost="mysqlm01" database="mysqldb2"/>
<dataNode name="dnmycatdb3" dataHost="mysqlm01" database="mysqldb3"/>
<!-- 读写分离的配置 -->
<dataHost name="mysqlm01" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- <heartbeat>select user()</heartbeat> -->
<heartbeat>show slave status</heartbeat>
<writeHost host="192.168.21.161" url="192.168.21.161:3306" user="root" password="root">
<!-- 可以在这配置它对应的多个读库 -->
<readHost host="192.168.21.162" url="192.168.21.162:3306" user="root" password="root" />
<readHost host="192.168.21.163" url="192.168.21.163:3306" user="root" password="root" />
<readHost host="192.168.21.164" url="192.168.21.164:3306" user="root" password="root" /> 
<readHost host="192.168.21.165" url="192.168.21.165:3306" user="root" password="root" /> 

</writeHost>
<!-- 主故障，顶替写节点,主正常是分担读压力,前提是21.162可以读写-->
<writeHost host="192.168.21.162" url="192.168.21.162:3306" user="root" password="root" >
</writeHost>
</dataHost>
</mycat:schema>

2、重启mycat生效新配置

mycat restart
systemctl stop mysqld
mysql -utest -pLLL123 -192.168.21.161 P8066 -e "delete from testdb.LLL12 limit 1;commit;show variables like 'server_id';"; 
mysql -utest -pLLL123 -P8066 -192.168.21.161 -e "select @@hostname";
systemctl start mysqld
mysql -uuser -puser -P8066 -192.168.21.161 -e "select @@hostname";

3、测试

（1）测试思路

配置/mysql/app/mycat/conf/log4j2.xml，对 <asyncRoot level="info" includeLocation="true">改为debug后，重启mycat服务，进入mycat数据库，执行select * from llldb1.testbak11;查看执行走的节点

（2）其他测试

mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb1;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb1;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb1;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb2;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb2;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb2;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb2;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb2;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb2;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb2;select @@hostname"
mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "use llldb2;select @@hostname"

mysql -utest -ptest123 -h192.168.21.176 -P 8066 -e "insert into llldb1.testbak11 values(6,'LLL006');select @@hostname";
mysql -uroot -prootroot -e "select * from llldb1.testbak11;"

四、存在问题

mycat 1.6版本sql语句无法实现多语句功能。

本作品采用知识共享署名 4.0 国际许可协议进行许可