MySQL+keepalived高可用

资源列表

操作系统	配置	主机名	IP
CentOS7.9	2C4G	mysql01	192.168.10.51
CentOS7.9	2C4G	mysql02	192.168.10.52

安装MySQL

#参考编译安装MySQL文章

 MySQL01配置文件

cat >> /etc/my.cnf << EOF
server-id=1
log-bin=/usr/local/mysql/data/mysql-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

# mysql1和mysql2只有server-id不同和auto-increment-offset不同,其他必须相同
#log-bin 二进制日志存放的位置
# binlog_format= mixed：指定mysql的binlog日志的格式，mixed是混合模式
# relay-log：开启中继日志功能
# relay-log-index：中继日志清单
# auto-increment-increment= 2：表示自增长字段每次递增的量，其默认值是1。它的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2。
# auto-increment-offset= 2：用来设定数据库中自动增长的起点(即初始值)，因为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突。

MySQL02配置文件

cat >> /etc/my.cnf << EOF
server-id=2
log-bin=/usr/local/mysql/data/mysql-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

启动MySQL

systemctl enable mysqld
systemctl restart mysqld
//启动和开机自启动

MySQL主主复制

在两个节点授权用户（两台机器都要做）

grant replication slave on *.* to repl@'%' identified by '1qaz@WSX';
flush privileges;
show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

对授权的用户进行连接

#在MySQL01节点操作，其中master_log_file和master_log_pos需要是mysql02节点show master status;查出来的数据
change master to master_host='192.168.10.52',
master_user='repl',
master_password='1qaz@WSX',
master_log_file='mysql-bin.000001',
master_log_pos=154;


# mysql02节点执行，其中master_log_file和master_log_pos需要是mysql01节点show master status;查出来的数据

change master to master_host='192.168.10.51',
master_user='repl',
master_password='1qaz@WSX',
master_log_file='mysql-bin.000001',
master_log_pos=154;

start slave;
show slave status\G;
//保证两个都为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"
  interval 2
}
vrrp_instance VI_1 {
  interface ens33
  state BACKUP           # 通过下面的priority来区分MASTER和BACKUP
  virtual_router_id 51
  priority 100
  nopreempt              # 防止切换到从库后，主keepalived恢复后自动切换回主库
  virtual_ipaddress {
    192.168.10.100/24
  }
  track_script {
    chk_service_ok
  }
}
EOF

/usr/bin/keepalived_set_priority 是 Keepalived 提供的一个工具，用于动态调整实例的优先级。若 3306 端口未监听，则将优先级设为 0，使当前节点不再具备成为 MASTER 的资格。

mysql02的配置文件

cat > /etc/keepalived/keepalived.conf << EOF
vrrp_script chk_service_ok {
  script "netstat -an | grep LISTEN | grep 3306 || killall keepalived"
  interval 2
}
vrrp_instance VI_1 {
  interface ens33
  state BACKUP           # 通过下面的priority来区分MASTER和BACKUP
  virtual_router_id 51
  priority 90
  nopreempt              # 防止切换到从库后，主keepalived恢复后自动切换回主库
  virtual_ipaddress {
    192.168.10.100/24
  }
  track_script {
    chk_service_ok
  }
}
EOF

启动keepalived服务

systemctl start keepalived
systemctl enable keepalived

通过查询IP可以验证