MySQL双主模式的高可用

MySQL双主模式的高可用架构图:

1.将MASTER和BAKCUP上的Mysql配置为双主架构
1）开启Mysql二进制日志功能及配置server-id
MASTER和BAKCUP：

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
slow_query_log

log-bin=ON
server-id=10      #BAKCUP的Mysql配置文件中server-id为20,不可与MASTER一致。
log-bin=mysql-bin

#skip_name_resolve=1

[client]
socket=/data/mysql/mysql.sock

[mysql]
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"

2）在MASTER上创建主从数据库间相互复制数据以及用于Client连接数据库进行测试的账号。

grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';

 

  grant all on *.* to test@'10.0.0.%' identified by '123456';

 

2.互为主备,相互以对方为MASTER实现双主架构Mysql
BAKCUP：
1)备机数据库先连主(),待将MASTER上创建的 repluser 账号复制过来后,主机数据库再连备。

CHANGE MASTER TO
MASTER_HOST='10.0.0.200',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=959;

2）开启复制,查看slave状态，IO SQL线程已被开启

start slave;
show slave status\G;

3）可以看到test账号复制过来了但是repluser账号却没过来.建议：先进行完全备份,然后再根据完全备份的标记位置往后复制。因此这里为BACKUP手动创建repluser账号。

select user,host from mysql.user;

grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';

MASTER：

CHANGE MASTER TO
MASTER_HOST='10.0.0.201',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=177;

3.Keepalive全局配置
MASTER和BACKUP：

cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id node1.qinglin.com   #BAKCUP改为 node2.qinglin.com 以示区分。
   vrrp_skip_check_adv_addr
   #vrrp_strict
   #vrrp_garp_interval 0
   #vrrp_gna_interval 0
   vrrp_mcast_group4 233.6.6.6   #定义MASTER和BAKCUP之间用于通信的组播地址
}

vrrp_script check_mysql {  #定义VRRP脚本,用来检测Mysql服务状态
    script "/etc/keepalived/check_mysql.sh"
    interval 1
    weight -30
    fall 3
    rise 2
    timeout 2
}

include /etc/keepalived/conf.d/*.conf

4.编写检测Mysql服务的脚本

vim /etc/keepalived/check_mysql.sh

#!/bin/bash
mysqladmin -uroot -p123456 status &> /dev/null

chmod +x /etc/keepalived/check_mysql.sh

5.虚拟路由器配置(VIP)
1）MASTER：

cat /etc/keepalived/conf.d/vip_mysql.conf 
vrrp_instance VI_1 {  
    state MASTER      
    interface eth0    
    virtual_router_id 50  
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.0.0.100/24 dev eth0 label eth0:1
    }

  track_script {
  check_mysql
}

notify_master "/etc/keepalived/notify.sh master"
notify_backup "/etc/keepalived/notify.sh backup"
notify_fault "/etc/keepalived/notify.sh fault"
}

2）BAKCUP：

cat /etc/keepalived/conf.d/vip_mysql.conf

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 50
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.0.0.100/24 dev eth0 label eth0:1
}
track_script {
check_mysql
}

notify_master "/etc/keepalived/notify.sh master"
notify_backup "/etc/keepalived/notify.sh backup"
notify_fault "/etc/keepalived/notify.sh fault"
}

测试环节：
1）配置好后重启Keepalive服务可以看到VIP浮动在优先级较高的MASTER节点上。

 

2）关闭Mysql服务后根据定义的VRRP脚本逻辑,MASTER在降低优先级后会浮动到BAKCUP上。(成功)

systemctl stop mysql.service 

3)因为VIP设置为抢占式,重启Mysql服务后又回归到MASTER上,此时在Client通过测试账号连接数据库后,在进行模拟MASTER节点数据库宕机。

while true ;do mysql -utest -p123456 -h 10.0.0.100 -P3306 -e 'select @@hostname';sleep 1.5;done  #持续连接,便于查看调度情况。

 

systemctl stop mysql.service

 

可以看到在切换的过程有短暂的丢包现象,但还是能够切换过去。优化点：可以减少VRRP脚本对Mysql服务故障的判断次数和时间,以此加快判断速度和切换VIP的时间。