高可用架构一

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

一、proxysql

1、安装

tar -zxvf proxysql.tar.gz -C /usr/local/chmod -R 700 /usr/local/proxysqlcd /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/binroot># ./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 1timeout 1fall 3rise 3user root
}
vrrp_instance VI_1 {state backup    ——两边都为backupinterface eth0virtual_router_id 66priority 100advert_int 1unicast_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 1timeout 1fall 3rise 3user root
}
vrrp_instance VI_1 {state backupinterface eth2virtual_router_id 66priority 100advert_int 1unicast_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.cnfroot># masterha_check_repl --conf=/etc/masterha/proxy_app_default.cnfroot># masterha_check_status --conf=/etc/masterha/proxy_app_default.cnf###vip模式
root># masterha_check_ssh --conf=/etc/masterha/vip_app_default.cnfroot># masterha_check_repl --conf=/etc/masterha/vip_app_default.cnfroot># 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 为新的mastermysql> 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/shif [ $# -ne 1 ];thenecho "Usage vip.sh {start|stop} " exit 0
fiif="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.cnfroot># masterha_check_repl --conf=/etc/masterha/vip_app_default.cnfroot># 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...