Mycat中间件
环境
- 目前已准备好一主两从节点,使用GTID进行复制;
- 主机信息如下:
主机名 | 操作系统版本 | 主机IP地址 | 角色 | 安装组件 |
---|---|---|---|---|
master | Rocky Linux release 8.6 | 172.25.3.80 | master节点 | mysql-server 8.0.26 |
slave | Rocky Linux release 8.6 | 172.25.3.82 | slave节点 | mysql-server 8.0.26 |
slave02 | Rocky Linux release 8.6 | 172.25.3.83 | slave节点 | mysql-server 8.0.26 |
mycat | Rocky Linux release 8.6 | 172.25.3.85 | mycat节点 | java-1.8.0-openjdk-1.8.0.345、Mycat-server-1.6.7.4-release-20200105164103、mysql-8.0.26 |
下载安装JDK
[root@mycat ~]# yum -y install java
[root@mycat ~]# java -version
openjdk version "1.8.0_345"
OpenJDK Runtime Environment (build 1.8.0_345-b01)
OpenJDK 64-Bit Server VM (build 25.345-b01, mixed mode)
下载安装Mycat
[root@mycat ~]# wget https://github.com/MyCATApache/Mycat-Server/releases/download/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@mycat ~]# tar -xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local/
[root@mycat ~]# ls -l /usr/local/mycat/
total 12
drwxr-xr-x 2 root root 190 Oct 25 10:36 bin
drwxrwxrwx 2 root root 6 Oct 22 2019 catlet
drwxrwxrwx 4 root root 4096 Oct 25 10:36 conf
drwxr-xr-x 2 root root 4096 Oct 25 10:36 lib
drwxrwxrwx 2 root root 6 Jan 5 2020 logs
-rwxrwxrwx 1 root root 227 Jan 5 2020 version.txt
- 配置环境变量
[root@mycat ~]# cat /etc/profile.d/mycat.sh
PATH=/usr/local/mycat/bin:$PATH
[root@mycat ~]# source /etc/profile.d/mycat.sh
[root@mycat ~]# echo $PATH
/usr/local/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
- 启动mycat
[root@mycat ~]# mycat start
Starting Mycat-server...
- 查看启动日志文件,确认启动成功。
[root@mycat ~]# cat /usr/local/mycat/logs/wrapper.log
STATUS | wrapper | 2022/10/25 10:44:42 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/10/25 10:44:42 | Launching a JVM...
INFO | jvm 1 | 2022/10/25 10:44:43 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/10/25 10:44:43 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/10/25 10:44:43 |
INFO | jvm 1 | 2022/10/25 10:44:45 | MyCAT Server startup successfully. see logs in logs/mycat.log
- 查看端口号
[root@mycat ~]# ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* users:(("java",pid=2791,fd=4))
LISTEN 0 50 *:1984 *:* users:(("java",pid=2791,fd=67))
LISTEN 0 100 *:8066 *:* users:(("java",pid=2791,fd=97))
LISTEN 0 50 *:37321 *:* users:(("java",pid=2791,fd=68))
LISTEN 0 100 *:9066 *:* users:(("java",pid=2791,fd=93))
LISTEN 0 50 *:37591 *:* users:(("java",pid=2791,fd=66))
初次使用Mycat
- 使用默认配置来连接
mycat
[root@mycat ~]# mysql -u root -p123456 -h 172.25.3.85 -P 8066
## 查看到mycat上有一个虚拟库
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> show tables from TESTDB;
+------------------+
| Tables in TESTDB |
+------------------+
| address |
| travelrecord |
+------------------+
2 rows in set (0.01 sec)
## 虚拟库中的表无法查看数据
mysql> select * from TESTDB.travelrecord;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0
修改Mycat 配置文件server.xml
- 修改Mycat连接端口号
注意:修改Mycat连接端口是为了使用 ‘mysql’ 命令方便连接。
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!-- ## 方法二:或者取消注释
<property name="serverPort">8066</property> <property name="managerPort">9066</property> ##方法二:或者修改'serverPort' 的端口号为'3306'
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查 ## 删除'5* 60 * 1000L' 注释
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
--> ##方法二:或者取消这里的注释'-->'
<property name="serverPort">3306</property> ## 方法一:从上面复制此行,将端口改为3306,或者取消上面的注释。
<!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布
式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
- 修改连接Mycat的账号密码
解释:
- ‘user name’:表示连接Mycat的用户账号;
- ‘password’:表示连接Mycat 的用户密码;
- ‘schemas’:表示连接Mycat 后显示的虚拟数据库(MySQL真正的数据库映射至指定的名称);这里指定的数据库名称需要对应 ‘schema.xml’ 配置文件中指定的 ‘schema name=<对应其值>’
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
......(省略)
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
修改schema.xml配置文件实现读写分离
- 创建Mycat 连接主从节点的账号
- 在
master
节点上创建账号后同步至所有从节点,在主节点上创建admin
账号,并授权hellodb
数据库。
- 在
mysql> create user 'admin'@'172.25.3.%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> grant all on hellodb.* to 'admin'@'172.25.3.%';
Query OK, 0 rows affected (0.01 sec)
- 修改
schema.xml
配置文件
解释:
- <schema
- ‘name’:需要对应 ‘server.xml’ 中 ‘schema’ 字段指定的数据库名称;
- ‘dataNode’:指定的数据库(自定义名称,之后会引用此名称);
- <dataNode
- ‘name’:对应’schema’ 定义的 'dataNode’的值;
- ‘dataHost’:指定mysql数据库的主机地址(自定义名称,之后会引用此名称);
- ‘database’:表示 ‘schema’ 指定的’name’ 数据库名称映射为此规定的数据库(即:TESTDB -> ‘hellodb’);
- <dataHost
- ‘name’:对应 ‘dataNode’ 定义的 ‘dataHost’ 的值;
- ‘balance’:其值为1,读请求随机分发到当前 ‘writeHost’ 对应的 ‘readHost’ 和 ‘备用(standby)WriteHost’ 上(即:当双主双从模式下【M1 -> S1、M2 -> S2,并且M1与M2互为主备】,M2、S1、S2都参与 'select’语句的负载均衡)。
- <writeHost 和 <readHost
- ‘host’:自定义名称;
- ‘url’:指定连接 MySQL服务的主机地址和端口号;
- ‘user’:指定连接MySQL服务的用户;
- ‘password’:指定连接MySQL服务的密码。
<mycat:schema xmlns:mycat="http://io.mycat/">
## 注销此行
<!-- <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1"> -->
## 添加如下信息,将'checkSQLschema'的值修改,将'randomDataNode' 改为 'dataNode'
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" randomDataNode="dn1">
##注销此行
<!-- <table name="travelrecord,address" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames ="true"/> -->
......(省略)
## 插入如下行
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<!-- ## 注销下面几行
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
-->
......(省略)
<!-- <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" --> ##注销此行
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" ##新增此行并修改'balance'的值
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<!--
<writeHost host="hostM1" url="localhost:3306" user="root"
password="">
</writeHost>
--> ##注销并新增MySQL读写节点
<writeHost host="hostM1" url="172.25.3.80:3306" user="admin"
password="123456" >
<readHost host="hostM2" url="172.25.3.82:3306" user="admin"
password="123456" />
<readHost host="hostM3" url="172.25.3.84:3306" user="admin"
password="123456" />
</writeHost>
</dataHost>
- 重启Mycat
[root@mycat ~]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
## 以下为重启日志。
STATUS | wrapper | 2022/10/25 14:04:19 | TERM trapped. Shutting down.
STATUS | wrapper | 2022/10/25 14:04:20 | <-- Wrapper Stopped
STATUS | wrapper | 2022/10/25 14:04:21 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/10/25 14:04:21 | Launching a JVM...
INFO | jvm 1 | 2022/10/25 14:04:22 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/10/25 14:04:22 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/10/25 14:04:22 |
INFO | jvm 1 | 2022/10/25 14:04:24 | MyCAT Server startup successfully. see logs in logs/mycat.log
验证读写分离
- 先连接
mycat
查看虚拟库
[root@mycat ~]# mysql -uroot -p123456 -h 172.25.3.85
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
## 这里发现了一个错误,将'schama.xml'中'dataName'的值直接改为后面MySQL的数据库。
mysql> show tables from TESTDB;
ERROR 1049 (HY000): Unknown database 'dn1'
## 或者使用如下方法查看。
mysql> use TESTDB
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_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
- 通过
MySQL
的通用日志确认是否实现了读写分离,在所有MySQL
中动态开启通用日志并查看
## 永久开启
[mysqld]
general_log=ON
## 临时开启
### master主节点
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.01 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.03 sec)
mysql> show global variables like 'general_log%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/master.log |
+------------------+---------------------------+
2 rows in set (0.08 sec)
### slave从节点
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'general_log%';
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/mysql-slave.log |
+------------------+--------------------------------+
2 rows in set (0.02 sec)
- 在
hellodb
中创建一个表,查看是否在主节点上进行写入
注意:‘select user()’ 是Mycat 的健康性检查。
## mycat 创建表
mysql> status;
Current database: hellodb
mysql> create table t1(id int not null primary key,name varchar(10) null);
Query OK, 0 rows affected (0.13 sec)
## 查看主节点通用日志
[root@master ~]# tail -f /var/lib/mysql/master.log
2022-10-25T06:54:25.267613Z 49 Query select user()
2022-10-25T06:54:31.552192Z 54 Query create table t1(id int not null primary key,name varchar(10) null)
- 在
mycat
上查看server-id
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 82 |
+-------------+
1 row in set (0.01 sec)
mysql> select @@hostname;
+-------------+
| @@hostname |
+-------------+
| mysql-slave |
+-------------+
1 row in set (0.01 sec)
[root@mysql-slave ~]# tail -f /var/lib/mysql/mysql-slave.log
2022-10-25T07:17:31.316842Z 38 Query select @@server_id
2022-10-25T07:17:32.581290Z 40 Query select user()
- 分别停止主从节点服务,查看写入读取操作的实现。
## 停止所有从节点,然后连接mycat读取数据,读入的数据发现在master节点上
[root@mysql-slave ~]# systemctl stop mysqld
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 80 |
+-------------+
1 row in set (0.00 sec)
## 启动从节点,停止所有的master节点,连接mycat进行数据写入,数据无法写入。
[root@master ~]# systemctl stop mysqld
mysql> insert into t1(name) values('test');
ERROR 1184 (HY000): java.net.ConnectException: Connection refused