MySQL高可用方案
 ——双主+keepalived一、配置MySQL互为主从
 1、环境准备
 系统 IP 主机名 服务
 Centos 7.5 10.0.0.235 db01 MySQL+keepalived
 Centos 7.5 10.0.0.236 db02 MySQL+keepalived2、开启二进制日志及中继日志
 1）主机db01配置文件如下
 [root@db01 mysql]# cat /etc/my.cnf
 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 symbolic-links=0
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pidserver_id=1 #server_id必须唯一
 log-bin=/var/lib/mysql/data/log_bin #指定二进制日志文件
 relay-log=/var/lib/mysql/data/relay-bin #指定中继日志
 binlog_format=mixed #二进制日志的格式为混合模式
 relay-log-index=relay-bin.index
 auto_increment_increment=2 #表示自增长字段每次递增的量
 auto_increment_offset=1 #用来设定数据库中自动增长的起点(即初始值)2）主机db02配置文件如下
[root@db02 mysql-5.7.28-1.el7.x86_64.rpm-bundle]# cat /etc/my.cnf
 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 symbolic-links=0
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pidserver_id=2 #server_id必须唯一
 log-bin=/var/lib/mysql/data/log_bin #指定二进制日志文件
 relay-log=/var/lib/mysql/data/relay-bin #指定中继日志
 binlog_format=mixed #二进制日志的格式为混合模式
 relay-log-index=relay-bin.index
 auto_increment_increment=2 #表示自增长字段每次递增的量
 auto_increment_offset=2 #用来设定数据库中自动增长的起点(即初始值)mkdir /var/lib//mysql/data/
 chown -R mysql:mysql /var/lib/mysql
 chmod -R 777 /var/lib/mysql
 systemctl restart mysqld
 3、将db02设置为db01的从服务器
 3）db01上创建授权用户
 grant replication slave on . to zyqt@‘10.0.0.%’ identified by ‘ZYqt@123’;4）查看db01的当前binlog状态信息
 Mysql> show master status\G5）在db02上指定db01为master，并开启slave功能
 mysql> change master to
 -> master_host=‘10.0.0.235’,
 -> master_user=‘zyqt’,
 -> master_password=‘ZYqt@123’,
 -> master_log_file=‘log_bin.000001’,
 -> master_log_pos=449;开启主从
 mysql> start slave;查看slave状态
 mysql> show slave status \G；IO线程是从库去master上面读取二进制日志到本地的中继日志中；
 SQL线程是将本地的中继日志中的内容转换为sql语句并执行。#只要上面两个值为yes，则表示主从已经做好了
4、将db01设置为db02的从服务器
 1）db02上创建授权用户
 grant replication slave on . to zyqt@‘10.0.0.%’ identified by ‘ZYqt@123’;2）查看db02的当前binlog状态信息
 mysql> show master status\G3）在db01上指定db02为master，并开启slave功能
 mysql> change master to
 -> master_host=‘10.0.0.236’,
 -> master_user=‘zyqt’,
 -> master_password=‘ZYqt@123’,
 -> master_log_file=‘log_bin.000001’,
 -> master_log_pos=449;开启主从
 mysql> start slave;查看slave状态
 mysql> show slave status \G；5、测试主主同步
 1）主机db01创建测试数据
 mysql> show databases;
 mysql> create database test;
 mysql> use test
 mysql> create table t1(id int,name varchar(4));
 mysql> insert into t1 values(1,‘a’),(2,‘b’);
 mysql> select * from t1;
 ±-----±-----+
 | id | name |
 ±-----±-----+
 | 1 | a |
 | 2 | b |
 ±-----±-----+
 2 rows in set (0.00 sec)2）确认db02已经同步并插入新的数据
 mysql> show databases;
 mysql> use test
 mysql> select * from t1;
 ±-----±-----+
 | id | name |
 ±-----±-----+
 | 1 | a |
 | 2 | b |
 ±-----±-----+
 2 rows in set (0.00 sec)3）在db02插入数据测试可以同步至db01
 mysql> insert into t1 values(3,‘c’),(4,‘d’);
 mysql> select * from t1;
 ±-----±-----+
 | id | name |
 ±-----±-----+
 | 1 | a |
 | 2 | b |
 | 3 | c |
 | 4 | d |
 ±-----±-----+
 4 rows in set (0.00 sec)4）在db01上查看db02插入的数据是否同步
 mysql> select * from t1;二、配置keepalived实现高可用
 1、两台主机分别安装keepalived
 yum -y install keepalived
 cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak2、修改主机db01的keepalived配置文件
 [root@db01 ~]# cat /etc/keepalived/keepalived.conf
 ! Configuration File for keepalivedglobal_defs {
 router_id mysql-01 #此处的值必须唯一
 }vrrp_instance VI_1 {
 state BACKUP #指定角色为backup，两台MySQL服务器的角色均为backup，设置backup将根据优先级决定主从interface eth0      #指定承载虚拟IP的网卡
virtual_router_id 51         #指定组，同一个集群内的值必须一致。并且不可和局域网中的其他组冲突
priority 100          #优先级范围为：0~100
advert_int 1     #发vrrp包的时间间隔，即多久进行一次master选举（可认为是健康检查时间间隔）
nopreempt             #不抢占，即允许一个priority比较低的节点作为master，     
authentication {          #认证区域
    auth_type PASS
    auth_pass 1111
}
virtual_ipaddress {         #VIP区域，指定vip地址
    10.0.0.200
}}
virtual_server 10.0.0.200 3306 { #设置虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开
 delay_loop 2 #设置运行情况检查时间，单位是秒
 lb_algo rr #设置后端调度算法
 lb_kind DR #设置lvs实现负载均衡的机制，有NAT、TUN、DR三个模式，DR模式效率最高
 persistence_timeout 60 #会话保持时间，单位是秒
 protocol TCP #指定转发协议类型，有TCP和UDP两种real_server 10.0.0.235 3306 {          #配置服务节点，这里指定的也就是本机的真实IP
    weight 1     #设置权重
notify_down /etc/keepalived/bin/mysql.sh    #检测到real_server的MySQL服务宕机后执行的脚本。
TCP_CHECK {
    connect_port 3306    #健康检查端口
    connect_timeout 3       #连接超时时间
    retry 3    #重试次数
    delay_before_retry 3      #重连间隔时间
 }}
 }
 #准备指定的脚本
 [root@db01 keepalived]# pwd
 /etc/keepalived
 [root@db011 keepalived]# mkdir bin
 [root@db01 keepalived]# vim bin/mysql.sh
 #!/bin/bash
 pkill keepalived #停止keepalived服务
 [root@db01 keepalived]# chmod +x bin/mysql.sh #赋予脚本执行权限
 [root@db01 ~]# systemctl start keepalived #启动keepalived服务#将keepalived的配置文件发送到db02主机上
 [root@db01 ~]# scp /etc/keepalived/keepalived.conf root@10.0.0.236:/etc/keepalived/
 3、修改主机db02的keepalived配置文件
 #修改db01发送来的配置文件
 [root@db02 keepalived]# cat /etc/keepalived/keepalived.conf
 ! Configuration File for keepalivedglobal_defs {
 router_id mysql-02 #更改router_id，此处在热备组中必须要唯一
 }vrrp_instance VI_1 {
 state BACKUP
 interface eth0
 virtual_router_id 51
 priority 90 #更改优先级
 advert_int 1
 nopreempt
 authentication {
 auth_type PASS
 auth_pass 1111
 }
 virtual_ipaddress {
 10.0.0.200
 }
 }virtual_server 10.0.0.200 3306 {
 delay_loop 2
 lb_algo rr
 lb_kind DR
 persistence_timeout 60
 protocol TCPreal_server 10.0.0.236 3306 {        #更改为本机的IP地址及监听端口
    weight 1
notify_down /etc/keepalived/bin/mysql.sh
TCP_CHECK {
    connect_port 3306
    connect_timeout 3
    retry 3
    delay_before_retry 3
 }}
 }
 #准备所需脚本
 [root@db02 keepalived]# pwd
 /etc/keepalived
 [root@db02 keepalived]# mkdir bin
 [root@db02 keepalived]# vim bin/mysql.sh
 #!/bin/bash
 pkill keepalived
 [root@db02 keepalived]# chmod +x bin/mysql.sh
 #启动keepalived
 [root@db02 ~]# systemctl start keepalived4、keepalived高可用vip漂移测试
 1）测试关闭db01的mysql2）查看db01的系统日志
3）查看db02的日志
4）查看db02的vip漂移
5、继续漂移测试
 1）起db01上的keepalived2）观察db01日志
3）然后停掉db02的mysql
4）观察db02日志
5）继续观察db01的日志
6）最后验证vip是否漂移成功