MySQLLelove 2019-06-28
proxysql+keepalived+mysql+mha
优势,最大程序的降低脑裂风险,可以读写分离(需要开启相应的插件支持)
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:
解决办法:
修复主从关系即可
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>
直接将主库服务器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