#!/bin/sh
#author: array
users="monitoring"
passs="8121211210"
sockets="/data/hgame_M/run/mysql.sock"
OLD_IFS="$IFS"
rf=$(mktemp)
node_ip="hx_OL_hgame_M"
log_file="/data/shell/monitor_replication/monitor_replication_hgame.log"
IFS=','
db_user=($users)
db_pass=($passs)
db_socket=($sockets)
echo -e "=========`date +"%F %H:%M:%S"`=========\nstart exec check..." >> $log_file
for ((i=0;i<${#db_user[@]};i++))
do
echo "show slave status\G"|/usr/local/webserver/mysql-5.7/bin/mysql --user=${db_user[$i]} --password=${db_pass[$i]} --socket=${db_socket[$i]} > $rf 2>&1
IFS="$OLD_IFS"
repl_IO=$(cat $rf|grep "Slave_IO_Running"|cut -f2 -d':')
repl_SQL=$(cat $rf|grep "Slave_SQL_Running"|head -n 1|cut -f2 -d':')
repl_BEHIND=$(cat $rf|grep "Seconds_Behind_Master"|cut -f2 -d':')
#repl_IO='NO'
#alert down
if [ "$repl_IO" != " Yes" -o "$repl_SQL" != " Yes" ] ; then
echo "MYSQL HOT BACKUP NODE: ${node_ip} FAILED " >> $log_file
#python /data/shell/mail.py 邮箱地址 "MYSQL HOT BACKUP NODE: ${node_ip} FAILED" "${rf}"
python /data/shell/mail.py 邮箱地址 "MYSQL HOT BACKUP NODE: ${node_ip} FAILED" "${rf}"
if [ -f $rf ] ; then
rm $rf
fi
fi
# alert slow
if [ "$repl_BEHIND" != " NULL" ] && [[ "$repl_BEHIND" -ge 30 ]] ; then
echo "MYSQL HOT BACKUP NODE: ${node_ip} FAILED,slave Behind Master too long" >> $log_file
python /data/shell/mail.py ph.admin@weststarinc.co "MYSQL HOT BACKUP NODE: ${node_ip} FAILED,slave Behind Master too long" "${rf}"
if [ -f $rf ] ; then
rm $rf
fi
fi
if [ -f $rf ] ; then
rm $rf
fi
IFS=','
done
IFS="$OLD_IFS"
echo -e "check end\n==================" >> /data/shell/monitor_replication/monitor_replication_hgame.log
check_mysql_info.py
#coding:utf-8
import pymysql
import getopt
from sys import argv
import os
import telegram
import psutil
import time
host=argv[1]
user=argv[2]
passwd=argv[3]
dbname=argv[4]
port=argv[5]
def check_fuzi(command):
db = pymysql.connect(host=host,user=user,port=int(port),password=passwd,db=dbname)
cursor = db.cursor()
cursor.execute(command)
data = cursor.fetchall()
db.close()
return data
def getip():
hostname=os.popen("hostname").read()
out=os.popen("/sbin/ip a|grep inet|grep -v '127.0.0.1'|awk '{print $2}'").read()
return out,hostname
def send(contact,subject,content):
bot=telegram.Bot(token='639333282:AAGKWz0MqqtHO9GqLdKVZ7qmM8mMXNyJKVw')
chat_id=contact
bot.send_message(chat_id=chat_id, text=subject+'\n'+content)
def replication_info():
flag=0
command="show slave status"
data=check_fuzi(command)
io_thread,sql_thread=data[0][10],data[0][11]
while flag<1:
if io_thread!="Yes" or sql_thread!="Yes":
send(587326864,'{}从库{}断掉了,请进行测试[wrong]'.format(getip()[1],dbname),getip()[0])
time.sleep(3)
flag=flag+1
else:
break
def connections_info():
flag=0
command1="show global status like 'Threads_connected'"
command2="show global variables like 'max_user_connections'";
Threads_connected=int(check_fuzi(command1)[0][1])
#Threads_connected=check_fuzi(command1)[0]
max_connections=int(check_fuzi(command2)[0][1])
try:
result=Threads_connected/max_connections
except:
return
result=result*100
while flag<1:
if result>=60:
time.sleep(3)
flag=flag+1
send(587326864,'{}库{}连接数超过%60,请进行测试'.format(getip()[1],dbname),getip()[0])
else:
break
def buffer_pool_hit_info():
command1="show global status like 'innodb_buffer_pool_read_requests'"
command2="show global status like 'innodb_buffer_pool_reads'";
innodb_buffer_pool_read_requests=int(check_fuzi(command1)[0][1])
innodb_buffer_pool_reads=int(check_fuzi(command2)[0][1])
a=innodb_buffer_pool_read_requests - innodb_buffer_pool_reads
b=a/innodb_buffer_pool_read_requests
b=b*100
if b<99:
send(587326864,'{}库{}缓冲池命中率小于%99({}),请进行测试'.format(getip()[1],dbname,b),getip()[0])
def dirty_page():
flag=0
command1="show global status like 'Innodb_buffer_pool_pages_dirty'"
command2="show global status like 'Innodb_buffer_pool_pages_total'";
Innodb_buffer_pool_pages_dirty=int(check_fuzi(command1)[0][1])
Innodb_buffer_pool_pages_total=int(check_fuzi(command2)[0][1])
result=Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
result=result*100
while flag<1:
if result>60:
time.sleep(3)
flag=flag+1
send(587326864,'{}库{}脏页比例快到达75%,请进行优化'.format(getip()[1],dbname),getip()[0])
else:
break
def thread_run():
flag=0
command="show global status like 'threads_running' "
threads_running=int(check_fuzi(command)[0][1])
if threads_running<6:
return
while flag<1:
if threads_running>= int(psutil.cpu_count())*1.5:
time.sleep(3)
flag=flag+1
send(587326864,'{}库{}并发线程数大于cpu核心,请进数据查看'.format(getip()[1],dbname),getip()[0])
else:
break
def trans():
command="select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>30"
trx=check_fuzi(command)
print(trx)
def alive():
flag=0
command="select @@version"
try:
result=check_fuzi(command)[0][0]
except:
while flag<1:
time.sleep(3)
flag=flag+1
send(587326864,'{}库{}可能down掉,请进数据查看'.format(getip()[1],dbname),getip()[0])
replication_info()
connections_info()
#buffer_pool_hit_info()
dirty_page()
thread_run()
alive()
mysql备份脚本
find /data/backup/mmc/ -mtime +7 |grep ".gz"|xargs rm -f
/usr/local/webserver/mysql-5.7/bin/mysqldump -ubackuser -p1233 -S /data/mmc_S/run/mysql.sock --single-transaction --master-data=2 -E -R --triggers mmc | gzip > /data/backup/mmc/all_mmc`date +%Y%m%d`.sql.gz
if [ $? -eq 0 ];then
#cd /data/backup/mmc/
#/bin/gzip all_mmc`date +%Y%m%d`.sql
python /data/shell/mail.py "邮箱地址" "备份成功10.100.3.17$DATE" "ok"
else
python /data/shell/mail.py "邮箱地址" "备份失败10.100.3.17$DATE" "fail"
fi