VirsualBox部署CentOS7 Keepalived+LVS+MGR多主实践

参考了文章https://www.cnblogs.com/JiangLe/p/6727281.html，谢谢该文的作者，但完全按照该文操作没有成功，做了一些调整：

    1、由于需要多个虚拟机，偷懒安装了mysql后复制，结果MGR死活连不上，虚拟机文件不要初始化mysql后再复制，如果先初始化再复制虚拟机文件会造成两台服务器的MEMBER_ID相同，导致两台mysql无法找到对方。应当在各个干净的虚拟机上初始化MySQL。

    2、虚拟机必须设置静态IP地址。

    3、增加了hosts的配置以及my.cnf中增加report-host的配置。

====================开始了，割一下====================

环境准备：

VirsualBox所在主机的网络共享设置：
首先在『控制面板\网络和 Internet\网络连接』中找到『本地连接』或者『以太网』，就是能联网的网卡。
右键它选择属性，然后点最上面的『共享』，选中『允许其他用户通过此Internet连接到互联网』，然后在下面选择 Vbox 的 Host-only 的网卡。

确定之后 VirtualBox 的网卡 IP 会变成 192.168.137.1 。

准备三台安装mysql的虚拟机：192.168.137.100、192.168.137.101、192.168.137.102

virsualbox虚拟机设置：

        网络设置中的连接方式设置为；仅主机（Host-Only）方式

开启虚拟机

关闭防火墙 
    systemctl stop firewalld
永久防火墙开机自关闭 
    systemctl disable firewalld

关闭selinux：
    查看selinux状态
        sestatus
    临时关闭SELinux:
        setenforce 0
    永久关闭SELinux:
        编辑/etc/selinux/config文件
        SELINUX=disabled

设置各个虚拟机的网络(必须静态IP，否则MGR无法工作)：
编辑/etc/sysconfig/network-scripts/ifcfg-enp0s3
BOOTPROTO="static"
IPADDR=192.168.137.100
NETMASK=255.255.255.0
GATEWAY=192.168.137.1

设置 DNS：
vi /etc/resolv.conf
nameserver 192.168.137.1

重启网卡：
    service network restart;
查看网卡地址：
    ip addr

安装MySQL

下载：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-el7-x86_64.tar

解压后里面有两个文件：mysql-5.7.22-el7-x86_64.tar.gz和mysql-test-5.7.22-el7-x86_64.tar.gz

将mysql-5.7.22-el7-x86_64.tar.gz上传至CentOS的/tmp文件夹下

安装mysql：
cd /tmp/
tar -xvf mysql-5.7.22-el7-x86_64.tar.gz -C /usr/local/

cd /usr/local/
ln -s mysql-5.7.22-el7-x86_64 mysql

创建mysql数据目录 与 增加系统用户mysql：
mkdir -p /database/mysql/data/3306
useradd mysql
chown -R mysql:mysql /database/mysql/data/3306
chown -R mysql:mysql /usr/local/mysql*

************将虚拟机复制两份，并获得虚拟机的地址************

修改hosts;
192.168.137.100 mgr1 host_192_168_137_100
192.168.137.101 mgr2 host_192_168_137_101
192.168.137.102 mgr3 host_192_168_137_102

增加mysql的配置文件：
touch /etc/my.cnf
加入下面内容：
[mysqld]
basedir=/usr/local/mysql/
datadir=/database/mysql/data/3306
port=3306
socket=/tmp/mysql.sock

server_id=1
gtid_mode=on
enforce_gtid_consistency=on
master_info_repository=table
relay_log_info_repository=table
binlog_checksum=none
log_slave_updates=on
log_bin = mysql-bin
log-error=mysql.log
binlog_format=row
report-host=192.168.137.100 #本机的ip


transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.137.100:33060"
loose-group_replication_group_seeds= "192.168.137.100:33060,192.168.137.101:33060,192.168.137.102:33060"
loose-group_replication_bootstrap_group= off

配置mysql与systemd结合：
touch /usr/lib/systemd/system/mysql.service
加入下面内容：
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
#LimitNOFILE = 5000
#Restart=on-failure
#RestartPreventExitStatus=1

初始化mysql数据库（如果先初始化再复制虚拟机文件会造成两台服务器的MEMBER_ID相同，导致两台mysql无法找到对方）：
cd /usr/local/mysql/
./bin/mysqld --defaults-file=/etc/my.cnf --datadir=/database/mysql/data/3306/ --user=mysql --initialize-insecure

设置mysql开机启动:
systemctl enable mysql

启动mysql：
systemctl start mysql

为了方便使用重新设置一下PATH环境变量：
echo 'PATH=/usr/local/mysql/bin/:$PATH' >>/etc/profile
source /etc/profile

配置mgr的第一个结点：
mysql -u root -p

set sql_log_bin=0;
create user mgruser@'%' identified by 'mtls@352';
grant replication slave,replication client on *.* to mgruser@'%';
create user mgruser@'127.0.0.1' identified by 'mtls@352';
grant replication slave,replication client on *.* to mgruser@'127.0.0.1';
create user mgruser@'localhost' identified by 'mtls@352';
grant replication slave,replication client on *.* to mgruser@'localhost';
FLUSH PRIVILEGES;
set sql_log_bin=1;

change master to 
    master_user='mgruser',
    master_password='mtls@352'
    for channel 'group_replication_recovery';
    
install plugin group_replication soname 'group_replication.so';

#stop group_replication;

#reset master;

set global group_replication_bootstrap_group=on;

set global group_replication_single_primary_mode=off;

start group_replication;

set global group_replication_bootstrap_group=off;

select * from performance_schema.replication_group_members;

配置mgr的第二个结点： 
mysql -u root -p

set sql_log_bin=0;
create user mgruser@'%' identified by 'mtls@352';
grant replication slave,replication client on *.* to mgruser@'%';
create user mgruser@'127.0.0.1' identified by 'mtls@352';
grant replication slave,replication client on *.* to mgruser@'127.0.0.1';
create user mgruser@'localhost' identified by 'mtls@352';
grant replication slave,replication client on *.* to mgruser@'localhost';
set sql_log_bin=1;

change master to 
    master_user='mgruser',
    master_password='mtls@352'
    for channel 'group_replication_recovery';

install plugin group_replication soname 'group_replication.so';

#stop group_replication;

set global group_replication_allow_local_disjoint_gtids_join=ON;

set global group_replication_single_primary_mode=off;

start group_replication;

select * from performance_schema.replication_group_members;

验证mgr各个结点是否正常：

    登录192.168.137.100：

    mysql -u root -p

    select * from performance_schema.replication_group_members ;

    create database tempdb char set utf8;
    use tempdb;
    create table person(id int not null auto_increment primary key,name varchar(4),v int) char set utf8;
    insert into person(name,v) values('zs',100),('ls',200),('ww',300);

    

    登录192.168.137.101：

    mysql -u root -p

    use tempdb
    
    select * from person;
    
    insert into person(name,v) values('aa',400);
    
    insert into person(name,v) values('bb',500);
    
    停掉一个从mysql：
    service mysql stop
    在主mysql插入一条数据
    启动从mysql，启动复制
    service mysql start
    select * from person;

    查看数据已经自动同步到从mysql了

其他常用命令：
修改密码:
set password for root@localhos =password('1234');
允许root远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1234' WITH GRANT OPTION;

######################################################################

部署Keepalived+LVS

新增两台虚拟机：192.168.137.103（主lvs）和192.168.137.104（从lvs）

安装依赖的程序

yum install wget
yum install ipvsadm
yum install keepalived

yum install net-tools

启动防火墙：systemctl start firewalld.service
开机时启用防火墙：systemctl enable firewalld.service

开放3306端口：
firewall-cmd --zone=public --add-port=3306/tcp --permanent
重新载入生效：
firewall-cmd --reload

编辑/etc/keepalived/keepalived.conf文件：

! Configuration File for keepalived
global_defs {
notification_email {
              test@qq.com 
        }
        notification_email_from test@qq.com 
        smtp_server smtp.qq.com 
        smtp_connect_timeout 30
        router_id LVS1
}
vrrp_sync_group test {
group {
       loadbalance
}
}
 
vrrp_instance loadbalance {
        state BACKUP
        interface enp0s3
        lvs_sync_daemon_inteface enp0s3
        virtual_router_id 51
        priority 180
        advert_int 5
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.137.200
}
}
 
virtual_server 192.168.137.200 3306 {
        delay_loop 6
        lb_algo rr
        lb_kind DR
        #persistence_timeout 20
        protocol TCP
        real_server 192.168.137.100 3306 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306 
}
        }
        real_server 192.168.137.101 3306 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306 
}
        }
        real_server 192.168.137.102 3306 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306 
}
        }
}

在从LVS服务器上部署一样的keepalived和lvs环境
拷贝主LVS服务器的keepalived.conf文件
只修改一下两项：
router_id LVS2
priority 150

在sql服务器上新增文件/etc/rc.d/init.d/realserver.sh

#!/bin/bash
# description: Config realserver lo and apply noarp
 
SNS_VIP=192.168.137.200

case "$1" in
start)
 ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
 /sbin/route add -host $SNS_VIP dev lo:0
 echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
 echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
 sysctl -p >/dev/null 2>&1
 echo "RealServer Start OK"
 ;;
stop)
 ifconfig lo:0 down
 route del $SNS_VIP >/dev/null 2>&1
 echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
 echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
 echo "RealServer Stoped"
 ;;
 *)
 echo "Usage: $0 {start|stop}"
 exit 1
esac
exit 0

赋予执行权限
chmod +x /etc/rc.d/init.d/functions
chmod +x /etc/rc.d/init.d/realserver.sh
#启动
/etc/rc.d/init.d/realserver.sh start 

启动keepalived
service keepalived start 

用Navicat for MySQL登录虚拟IP 192.168.137.200即可对MGR集群进行操作即可。