环境

主机名/IP	角色	系统
server01/192.168.1.101	keepalived-master/mysql-master/mysql-slave	ubuntu22.04
server02/192.168.1.102	keepalived-slave/mysql-slave/mysql-master	ubuntu22.04

配置mysql双主

安装

apt install mysql-server -y
systemctl start mysql-server && systemctl enable mysql

server01配置

锁表，禁止备份途中进行写操作

flush tables with read lock;

备份数据库（一般只备份业务数据库）

mysqldump -u root -p --databases test > /tmp/bak.sql

解锁

unlock tables;

创建同步用户，赋予复制权限

mysql -u root -p
create user 'slave'@'192.168.1.102' identified by 'wakamizu';
grant replication slave on *.* to 'slave'@'192.168.1.102';
flush privileges;

开启二进制日志，开放监听接口

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
log_bin=mysql-bin
server_id=1
bind-address=0.0.0.0
#防止主键重复
binlog_format=mixed
#强制binlog与磁盘同步
sync_binlog=1 
#数据库主键不连续防止主键冲突
#自增值
auto_increment_increment=2
#起始序号
auto_increment_offset=1
#开启gtid，提供更可靠的数据一致性，防止数据回环问题
enforce-gtid-consistency=on
gtid-mode=on
relay_log=mysql-relay-bin

重启服务器

systemctl restart mysql

server02配置

导入备份数据库

mysql < /tmp/backup.sql

创建同步用户，赋予复制权限

mysql -u root -p
create user 'slave'@'192.168.1.101' identified by 'wakamizu';
grant replication slave on *.* to 'slave'@'192.168.1.101';
flush privileges;

开启二进制日志，开放监听接口

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
log_bin=mysql-bin
server_id=2
bind-address=0.0.0.0
#防止主键重复
binlog_format=mixed
#强制binlog与磁盘同步
sync_binlog=1 
#数据库主键不连续防止主键冲突
#自增值
auto_increment_increment=2
#起始序号
auto_increment_offset=2
#开启gtid，提供更可靠的数据一致性，防止数据回环问题
enforce-gtid-consistency=on
gtid-mode=on
relay_log=mysql-relay-bin

重启服务器

systemctl restart mysql

将备份数据复制到主服务器B，然后导入数据库

mysql -u root -p < /tmp/bak.sql

配置主从复制

mysql -u root -p 
change master to
master_host='192.168.1.101',
master_user='slave',
master_password='wakamizu',
master_auto_position = 1,
get_master_public_key=1;

当master_auto_position设置为 1 时，从服务器会连接到主服务器并请求主服务器 binlog 的 GTID 集合。

注意：如果slave用户的身份验证插件是caching_sha2_password，则需要指GET_MASTER_PUBLIC_KEY=1，以获取主服务器的RSA公钥进行身份验证增强连接的安全性

开启复制

start slave;

查看复制状态

show slave status \G;

server02配置

配置主从复制

mysql -u root -p 
change master to
master_host='192.168.1.102',
master_user='slave',
master_password='wakamizu',
master_auto_position = 1,
get_master_public_key=1;

开启复制

start slave;

查看复制状态

show slave status \G;

keepalived配置

安装

apt install keepalived -y

keepalived配置

vim /etc/keepalived/keepalived.conf

vrrp_script check_mysql {
    script "/usr/bin/mysqladmin ping"
    interval 2 #执行间隔
    weight -2  #失败时权重-2
	fall 2 #连续执行两次失败后才算失败
    rise 2 #连续执行两次成功后才算成功
}

#定义虚拟路由，配置高可用
vrrp_instance VI_1 {   
    state MASTER         #定义角色,server02需要更改成BACKUP
    interface ens32       #流量经过的网卡
    virtual_router_id 51 #虚拟路由id
    priority 100         #优先级，server02需要更改成99
    advert_int 1        #通讯间隔
    authentication {    #配置认证
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress { #虚拟地址
        192.168.1.150
    }
	#执行脚本
	track_script {
		#检查mysql状态
        check_mysql
    }
	#配置通知脚本
	notify "/etc/keepalived/scripts/notify.sh"
}

#定义虚拟服务器，配置负载均衡
virtual_server 192.168.1.120 3306 {
   delay_loop 6  #检查间隔
   lb_algo rr  #调度程序rr|wrr|lc|wlc|sh|dh|lblc
   lb_kind DR  #转发方法NAT|DR|TUN
   persistence_timeout 50  #持久连接超时时间
   protocol TCP  #协议类型TCP|UDP
   real_server 192.168.1.101 3306 {
     weight 1
     TCP_CHECK { #使用TCP连接检查真实服务器的可用性
       connect_timeout 3  #连接超时时间
       nb_get_retry 3  #失败重试次数
       delay_before_retry 3  #重试之间的间隔
     }
   }

   real_server 192.168.1.102 3306 {
     weight 1
     TCP_CHECK {
       connect_timeout 3
       nb_get_retry 3
       delay_before_retry 3
     }
   }
}

配置脚本

mkdir /etc/keepalived/scripts
touch /etc/keepalived/scripts/notify.sh
chmod u+x /etc/keepalived/scripts/notify.sh
vim /etc/keepalived/scripts/notify.sh

#!/bin/bash
TYPE=$1
NAME=$2
STATE=$3
DATE=`date '+%Y-%m-%d %H:%M:%S'`
echo "$DATE - Keepalived $TYPE $NAME is now $STATE" >> /var/log/keepalivedstatus.log