MyCAT实现MySQL读写分离

服务器名	IP地址	安装软件
Mycat-server	192.168.31.8	Mysql8.0
Mysql-Master	192.168.31.18	Mysql8.0
Mysql-Slave	192.168.31.28	Mysql8.0

1. Mycat安装

1.1 Mysql-server安装

yum install -y java mysql-server
mkdir /apps
cd /apps/

1.2 下载安装mycat

http://dl.mycat.org.cn/

wget http://dl.mycat.org.cn/1.6.7.6/20210831141727/Mycat-server-1.6.7.6-release-20210831170644-linux.tar.gz
tar xf Mycat-server-1.6.7.6-release-20210831170644-linux.tar.gz

1.3 配置环境变量

echo 'PATH=/apps/mycat/bin:$PATH' >/etc/profile.d/mycat.sh
source /etc/profile.d/mycat.sh

1.4 启动mycat

mycat start

默认使用8066端口

修改配置文件

vi /apps/mycat/conf/server.xml

修改端口

	<user name="root" defaultAccount="true">
   	<property name="password">Pana#123</property>
   	<property name="schemas">TESTDB</property>

1.5 修改密码

1.6 修改读写分离配置文件

vi /apps/mycat/conf/schema.xml

1表示读写分离

指定读写服务器

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" randomDataNode="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="192.168.31.18:3306" user="root" password="123456">
        <readHost host="host2" url="192.168.31.28:3306" user="root" password="123456"/>
        </writeHost>
    </dataHost>
</mycat:schema>

2. 创建数据库用户

systemctl start mysqld
mysql -uroot
create database mycat;
create user root@'192.168.31.%' identified by '123456';
grant all on *.* to 'root'@'192.168.31.%';
flush privileges;

2.1 查看读写日志

show varialbes like 'general_log';			#查看日志是否开启
set global general_log=on;				#开启日志
show varialbes like 'general_log_file';		#查看日志保存位置
set global general_log_file=/var/logs/mysql_general.log		#设置日志保存位置

配置文件中

[mysqld]
general_log=on

2.2 创建账号

在mycat上添加记录

[18:16:21 root@mycat-server ~]#mysql -uroot -p123456 -h192.168.31.8

2.3 写节点上开启日志

show variables like '%general%';
set global general_log=on;

修改密码

set password for root@localhost =password("Pana#123");

测试

update students set name="Zhazha hui" where stuid=31;

2.4 写节点主节点(18)

cat /var/lib/mysql/master.log

2.5 读节点从节点(28)