Mycat1.6 高可用配置

1. 读写分离

Mycat通过 schema.xml 中的 dataHost 配置实现读写分离。

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
    writeType="0" dbType="mysql" dbDriver="native" switchType="1">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="123456">
        <readHost host="hostS1" url="192.168.1.101:3306" user="root" password="123456" />
        <readHost host="hostS2" url="192.168.1.102:3306" user="root" password="123456" />
    </writeHost>
</dataHost>

balance 参数详解

writeType 参数

switchType 参数

2. MySQL主从复制配置

# 主库配置 (my.cnf)
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW

# 从库配置 (my.cnf)
[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-bin=mysql-bin
read-only=1

# 在主库创建复制用户
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_pwd';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# 从库执行
mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_pwd',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4;
mysql> START SLAVE;

3. 双主双从架构

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
    writeType="0" dbType="mysql" dbDriver="native" switchType="1">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="123456">
        <readHost host="hostS1" url="192.168.1.101:3306" user="root" password="123456" />
    </writeHost>
    <writeHost host="hostM2" url="192.168.1.102:3306" user="root" password="123456">
        <readHost host="hostS2" url="192.168.1.103:3306" user="root" password="123456" />
    </writeHost>
</dataHost>

4. ZooKeeper集群管理

Mycat支持通过ZooKeeper管理集群配置，实现配置动态加载。

# myid.properties
loadZk=true
zkURL=127.0.0.1:2181
clusterId=mycat-cluster-1
myid=mycat_fz_01
clusterSize=3

5. Mycat自身高可用

5.1 使用Keepalived + HAProxy

# 安装HAProxy
yum install -y haproxy

# haproxy.cfg
listen mycat_service
    bind 0.0.0.0:3307
    mode tcp
    option tcplog
    balance roundrobin
    server mycat1 192.168.1.10:8066 check inter 5000 rise 2 fall 3
    server mycat2 192.168.1.11:8066 check inter 5000 rise 2 fall 3

5.2 心跳检测配置

<heartbeat>select user()</heartbeat>
# 或使用更严格的心跳
<heartbeat>show slave status</heartbeat>