MySQL8 Keepalived+双主

坑：用keepalived 时，双主或是主从的PORT是相同的，因为keepalived 不会转发PORT

1. 配置双主：

双机名

win89.inno.com

win88.inno.com

 

准备账号

SET SQL_LOG_BIN=0; 
set password for 'root'@'localhost'='123456'; 
grant all privileges on *.* to root@'localhost' with grant option;
create user 'repl'@'win88.inno.com' identified WITH mysql_native_password by '123456' PASSWORD EXPIRE NEVER ; 
grant replication slave on *.* to 'repl'@'win88.inno.com' with grant option; 
create user 'repl'@'win89.inno.com' identified WITH mysql_native_password by '123456' PASSWORD EXPIRE NEVER ; 
grant replication slave on *.* to 'repl'@'win89.inno.com' with grant option; 
create user 'repl'@'192.168.68.88' identified WITH mysql_native_password by '123456' PASSWORD EXPIRE NEVER ; 
grant replication slave on *.* to 'repl'@'192.168.68.88' with grant option; 
create user 'repl'@'192.168.68.89' identified WITH mysql_native_password by '123456' PASSWORD EXPIRE NEVER ; 
grant replication slave on *.* to 'repl'@'192.168.68.89' with grant option; 
flush privileges;
SET SQL_LOG_BIN=1; 

配置主从

设win88.inno.com 的主机

change master to master_host='win89.inno.com',master_port=3308,master_user='repl',master_password='123456',master_auto_position=1; 
start slave; 
show slave status\G

设win89.inno.com 的主机

 

change master to master_host='win88.inno.com',master_port=3308,master_user='repl',master_password='123456',master_auto_position=1; 
start slave; 
show slave status\G

 

设置mysql 开机自启

cp -p /data/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
cp -p /data/3308/my.cnf    /etc/my.cnf.d/my.cnf
chkconfig --list

安装keepalive

yum -y install keepalived

win89.inno.com 的keepalived 设定

[root@win89 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id mysql_slave_
   vrrp_skip_check_adv_addr
   vrrp_script check_run {
   script "/data/mysql_check.sh"
   interval 10
}
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens160
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.68.188
    }
    track_script {
    check_run
    }
}

virtual_server 192.168.68.188 3308 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.68.89 3308 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            connect_port 3308
        }
    }
}

win88.inno.com 的keepalived 设定

[root@win88 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id mysql_master_
   vrrp_skip_check_adv_addr
   vrrp_script check_run {
   script "/data/mysql_check.sh"
   interval 10
}
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens160
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.68.188
    }
    track_script {
    check_run
    }
}

virtual_server 192.168.68.188 3308 {
    delay_loop 6
    lb_algo wrr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.68.88 3308 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            connect_port 3308
        }
    }
}

 设置keepalived 开机自启

 systemctl enable keepalived
 systemctl start keepalived

 脚本

MYSQL_HOST=192.168.68.88
MYSQL_USER=root
PW='123456'
LOG_FILE=/var/log/check_mysql.log
CHECK_TIME=10
MYSQL_OK=1


check_mysql_health()
{
  mysql -u${MYSQL_USER} -p${PW} --socket=/data/3308/data/mysql.sock -e "show status;" >/dev/null 2>&1
  if [ $? == 0 ]
  then  
    MYSQL_OK=1
  else  
    MYSQL_OK=0
    echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [Warnings] - mysql Not available: failed[$MYSQL_OK] >> ${LOG_FILE}
  fi  
}

while [ $CHECK_TIME -ne 0 ]
  do
    let "CHECK_TIME -= 1"

    check_mysql_health

    if [ $MYSQL_OK = 1 ] ; then
      CHECK_TIME=0
      echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - mysql available: success[$MYSQL_OK] >> ${LOG_FILE}
      exit 0
    fi

    if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ] ; then
      systemctl stop keepalived
      echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - mysql invaild. keepalived stop. >> ${LOG_FILE}
      exit 1
    fi

    sleep 1

  done