采用Atlas+Keepalived实现MySQL读写分离、读负载均衡
jopen
9年前
[mysql-proxy] admin-username = sysadmin admin-password = admin2356!@() proxy-backend-addresses = 10.222.5.224:3306 proxy-read-only-backend-addresses = 10.240.95.107:3306,10.240.95.108:3306 pwds = health_check1:/iZxz+0GRoA=,health_check2:/iZxz+0GRoA= daemon = true keepalive = true event-threads = 16 log-level = message log-path = /usr/local/mysql-proxy/log sql-log = ON proxy-address = 0.0.0.0:3306 admin-address = 10.209.6.101:3307 charset = utf8
#!/bin/sh # # mysql-proxy This script starts and stops the mysql-proxy daemon # # chkconfig: - 78 30 # processname: mysql-proxy # description: mysql-proxy is a proxy daemon to mysql # config: /usr/local/mysql-proxy/conf/mysql-proxy.cnf # pidfile: /usr/local/mysql-proxy/log/mysql-proxy.pid # PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin DAEMON="/usr/local/mysql-proxy/bin/mysql-proxy" CONFIGFILE="/usr/local/mysql-proxy/conf/mysql-proxy.cnf" PIDFILE="/usr/local/mysql-proxy/log/mysql-proxy.pid" LOCKFILE="/var/lock/subsys/mysql-proxy" PROG=`basename $DAEMON` RETVAL=0 start() { echo -n $"Starting ${PROG}......" [ -x $DAEMON ] || exit 5 [ -f $CONFIGFILE ] || exit 6 ${DAEMON} --defaults-file=${CONFIGFILE} || echo -n "${PROG} already running" RETVAL=$? echo [[ $RETVAL -eq 0 ]] && touch $LOCKFILE return $RETVAL } stop() { echo -n $"Stopping ${PROG}......" if [[ `ps aux | grep bin/mysql-proxy | grep -v grep | wc -l` -gt 0 ]]; then kill -TERM `ps -A -oppid,pid,cmd | grep bin/mysql-proxy | grep -v grep | awk '{print $2}'` fi RETVAL=$? echo [[ $RETVAL -eq 0 ]] && rm -f $LOCKFILE $PIDFILE return $RETVAL } restart() { stop sleep 1 start } case "$1" in start) start ;; stop) stop ;; restart) restart ;; condrestart) [[ -e $LOCKFILE ]] && restart ;; *) echo "Usage: $0 {start|stop|restart|condrestart}" RETVAL=1 ;; esac exit $RETVAL 
global_defs { notification_email { lovezym5@126.com } notification_email_from lovezym5@126.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id dbproxy1 } vrrp_script chk_mysql_proxy_health { script "/data/scripts/keepalived_check_mysql_proxy.sh" interval 1 weight -2 } vrrp_instance VI_1 { state MASTER interface eth1 virtual_router_id 51 priority 100 advert_int 1 smtp_alert authentication { auth_type PASS auth_pass 123456 } virtual_ipaddress { 10.209.6.115 } track_script { chk_mysql_proxy_health } notify_master "/data/scripts/notify.sh master" notify_bakcup "/data/scripts/notify.sh backup" notify_fault "/data/scripts/notify.sh fault" } global_defs { notification_email { lovezym5@126.com } notification_email_from lovezym5@126.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id dbproxy2 } vrrp_script chk_mysql_proxy_health { script "/data/scripts/keepalived_check_mysql_proxy.sh" interval 1 weight -2 } vrrp_instance VI_1 { state BACKUP interface eth1 virtual_router_id 51 priority 90 advert_int 1 smtp_alert authentication { auth_type PASS auth_pass 123456 } virtual_ipaddress { 10.209.6.115 } track_script { chk_mysql_proxy_health } notify_master "/data/scripts/notify.sh master" notify_bakcup "/data/scripts/notify.sh backup" notify_fault "/data/scripts/notify.sh fault" } #!/bin/sh PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/sbin KEEPALIVE_CONF="/etc/keepalived/keepalived.conf" VIP=`grep -A 1 virtual_ipaddress ${KEEPALIVE_CONF} | tail -1 | sed 's/\t//g; s/ //g'` ETH1_ADDR=`/sbin/ifconfig eth1 | awk '/inet addr:/{print $2}' | awk -F: '{print $2}'` MONITOR="/usr/local/oms/agent/alarm/BusMonitorAgent" TOKEN="ha_monitor" function notify() { TITLE="$ETH1_ADDR to be $1: $VIP floating" CONTENT="vrrp transition, $ETH1_ADDR changed to be $1" ${MONITOR} -c 2 -f ${TOKEN} -t "${TITLE}" -i "${CONTENT}" } case "$1" in master) notify master exit 0 ;; backup) notify backup exit 0 ;; fault) notify fault exit 0 ;; *) echo 'Usage: `basename $0` {master|backup|fault}' exit 1 ;; esac #!/bin/sh PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/sbin if [[ `pgrep mysql-proxy | wc -l` -eq 0 ]]; then /sbin/service mysql-proxy start && sleep 5 [[ -z `pgrep mysql-proxy` ]] && /sbin/service keepalived stop fi
#!/bin/sh PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/sbin [[ $# -ne 3 ]] && echo "$0 端口号 协议类型 服务名" && exit 1 SRV_PORT=$1 ## 端口号 SRV_PROT=$2 ## 协议类型 SRV_NAME=$3 ## 服务名 MONITOR="/usr/local/oms/agent/alarm/BusMonitorAgent" TOKEN="ha_monitor" TITLE="${SRV_NAME}服务异常监控" CONTENT="${SRV_NAME}服务发生异常，已自动拉起！" ## 是否已正确扫描 SCAN_FLAG=0 function RESTART_SRV_AND_ALERT() { local CUR_SRV_NAME [[ $# -ne 1 ]] && exit 1 CUR_SRV_NAME=$1 TMP_SRV_NAME=`echo ${CUR_SRV_NAME} | tr '[A-Z]' '[a-z]'` [[ ! -f /etc/init.d/${TMP_SRV_NAME} ]] && TMP_SRV_NAME="${TMP_SRV_NAME}d" killall -9 ${TMP_SRV_NAME} if [[ -z `ps aux | grep ${TMP_SRV_NAME} | grep -v grep` ]]; then /sbin/service ${TMP_SRV_NAME} start >/dev/null 2>&1 fi ${MONITOR} -c 2 -f ${TOKEN} -t "${TITLE}" -i "${CONTENT}" rm -f `pwd`/connect_error.log } ETH1_ADDR=`/sbin/ifconfig eth1 | awk -F ':' '/inet addr/{print $2}' | sed 's/[a-zA-Z ]//g'` TMP_SRV_PROT=`echo ${SRV_PROT} | tr '[A-Z]' '[a-z]'` if [[ "${TMP_SRV_PROT}" == "tcp" ]]; then PROT_OPT="S" elif [[ "${TMP_SRV_PROT}" == "udp" ]]; then PROT_OPT="U" else echo "未知的协议类型！" && exit 1 fi ## 最多扫描3次，成功一次即可，以避免网络抖动而导致误判 for ((i=0; i<3; i++)); do RETVAL=`/usr/bin/nmap -n -s${PROT_OPT} -p ${SRV_PORT} ${ETH1_ADDR} | grep open` [[ -n "${RETVAL}" ]] && SCAN_FLAG=1;break || sleep 10 done ## 1、针对Atlas服务端口不通的情况，也就是服务彻底挂掉 [[ ${SCAN_FLAG} -ne 1 ]] && RESTART_SRV_AND_ALERT ${SRV_NAME} ## 2、检查Atlas服务是否正常工作，也就是服务端口正常，但访问异常的情况【高权限DB用户】 mysqladmin -h${ETH1_ADDR} -uhealth_check1 -p123456 --connect-timeout=15 --shutdown-timeout=15 ping [[ $? -ne 0 ]] && RESTART_SRV_AND_ALERT ${SRV_NAME} ## 3、检查Atlas服务是否正常工作，也就是服务端口正常，高权限DB用户访问也正常，但低权限 ## DB用户访问异常的情况【低权限DB用户】 mysqladmin -h${ETH1_ADDR} -uhealth_check2 -p123456 --connect-timeout=15 --shutdown-timeout=15 ping [[ $? -ne 0 ]] && RESTART_SRV_AND_ALERT ${SRV_NAME} #!/bin/sh # 切割Atlas的访问日志，同时清理15天之前的日志 # PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/sbin ## mysql-proxy日志路径 LOGPATH="/usr/local/mysql-proxy/log" [[ `/sbin/ip addr show eth1 | grep inet | wc -l` -eq 2 ]] || exit 1 cd ${LOGPATH} ## 日志切割 HISTORY_LOG_PATH=`date -d '-1 hour' +"%Y-%m-%d/sql_mysql-proxy_%H.log"` [[ -d `dirname ${HISTORY_LOG_PATH}` ]] || mkdir -p `dirname ${HISTORY_LOG_PATH}` cp -a sql_mysql-proxy.log ${HISTORY_LOG_PATH} echo > sql_mysql-proxy.log ## 日志清理 HISTORY_LOG_PATH=`date -d '15 days ago' +'%Y-%m-%d'` [[ -d ${HISTORY_LOG_PATH} ]] && rm -rf ${HISTORY_LOG_PATH} * * * * * (flock --timeout=0 /var/lock/check_service.lock /usr/local/mysql-proxy/bin/check_service.sh 3306 tcp mysql-proxy >/dev/null 2>&1) 00 * * * * /data/scripts/cut_and_clear_access_log.sh >/dev/null 2>&1
