M1 my.cnf：

[mysqld]
server-id = 1
log-bin = M1-log-bin
gtid_mode=ON
enforce-gtid-consistency=ON #服务器通过允许仅执行可以使用 GTID 安全记录的语句来实现 GTID 一致性

M2 my.cnf：

[mysqld]
server-id = 2
log-bin = M2-log-bin
gtid_mode=ON
enforce-gtid-consistency=ON 

S1 my.cnf：

[mysqld]
server-id = 3
gtid_mode=ON
enforce-gtid-consistency=ON 

schema.xml：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="test" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="test01" dataNode="test01"></schema>  <!--逻辑库-->
        <dataNode name="test01" dataHost="mall" database="test"></dataNode>  <!--分片节点-->
        <dataHost name="mall" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  <!--节点主机-->
                <heartbeat>show slave status</heartbeat>
                <writeHost host="M1" url="192.168.3.102:3306" user="root" password="root">
                        <readHost host="S1" url="192.168.3.102:3308" user="root" password="root"></readHost>
                </writeHost>
                <writeHost host="M2" url="192.168.3.102:3307" user="root" password="root"></writeHost>
        </dataHost>
</mycat:schema>

server.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="nonePasswordLogin">0</property>
    <property name="ignoreUnknownCommand">0</property>
    <property name="useHandshakeV10">1</property>
    <property name="removeGraveAccent">1</property>
    <property name="useSqlStat">0</property> 
    <property name="useGlobleTableCheck">0</property> 
        <property name="sqlExecuteTimeout">300</property>
        <property name="sequnceHandlerType">1</property>
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
    <property name="subqueryRelationshipCheck">false</property> 
    <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
        <property name="processorBufferPoolType">0</property>
    <property name="serverPort">8066</property> 
    <property name="managerPort">9066</property> 
    <property name="bindIp">0.0.0.0</property>
        <property name="handleDistributedTransactions">0</property>
        <property name="useOffHeapForMerge">0</property>
        <property name="memoryPageSize">64k</property>
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">false</property>
        <property name="strictTxIsolation">false</property>
        <property name="useZKSwitch">true</property>
        <property name="parallExecute">0</property>
    </system>
　　　　<!--上面都是默认配置文件只修改了<user>标签内容-->
    <user name="root" defaultAccount="true"> <!--用户和权限-->
        <property name="password">root</property> <!--密码-->
        <property name="schemas">test</property> <!--可访问的逻辑库-->
        <property name="defaultSchema">test</property><!--默认逻辑库-->
    </user>
</mycat:server>

docker-compose.yml：

version: "3.9"
services:

  mysql-M1:
    image: mysql:5.7
    container_name: mysql-M1
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - '3306:3306'
    volumes:
      - /db/mysql-M1/data:/var/lib/mysql
      - /db/mysql-M1/conf:/etc/mysql/conf.d

  mysql-M2:
    image: mysql:5.7
    container_name: mysql-M2
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - '3307:3306'
    volumes:
      - /db/mysql-M2/data:/var/lib/mysql
      - /db/mysql-M2/conf:/etc/mysql/conf.d

  mysql-S1:
    image: mysql:5.7
    container_name: mysql-S1
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - '3308:3306'
    volumes:
      - /db/mysql-S1/data:/var/lib/mysql
      - /db/mysql-S1/conf:/etc/mysql/conf.d

  mycat-01:
    image: mycat:1.6.7.6
    container_name: mycat-01
    ports:
      - '8066:8066'
      - '9066:9066'
    volumes:
      - /db/mycat-01/log:/usr/local/mycat/logs
      - /db/mycat-01/conf:/usr/local/mycat/conf

  mycat-02:
    image: mycat:1.6.7.6
    container_name: mycat-02
    ports:
      - '8067:8066'
      - '9067:9066'
    volumes:
      - /db/mycat-02/log:/usr/local/mycat/logs
      - /db/mycat-02/conf:/usr/local/mycat/conf

#启动前先放置好mysql和mycat的配置文件  启动执行以下命令:

docker-compose up -d

查看：#基于二进制的话就要记下“File”和"Position" 供后续使用

 

 

 

创建用于复制的用户：

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

此案例使用的是下面用户和权限

CREATE USER 'root'@'%' IDENTIFIED BY 'root';

GRANT ALL ON *.* TO 'root'@'%';

在从节点执行 更改主声明：

#基于GITD

CHANGE MASTER TO MASTER_HOST='IP',MASTER_USER='账号',MASTER_PASSWORD='密码',MASTER_PORT=端口,MASTER_AUTO_POSITION=1;

#基于二进制

CHANGE MASTER TO MASTER_HOST='IP',MASTER_USER='账号',MASTER_PASSWORD='密码',MASTER_PORT=端口 MASTER_LOG_POS = 位置，MASTER_LOG_FILE = '二进制文件名 ';

检查：双YES即可

 

 

 分别M1 M2互为主从 S1为M1的从操作

有可能需要用到的命令

START SLAVE;    #启动从
STOP SLAVE;     #停从
reset slave all;   #重置从
SHOW SLAVE STATUS \G  #查看从状态
RESET MASTER; #重置主

验证：

登录【主服务器】
mysql> CREATE DATABASE test; #创建test数据库
mysql> USE test; #进库

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;  #创建user表

INSERT INTO user (id, name, age) VALUES (2, 'zhangsan', 18); #插数据

SELECT * FROM user; 查看

 

 

登录 【从服务器】查看有没有即可

use test;

SELECT * FROM user;

 

--------------------------------------------------------------------------------------------

mycat

制作镜像 可参考官网

cd /db
tar -zxvf mycat1.6.7.6.tar.gz -C /db/ mycat/conf 
mv mycat mycat-01
cp -r  mycat-01 mycat-02
编辑或替换成启动配置文件
mycat01和mycat-02配置一样的

因记录搭建实践所以还修改了wrapper.conf调低资源消耗
wrapper.java.additional.9=-Xmx512M
wrapper.java.additional.10=-Xms256M

--------------------------------------------------------------------------------------------

keepalived+LVS

主  keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id lvs01
   vrrp_skip_check_adv_addr
}
vrrp_instance API {
    state MASTER
    interface ens32
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.3.200/24
    }
    notify_master "/etc/keepalived/notify.sh master"
    notify_backup "/etc/keepalived/notify.sh backup"
    notify_fault  "/etc/keepalived/notify.sh fault"
}
virtual_server 192.168.3.200 8066 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP
    real_server 192.168.3.102 8066 {
        weight 1
        TCP_CHECK {
                connect_timeout 10  
                retry 3             
                delay_before_retry 3
                connect_port 8066
            }
        }
    real_server 192.168.3.102 8067 {
        weight 1
        TCP_CHECK {
                connect_timeout 10  
                retry 3             
                delay_before_retry 3
                connect_port 8067
            }
        }
}

备  keepalived.conf：

! Configuration File for keepalived

global_defs {
   router_id lvs02
   vrrp_skip_check_adv_addr
}
vrrp_instance API {
    state BACKUP
    interface ens32
    virtual_router_id 51
    priority 50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.3.200/24
    }
    notify_master "/etc/keepalived/notify.sh master"
    notify_backup "/etc/keepalived/notify.sh backup"
    notify_fault  "/etc/keepalived/notify.sh fault"
}
virtual_server 192.168.3.200 8066 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP
    real_server 192.168.3.102 8066 {
        weight 1
        TCP_CHECK {
                connect_timeout 10  
                retry 3             
                delay_before_retry 3
                connect_port 8066
            }
        }
    real_server 192.168.3.102 8067 {
        weight 1
        TCP_CHECK {
                connect_timeout 10  
                retry 3             
                delay_before_retry 3
                connect_port 8067
            }
        }
}

RS上使用的绑定VIP脚本 realserver.sh：

#!/bin/bash
#description: Config realserver

VIP=192.168.3.200

 
case "$1" in
start)
       /sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 broadcast $VIP
       /sbin/route add -host $VIP dev lo:0
       echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
       echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
       echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
       echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
       sysctl -p >/dev/null 2>&1
       echo "RealServer Start OK"
       ;;
stop)
       /sbin/ifconfig lo:0 down
       /sbin/route del $VIP >/dev/null 2>&1
       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
       echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
       echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
       echo "RealServer Stoped"
       ;;
*)
       echo "Usage: $0 {start|stop}"
       exit 1
esac
 
exit 0

主备切换通知邮件脚本 notify.sh: 前提需要配下mail.rc才发的出去

#!/bin/bash
#
contact='502369651@qq.com'
notify() {
local mailsubject="$(hostname) to be $1, vip floating"
local mailbody="$(date +'%F %T'): vrrp transition, $(hostname) changed to be $1"
echo "$mailbody" | mail -s "$mailsubject" $contact
}

case $1 in
master)
        notify master
        ;;
backup)
        notify backup
        ;;
fault)
        notify fault
        ;;
*)
        echo "Usage: $(basename $0) {master|backup|fault}"
        exit 1
        ;;
esac

mail.rc：

#vim /etc/mail.rc #添加如下内容
set from=xxxx@126.com
set smtp=smtp.126.com
set smtp-auth-user=xx@126.com
set smtp-auth-password=xxx
set smtp-auth=login
#---说明
#from：对方收到邮件时显示的发件人
#smtp：指定第三方发邮件的smtp服务器地址
#set smtp-auth-user：第三方发邮件的用户名
#set smtp-auth-password：用户名对应的密码,有些邮箱填的是授权码
#smtp-auth：SMTP的认证方式，默认是login，也可以改成CRAM-MD5或PLAIN方式

看看效果启备再启主；启动命令：systemctl start keepalived.service

 

 还可以通过 ip a 查看VIP在谁那

测试：通过VIP进入

 

 

这样整体就完成了mysql+mycat+keepalived+LVS的高可用负载均衡读写分离的方案

--------------------------------------------------------------