高可用架构一

proxysql+keepalived+mysql+mha
优势，最大程序的降低脑裂风险，可以读写分离（需要开启相应的插件支持）

一、proxysql

1、安装

tar -zxvf proxysql.tar.gz -C /usr/local/

chmod -R 700 /usr/local/proxysql

cd /usr/local/proxysql/bin;./proxysqlServer.sh start

2、配置业务通过proxysql访问

1.  添加用户
cd /usr/local/proxysql/bin;

server02<2018-08-15 15:34:53> /usr/local/proxysql/bin

root># ./add_user.sh test test 添加业务用户（./add_user name password）
Warning: Using a password on the command line interface can be insecure.

2.添加mysql master地址
cd /usr/local/proxysql/bin;

root># ./set_server.sh 192.168.56.131 online   ---初始配置需要指定将流量打到哪台mysql上面
param  check ok!
Warning: Using a password on the command line interface can be insecure.

(注意2个proxysql都需要进行上面的配置)

3.验证proxysql
root># mysql -h 192.168.56.131 -uroot -p123456
在master上创建测试数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on test.* to test identified by 'test';
Query OK, 0 rows affected (0.00 sec)

然后使用proxysql测试验证（连接和插入正常）
##Proxysql的端口为6033
root># mysql -h 192.168.56.200 -P 6033 -u test -ptest
mysql> create table test(a int);
Query OK, 0 rows affected (0.18 sec)
 
mysql> insert into test values(1);
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)

二、keepalived配置

keepalived 和 proxysql配套使用，负责proxysql vip 的漂移

root># yum install keepalived -y


###master keepalived配置文件
server02<2018-08-15 16:04:30> /etc/keepalived
root># cat keepalived.conf 
global_defs {
   router_id Ha_keepalived
}
vrrp_script chk_proxysql {
        script "/usr/local/proxysql/bin/proxysqlServer.sh status"
        interval 1
        timeout 1
        fall 3
        rise 3
        user root
}
vrrp_instance VI_1 {
    state backup    ——两边都为backup
    interface eth0
    virtual_router_id 66
    priority 100
    advert_int 1
    unicast_src_ip 192.168.56.131   —使用单播模式
    unicast_peer {
       192.168.56.132
    }
    virtual_ipaddress {
       192.168.56.200
    }
    track_script {
        chk_proxysql
    }
    notify_master /etc/keepalived/notify.sh


###slave keepalved配置文件
server03<2018-08-15 16:04:30> /etc/keepalived
global_defs {
   router_id Ha_keepalived
}
vrrp_script chk_proxysql {
        script "/usr/local/proxysql/bin/proxysqlServer.sh status"
        interval 1
        timeout 1
        fall 3
        rise 3
        user root
}
vrrp_instance VI_1 {
    state backup
    interface eth2
    virtual_router_id 66
    priority 100
    advert_int 1
    unicast_src_ip 192.168.56.132  —使用单播模式
    unicast_peer {
       192.168.56.131
    }
    virtual_ipaddress {
       192.168.56.200
    }
    track_script {
        chk_proxysql
    }
    notify_master /etc/keepalived/notify.sh
}

三、MHA检测

###proxysql模式
root># masterha_check_ssh --conf=/etc/masterha/proxy_app_default.cnf

root># masterha_check_repl --conf=/etc/masterha/proxy_app_default.cnf

root># masterha_check_status --conf=/etc/masterha/proxy_app_default.cnf


###vip模式
root># masterha_check_ssh --conf=/etc/masterha/vip_app_default.cnf

root># masterha_check_repl --conf=/etc/masterha/vip_app_default.cnf

root># masterha_check_status --conf=/etc/masterha/vip_app_default.cnf

四、报错汇总

报错1：

解决办法：
修复主从关系即可

模拟故障1

kill 主库的mysql进程

查看mha的运行日志（发现mha正常切换了）
root># vim  /etc/masterha/app1/manager.log 

Wed Aug 15 16:22:18 2018 - [info]  All relay logs were successfully applied.
Wed Aug 15 16:22:18 2018 - [info] Getting new master's binlog name and position..
Wed Aug 15 16:22:18 2018 - [info]  mysql-bin.000004:120
Wed Aug 15 16:22:18 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.132', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Wed Aug 15 16:22:18 2018 - [info] Executing master IP activate script:
Wed Aug 15 16:22:18 2018 - [info]   /etc/masterha/app1/scripts/master_ip_failover_proxy --command=start --ssh_user=root --orig_master_host=192.168.56.131 --orig_master_ip=192.168.56.131 --orig_master_port=3306 --new_master_host=192.168.56.132 --new_master_ip=192.168.56.132 --new_master_port=3306 --new_master_user='mha' --new_master_password='123456'
Set read_only=0 on the new master.
Enabling write on the new master - 192.168.56.132:3306Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Wed Aug 15 16:22:18 2018 - [info]  OK.
Wed Aug 15 16:22:18 2018 - [info] ** Finished master recovery successfully.


mha切换成功 192.168.56.132 为新的master

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         1 |      | 3306 |         2 | bce5f247-a052-11e8-b2f6-000c2911ac5e |
|         3 |      | 3306 |         2 | b9543701-a052-11e8-b2f6-000c29c77f26 |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

mysql>

模拟故障2

直接将主库服务器shutdown
验证也是OK

五、手动切换维护

masterha_master_switch --master_state=alive --conf=$conf —orig_master_is_new_slave    —旧的master还存活，并且设置为slave节点（手动切换的时候，注意先停止mha——manager服务, $conf为指定的使用哪种模式的配置文件）

样例
masterha_master_switch --master_state=alive --conf=/etc/masterha/proxy_app_default.cnf --orig_master_is_new_slave

高可用架构二

vip+mysql+mha

mha+vip 模式和mha+proxysql模式的不同就在于要初始化不同文件（需要将vip脚本同步到mysql的master和node节点的/etc/masterha/app1/scripts/vip.sh）

1、vip.sh脚本

修改1
server01<2018-08-15 16:56:38> /etc/masterha/app1/scripts
root># cat vip.sh 
#!/bin/sh

if [ $# -ne 1 ];then
    echo "Usage vip.sh {start|stop} " 
    exit 0
fi

if="eth0"
key=2
vip="192.168.56.200"


start_vip(){
/sbin/ifconfig $if:$key $vip/24;arping -A -c 1 $vip
}

stop_vip(){
/sbin/ifconfig $if:$key down
}

case $1 in 
"stop")
    stop_vip $2;
    ;;
"start")
    start_vip $2;
    ;;
*)
    echo "Usage vip.sh {start|stop} "
    ;;
esac

手动绑定VIP到master服务器上
ifconfig eth0:2 192.168.56.200 up

###vip模式
root># masterha_check_ssh --conf=/etc/masterha/vip_app_default.cnf

root># masterha_check_repl --conf=/etc/masterha/vip_app_default.cnf

root># masterha_check_status --conf=/etc/masterha/vip_app_default.cnf

手动切换

手动切换
masterha_master_switch --master_state=alive --conf=/etc/masterha/vip_app_default.cnf --orig_master_is_new_slave

相关脚本
https://pan.baidu.com/s/1hTdh...