Mycat 实现 MySQL 读写分离
目前MyCat配置Mysql 读写分离的文章虽然很多,但是看起来比较杂乱,造成很多困扰,所以自己根据实践并采用新的Mycat 版本将搭建步骤自始至终重写一遍。
在根据本文档操作前,基于服务器的环境已经配置了Mysql 的 主从复制模式,若没有配置参考如下文章:
1.环境介绍
主服务器(master)
mycat服务器
IP:10.16.195.49
系统:Linux(redhat centos7 64)
mysql版本:8.0.17
同步数据库:AIDCDATA, AIHsMdlSrv
同步帐号:model_slave
同步密码:slave123
从服务器(slave)两台
IP:10.16.195.50/10.16.195.51
系统:Linux(redhat centos7 64)
mysql版本:8.0.17
同步数据库:AIDCDATA, AIHsMdlSrv
2.mycat安装
1.安装java环境
# 解压到/usr/local
tar -zxcf jdk-8u20-linux-x64.tar.gz /usr/local
mv jdk-8u20-linux-x64 jdk
添加环境变量
sudo vim /etc/profile
export JDK_HOME=/usr/local/jdk
export PATH=%PATH:$JDK_HOME/bin
# 保存退出
source /etc/profile
2.mycat安装
注意下载release版linux系统的安装包
# 解压到/usr/local
tar -zxcf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz /usr/local
# 解压后文件名就是mycat
配置环境变量
sudo vim /etc/profile
export MYCAT_HOME=/usr/local/mycat
export PATH=%PATH:$MYCAT_HOME/bin
# 保存退出
source /etc/profile
3.mycat配置详解
mycat核心配置文件有三个:
server.xml:定义用户以及系统相关变量,如端口等
Schema.xml:定义逻辑库,表、分片节点等内容
rule.xml:定义分片规则
server.xml配置详解
1.user标签--用户配置节点
mycat提供给用户登录的节点
123456
AIDCDATA,AIHsMdlSrv
123456
AIDCDATA,AIHsMdlSrv
true
2.system标签 -- 系统配置节点
默认端口8066,默认管理端口9066
utf8
0
0
5.6.20
40960
1
4096000
32
0
100
1000
65535
0
1
4
16M
1
1
16
1800000
3
300
300000
10000
0.0.0.0
8066
9066
4096
0
1
1m
1k
0
384m
true
3.firewall标签 -- 防火墙配置节点
4.本案例中server.xml配置文件
只修改用户配置部分,其他默认。
123456
AIDCDATA,AIHsMdlSrv
123456
AIDCDATA,AIHsMdlSrv
true
schema.xml配置详解
1.schema标签 -- mycat逻辑库相关配置
注意:
schema是mycat服务中会生成的逻辑库,是虚拟的。
一个逻辑库可以对应多个真实数据库,通过dataNode来指定,dn1,dn2,dn3。
name与server.xml中schemas要对应
如果中不指定
2.dataNode标签:分片分库信息相关配置
3.dataHost标签:mysql物理数据库相关配置
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
select user()
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
select user()
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
select user()
4.该案例中schema.xml配置
select user()
rule.xml配置详解
1.tableRule标签 -- 表拆分规则
sharding_id
hash-int
2.function标签 -- 分片规则
partition-hash-int.txt
3.分片规则种类
连续分片之自定义数字范围分片,0-2标识节点为数据库节点,取在schema中的节点配置顺序;该配置表示按照索引进行数据分片,索引0-5000000分配在0节点,5000000-10000000分配到1节点,以此类推
class="io.mycat.route.function.AutoPartitionByLong">
autopartition-long.txt
0
连续分片之按时间(月,天,小时)分片
yyyy-MM-dd
2014-01-01
10
yyyy-MM-dd
2014-01-01
24
离散分片之枚举分片 -- 通过在配置文件中配置可能的枚举id,自己配置分片,本规则适用于特定的场景,比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的
partition-hash-int.txt
0
0
十进制求模分片:规则为对分片字段十进制取模运算,数据分布最均匀
3
离散分片之应用指定分片 -- 规则为对分片字段进行字符串截取,获取的字符串即指定分片
例如 id=05-100000002 在此配置中代表根据 id 中从startIndex=0 开始,截取 size=2 位数字即 05,05 就是获取的分区,如果没传默认分配到 defaultPartition
0
2
8
0
离散分片之字符串截取数字hash分片 -- 此规则是截取字符串中的int数值hash分片
512
2
0:2
离散分片之一致性Hash分片 -- 此规则优点在于扩容时迁移数据量比较少
0
2
160
function>
综合分片之范围求模分片
先进行范围分片计算出分片组,组内再求模,可以进行范围分片到0-200M,再组内分片到5个分片其中之一
可以避免扩容时的数据迁移,又可以一定程度上避免范围分片的热点问题
分片组内使用求模可以保证组内数据比较均匀,分片组之间是范围分片可以兼顾范围查询
partition-range-mod.txt
32
以下配置一个范围代表一个分片组,=号后面的数字代表该分片组所拥有的分片的数量
综合分片之取模范围约束分片 -- 对指定分片列进行取模后再由配置决定数据的节点分布
1-32 即代表id%256后分布的范围,=后面的数字表示分配的节点
如果id非数字,则分配在defaoultNode默认节点
256
2
partition-pattern.txt
4.该案例中没有涉及拆分表的操作,没有配置rule.xml
4.Mycat启动
主从mysql数据库中都需要新建scheme.xml配置中的数据库用户。案例使用的是root。
1.启动mycat服务
/usr/local/mycat/bin/mycat start
# 配置了环境变量
mycat start
2.查看错误日志
cat /usr/local/mycat/logs/wrapper.log
看到这个表示成功启动
3.mycat登录
如果在server.xml中没有配置serverPort,默认连接端口为8066
# 登录mycat服务
mysql -h 127.0.0.1 -umycat_writer -pmycat_writer -P8066
可以在mysql控制台执行sql命令
5.Mycat 管理命令与监控
管理命令
1.登录方式
MyCAT 自身有类似其他数据库的管理监控方式,可以通过 Mysql 命令行,登录管理端口(9066)执行相应 的 SQL 进行管理
8066 数据端口默认
9066 管理端口默认
命令行的登陆是通过 9066 管理端口来操 作,登录方式类似于 mysql 的服务端登陆。
mysql -h127.0.0.1 -umycat_writer -pmycat_writer -P9066 [-dmycat]
-h 后面是主机,即当前 mycat 按照的主机地址,本地可用 127.0.0.1 远程需要远程 ip -u Mycat server.xml 中配置的逻辑库用户
-p Mycat server.xml 中配置的逻辑库密码
-P 后面是端口 默认 9066,注意 P 是大写
-d Mycat server.xml 中配置的逻辑库
管理端口用于执行管理命令:
mysql -h127.0.0.1 -umycat_writer -pmycat_writer -P9066
命令端口用户执行增删改查等 SQL 语句:
mysql -h127.0.0.1 -umycat_writer -pmycat_writer -P8066
2.管理命令
从9066 管理端口登陆后,执行 show @@help 可以查看到所有命令
mysql> show @@help;
+-------------------------------------+--------------------------------------------+
| STATEMENT | DESCRIPTION |
+-------------------------------------+--------------------------------------------+
| show @@time.current | Report current timestamp |
| show @@time.startup | Report startup timestamp |
·
·
| online | Change MyCat status to ON |
| clear @@slow where schema = ? | Clear slow sql by schema |
| clear @@slow where datanode = ? | Clear slow sql by datanode |
+-------------------------------------+--------------------------------------------+
58 rows in set (0.00 sec)
3.查看mycat版本
mysql> show @@version;
+-----------------------------------------+
| VERSION |
+-----------------------------------------+
| 5.6.29-mycat-1.6-RELEASE-20161028204710 |
+-----------------------------------------+
1 row in set (0.00 sec)
4.查看当前的库
mysql> show @@database;
+----------+
| DATABASE |
+----------+
| testdb |
+----------+
1 row in set (0.00 sec)
5.查看 MyCAT 的数据节点的列表,对应 schema.xml 配置文件的 dataNode 节点:
mysql> show @@datanode;
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | vm3306/db1 | 0 | mysql | 0 | 8 | 1000 | 244 | 0 | 0 | 0 | -1 |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
1 row in set (0.00 sec)
其中,“NAME”表示 dataNode 的名称;“dataHost”表示对应 dataHost 属性的值,即数据主机; “ACTIVE”表示活跃连接数;“IDLE”表示闲置连接数;“SIZE”对应总连接数量。
这里有 8 个空闲连接,去主从节点用 netstat -ntp 命令看看建立的连接情况:
master:
[root@vm1 ~]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 10.16.195.49 192.168.0.104:60060 ESTABLISHED 1492/sshd
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.121:58636 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.121:58640 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.121:58582 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.121:58644 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.121:58646 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.121:58641 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.121:58635 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.121:58632 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:10.16.195.49:3306 ::ffff:192.168.0.122:48205 ESTABLISHED 1414/mysqld
slave:
[root@vm3 ~]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 10.16.195.50:48205 192.168.0.120:3306 ESTABLISHED 1607/mysqld
tcp 0 0 10.16.195.50:22 192.168.0.104:60102 ESTABLISHED 1196/sshd
tcp 0 0 ::ffff:10.16.195.50:3306 ::ffff:192.168.0.121:45593 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:10.16.195.50:3306 ::ffff:192.168.0.121:45591 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:10.16.195.50:3306 ::ffff:192.168.0.121:45583 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:10.16.195.50:3306 ::ffff:192.168.0.121:45589 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:10.16.195.50:3306 ::ffff:192.168.0.121:45579 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:10.16.195.50:3306 ::ffff:192.168.0.121:45580 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:10.16.195.50:3306 ::ffff:192.168.0.121:45588 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:10.16.195.50:3306 ::ffff:192.168.0.121:45577 ESTABLISHED 1607/mysqld
可看到有很多从 mycat 服务器发起数据库连接(主有9个连接,从有8个连接)。
6.查看心跳报告:
mysql> show @@heartbeat;
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 10.16.195.49 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2020-02-18 06:44:38 | false |
| hostS1 | mysql | 10.16.195.50 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2020-03-18 06:44:38 | false |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
2 rows in set (0.00 sec)
该命令用于报告心跳状态
RS_CODE 状态:
OK_STATUS = 1;正常状态
ERROR_STATUS = -1; 连接出错
TIMEOUT_STATUS = -2; 连接超时
INIT_STATUS = 0; 初始化状态
若节点故障,会连续默认 5 个周期检测,心跳连续失败,就会变成-1,节点故障确认,然后可能发生切换
7.查看 Mycat 的前端连接状态,即应用与 mycat 的连接:
mysql> show @@connection\G
*************************** 1. row ***************************
PROCESSOR: Processor0
ID: 1
HOST: 127.0.0.1
PORT: 9066
LOCAL_PORT: 50317
SCHEMA: NULL
CHARSET: latin1:8
NET_IN: 257
NET_OUT: 6343
ALIVE_TIME(S): 1264
RECV_BUFFER: 4096
SEND_QUEUE: 0
txlevel:
autocommit:
1 row in set (0.00 sec)
从上面获取到的连接 ID 属性,可以手动杀掉某个连接。
kill @@connection id,id,id
8.显示后端连接状态:
mysql> show @@backend\G
...
...
...
*************************** 16. row ***************************
processor: Processor0
id: 4
mysqlId: 8
host: 192.168.0.122
port: 3306
l_port: 45583
net_in: 7018
net_out: 1646
life: 6287
closed: false
borrowed: false
SEND_QUEUE: 0
schema: db1
charset: utf8:33
txlevel: 3
autocommit: true
16 rows in set (0.00 sec)
一共有16个后端连接,这里截取最后一个。
9.显示数据源:
mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 10.16.195.49 | 3306 | W | 0 | 8 | 1000 | 231 | 0 | 2 |
| dn1 | hostS1 | mysql | 10.16.195.50 | 3306 | R | 0 | 8 | 1000 | 211 | 8 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
可以看到主从信息。
执行SQL语句
登录数据管理端口
mysql -h127.0.0.1 -utest -ptest -P8066
创建 users表:
mysql> show databases;
+------------+
| DATABASE |
+------------+
| AIDCDATA |
| AIHsMdlSrv |
+------------+
1 row in set (0.00 sec)
mysql> use AIDCDATA;create table users (id INT, name VARCHAR(20));
Database changed
Query OK, 0 rows affected (0.25 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| users |
+---------------+
1 row in set (0.01 sec)
插入两条数据:
mysql> insert into users values (1, 'guli'), (2, 'xie');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
查看一下插入结果:
mysql> select * from users;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
+------+------+
2 rows in set (0.00 sec)
没问题。
再分别到主从节点看数据插入没有:
master:
mysql> use AIDCDATA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------+
| Tables_in_AIDCDATA |
+--------------------+
| users |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from users;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
+------+------+
2 rows in set (0.00 sec)
slave:
mysql> use AIDCDATA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
mysql> select * from users;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
+------+------+
2 rows in set (0.00 sec)
好,可以看到 OK 了。
查看刚才执行过的 sql 语句:
mysql> show @@sql;
+------+------+---------------+--------------+-------------------+
| ID | USER | START_TIME | EXECUTE_TIME | SQL |
+------+------+---------------+--------------+-------------------+
| 1 | mycat_writer | 1485212346188 | 1 | select * from AIDCDATA |
| 2 | mycat_writer | 1485212040101 | 1 | select * from AIDCDATA |
| 3 | mycat_writer | 1485211834831 | 1 | select * from AIDCDATA |
| 4 | mycat_writer | 1485211803688 | 1 | select * from AIDCDATA |
| 5 | mycat_writer | 1485209518691 | 2 | select * from AIDCDATA |
+------+------+---------------+--------------+-------------------+
5 rows in set (0.00 sec)
遇到的问题:
似乎无法统计 insert 语句,不知为什么。
查看统计数据:
mysql> show @@sql.sum;
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
| ID | USER | R | W | R% | MAX | NET_IN | NET_OUT | TIME_COUNT | TTL_COUNT | LAST_TIME |
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
| 1 | mycat_writer | 5 | 0 | 1.00 | 1 | 85 | 709 | [5, 0, 0, 0] | [5, 0, 0, 0] | 1485212346189 |
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
1 row in set (0.00 sec)
端口号: 该命令工作在 9066 端口,用来记录用户通过本地 8066 端口向 Mycat-Server 发送的 SQL 请求执行
信息。信息包括有 ID 值,执行 SQL 语句的用户名称,执行的 SQL 语句,命令执行的起始时间,命令执行消耗时间
查看慢查询语句:
设置慢查询阈值为0:reload @@sqlslow=0;
mysql> reload @@sqlslow=0;
Query OK, 1 row affected (0.00 sec)
Reset show @@sql.slow time success
在8066端口执行查询:select * from users;
mysql> select * from users;
+------+-------+
| id | name |
+------+-------+
| 1 | guli |
| 2 | xie |
| 3 | xu |
| 4 | he |
| 5 | huang |
| 6 | ma |
| 7 | liu |
| 8 | zeng |
+------+-------+
8 rows in set (0.00 sec)
在 9066 端口执行 show @@sql.slow 查看抓取的慢查询SQL语句:
mysql> show @@sql.slow;
+------+------------+---------------+--------------+-------------------+
| USER | DATASOURCE | START_TIME | EXECUTE_TIME | SQL |
+------+------------+---------------+--------------+-------------------+
| test | NULL | 1485213017329 | 1 | select * from tb1 |
+------+------------+---------------+--------------+-------------------+
1 row in set (0.00 sec)
验证读写分离是否已经成功
使用mysql客户端连接9066管理端口,执行 show @@datasource 可以观察到 READ_LOAD,WRITE_LOAD 两个统计参数的变化:
这里显示 hostM1 为写节点,hostS1、hostS2 为读节点:
hostM1 的 WRITE_LOAD = 2
hostS1 的 READ_LOAD = 3
hostS2 的 READ_LOAD = 5
mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 10.16.195.49 | 3306 | W | 0 | 8 | 1000 | 287 | 0 | 2 |
| dn1 | hostS1 | mysql | 10.16.195.50 | 3306 | R | 0 | 8 | 1000 | 271 | 3 | 0 |
| dn1 | hostS1 | mysql | 10.16.195.51 | 3306 | R | 0 | 8 | 1000 | 271 | 5 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
使用mysql客户端连接8066管理端口,执行查询,插入语句,同时使用mysql客户端连接 9066 端口观察一下读写统计参数的变化:
8066:执行查询 select * from users;
mysql> select * from users;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
| 3 | xu |
| 4 | he |
+------+------+
4 rows in set (0.00 sec)
9066:
mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 10.16.195.49 | 3306 | W | 0 | 8 | 1000 | 287 | 0 | 2 |
| dn1 | hostS1 | mysql | 10.16.195.50 | 3306 | R | 0 | 8 | 1000 | 271 | 4 | 0 |
| dn1 | hostS1 | mysql | 10.16.195.51 | 3306 | R | 0 | 8 | 1000 | 271 | 5 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
读节点的读计数加1,
hostM1 的 WRITE_LOAD = 2
hostS1 的 READ_LOAD = 4
hostS2 的 READ_LOAD = 5
8066:执行插入操作 insert into users values (5,'huang');
mysql> insert into users values (5,'huang');
Query OK, 1 row affected (0.02 sec)
9066:
mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 10.16.105.49 | 3306 | W | 0 | 8 | 1000 | 332 | 0 | 4 |
| dn1 | hostS1 | mysql | 10.16.105.52 | 3306 | R | 0 | 8 | 1000 | 315 | 13 | 0 |
| dn1 | hostS2 | mysql | 10.16.105.51 | 3306 | R | 0 | 8 | 1000 | 315 | 13 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
写节点的读计数加1
hostM1 的 WRITE_LOAD = 3
hostS1 的 READ_LOAD = 4
hostS2 的 READ_LOAD = 5
由此可见读写分离是成功的。可以看到数据也成功写入数据库:
mysql> select * from users;
+------+-------+
| id | name |
+------+-------+
| 1 | guli |
| 2 | xie |
| 3 | xu |
| 4 | he |
| 5 | huang |
+------+-------+
5 rows in set (0.00 sec)
到此基本演示了 mycat 的主从读写分离功能,配置的前提是已经有一个配置好的 mysql 主从复制架构,mycat 工作于 mysql 主从架构的前端,负责 SQL 语句的分发。
6.Mycat服务踩坑
1.错误现象
查看mycat日志,一直显示连不上服务器
cat /usr/local/mycat/logs/mycat.log
错误内容
2020-03-18 17:52:34.787 INFO [$_NIOREACTOR-7-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql: select user()
2020-03-18 17:52:34.787 ERROR [$_NIOREACTOR-7-RW] (io.mycat.backend.heartbeat.MySQLHeartbeat.nextDecter(MySQLHeartbeat.jave:215)) - set Error 3 DBHsotConfig [hostName=hostS1, url=10.16.195.50:3306]
2020-03-18 17:02:03.787 INFO [S_NIOREACTOR-7-RW] (io.mycat.net.AbstractConnection.close (Abstractconnection.java:520)) - close connection, reason:stream closed,MySQLConnection[id=165, LastTime=1584522123784, user=root, schema=AIHsMdl
Srv, old shema=AIHsMdlSrv, borrowed=false, fromSlaveDB=true, threadId=0, charset=utf8, txIsoation=3, autocommit=true, attachment=null, respHandler=null, host=10.16.195.50, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-03-18 17:52:34.787 INFO [$_NIOREACTOR-7-RW] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionError(PhysicalDatasource.java508))
遇到这个错误,去网上搜罗了很多解决方法,最主要的说法是:
检查schema.xml中读写用户的帐号和密码,是否和数据库配置的一致。
我确认了几次,确实是一致的。
2.错误现象2
然后偶然通过navicate连接mysql服务的时候,遇到错误:
1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client
修改用户密码的加密规则,降到原来的规则。
alter user 'root'@'%' identified with mysql_native_password by "123456";
flush privileges;
重新连接就可以。
3.解决方法
那么是不是mycat也有这种问题呢,导致连接不上mysql服务器。
于是将3台mysql服务器的密码加密规则全部退回mysql_native_password。
4.重启mysql服务和mycat服务
service mysql restart
mycat restart