MySQL双主复制

 

1.双主复制：实现和MHA相同的效果
    使用keepalive实现双主热备，防止单点故障的主从结构。
    两台mysql互做主从，各自安装keepalive实现热备，其他从服务器和热备的虚拟IP建立主从连接。

2.开启两台mysql，互相建立主从

在第一台mysql创建复制用户
mysql1:
grant replication slave on *.* to repl@'192.168.8.%' identified by '123';

在第二台mysql建立和第一台的主从关系
mysql2:
change master to
master_host='192.168.8.10',
master_user='repl',
master_port=3306,
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

在第一台mysql再建立和第二台的主从关系
mysql1:
change master to
master_host='192.168.8.20',
master_user='repl',
master_port=3306,
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

mysql1、mysql2:
show slave status \G

3.两台服务器安装keepalived
yum -y install keepalived

4.配置mysql1的keepalived
vim /etc/keepalived/keepalived.conf
修改为：
global_defs {
   router_id mysql1
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.8.254
    }
}
virtual_server 192.168.8.254 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 192.168.8.10 3306 {
        weight 1
        TCP_CHECK {
                connect_port 3306
                connect_timeout 3
                retry 3
                delay_before_retry 3
}
}
}
保存退出

启动：systemctl start keepalived

5.配置mysql2的keepalived
vim /etc/keepalived/keepalived.conf
修改为：
global_defs {
   router_id mysql2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.8.254
    }
}
virtual_server 192.168.8.254 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 192.168.8.20 3306 {
        weight 1
        TCP_CHECK {
                connect_port 3306
                connect_timeout 3
                retry 3
                delay_before_retry 3
}
}
}
保存退出

启动：systemctl start keepalived

6.两台服务器查看虚拟IP
ip a show dev ens33

7.开启第三台mysql,与虚拟IP建立主从
mysql3:
change master to
master_host='192.168.8.254',
master_user='repl',
master_port=3306,
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

show slave status \G

注：其他主机ping 192.168.8.254可能会不通，第三个从服务器也无法建立主从；
    keepalived切换几遍，再尝试ping。