数据库中间件之Mycat（一）：搭建MySQL主从复制+读写分离等

本文详细介绍了Mycat的核心原理、安装配置、读写分离与主从复制应用，包括MyCat如何解耦数据库、处理高并发、以及配合MySQL实现数据库集群的搭建过程。

本系列文章是尚硅谷Mycat核心教程的学习笔记，仅供参考

常用数据库中间件

Cobar 属于阿里 B2B 事业群，始于 2008 年，在阿里服役 3 年多，接管 3000 + 个 MySQL 数据库的 schema, 集群日处理在线 SQL 请求 50 亿次以上。由于 Cobar 发起人的离职， Cobar 停止维护。
Mycat 是开源社区在阿里 cobar 基础上进行二次开发，解决了 cobar 存在的问题，并且加入了许多新的功能在其中。青出于蓝而胜于蓝。
OneProxy 基于 MySQL 官方的 proxy 思想利用 c 进行开发的， OneProxy 是一款商业收费的中间件。舍弃了一些功能，专注在性能和稳定性上。
kingshard 由小团队用 go 语言开发，还需要发展，需要不断完善。
Vitess 是 Youtube 生产在使用， 架构很复杂。不支持 MySQL 原生协议，使用需要大量改造成本。
Atlas 是 360 团队基于 mysql proxy 改写，功能还需完善，高并发下不稳定。
MaxScale 是 mariadb（MySQL 原作者维护的一个版本）研发的中间件
MySQLRoute 是 MySQL 官方 Oracle 公司发布的中间件

Mycat解决的问题

java与数据库紧耦合，把数据库的分布式从代码中解耦出来
高访问量高并发对数据库的压力
读写请求数据不一致

Mycat原理

Mycat 的原理中最重要的一个动词是 “拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

Mycat前期环境安装

安装Mycat，去这里按需下载，然后解压即可（我用的是Mycat-server-1.6.7.6-release-20210730131311-win.tar.gz这个版本）
注意安装目录不要有中文
安装MySQL，最好不要用v8.0+，网上说Mycat1.6不支持MySQL8.0，而且mycat是java写的，Java版本最好是Java8，不然可能会有很多坑（最开始我下载的1.6-RELASE那个文件夹下的，最后mycat实在连不上树莓派上的MySQL8.0，就换了上面说的版本才ok了）
我的MySQL在两台主机上，一个是树莓派上的MySQL8.0（系统是Ubuntu20.04），一个是win10的MySQL5.7（系统架构不一样，MySQL版本也不一样，最后配置主从复制竟然成功了😅）
数据库安装完成后，务必测试远程登录，MySQL相关问题参考文末

Mycat配置文件

schema.xml：配置读写主机的ip、端口、数据库用户密码等，定义逻辑库，表、分片节点等内容
- 注意datahost的balance属性，通过此属性配置读写分离的类型
- 负载均衡类型，目前的取值有4 种：
  - （1） balance=“0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
  - （2） balance=“1”，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1,S2 都参与 select 语句的负载均衡。
  - （3） balance=“2”，所有读操作都随机的在 writeHost、 readhost 上分发。
  - （4） balance=“3”，所有读请求随机的分发到 readhost 执行， writerHost 不负担读压力
- datahost节点的其他属性：
  - writeType=“0”: 所有写操作发送到配置的第一个writeHost，第一个挂了切换到生存的第二个
  - writeType=“1”，所有写操作都随机的发送到配置的 writeHost， 1.5 以后废弃不推荐
  - switchType=“1”: 1 默认值，自动切换。
rule.xml：定义分片规则
server.xml：定义用户以及系统相关变量，如端口等

修改Mycat配置

修改配置文件 server.xml，修改用户信息，与 MySQL 区分

…
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
…

修改配置文件 schema.xml，我的配置如下（一主一从配置，数据库是testdb）：

<?xml version="1.0"?>
<!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="host1" database="testdb" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="2"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.101.2:3306" user="root" password="root">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.101.4:3306" user="root" password="root" />
                </writeHost>
        </dataHost>
</mycat:schema>

启动Mycat

cmd进到bin目录，执行：mycat.bat install注册mycat服务到Windows，然后执行mycat.bat start启动mycat
mycat日志在logs文件夹下查看

登录Mycat

登录方式和mysql差不多：mysql -umycat -p123456 -P 8066 -h 192.168.101.2
- 注意 mycat 有两个端口，8066 数据端口，9066 管理端口
切换到逻辑库：use TESTDB;
查看表：show tables;
没有报错就ok了

MySQL主从复制搭建（一主一从）

通过 Mycat 和 MySQL 的主从复制配合搭建数据库的读写分离，实现 MySQL 的高可用性
所以首先要搭建MySQL的主从复制
MySQL主从复制是从接入点复制，不像redis是全部复制
MySQL主从复制原理如下：

主机配置

我的主机是win10下的MySQL5.7
配置后重启MySQL

#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT

从机配置

从机是Ubuntu下的MySQL8.0
配置后重启MySQL

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

在主机上建立帐户并授权 slave

登录MySQL后，执行命令：GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password';
查询master的状态：show master status;（记录下File和Position的值）

在从机上配置需要复制的主机

登录MySQL，执行以下命令：

CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

启动从服务器复制功能：start slave;
查看从服务器状态：show slave status\G;
能看到如下信息就ok了：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试主从复制、读写分离

主从复制测试：主机新建库testdb、新建表、 insert记录，从机查询是否同步即可
读写分离测试：
- 在写主机数据库表中插入带系统变量数据，造成主从数据不一致INSERT INTO mytbl VALUES(2,@@hostname);
- 在Mycat里查询mytbl表,可以看到查询语句在主从两个主机间切换

其他问题

停止从服务复制功能：stop slave;
重新配置主从：stop slave; reset master;

MySQL主从复制搭建（双主双从）

Slave1（简称s1）复制 Master1（简称m1）， Slave2（简称s2）复制 Master2（简称m2）
m1 用于处理所有写请求， s1、m2、s2负责所有读请求。当 m1 宕机后， m2 负责写请求， m1、 m2 互为备机
注意：由于上文配置过一主一从，需要重新配置双主双从，所以先stop slave，并reset master
删除之前建的testdb，一会儿重新建
这里我的m1在win10上，s1在Ubuntu上，m2、s2在docker里（通过映射宿主机的端口来登录），保证相互之间MySQL能登录就ok

Master1 配置

注意server-id=1
在原来的配置文件追加如下配置：

# 在作为从数据库的时候， 有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1， 取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

log-slave-updates
auto-increment-increment=2
auto-increment-offset=1

Master2 配置

同m1，但auto-increment-offset=2
修改server-id=3

Slave1 配置

修改server-id=2
其余配置同一主一从时的配置

Slave2 配置

修改server-id=4

重启MySQL

重启 4台 mysql 服务
在两台master上建立帐户并授权 slave（方法同上文）
分别记录下两个master的File和Position的值

在从机上配置需要复制的主机

Slava1 复制 Master1， Slava2 复制 Master2
复制命令同上文，只需修改变化的值
启动两台从服务器复制功能：start slave;
查看从服务器状态：show slave status\G;

两个主机互相复制

命令如下：（注意这里我还配了MASTER_PORT，因为是docker映射的端口，不配应该是没法登录的）

CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_PORT=3309,
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

启动两台主服务器复制功能：start slave;
查看从服务器状态：show slave status\G;

测试MySQL双主双从

master1新建库、表，并插入数据，看master2和slave1、2是否同步

测试Mycat读写分离

先修改schema.xml中的 balance 属性为1
由于现在是4台MySQL，所以要添加新的datahost结点，完整配置如下：

<?xml version="1.0"?>
<!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="host1" database="testdb" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.101.2:3306" user="root" password="root">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.101.4:3306" user="root" password="root" />
                </writeHost>
				<writeHost host="hostM2" url="192.168.101.2:3309" user="root" password="root">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.101.2:3310" user="root" password="root" />
                </writeHost>
        </dataHost>
</mycat:schema>

然后重启mycat
在写主机Master1数据库表中插入带系统变量数据，造成主从数据不一致
在Mycat里查询表，可以看到查询语句在Master2 、 Slava1 、 Slava2三个主机间切换，测试成功😃

测试高可用性

停止数据库Master1
在Mycat里插入数据依然成功（因为Master2自动切换为写主机了），注意这时在Mycat查询，只能看到s2的数据了，因为s1是复制m1（m1挂了），而m2此时只负责写数据
又启动数据库Master1
在Mycat里查询表，可以看到查询语句在m1 、 s1 、 s2三个主机间切换，m2此时只负责写数据了（注意如果重启m1后立刻查询，可以看到数据不一致的现象，过一会就完全同步了）

MySQL相关问题

mysql的配置文件中添加default-time_zone = '+8:00'，然后重启mysql，可以配置mysql的系统时间
mysql_secure_installation：用于安装完MySQL后配置root密码等
/var/log/mysql/error.log是MySQL的错误日志
MySQL配置bind-address='*'是开启远程登录的前提
Ubuntu20.04中MySQL8.0配置在/etc/mysql/mysql.conf.d/mysqld.cnf
win10中MySQL配置文件在C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
mysql配置一个用户远程访问数据库
- 切换数据库：use mysql;
- 创建用户：create user ‘username’@‘192.168.101.7’ identified by ‘password’;
- 授权：GRANT ALL PRIVILEGES ON . TO ‘username’@‘192.168.101.7’;
- flush privileges;
- select User, Host from user;
Mycat报错：can't connect to mysql server ,errmsg:Host '192.168.101.2' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'，在对应的MySQL中执行flush hosts;即可