KeepAlived+MySQL实现高可用(双主热备)

KeepAlived+MySQL实现高可用(双主热备)

---

准备工作:
Master1: 192.168.0.6
Master2: 192.168.0.9
VIP: 192.168.0.100

---

一. Master1:

1. 修改配置文件

# vim /etc/my.cnf

server-id=1
log-bin=mysql-bin
relay-log=mysql-relay

2. MySQL添加账号并赋予权限

mysql> grant replication slave on *.* to backup@'%' identified by 'backup';
mysql> show master status \G;

3. 输出

File: mysql-bin.000001
Position: 323
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

4. 安装并配置keepalived

# yum -y install keepalived
# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id MySQL-Master	# id自定义
}

vrrp_instance VI_1 {
    state MASTER	# 设置为MASTER
    interface eth0
    virtual_router_id 51
    priority 100	# 优先级100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.100	# 设置VIP
    }
}

virtual_server 192.168.0.100 3306 {	# 监听VIP3306端口
    delay_loop 6
    lb_algo rr
    lb_kind DR		# 使用DR模式轮循
    nat_mask 255.255.255.0
    protocol TCP

    real_server 192.168.0.6 3306 {	# 本机MySQL开启3306
        weight 1
        TCP_CHECK {		# 健康检查
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306	# 设置连接端口3306
        }
    }
    real_server 192.168.0.9 3306 {	# Master2服务器MySQL
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306	# 设置连接端口3306
        }
    }
}

5. 启动

# service keepalived start

---

二. Master2:

1. 修改配置文件

# vim /etc/my.cnf

server-id=2
log-bin=mysql-bin
relay-log=mysql-relay

2. MySQL创建账号授予权限并连接Master1

mysql> grant replication slave on *.* to backup@'%' identified by 'backup';
mysql> stop slave
mysql> change master to master_host='192.168.0.6',  master_user='backup', master_password='backup',  master_log_file='mysql-bin.000001', master_log_pos=323;
mysql> flush privileges;
mysql> start alave;
mysql> show slave status\G;

3. 输出

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

连接成功
查看本机master状态

mysql> show master status\G;

4. . 输出

File: mysql-bin.000001
Position: 323
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

5. 回到Master1

---

三. Master1:

1. 配置MySQL互为主从

mysql> stop slave
mysql> change master to master_host='192.168.0.9',  master_user='backup', master_password='backup',  master_log_file='mysql-bin.000001', master_log_pos=323;
mysql> flush privileges;
mysql> start alave;
mysql> show slave status\G;

2. 输出

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

连接成功

---

四. Master2:

1. 安装并配置keepalived

# yum -y install keepalived
# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id MySQL-Backup	# id自定义
}

vrrp_instance VI_1 {
    state BACKUP	# 设置为BACKUP
    interface eth0	# 使用网卡(注意)
    virtual_router_id 51
    priority 90	# 优先级要低于Master1的100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.100	# vip(一致)
    }
}

virtual_server 192.168.0.100 3306 {	# 设置VIP的MySQL3306
    delay_loop 6
    lb_algo rr
    lb_kind DR	# 使用DR模式
    nat_mask 255.255.255.0
    protocol TCP

    real_server 192.168.0.6 3306 {	# Master1IP及3306端口
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306	# 配置连接端口3306
        }
    }
    real_server 192.168.0.9 3306 {	# 本机IP及3306端口
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306	# 配置连接端口为3306
        }
    }
}

2. 开启keepalived

# service keepalived start

---

五.测试

1. keepalived 单点故障测试

Master1主机分别查看VIP

# ip addr

查看Master1结果存在vip,Master2不存在VIP,即keepalived启动正常

停止Master1的keepalived

# service keepalived stop

再次分别查看VIP是否存在

# ip addr

Master1VIP挂掉,Master2VIP启动,即单点故障测试成功
再次开启Master1的keepalived,VIP被抢回来.

注:默认优先级较大会争抢VIP不争抢模式在keepalived配置文件vrrp_instance VI_1下 添加nopreempt

2. 双主热备测试

互相创建数据库,表,字段,插入数据,在另一数据库能查询到,即双主热备成功.