MYSQL+Keepalived实现双主复制

用户5921339

发布于 2025-05-20 08:41:00

13700

代码可运行

运行总次数：0

代码可运行

在主从复制的架构中，比较常见的从库是作为主库的备份，承担读取功能，分担主库的压力。

这次我们来讨论双主架构，顾名思义就是两个节点都是主库，同时作为另外一个节点的从库，两个节点同时支持读写，数据完全同步。

其次还要实现故障自动切换，以下是双主架构图

一、MYSQL安装部署

1.1、安装所需要的环境：

yum install kernel-devel openssl-devel popt-devel gcc net-tools wget -y

1.2、按照顺序安装MYSQL，使用rmp包的形式安装：

rpm -ivh https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-community-devel-8.0.30-1.el7.x86_64.rpm
rpm -ivh https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-community-common-8.0.30-1.el7.x86_64.rpm
rpm -ivh https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-community-client-plugins-8.0.30-1.el7.x86_64.rpm
rpm -ivh https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-community-client-8.0.30-1.el7.x86_64.rpm
rpm -ivh https://dev.mysql.com/get/Downloads/mysql-community-icu-data-files-8.0.30-1.el7.x86_64.rpm
rpm -ivh https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-community-server-8.0.30-1.el7.x86_64.rpm

1.3、修改MYSQL主库01的配置文件（/etc/my.cnf）：

[client]
default-character-set=UTF8MB4

[mysql]
default-character-set=UTF8MB4

[mysqld]
bind_address=0.0.0.0
port=3306
user=mysql
character-set-server=UTF8MB4

#开启慢日志
slow_query_log=ON
#设置慢查询时间
long_query_time=10
#慢查询日志保存位置
slow_query_log_file=/var/log/slowquery/query.log
#记录所有没有用上索引全表扫描的语句
log_queries_not_using_indexes=1
#记录optimize table，analyze table和alter table等语句引发的慢查询
log_slow_admin_statements=1

#数据库实例唯一标识
server_id=1

#设置中继日志位置和名称
relay_log=/var/log/relaylog/relay.log
#设置中继日志索引位置和名称
relay_log_index=/var/log/relaylog/relay.index
log_replica_updates=1
#读写模式
read_only=0
#自动清空中继日志
relay_log_purge=1
#中继日志损坏自动恢复同步
relay_log_recovery=1

#二进制文件名称集路径
log_bin=/var/log/binlog/binlog
#二进制文件索引名称集路径
log_bin_index=/var/log/binlog/binlog.index
#基于行级别的日志
binlog_format=row
#在row模式下开启该参数，将把sql语句打印到binlog日志里面。默认是0(off);
binlog_rows_query_log_events=on
#binlog缓存大小
binlog_cache_size=4M
#binlog日志有效时间（14天）
binlog_expire_logs_seconds=604800
#提交事务刷新数据到磁盘，默认为0
sync_binlog=1
#每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去
innodb_flush_log_at_trx_commit=1

#启用全局事务ID，模式为3（默认为0）
gtid_mode=on
#强制启用GTID一致性
enforce_gtid_consistency=on
binlog_gtid_simple_recovery=1

#自增步长
auto_increment_increment=2
#自增起始值
auto_increment_offset=1

#修改MYSQL数据文件存放路径
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

1.4、同上，修改MYSQL主库02的配置文件：

[client]
default-character-set=UTF8MB4

[mysql]
default-character-set=UTF8MB4

[mysqld]
bind_address=0.0.0.0
port=3306
user=mysql
character-set-server=UTF8MB4

slow_query_log=ON
long_query_time=10
slow_query_log_file=/var/log/slowquery/query.log
log_queries_not_using_indexes=1
log_slow_admin_statements=1

server_id=2

relay_log=/var/log/relaylog/relay.log
relay_log_index=/var/log/relaylog/relay.index

log_replica_updates=1
read_only=0
relay_log_purge=1
relay_log_recovery=1

log_bin=/var/log/binlog/binlog
log_bin_index=/var/log/binlog/binlog.index
binlog_format=row
binlog_rows_query_log_events=on
binlog_cache_size=4M
binlog_expire_logs_seconds=604800
sync_binlog=1
innodb_flush_log_at_trx_commit=1

gtid_mode=on
enforce_gtid_consistency=on
binlog_gtid_simple_recovery=1

auto_increment_increment=2
auto_increment_offset=2

datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

1.5、以上参数设置好以后，启动MYSQL数据库

systemctl start mysqld
#设置开机自动重启
systemctl enable mysqld

1.6、创建复制用户并授权，主库01配置：

mysql> create user 'repl'@'192.168.0.112' identified with mysql_native_password by 'Repl@passw0rd';
mysql> grant replication slave on *.* to 'repl'@'192.168.0.112';
mysql> flush privileges;

1.7、创建复制用户并授权，主库02配置：

mysql> create user 'repl'@'192.168.0.111' identified with mysql_native_password by 'Repl@passw0rd';
mysql> grant replication slave on *.* to 'repl'@'192.168.0.111';
mysql> flush privileges;

1.8、配置主从复制，主库01配置：

mysql> reset master;
mysql> stop slave;
mysql> change master to
    master_host='192.168.0.112',
    master_port=3306,
    master_user='repl',
    master_password='Repl@passw0rd',
    master_auto_position=1;
mysql> start slave;

1.9、配置主从复制，主库02配置：

mysql> stop slave;
mysql> change master to
    master_host='192.168.0.111',
    master_port=3306,
    master_user='repl',
    master_password='Repl@passw0rd',
    master_auto_position=1;
mysql> start slave;

1.10、防火墙设置

#放开MYSQL端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent

1.11、MYSQL数据库双主已经配置好了，可以查看数据库的状态：

mysql> show slave status\G;

二、部署keepalived

2.1、下载并安装keepalived：

wget https://www.keepalived.org/software/keepalived-2.2.4.tar.gz
tar zxvf keepalived-2.2.4.tar.gz
cd keepalived-2.2.4
./configure --prefix=/usr/local/keepalived
make install
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

2.2、编辑主节点1的配置文件keepalived.conf

global_defs {
    router_id 192.168.0.111
    script_user root
    enable_script_security
}

vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 3
}

vrrp_instance v_mysql {
    state BACKUP
    interface ens192
    virtual_router_id 99
    nopreempt
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }

    track_script {
        chk_mysql
    }

    virtual_ipaddress {
        192.168.0.110
    }
}

2.3、编辑主节点2的配置文件keepalived.conf

global_defs {
    router_id 192.168.0.112
    script_user root
    enable_script_security
}

vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 3
}

vrrp_instance v_mysql {
    state BACKUP
    interface ens192
    virtual_router_id 99
    nopreempt
    priority 10
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }

    track_script {
        chk_mysql
    }

    virtual_ipaddress {
        192.168.0.110
    }
}

2.4、脚本文件check_mysql.sh内容如下：

#!/bin/bash
counter1=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl start mysqld
fi
sleep 5
counter2=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ];then
systemctl stop keepalived
exit 1
fi

2.5、启动keepalived，并让其开机启动

systemctl start keepalived
systemctl enable keepalived

2.6、防火墙设置：

firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --in-interface ens192 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --out-interface ens192 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload

2.7、修改keepalived日志保存路径，默认状态下keepalived的日志会写入到/var/log/message中

#修改keepalived运行参数
vim /usr/local/keepalived/etc/sysconfig/keepalived
KEEPALIVED_OPTIONS="-D -d -S 0"

#添加日志文件路径
vim /etc/rsyslog.conf
local0.* /var/log/keepalived.log

2.8、重启服务

systemctl restart rsyslog
systemctl restart keepalived

至此，所有服务已部署完成，可以通过停止MYSQL服务来观察keepalived的虚拟IP地址漂移，由此实现了MYSQL的HA。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-09-05，如有侵权请联系 cloudcommunity@tencent.com 删除

keepalived

本文分享自 IT人家微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度

MYSQL+Keepalived实现双主复制

MYSQL+Keepalived实现双主复制

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐