采用Atlas+Keepalived實現MySQL讀寫分離、讀負載均衡
[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
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！