Mycat

 

---

1 MyCat 常用命令

启动MyCat

./mycat start

查看启动状态

./mycat status

停止 

./mycat stop

重启

​./mycat restart

控制台启动，测试使用，方便发现报错信息

./mycat console

---

2 使用Mycat实现一主一从的读写分离

一主一从搭建过程 : https://yuanyu.blog.csdn.net/article/details/106320629#t0

tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

mv mycat /usr/local/ && cd /usr/local/mycat

vim server.xml  

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

vim 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="host1" database="easycode_db"/>
    <!--
        （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 name="host1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!--写-->
        <writeHost host="master" url="39.106.196.224:5569" user="root" password="123456">
            <!--读-->
            <readHost host="slave" url="121.36.33.154:5596" user="root" password="123456"/>
        </writeHost>
    </dataHost>
</mycat:schema>

控制台启动

./mycat console

//版本过高
λ mysql -umycat -p123456 -P 8066 -h 121.36.33.154
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (HY000): Access denied for user 'mycat', because password is error

9066端口用于管理维护Mycat

mysql -umycat -p123456 -P 9066 -h 39.106.196.224

8066端口用于通过Mycat查询数据

mysql -umycat -p123456 -P 8066 -h 39.106.196.224

使用java程序访问

spring:
  datasource:
    url: jdbc:mysql://39.106.196.224:8066/TESTDB?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
    username: mycat
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

//mycat 123456
jdbc:mysql://39.106.196.224:8066/?serverTimezone=UTC

use TESTDB;
select `name` from user where id = 6;

---

3 使用Mycat实现双主双从的读写分离

双主双从搭建过程 : https://yuanyu.blog.csdn.net/article/details/106320629#t4

vim 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="host1" database="easycode_db"/>
    <!--
        balance="1" : 全部的readHost与stand by writeHost参与select语句的负载均衡
        writeType="0" : 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个

        switchType="1" : 1 默认值，自动切换
           -1 表示不自动切换
           2 基于 MySQL 主从同步的状态决定是否切换
    -->
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master1" url="101.200.43.221:4500" user="root" password="123456">
            <readHost host="slave1" url="120.25.216.234:4501" user="root" password="123456"/>
        </writeHost>
        <writeHost host="master2" url="121.36.33.154:5000" user="root" password="123456">
            <readHost host="slave2" url="39.106.196.224:5001" user="root" password="123456"/>
        </writeHost>
    </dataHost>
</mycat:schema>

spring:
  datasource:
    url: jdbc:mysql://39.106.196.224:8066/TESTDB?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
    username: mycat
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

3.1 正常访问 

抗风险能力

3.2 master1宕机 

docker stop mysql_master1

3.3 master1故障恢复

docker start mysql_master1

docker exec -it mysql_master1 /bin/bash