8 MySQL中间件代理服务器-Mycat
实验拓扑:
图片.png
MySQL: 5.7.31版本
Mycat: 1.6.7.4
实验步骤:
- 搭建主从
主节点10.0.0.52
[mysqld]
server-id=52
log-bin=/data/mysql/mysql-bin
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[12:34:21 root@master ~]#service mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL.. [ OK ]
[12:34:53 root@master ~]#mysql -e 'show master logs';
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by '000000';
Query OK, 0 rows affected, 1 warning (0.01 sec)
从节点10.0.0.53
[mysqld]
server-id=53
log-bin=/data/mysql/mysql-bin
read-only=ON
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[12:39:19 root@slave ~]#service mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.52',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='000000',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.52
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 448
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 614
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| repluser | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
测试主从同步
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
- 搭建mycat节点
[12:31:49 root@mycat ~]#yum -y install java mysql
[12:51:00 root@mycat ~]#wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[12:54:04 root@mycat ~]#mkdir /apps
[12:54:35 root@mycat ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
[12:55:45 root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[12:56:12 root@mycat ~]#source /etc/profile.d/mycat.sh
[12:56:40 root@mycat ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
[12:56:58 root@mycat ~]#mycat start
Starting Mycat-server...
[12:46:13 root@mycat ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 1 127.0.0.1:32000 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 50 [::]:34047 [::]:*
LISTEN 0 50 [::]:1984 [::]:*
LISTEN 0 100 [::]:8066 [::]:*
LISTEN 0 50 [::]:39657 [::]:*
LISTEN 0 100 [::]:9066 [::]:*
[12:46:17 root@mycat ~]#tail /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2021/06/16 12:46:13 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/06/16 12:46:13 | Launching a JVM...
INFO | jvm 1 | 2021/06/16 12:46:13 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/06/16 12:46:13 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/06/16 12:46:13 |
INFO | jvm 1 | 2021/06/16 12:46:15 | MyCAT Server startup successfully. see logs in logs/mycat.log
- mycat对外就是一个数据库服务器, 客户端可以通过mysql协议与mycat连接, 只是mycat接受到请求后不会处理, 而是转到后端服务器处理
- 安装mycat后, 会默认创建TESTDB数据库, 用户名root, 密码123456, 端口8066, 客户端可以直接通过该账户登录mycat
- mycat的TESTDB会最终映射到后端真实数据库, 用户可以通过TESTDB最终访问后端数据库
[12:49:20 root@client ~]#yum -y install mysql
[12:49:39 root@client ~]#mysql -uroot -p123456 -h10.0.0.51 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.02 sec)
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 TESTDB |
+------------------+
| address |
| travelrecord |
+------------------+
2 rows in set (0.00 sec)
#由于我们还没有配置后端服务器的连接, 因此现在的数据库是没有数据的虚拟数据库
MySQL [TESTDB]> select * from address;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0
- 配置mycat节点
3.1 在mycat服务器上修改server.xml文件, 配置mycat的连接信息
[13:02:40 root@mycat ~]#vim /apps/mycat/conf/server.xml
#默认信息
<user name="root" defaultAccount="true"> #客户端连接Mycat的用户名
<property name="password">123456</property> #客户端连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名和schema.xml里的名字要相对应
<property name="defaultSchema">TESTDB</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>
- 这里使用的是root, 密码默认123456, 逻辑数据库TESTDB, 这些信息都可以自定义, 读写权限都有, 没有针对表做任何特殊的权限
[13:18:20 root@mycat ~]#vim /apps/mycat/conf/schema.xml
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
3.2 修改schema.xml实现读写分离策略
[13:53:10 root@mycat ~]#vim /apps/mycat/conf/schema.xml
<!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="localhost1" database="mycat"/> #mycat数据库要在后端数据库创建, 指定客户端连接mycat上的TESTDB, 进而连接到后端服务器的mycat数据库. 实际工作中, 需要根据后端是哪个数据库, 进行指定. 这里用mycat数据库进行演示
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> #balance="1", 表示读写分离
<heartbeat>select user()</heartbeat> #mycat会定期连接到后端数据库, 判断mysql是否存活
<writeHost host="host1" url="10.0.0.52:3306" user="root" password="123456"> #需要在主节点创建能远程访问的账户, mycat会使用这个账号连接主节点
<readHost host="host2" url="10.0.0.53:3306" user="root" password="123456"/> #需要在从节点创建能远程访问的账户, mycat会使用这个账号连接从节点
</writeHost>
</dataHost>
</mycat:schema>
# 直接把配置文件做个备份, 然后用以下内容覆盖即可
[13:01:39 root@mycat /apps/mycat/conf]#vim schema.xml
<!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="localhost1" database="mycat"/>
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.52:3306" user="root" password="123456">
<readHost host="host2" url="10.0.0.53:3306" user="root" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
#mycat/conf中的所有文件都是777权限
[13:52:43 root@mycat ~]#chmod 777 /apps/mycat/conf/schema.xml
3.3 重启mycat
[13:54:49 root@mycat ~]#mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[13:55:07 root@mycat ~]#tail /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2020/11/26 12:57:21 |
INFO | jvm 1 | 2020/11/26 12:57:24 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper | 2020/11/26 13:54:53 | TERM trapped. Shutting down.
STATUS | wrapper | 2020/11/26 13:54:54 | <-- Wrapper Stopped
STATUS | wrapper | 2020/11/26 13:54:55 | --> Wrapper Started as Daemon
STATUS | wrapper | 2020/11/26 13:54:55 | Launching a JVM...
INFO | jvm 1 | 2020/11/26 13:54:56 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2020/11/26 13:54:56 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2020/11/26 13:54:56 |
INFO | jvm 1 | 2020/11/26 13:54:58 | MyCAT Server startup successfully. see logs in logs/mycat.log
3.4 在后端主服务器创建mycat数据库,对mycat授权
要保证后端服务器能用root:123456登录mysql数据库, 同时也要授权mycat节点能使用root:123456登录后端mysql. 否则会导致登录mycat后, 对表和库操作失败
这个root用户是给mycat使用的, mycat会代替程序连接后端服务器, 而前段的程序的连接用户是不一样的
需要和mycat配置文件中定义的账户和密码一致
mysql> create database mycat;
Query OK, 1 row affected (0.00 sec)
# 这里创建mycat, 要和配置文件中的一致
# <dataNode name="dn1" dataHost="localhost1" database="mycat"/>
mysql> grant all on *.* to root@'10.0.0.%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
#验证从节点同步到数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mycat |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| repluser | 10.0.0.% |
| root | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.01 sec)
3.5 启用通用日志, 确认实现了读写分离
- 这时可以看到mycat会不断地向后端服务器发送select user(); 来判断后端服务器是否存活, 每10秒探测一次
#主节点
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
#从节点
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
[14:13:17 root@master ~]#tail -f /data/mysql/master.log
/usr/local/mysql/bin/mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
2020-11-26T06:12:58.767063Z 111 Query select user()
2020-11-26T06:13:08.766786Z 111 Query select user()
2020-11-26T06:13:17.020027Z 96 Quit
2020-11-26T06:13:18.769421Z 111 Query select user()
2020-11-26T06:13:28.769830Z 111 Query select user()
2020-11-26T06:13:38.768821Z 111 Query select user()
2020-11-26T06:13:48.767409Z 111 Query select user()
2020-11-26T06:14:58.794391Z 113 Connect root@10.0.0.51 on mycat using TCP/IP
2020-11-26T06:14:58.798255Z 114 Connect root@10.0.0.51 on mycat using TCP/IP
2020-11-26T06:14:58.804344Z 112 Connect root@10.0.0.51 on mycat using TCP/IP
[14:14:49 root@slave ~]#tail -f /data/mysql/slave.log
2020-11-26T06:13:19.285994Z 84 Quit
2020-11-26T06:13:28.776945Z 101 Query select user()
2020-11-26T06:13:38.776032Z 101 Query select user()
2020-11-26T06:13:48.775052Z 101 Query select user()
2020-11-26T06:13:58.774529Z 101 Query select user()
2020-11-26T06:14:08.778030Z 101 Query select user()
2020-11-26T06:14:18.776534Z 101 Query select user()
2020-11-26T06:14:28.776300Z 101 Query select user()
2020-11-26T06:14:38.775988Z 101 Query select user()
2020-11-26T06:14:48.777638Z 101 Query select user()
2020-11-26T06:14:58.795109Z 102 Connect root@10.0.0.51 on mycat using TCP/IP
2020-11-26T06:14:58.795577Z 103 Connect root@10.0.0.51 on mycat using TCP/IP
2020-11-26T06:14:58.807600Z 104 Connect root@10.0.0.51 on mycat using TCP/IP
3.6 主节点创建数据表t1
mysql> use mycat;
Database changed
mysql> create table t1(id int,name char(10));
Query OK, 0 rows affected (0.02 sec)
客户端连接TESTDB可以看到t1表
MySQL [TESTDB]> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
3.7 客户端连接mycat测试读写分离
测试1:
- 主节点在mycat数据库中, 创建的信息, 用户可以通过mycat上的TESTDB库看到
[13:41:50 root@client ~]#mysql -uroot -p123456 -h10.0.0.51 -P8066
mysql> use TESTDB;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.01 sec)
测试2:
- 客户端使用select @@server_id; 会查看到从服务器的id, 说明了读操作被调度到了从节点
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 53 |
+-------------+
1 row in set (0.02 sec)
# 查看从节点通用日志
2020-11-26T06:26:17.587241Z 105 Query SET names utf8;
2020-11-26T06:26:17.587456Z 105 Query select @@server_id
2020-11-26T06:26:18.408410Z 106 Query SET names utf8;
2020-11-26T06:26:18.408934Z 106 Query select @@server_id
测试3:
- 客户端插入数据, 请求会被转发到主节点
mysql> insert t1 value (1,'haha');
Query OK, 1 row affected (0.18 sec)
# 查看主节点日志
2020-11-26T06:27:40.435921Z 114 Query SET names utf8;
2020-11-26T06:27:40.436237Z 114 Query insert t1 value (1,'haha')
# 查看从节点日志
2021-06-16T05:16:25.003984Z 4 Query BEGIN
2021-06-16T05:16:25.017902Z 4 Query COMMIT /* implicit, from Xid_log_event */
# 从节点会从主节点同步插入信息
停止丛节点, mycat自动调度读请求至主节点, 但是有延迟时间, 此时读写不受影响
停止主节点, mycat不会自动调度写请求至从节点, 此时无法写数据, 只能读数据
此架构的问题
- mycat存在单点故障
- 主服务器存在单点故障, 主服务器故障, 数据只能读, 无法写
解决方案
- 双mycat组成keepalive集群, 对外发布虚拟vip, 两个服务器共享虚拟vip, 用户访问的是vip地址
- 后端数据库采用双主架构, 通过前端分离器实现写操作往一个服务器调度, 读操作往另一个服务器调度. 此时正常情况下, 主节点的写操作会被同步到另一个主节点. 一旦负责写操作的主节点宕机, 另一个主节点可以接受写操作,不过需要手动切换, 或者通过程序实现, 不过另一个主的配置已经做好了,所以无需再配置.
- mycat通过keepalive实现高可用, 并且配置相同的后端主从服务器
- 在mycat上利用策略监控后端主服务器, 一旦检测到主服务器宕机, 就把写请求转到备用的主服务器上. 比如, 用MHA实现高可用集群时, MHA管理节点可以通过master_ip_failover脚本来自动切换vip, 使得vip运行在可用的主节点上, 这样mycat中就不用写死主节点的ip地址了,而是写MHA提供的vip, 否则一旦主挂了, mycat还要手动切换主节点把写请求转发到另一个备用的主节点上
双主模型拓扑:
image.png
