Centos 7.3 Install Mysql+keepalived 高可用

======================
172.16.19.90 主-备
172.16.19.91 主-备
虚拟地址172.16.19.200
======================
========================================================================================================================================
172.16.19.90-172.16.19.91配置安装
========================================================================================================================================
环境依赖包
yum install gcc gcc-c++ make zlib-devel wget vim tcl numactl libaio perl-Time-HiRes per-devel epel-release unzip tclkernel-devel openssl-devel popt-devel ipvsadm  libnl libnl-devel libnfnetlink-devel kernel-headers kernel-devel kernel-tools kernel-tools-libs sendmail -y

安装mysql
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar

查看系统mariadb-libs
rpm -qa | grep mariadb-libs

卸载系统的mariadb-libs
rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64
rpm -e --nodeps postfix-2.10.1-6.el7.x86_64

解压mysql
tar xvf mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar

安装mysql
rpm -ivh mysql-community-common-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.18-1.el7.x86_64.rpm

配置优化及功能开启（两台只需要更换server_id）
vi /etc/my.cnf
log_bin = mysql-bin
   server_id = 90
lower_case_table_names=1
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-slave-updates

启动mysql
systemctl start mysqld

开机启动mysql
systemctl enable mysqld

mysql生成随机密码
grep 'temporary password' /var/log/mysqld.log

mysql重新登陆(使用随机生成的密码登录)
mysql -uroot -p  

重置Mysql 密码以及权限
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Report@123';

允许root用户在任何地方进行远程登录，并具有所有库任何操作权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Report@123' WITH GRANT OPTION;
FLUSH PRIVILEGES;
========================================================================================================================================

————————————————————————————————————————————————————————————————————
配置双主双备
172.16.19.90  master （slave）
172.16.19.91  slave  （master）
————————————————————————————————————————————————————————————————————
172.16.19.90

创建专用账户
mysql -u root -p  -e "GRANT REPLICATION SLAVE,RELOAD,SUPER ON*.* TO 'hz'@'172.16.19.91' IDENTIFIED BY 'Report@123';"

查询master的状态
mysql -u root -p -e "show master status"
Enter password:
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      770 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

对端master
mysql -u root -p -e "change master to master_host='172.16.19.91',master_user='hz',master_password='Report@123',master_log_file='mysql-bin.000001',master_log_pos=462; start slave;"

检查从服务器的状态
mysql -u root -p -e "show slave status\G;"

注意一下两项同时为yes时为正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
========================================================================================================================================

========================================================================================================================================
172.16.19.91

创建专用账户：
mysql -u root -p  -e "GRANT REPLICATION SLAVE,RELOAD,SUPER ON*.* TO 'hz'@'172.16.19.90' IDENTIFIED BY 'Report@123';"

查询master的状态
mysql -u root -p -e "show master status"
Enter password:
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      462 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
 
对端master
mysql -u root -p -e "change master to master_host='172.16.19.90',master_user='hz',master_password='Report@123',master_log_file='mysql-bin.000001',master_log_pos=770; start slave;"

检查从服务器的状态
mysql -u root -p -e "show slave status\G;"

注意一下两项同时为yes时为正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
========================================================================================================================================
========================================================================================================================================
安装keepalived
yum install keepalived -y

查看目录结构
find / -name keepalived
/etc/selinux/targeted/active/modules/100/keepalived
/etc/sysconfig/keepalived
/etc/keepalived
/usr/sbin/keepalived
/usr/libexec/keepalived

邮件sendmail
systemctl start sendmail
systemctl enable sendmail
========================================================================================================================================


========================================================================================================================================
172.16.19.90
========================================================================================================================================
备份一个keepalived
cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

配置keepalived
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
     [e-mail]
     [e-mail]
     [e-mail]
   }
   notification_email_from [e-mail]
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.16.19.200
    }
}

virtual_server 172.16.19.200 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 172.16.19.90 3306 {
        weight 1

        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
    real_server 172.16.19.91 3306 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

========================================================================================================================================
172.16.19.91
========================================================================================================================================
备份一个keepalived
cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

配置keepalived
vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

    global_defs {
       notification_email {
         [e-mail]
         [e-mail]
         [e-mail]
       }
       notification_email_from [e-mail]
       smtp_server 127.0.0.1
       smtp_connect_timeout 30
       router_id LVS_DEVEL
    }
! Configuration File for keepalived

    global_defs {
       notification_email {
         [e-mail]
         [e-mail]
         [e-mail]
       }
       notification_email_from [e-mail]
       smtp_server 127.0.0.1
       smtp_connect_timeout 30
       router_id LVS_DEVEL
    }

    vrrp_instance VI_1 {
        state BACKUP
        interface ens160
        virtual_router_id 51
        priority 99
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress {
            172.16.19.200
        }
    }

    virtual_server 172.16.19.200 3306{
        delay_loop 6
        lb_algo rr
        lb_kind DR
        nat_mask 255.255.255.0
        persistence_timeout 50
        protocol TCP

        real_server 172.16.19.90 3306{
            weight 1
            TCP_CHECK {
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
                connect_port 3306
            }
        }
        real_server 172.16.19.91 3306{
            weight 1
            TCP_CHECK {
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
                connect_port 3306
            }
        }
    }

查看keepalived日志
cat /var/log/messages

查看结果信息
ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  172.16.19.200:3306 rr persistent 50
  -> 172.16.19.90:3306            Route   1      0          0         
  -> 172.16.19.91:3306            Route   1      0          0