Keepalive+双主

一．建立3台服务器之间ssh互信
在mydb1，mydb2，mydb3服务器上分别执行：
ssh-keygen -t rsa
ssh-copy-id -i .ssh/id_rsa.pub root@192.168.1.101
ssh-copy-id -i .ssh/id_rsa.pub root@192.168.1.102
ssh-copy-id -i .ssh/id_rsa.pub root@192.168.1.103

在mydb1，mydb2，mydb3服务器上：
cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.101 mydb1
192.168.1.102 mydb2
192.168.1.103 mydb3

二. 配置主主，主从关系
在mydb1上操作
修改mysql配置文件：
vi /app/mysqldata/3306/my.cnf
在原有文件添加以下参数：
[mysqld]
server_id=1013306
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
log-bin = /app/mysqldata/3306/binlog/mysql-bin
log-bin-index = /app/mysqldata/3306/binlog/mysql-bin.index

启动mysql服务：
[root@mydb1 ~]# mysql_db_startup.sh 3306

添加复制用户并授权：
[root@mydb1 ~]# mysqlplus.sh 3306
(root@localhost) [(none)]> create user xtrabk@'192.168.1.%' identified by 'onlybackup';
(root@localhost) [(none)]> grant reload,lock tables,process,Replication client,super on *.* to xtrabk@'192.168.1.%';
(root@localhost) [(none)]> create user rep1@'192.168.1.%' identified by 'rep1';
(root@localhost) [(none)]> grant replication slave on *.* to 'rep1'@'192.168.1.%';
(root@localhost) [(none)]> flush privileges;

加载半同步复制模块：
(root@localhost) [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
(root@localhost) [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

毫秒单位，5秒该参数主服务器等待确认消息5秒后，不再等待，变为异步方式。
SET GLOBAL rpl_semi_sync_master_timeout = 5000 ;

在mydb1上建表，插入数据，使用Python脚本

创建完整备份
[root@mydb1 ~]# /app/mysqldata/scripts/my_full_backup.sh
复制和准备备份集
[root@mydb1 tmp]# scp -r xtrabackup/ mydb2:`pwd`
[root@mydb1 tmp]# scp -r xtrabackup/ mydb3:`pwd`

在mydb2上操作
mysql配置文件：
vi /app/mysqldata/3306/my.cnf
在原有文件添加以下参数：
[mysqld]
server_id = 1023306
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
log-bin = /app/mysqldata/3306/binlog/mysql-bin
log-bin-index = /app/mysqldata/3306/binlog/mysql-bin.index

进行数据恢复
[root@mydb2 ~]# /app/mysqldata/scripts/my_full_recover.sh

[root@mydb2 ~]# mysqlplus.sh 3306

配置Slave节点复制环境
查看备份文件中的位置信息
[root@mydb2 full]# cat xtrabackup_binlog_info
mysql-bin.000002 236874 9831a804-8c25-11e8-9a69-000c2983201e:1-3,
a5d58971-9ee1-11e8-8767-000c2983201e:1-576

设置GTID_PURGED
(root@localhost) [(none)]> prompt Slave>
Slave>stop slave;
Query OK, 0 rows affected (0.00 sec)
Slave>reset master;
Query OK, 0 rows affected (0.02 sec)
Slave>set global GTID_PURGED='9831a804-8c25-11e8-9a69-000c2983201e:1-3,a5d58971-9ee1-11e8-8767-000c2983201e:1-576';
Query OK, 0 rows affected (0.00 sec)
Slave > change master to master_host='192.168.1.101',master_port=3306,master_user='rep1',master_password='rep1',MASTER_AUTO_POSITION =1;

启动从库服务
Slave > start slave;

查看主从复制是否成功：
Slave > show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

查看mydb2上的master日志状态
Slave>show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| mysql-bin.000001 | 154 | | | 9831a804-8c25-11e8-9a69-000c2983201e:1-3,
a5d58971-9ee1-11e8-8767-000c2983201e:1-576 |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在mydb3上操作
mysql配置文件：
vi /app/mysqldata/3306/my.cnf
在原有文件添加以下参数：
[mysqld]
server_id = 1033306
read_only = 1
relay_log_purge = 0
log_slave_updates = 1
relay_log = /data/mysqldata/3306/binlog/mysql-relay-bin
relay_log_index = /data/mysqldata/3306/binlog/mysql-relay-bin.index
log-bin = /data/mysqldata/3306/binlog/mysql-bin
log-bin-index = /data/mysqldata/3306/binlog/mysql-bin.index

进行数据恢复
[root@mydb3 ~]# /app/mysqldata/scripts/my_full_recover.sh

[root@mydb3 ~]# mysqlplus.sh 3306

配置Slave节点复制环境
查看备份文件中的位置信息
[root@mydb3 full]# cat xtrabackup_binlog_info
mysql-bin.000002 236874 9831a804-8c25-11e8-9a69-000c2983201e:1-3,
a5d58971-9ee1-11e8-8767-000c2983201e:1-576

启动从库服务
Slave > start slave;

查看主从复制是否成功：
Slave > show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在mydb1上操作
将主指向mydb2：
(root@localhost) [(none)]> prompt Slave>
Slave > change master to master_host='192.168.1.102',master_port=3306,master_user='rep1',master_password='rep1',MASTER_AUTO_POSITION =1;

启动从库服务：
Slave > start slave;

查看主从复制是否成功：
Slave > show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在mydb2上，插入数据，使用Python脚本

增加高可用能力：
Keepalived主要功能：
实现IP地址的漂移
执行健康检查
[root@mydb1 /]# wget http://www.keepalived.org/software/keepalived-1.4.5.tar.gz
[root@mydb1 /]# tar xvfz keepalived-1.4.5.tar.gz
[root@mydb1 /]# cd keepalived-1.4.5
[root@mydb1 keepalived-1.4.5]# ./configure --prefix=/usr/local/keepalived
[root@mydb1 keepalived-1.4.5]# make && make install
复制文件到相关路径，以方便调用
[root@mydb1 /]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@mydb1 ~]# vim /etc/init.d/keepalived
#!/bin/sh
#
# Startup script for the Keepalived daemon
#
# processname: keepalived
# pidfile: /var/run/keepalived.pid
# config: /etc/keepalived/keepalived.conf
# chkconfig: - 21 79
# description: Start and stop Keepalived

# Source function library
. /etc/rc.d/init.d/functions

# Source configuration file (we set KEEPALIVED_OPTIONS there)
. /etc/sysconfig/keepalived

RETVAL=0

prog="keepalived"

start() {
    echo -n $"Starting $prog: "
    daemon keepalived ${KEEPALIVED_OPTIONS}
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && touch /var/lock/subsys/$prog
}

stop() {
    echo -n $"Stopping $prog: "
    killproc keepalived
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$prog
}

reload() {
    echo -n $"Reloading $prog: "
    killproc keepalived -1
    RETVAL=$?
    echo
}

# See how we were called.
case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    reload)
        reload
        ;;
    restart)
        stop
        start
        ;;
    condrestart)
        if [ -f /var/lock/subsys/$prog ]; then
            stop
            start
        fi
        ;;
    status)
        status keepalived
        RETVAL=$?
        ;;
    *)
        echo "Usage: $0 {start|stop|reload|restart|condrestart|status}"
        RETVAL=1
esac

exit $RETVAL

[root@mydb1 ~]# chmod +x /etc/init.d/keepalived
[root@mydb1 /]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
默认情况下Keepalived会查找/etc/keepalived/keepalived.conf文件
[root@mydb1 /]# mkdir -p /etc/keepalived
[root@mydb1 /]# vim /etc/keepalived/keepalived.conf
!Configuration File for keepalived
global_defs {
notification_email {
  allenhu@sina.com
   }
notification_email_from allenhu@sina.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id MySQL_HA
}
vrrp_script check_run {
script "/usr/local/keepalived/mysql.sh"
interval 5
}
vrrp_instance HA_1 {
state BACKUP
        nopreempt
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
  auth_type PASS
  auth_pass 3306
       }
track_script {
  check_run
}
virtual_ipaddress {
  192.168.1.205/24
}
}
[root@mydb1 /]# vim /usr/local/keepalived/mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=msds007
MYSQL_SOCKET=/app/mysqldata/3306/mysql.sock
CHECK_TIME=3
#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u${MYSQL_USER} -p${MYSQL_PASSWORD} -S ${MYSQL_SOCKET} -e "show status;" >/dev/null 2>&1
    if [ $? = 0 ] ;then
    MYSQL_OK=1
    else
    MYSQL_OK=0
    fi
    return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
    let "CHECK_TIME -= 1"
    check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
    CHECK_TIME=0
    exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
    /etc/init.d/keepalived stop
    exit 1
fi
sleep 1
done

[root@mydb1 /]# chmod +x /usr/local/keepalived/mysql.sh
[root@mydb1 /]# /etc/init.d/keepalived start

[root@mydbl1 ~]# tail -100f /var/log/messages
Aug 13 20:47:55 mydb1 systemd: Starting LVS and VRRP High Availability Monitor...
Aug 13 20:47:55 mydb1 Keepalived[22449]: Starting Keepalived v1.4.5 (05/26,2018)
Aug 13 20:47:55 mydb1 Keepalived[22449]: Running on Linux 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 (built for Linux 3.10.0)
Aug 13 20:47:55 mydb1 Keepalived[22449]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 13 20:47:55 mydb1 systemd: PID file /var/run/keepalived.pid not readable (yet?) after start.
Aug 13 20:47:55 mydb1 Keepalived[22451]: Starting Healthcheck child process, pid=22452
Aug 13 20:47:55 mydb1 Keepalived[22451]: Starting VRRP child process, pid=22453
Aug 13 20:47:55 mydb1 systemd: Started LVS and VRRP High Availability Monitor.
Aug 13 20:47:55 mydb1 Keepalived_healthcheckers[22452]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: Registering Kernel netlink reflector
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: Registering Kernel netlink command channel
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: Registering gratuitous ARP shared channel
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: WARNING - default user 'keepalived_script' for script execution does not exist - please create.
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: SECURITY VIOLATION - scripts are being executed but script_security not enabled.
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: VRRP_Instance(HA_1) removing protocol VIPs.
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: Using LinkWatch kernel netlink reflector...
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: VRRP_Instance(HA_1) Entering BACKUP STATE
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Aug 13 20:47:55 mydb1 Keepalived_vrrp[22453]: VRRP_Script(check_run) succeeded
Aug 13 20:47:59 mydb1 Keepalived_vrrp[22453]: VRRP_Instance(HA_1) Transition to MASTER STATE
Aug 13 20:48:00 mydb1 Keepalived_vrrp[22453]: VRRP_Instance(HA_1) Entering MASTER STATE
Aug 13 20:48:00 mydb1 Keepalived_vrrp[22453]: VRRP_Instance(HA_1) setting protocol VIPs.
Aug 13 20:48:00 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:00 mydb1 Keepalived_vrrp[22453]: VRRP_Instance(HA_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.1.205
Aug 13 20:48:00 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:00 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:00 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:00 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:00 mydb1 avahi-daemon[797]: Registering new address record for 192.168.1.205 on ens33.IPv4.
Aug 13 20:48:05 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:05 mydb1 Keepalived_vrrp[22453]: VRRP_Instance(HA_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.1.205
Aug 13 20:48:05 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:05 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:05 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205
Aug 13 20:48:05 mydb1 Keepalived_vrrp[22453]: Sending gratuitous ARP on ens33 for 192.168.1.205

[root@mydb2 /]# wget http://www.keepalived.org/software/keepalived-1.4.5.tar.gz
[root@mydb2 /]# tar xvfz keepalived-1.4.5.tar.gz
[root@mydb2 /]# cd keepalived-1.4.5
[root@mydb2 keepalived-1.4.5]# ./configure --prefix=/usr/local/keepalived
[root@mydb2 keepalived-1.4.5]# make && make install
复制文件到相关路径，以方便调用
[root@mydb2 /]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@mydb2 ~]# vim /etc/init.d/keepalived
#!/bin/sh
#
# Startup script for the Keepalived daemon
#
# processname: keepalived
# pidfile: /var/run/keepalived.pid
# config: /etc/keepalived/keepalived.conf
# chkconfig: - 21 79
# description: Start and stop Keepalived

# Source function library
. /etc/rc.d/init.d/functions

# Source configuration file (we set KEEPALIVED_OPTIONS there)
. /etc/sysconfig/keepalived

RETVAL=0

prog="keepalived"

start() {
    echo -n $"Starting $prog: "
    daemon keepalived ${KEEPALIVED_OPTIONS}
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && touch /var/lock/subsys/$prog
}

stop() {
    echo -n $"Stopping $prog: "
    killproc keepalived
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$prog
}

reload() {
    echo -n $"Reloading $prog: "
    killproc keepalived -1
    RETVAL=$?
    echo
}

exit $RETVAL

[root@mydb2 ~]# chmod +x /etc/init.d/keepalived
[root@mydb2 /]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
默认情况下Keepalived会查找/etc/keepalived/keepalived.conf文件
[root@mydb2 /]# mkdir -p /etc/keepalived
[root@mydb2 /]# vim /etc/keepalived/keepalived.conf
!Configuration File for keepalived
global_defs {
notification_email {
  allenhu@sina.com
   }
notification_email_from allenhu@sina.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id MySQL_HA
}
vrrp_script check_run {
script "/usr/local/keepalived/mysql.sh"
interval 5
}
vrrp_instance HA_1 {
state BACKUP
        nopreempt
interface ens33
virtual_router_id 51
priority 90
advert_int 1
authentication {
  auth_type PASS
  auth_pass 3306
       }
track_script {
  check_run
}
virtual_ipaddress {
  192.168.1.205/24
}
}
[root@mydb2 /]# vim /usr/local/keepalived/mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=msds007
MYSQL_SOCKET=/app/mysqldata/3306/mysql.sock
CHECK_TIME=3
#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u${MYSQL_USER} -p${MYSQL_PASSWORD} -S ${MYSQL_SOCKET} -e "show status;" >/dev/null 2>&1
    if [ $? = 0 ] ;then
    MYSQL_OK=1
    else
    MYSQL_OK=0
    fi
    return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
    let "CHECK_TIME -= 1"
    check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
    CHECK_TIME=0
    exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
    /etc/init.d/keepalived stop
    exit 1
fi
sleep 1
done

[root@mydb2 /]# chmod +x /usr/local/keepalived/mysql.sh
[root@mydb2 /]# /etc/init.d/keepalived start

[root@mydbl2 ~]# tail -100f /var/log/messages
Aug 14 04:51:27 mydb2 systemd: Starting LVS and VRRP High Availability Monitor...
Aug 14 04:51:27 mydb2 Keepalived[13561]: Starting Keepalived v1.4.5 (05/26,2018)
Aug 14 04:51:27 mydb2 Keepalived[13561]: Running on Linux 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 (built for Linux 3.10.0)
Aug 14 04:51:27 mydb2 Keepalived[13561]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 14 04:51:27 mydb2 systemd: PID file /var/run/keepalived.pid not readable (yet?) after start.
Aug 14 04:51:27 mydb2 Keepalived[13563]: Starting Healthcheck child process, pid=13564
Aug 14 04:51:27 mydb2 Keepalived[13563]: Starting VRRP child process, pid=13565
Aug 14 04:51:27 mydb2 systemd: Started LVS and VRRP High Availability Monitor.
Aug 14 04:51:27 mydb2 Keepalived_healthcheckers[13564]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: Registering Kernel netlink reflector
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: Registering Kernel netlink command channel
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: Registering gratuitous ARP shared channel
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: WARNING - default user 'keepalived_script' for script execution does not exist - please create.
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: SECURITY VIOLATION - scripts are being executed but script_security not enabled.
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: VRRP_Instance(HA_1) removing protocol VIPs.
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: Using LinkWatch kernel netlink reflector...
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: VRRP_Instance(HA_1) Entering BACKUP STATE
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Aug 14 04:51:27 mydb2 Keepalived_vrrp[13565]: VRRP_Script(check_run) succeeded

[root@mydb3 ~]# /usr/local/mysql/bin/mysql -h 192.168.1.205 -udba_user -pmsds007 -S /app/mysqldata/3306/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.7.22-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL故障转移测试：
通过查看/var/log/messges日志，看出主备切换过程
在192.168.1.101上关闭MySQL服务，看VIP是否会切换到192.168.1.102上
开启192.168.1.101上的MySQL和keepalived，然后关闭192.168.1.102上的MySQL，看VIP是否会切换到192.168.1.101上

keepalived启动流程：
主进程
healthcheck
Vrrp
先进入backup state，运行一次vrrp_script，成功后，发现没有主 -> master -> 拉起vip -> 完成启动

切换的流程：
原keepalived master节点，运行检查脚本异常，则keepalived进入FAULT状态，释放vip，原backup的keepalived会接管vip

主节点挂掉：
VRRP通信断掉了

BACKUP
MASTER
FAULT