实战案例：利用 Mycat 实现 MySQL 的读写分离

一.环境准备

mycat-server 10.0.0.6 #内存建议2G以上
mysql-master 10.0.0.5 MySQL 8.0
mysql-slave  10.0.0.4 MySQL 8.0

systemctl stop firewalld
setenforce 0
时间同步

二.详细配置

1、创建 MySQL 主从数据库

[root@centos8 ~]#yum -y install mysql-server

1) 修改master和slave上的配置文件

#master上的my.cnf
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
server-id = 5
log-bin

#slave上的my.cnf
[mysqld]
server-id = 4

[root@centos8 ~]#systemctl start mysqld

2) Master上创建复制用户

[root@centos8 ~]#mysql
mysql> create user repluser@'10.0.0.%' identified by '123456'; 
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
mysql> FLUSH PRIVILEGES;    
mysql> show master status;

3) Slave上执行

[root@centos8 ~]#mysql -uroot -p
mysql> CHANGE MASTER TO
->     MASTER_HOST='10.0.0.%',
->     MASTER_USER='repluser',
->     MASTER_PASSWORD='replpass',
->     MASTER_LOG_FILE='localhost-bin.000002',
->     MASTER_LOG_POS=849;
mysql> start slave;
mysql> show slave status\G

2、在MySQL代理服务器10.0.0.8安装mycat并启动

[root@centos8 ~]#yum -y install java 
#确认安装成功
[root@centos8 ~]#java -version

#下载并安装
[root@centos8 ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@centos8 ~]#mkdir /apps
[root@centos8 ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

#配置环境变量
[root@centos8 ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos8 ~]#source /etc/profile.d/mycat.sh

#查看端口
[root@centos8 ~]#ss -ntl

#启动mycat
[root@mycat ~]#file /apps/mycat/bin/mycat

#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动
[root@centos8 ~]#mycat start
Starting Mycat-server...

#可以看到打开多个端口,其中8066端口用于连接MyCAT
[root@centos8 ~]#ss -ntlp

#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@centos8 ~]#tail /apps/mycat/logs/wrapper.log

#用默认密码123456来连接mycat
[root@centos8 ~]#mysql -uroot -p123456 -h 10.0.0.8 -P8066

3、在mycat 服务器上修改server.xml文件配置Mycat的连接信息

[root@centos8 ~]#vim /apps/mycat/conf/server.xml

#修改下面行的8066改为3306复制到到独立非注释行
<property name="handlelDistributedTransactions">0</property>
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>

4、修改schema.xml实现读写分离策略

[root@centos8 ~]#vim /apps/mycat/conf/schema.xml
<?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="localhost1" database="hellodb" />
 <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.5:3306" user="root" password="123456">
 <readHost host="host2" url="10.0.0.4:3306" user="root" password="123456" />
 </writeHost>
   </dataHost>
</mycat:schema>

#重新启动mycat
[root@centos8 ~]#mycat restart

上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.5为主库，10.0.0.4为从库

注意：要保证能使用root/123456权限成功登录10.0.0.5和10.0.0.4机器上面的mysql数据库。同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！

5、在后端主服务器创建用户并对mycat授权

[root@centos8 ~]#mysql
mysql> create user root@'10.0.0.%' identified by '123456'; 
mysql> grant all on *.* to root@'10.0.0.%';
mysql> flush privileges;

6、在Mycat服务器上连接并测试

[root@centos8 ~]#mysql -uroot -p123456 -h127.0.0.1 TESTDB
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |   //只能看一个虚拟数据库
+----------+
mysql> use TESTDB;
mysql> create table t1(id int);
MySQL> select @@server_id;
MySQL> select @@hostname;

7、通过通用日志确认实现读写分离

在mysql中查看通用日志

show variables like 'general_log';  #查看日志是否开启
set global general_log=on;    #开启日志功能
show variables like 'general_log_file'; #查看日志文件保存位置
set global general_log_file='tmp/general.log'; #设置日志文件保存位置

在主和从服务器分别启用通用日志，查看读写分离

[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
general_log=ON

[root@centos8 ~]#systemctl restart mysqld
[root@centos8 ~]#tail -f /var/lib/mysql/centos8.log

8、停止从节点，MyCAT自动调度读请求至主节点

[root@slave ~]#systemctl stop mariadb
[root@client ~]#mysql -uroot -p123456 -h10.0.0.6 -P8066
MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          1 |
+-------------+
1 row in set (0.00 sec)

#停止主节点，MyCAT不会自动调度写请求至从节点
MySQL [TESTDB]> insert teachers values(5,'wang',30,'M');
ERROR 1184 (HY000): java.net.ConnectException: Connection refused

9、MyCAT对后端服务器的健康性检查方法select user()

#开启通用日志
[root@master ~]#mysql
mysql> set global  general_log=1;

[root@slave ~]#mysql
mysql> set global  general_log=1;

#查看通用日志
[root@master ~]#tail -f /var/lib/mysql/master.log

[root@slave ~]#tail -f /var/lib/mysql/slave.log

三.总结

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