MySQL 配置高可用双主

前言

生产中需要配置MySQL双主高可用

配置keepalived

#/etc/keepalived/keepalived.conf 
vrrp_script check_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 2
    timeout 2
}

vrrp_instance OMS_MYSQL {
    interface enp4s1 #网卡
    state MASTER  # 主服务器配置为MASTER，从服务器配置为BACKUP
    virtual_router_id 51
    priority 100  # 主服务器优先级高于从服务器
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456  # 验证密码
    }
    virtual_ipaddress {
        172.22.150.201  # 虚拟IP地址，用于连接数据库
    }
    track_script {
        check_mysql
    }
}

# /etc/keepalived/check_mysql.sh 
#!/bin/bash

# 检查MySQL服务是否在运行
mysql_status=$(systemctl is-active mysqld)

if [ "$mysql_status" = "active" ]; then
    exit 0  # MySQL服务正常
else
    exit 1  # MySQL服务异常
fi

配置主从复制

# 创建用户
create user 'repl'@'%' identified by '123456';
grant REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;

# 半同步复制配置,安装插件，确认my.cnf是否加载配置
rpl_semi_sync_master_enabled=1  #开启半同步
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=1000       #设置超时时间
 
mysql> show global variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
9 rows in set (0.00 sec)
 
mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+    |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
46 rows in set (0.00 sec)

#5.7配置双主复制，开启自动复制，下面命令适合空库
change master to master_host='172.22.150.63',master_user='repl',master_password='Z1xI7zSa3OMK',master_port=3306,master_auto_position = 1;
change master to master_host='192.168.152.147',master_user='repl',master_password='123456',master_port=3306,master_auto_position = 1;


#8.0+ 配置双主复制，开启自动复制，下面命令适合空库
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='172.22.150.64',
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='Z1xI7zSa3OMK',
SOURCE_AUTO_POSITION=1;