Mariadb读写分离

修改主机名

[root@vm1 ~]# hostnamectl set-hostname vm1
[root@vm2 ~]# hostnamectl set-hostname vm2
主机映射

[root@vm1/2 ~]# vim /etc/hosts
192.168.100.10 vm1
192.168.100.20 vm2
安装java环境

[root@mysql1 ~]# yum install -y java java-devel
解压Mycat

[root@vm1 ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /opt/
配置文件

[root@vm1 ~]# vim /opt/mycat/conf/schema.xml
<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="mysql" checkSQLschema="true" sqlMaxLimit="100"
	dataNode="dn1"></schema>
    <dataNode name="dn1" dataHost="localhost1" database="mysql" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql"
	dbDriver="native" writeType="0" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="vm1" url="192.168.100.10:3306" user="root"
		password="000000">
            <readHost host="vm2" url="192.168.100.20:3306" user="root"
			password="000000" />
        </writeHost>
    </dataHost>
    </mycat:schema>

[root@vm1 ~]# vim /opt/mycat/conf/server.xml
修改密码和提要
        <user name="root">
                <property name="password">000000</property>
                <property name="schemas">mysql</property>
删除
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>
启动mycat服务

[root@vm1 ~]# cd /opt/mycat/bin/
[root@vm1 bin]# ./mycat start 
查看mycat数据库端口

[root@vm1 bin]# yum install -y net-tools
[root@vm1 bin]# netstat -ntpl
8066数据端口
9066管理端口

查看数据库的读写分离效果
[root@vm1 ~]# mysql -uroot -p000000 -h192.168.100.10 -P9066 -e "show @@datasource;"
+----------+--------+-------+--------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST   | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+--------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | mysql1 | mysql | mysql1 | 3306 | W    |      0 |   10 | 1000 |     102 |         0 |          0 |
| dn1      | mysql2 | mysql | mysql2 | 3306 | R    |      0 |    8 | 1000 |      99 |         0 |          0 |
+----------+--------+-------+--------+------+------+--------+------+------+---------+-----------+------------+

Mycat心跳机制
[root@vm1 ~]# mysql -uroot -p000000 -h192.168.100.10 -P9066 -e "show @@heartbeat;"
+--------+-------+--------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST   | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+--------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| mysql1 | mysql | mysql1 | 3306 |       1 |     0 | idle   |       0 | 0,0,0        | 2023-05-17 17:28:51 | false |
| mysql2 | mysql | mysql2 | 3306 |       1 |     0 | idle   |       0 | 0,0,0        | 2023-05-17 17:28:51 | false |
+--------+-------+--------+------+---------+-------+--------+---------+--------------+---------------------+-------+