MySQL 读写分离配置实践

文章目录

一、环境准备

master（虚拟机centos7，NAT模式，固定ip）：192.168.131.129
slave（win10，路由器局域网，DHCP协议）：192.168.31.27

1. 由于MyCat是用Java写的，需要JDK1.7版本以上
2. MySQL的root账户有远程访问权限

1. 查看主从复制状态

读写分离基于主从复制，查看主从复制状态

2. 查看JDK版本

3. 打开root的远程连接权限

一般MySQL Server和代理中间件是不在一台机器上的，涉及数据库的远程访问和连接

我们可以拿root进行连接，也可以创建新的用户进行连接

root用户默认是localhost，只能本地连接，不支持远程连接，所以需要root远程连接的权限打开。%表示允许任意地址连接，如果缩小权限，写成MyCat所在机器的ip地址就可以，用root连接MySQL服务器

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with
grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>

[root@localhost Downloads]# service mysqld restart

我们的MyCat和主库跑在同一台Linux上

4. 安装MyCat

安装​​lrzsz​​，用于windows和Linux传输文件（xftp也行）

用​​rz​​命令将MyCat包传输到Linux

Linux上的文件上传到Windows：​​sz+文件路径​​​解压MyCat包放到合适的目录下，可以放到​​/usr/local​​下

​​mycat/bin​​：放的是可执行文件

​​mycat/conf​​：放的MyCat的配置文件

​​mycat/logs​​：放的MyCat的日志文件

• wrapper.log：记录启动过程中遇到的错误
• mycat.log：记录运行过程中遇到的错误

由于我们是直接解压的，没有安装，为了不用手动指定mycat的路径，我们 在​​/usr/bin​​下建立软连接，连接用户目录下的mycat和我们解压路径下的mycat

这样就不用指定路径，直接使用mycat

二、配置文件

配置文件在​​mycat/conf​​下

1. server.xml

配置登录Mycat的账号信息，功能很强大，还可以配置白名单黑名单，限制客户的连接等

用户配置

不需要和MySQL的账号密码一样，因为我们的MySQL Client直接访问的是MyCat，再由MyCat登录MySQL Server

USERDB是逻辑库，由于MySQL Client访问的是MyCat，在MyCat上直接操作USERDB这个库即可，这个库其实是不存在的，这个库最终会映射到MySQL Server真实的MySQL库表上

这个逻辑库看起来好像在MyCat一台机器上，实际上经过分库分表操作可能分配在不同的机器上，我们只需要操作这个逻辑库就可以，其他的不用关心。多个逻辑库的话，用逗号分隔开

防火墙配置

2. schema.xml

schema.xml用于配置逻辑库和数据源、续写分离、分库分表信息等

​​schema.xml​​配置以下三点：

• 逻辑库和逻辑表：MySQL Client都是操作的MyCat上的逻辑库（schema）和逻辑表
• 数据节点：这个库或者表的内容放在哪个节点（dataNode）上，这个节点对应具体的物理机器（dataHost）

以下三个地方需要相同（其中逻辑库、数据节点以及数据库主机名称都可以随便取）

• ​​maxCon、minCon​​：MyCat内置连接池的最大、最小连接量
• ​​balance​​​：
  0：不开启读写分离
  1：全部的readHost和stand by writeHost参与select语句的负载 ，比如2套1主2从，M1叫做writeHost，S1、S2、S3、S4 叫做readHost，M2叫做stand by writeHost
  2：所有读操作随机在readHost和writeHost上分发
  3：所有读请求随机分发到writeHost对应的readHost上执行（最常使用，所有的select操作都在从库上执行，主库只做写操作）
• ​​writeType​​： 0表示所有写操作发送到配置的第一个writeHost，第一个挂掉切换到还在的第二个 writeHost
• ​​switchType​​​（切换的类型，当一个master挂了，切换到另一个master上）：
  -1：不自动切换
  1：根据心跳select user()自动切换
  2：基于MySQL的主从同步状态决定是否进行切换，即MyCat发送show slave status给MySQL Server
• ​​writeHost、readHost​​：配置写服务器（master）和读服务器（slave），readHost标签在writeHost内，表示读服务器是slave，图中黄色框中是配置了一个一主一从，嵌套多个readHost标签就是配置一主多从。图中并列的writeHost标签表示备份的写库，当master宕机后，slave也将无法和master配合工作，会切换到备份的写库继续工作。其实图中配置的是多主多从
• ​​heartbeat​​：MyCat定时发送指定语句给MySQL Server，如果能正常返回数据，则表示正常工作；若不能正常返回数据，则表示机器故障，MyCat需要进行容灾切换

如果slave有问题，master是正常，就会在master上做读和写操作
如果master有问题，slave是正常，此时slave是没法单独使用的，它会在多主多从的配置中找下一套主从配置来使用
如果主从都正常，master做写操作，slave做读操作

三、启动服务

查看配置文件​​mycat/conf/schema.xml​​

启动MyCat服务

查看端口

这表示mycat正常监听8066和9066端口

1. 配置文件问题一

​​mycat/schema.xml​​中备份的主库没有结束标签

配置好后，我们重启mycat程序

查看​​mycat/logs/warpper.log​​，记录了mycat启动过程中的错误

2. 配置文件问题二

​​mycat/schema.xml​​中读库的端口出错

配置好后，我们重启mycat程序

查看​​mycat/logs/warpper.log​​，记录了mycat启动过程中的错误

我们看到心跳不成功了，就应该判断是网络原因，或者是ip:port配置原因，于是我们看到了3309端口，就知道是配置的端口错误

四、MyCat 9066端口和8066端口

我们开启mycat后台服务

1. 9066管理端口

在Linux Shell下登录mycat的9066端口（使用​​mycat/conf/server.xml​​中配置的登录用户名和密码登录）

登录mycat后也是进入了一个MySQL Shell，monitor表示状态监控

​​show help​​显示mycat支持的命令

查看逻辑库：​​show @@database​​

查看逻辑节点和真实库的映射关系​​show @@datanode​​

查看数据源​​show @@datasource​​

2. 8066数据端口

在Linux Shell下登录mycat的8066端口（使用​​mycat/conf/server.xml​​中配置的登录用户名和密码登录）

OpenCloundDB表示我们看到的是一个云状数据库，云后面是如何提供的库表的服务能力，我们是不知道的。mycat就是云DB，把后端所有的细节给客户端隐藏了，客户端只需要去处理代理服务器上的DB就可以了。可以看作一个反向代理服务器

查看数据库

这个逻辑库USERDB对应的就是真实库mytest

五、验证读写分离

查看查询日志general_log，这个日志记录了MySQL Server收到的所有SQL语句

1. 打开查询日志general_log

打开windows从库上的general_log

在Linux下的MySQL Server中也打开一下查询日志

2. 验证读操作在slave

我们现在登录MyCat 8066数据端口，查询user表

在Linux下的master服务器查看general_log，我们只看见了mycat发送的心跳包，并没有看见查询user表的SQL

在windows下的slave服务器中查看general_log，看到了mycat发送的查询user表的SQL

没有问题，现在读操作是正确发送给了slave

3. 验证写操作在master

我们现在登录MyCat 8066数据端口，给user表insert一条数据

在Linux下的master服务器查看general_log，我们看见了insert数据的SQL

在windows下的slave服务器中查看general_log，没有发现insert数据的SQL

没有问题，写操作正确发送给了master

4. 验证容灾功能

我们在​​mycat/conf/schema.xml​​中配置的是多住多从，M1挂了，读写操作会全部转发到M2

在我们当前环境中，就是Linux上的MySQL Server挂了，所有的读写操作都会转发给Windows上的MySQL Server

关闭Linux的mysqld服务，相当于关闭了master

我们现在登录MyCat 8066数据端口，对user表分别读写操作

查看我们多主多从中备用系统的general_log，即Windows上的MySQL Server的general_log

可以看见，由于master挂了，读写操作都被转发到了备用的Windows上的MySQL Server，证明容灾没有问题