目录
本文详细记录了使用MySQL主主复制结合keepalived实现数据库高可用的流程。方便大家进行参考。
资源列表
| 操作系统 | 配置 | 主机名 | IP |
|---|---|---|---|
| CentOS7.3.1611 | 2C4G | mysql01 | 192.168.207.131 |
| CentOS7.3.1611 | 2C4G | mysql02 | 192.168.207.131 |
基础环境
关闭防火墙
systemctl stop firewalld
systemctl disable firewalld关闭内核安全机制
setenforce 0
sed -i "s/^SELINUX=.*/SELINUX=disabled/g" /etc/selinux/config修改主机名
hostnamectl set-hostname mysql01
hostnamectl set-hostname mysql02时间同步
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
chronyc sources -v一、安装MySQL
准备yum源
# 下载MySQL源
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
# 安装MySQL源
yum -y install mysql57-community-release-el7-10.noarch.rpm清理mariadb相关的包
for i in $(rpm -qa | grep mariadb); do yum -y remove $i ;done安装
# 安装MySQL
yum -y install mysql-community-server启动
# mysql1和mysql2只有server-id不同和auto-increment-offset不同,其他必须相同
# binlog_format= mixed：指定mysql的binlog日志的格式，mixed是混合模式
# relay-log：开启中继日志功能
# relay-log-index：中继日志清单
# auto-increment-increment= 2：表示自增长字段每次递增的量，其默认值是1。它的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2。
# auto-increment-offset= 2：用来设定数据库中自动增长的起点(即初始值)，因为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突。
cat >> /etc/my.cnf << EOF
server-id=1
log-bin=node1-bin
binlog_format=mixed
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
auto-increment-increment=2
auto-increment-offset=1
EOF
cat >> /etc/my.cnf << EOF
server-id=2
log-bin=node2-bin
binlog_format=mixed
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
auto-increment-increment=2
auto-increment-offset=2
EOF
systemctl enable mysqld
systemctl start mysqld
# 查看密码登陆数据库
grep password /var/log/mysqld.log
[root@mysql01 ~]# grep password /var/log/mysqld.log
2022-02-11T03:52:31.002250Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 9jI=b.vbijSy
# 重新设置root密码
alter user 'root'@'localhost' identified by '1qaz@WSX';
flush privileges;二、MySQL主主复制
两个节点授权用户
grant replication slave on *.* to repl@'%' identified by '1qaz@WSX';
flush privileges;
show master status;两个节点使用授权用户进行连接
# mysql02节点执行，其中master_log_file和master_log_pos需要是mysql01节点show master status;查出来的数据
change master to master_host='192.168.207.131',
master_user='repl',
master_password='1qaz@WSX',
master_log_file='node1-bin.000002',
master_log_pos=1005;
# mysql01节点执行，其中master_log_file和master_log_pos需要是mysql02节点show master status;查出来的数据
change master to master_host='192.168.207.165',
master_user='repl',
master_password='1qaz@WSX',
master_log_file='node2-bin.000002',
master_log_pos=1005;启动同步
start slave;
show slave status\G;
# 确保show slave status\G;的这个两个是yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes三、安装keepalived
安装
yum -y install keepalived修改配置文件
cp /etc/keepalived/keepalived.conf{,.bak}
# mysql01的配置文件
cat > /etc/keepalived/keepalived.conf << EOF
vrrp_script chk_service_ok {
script "netstat -an | grep LISTEN | grep 3306 || killall keepalived"
interval2
}
vrrp_instance VI_1 {
interface ens33
state BACKUP # 通过下面的priority来区分MASTER和BACKUP
virtual_router_id 51
priority 100
nopreempt # 防止切换到从库后，主keepalived恢复后自动切换回主库
virtual_ipaddress {
192.168.207.200/24
}
track_script {
chk_service_ok
}
}
EOF
# mysql02的配置文件
cat > /etc/keepalived/keepalived.conf << EOF
vrrp_script chk_service_ok {
script "netstat -an | grep LISTEN | grep 3306 || killall keepalived"
interval2
}
vrrp_instance VI_1 {
interface ens33
state BACKUP # 通过下面的priority来区分MASTER和BACKUP
virtual_router_id 51
priority 80
nopreempt # 防止切换到从库后，主keepalived恢复后自动切换回主库
virtual_ipaddress {
192.168.207.200/24
}
track_script {
chk_service_ok
}
}
EOF启动服务
systemctl start keepalived
systemctl enable keepalived验证
[root@mysql01 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:6f:a6:fa brd ff:ff:ff:ff:ff:ff
inet 192.168.207.131/24 brd 192.168.207.255 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.207.200/24 scope global secondary ens33
valid_lft forever preferred_lft forever
inet6 fe80::7ef6:a206:d3ad:5f6f/64 scope link
valid_lft forever preferred_lft forever