MySQL的一些功能实用的Linux shell脚本分享

2024-03-24 03:01:04 238

Memcached启动脚本

#vim/etc/init.d/memcached

#!/bin/bash
#=======================================================================================
#chkconfig:-8012
#description:Distributedmemorycachingdaemon
#processname:memcached
#=======================================================================================
IPADDR=`/sbin/ifconfigeth1|awk-F':''/inetaddr/{print$2}'|sed's/[a-zA-Z]//g'`
PORT="11211"
USER="root"
SIZE="2048"
CONNNUM="51200"
PIDFILE="/var/run/memcached.pid"
BINFILE="/usr/local/memcached/bin/memcached"
LOCKFILE="/var/lock/subsys/memcached"
RETVAL=0

start(){
echo-n$"Startingmemcached......"
$BINFILE-d-l$IPADDR-p$PORT-u$USER-m$SIZE-c$CONNNUM-P$PIDFILE
RETVAL=$?
echo
[$RETVAL-eq0]&&touch$LOCKFILE

return$RETVAL
}

stop(){
echo-n$"Shuttingdownmemcached......"
/sbin/killproc$BINFILE
RETVAL=$?
echo
[$RETVAL-eq0]&&rm-f$LOCKFILE

return$RETVAL
}

restart(){
stop
sleep1
start
}

reload(){
echo-n$"Reloadingmemcached......"
/sbin/killproc$BINFILE-HUP
RETVAL=$?
echo

return$RETVAL
}

case"$1"in
start)
start
;;

stop)
stop
;;

restart)
restart
;;

condrestart)
[-e$LOCKFILE]&&restart
RETVAL=$?
;;

reload)
reload
;;

status)
status$prog
RETVAL=$?
;;

*)
echo"Usage:$0{start|stop|restart|condrestart|status}"
RETVAL=1
esac

exit$RETVAL
#chmod+x/etc/init.d/memcached
#chkconfig--addmemcached
#chkconfig--level235memcachedon
#servicememcachedstart

binlog自动清理脚本

#vim/data/scripts/delete_mysql_binlog.sh

#!/bin/bash
#=======================================================================================
#用于删除MySQLMaster端已经同步完的binlog【需在Master端运行】，以减少磁盘空间
#每天凌晨5:30分运行一次
#
#注：需在Slave端添加允许Master端访问的帐号【帐号：check_binlog，密码：binlog_2356】
#运行于MySQLMaster端【目前只用于一主一从的同步模式，对于多从的情况暂时未考虑】
#=======================================================================================
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin

##Slave端连接信息
SLAVE_ADDR="XXX.XXX.XXX.XXX"
SLAVE_USER="check_binlog"
SLAVE_PWD="binlog_2356"

LOGFILE="/data/logs/db_sync_info.log"
PINGFILE="/tmp/mysqlping.log"

##MySQL状态信息查看命令
SQLCMD="showslavestatus"

#=======================================================================================
##检查MySQL是否已经运行
if[[`psaux|grepmysql[d]|wc-l`-eq0]];then
echoTheMySQLisnotrunningat:`date+%F""%H-%M-%S`>>${LOGFILE}
exit1
fi

##测试Slave端的连通性
nohupmysqladmin-h${SLAVE_ADDR}-u${SLAVE_USER}-p${SLAVE_PWD}ping>${PINGFILE}
retval=`grep"^error"${PINGFILE}`
rm-f${PINGFILE}
if[["${retval}X"!="X"]];then
echoTheMySQLSlavecannotbeconnectedat:`date+%F""%H-%M-%S`>>${LOGFILE}
exit1
fi

##检查是否合法的Slave
MASTER_ADDR=`mysql-h${SLAVE_ADDR}-u${SLAVE_USER}-p${SLAVE_PWD}-e"${SQLCMD}\G;"|awk'$1=="Master_Host:"{print$2}'`
LOCAL_ADDR=`/sbin/ifconfigeth1|awk-F':''/inetaddr/{print$2}'|sed's/[a-zA-Z]//g'`
if[["${MASTER_ADDR}"!="${LOCAL_ADDR}"]];then
echoTheMySQLSlaveisnotlawfulat:`date+%F""%H-%M-%S`>>${LOGFILE}
exit1
fi

##获得Slave端信息，以此来确定是否处于正常同步的情况
IO_STATUS=`mysql-h${SLAVE_ADDR}-u${SLAVE_USER}-p${SLAVE_PWD}-e"${SQLCMD}\G;"|awk'$1=="Slave_IO_Running:"{print$2}'`
SQL_STATUS=`mysql-h${SLAVE_ADDR}-u${SLAVE_USER}-p${SLAVE_PWD}-e"${SQLCMD}\G;"|awk'$1=="Slave_SQL_Running:"{print$2}'`
if[["${IO_STATUS}"!="Yes"||"${SQL_STATUS}"!="Yes"]];then
echoTheMySQLReplicationisnotsynchronousat:`date+%F""%H-%M-%S`>>${LOGFILE}
exit1
fi

##再做一次判断，以保证数据同步绝对正常【创建测试数据】
mysql-uroot-e"createdatabaseifnotexistsmytestdb;"
sleep3

retval=`mysql-h${SLAVE_ADDR}-u${SLAVE_USER}-p${SLAVE_PWD}-e"showdatabases;"|grepmytestdb`
mysql-uroot-e"dropdatabaseifexistsmytestdb;"
if[["${retval}X"="X"]];then
echoTheMySQLReplicationisnotsynchronousat:`date+%F""%H-%M-%S`>>${LOGFILE}
exit1
fi

##在已经同步的情况，还需要判断当前同步的binlog，以此来确定哪些已经是过期的binlog
SLAVE_BINLOG1=`mysql-h${SLAVE_ADDR}-u${SLAVE_USER}-p${SLAVE_PWD}-e"${SQLCMD}\G;"|awk'$1=="Master_Log_File:"{print$2}'`
SLAVE_BINLOG2=`mysql-h${SLAVE_ADDR}-u${SLAVE_USER}-p${SLAVE_PWD}-e"${SQLCMD}\G;"|awk'$1=="Relay_Master_Log_File:"{print$2}'`
##获得Master端，当前的binlog文件以及binlog路径
MASTER_BINLOG=`mysql-uroot-e"showmasterstatus;"|grep-v'^+'|tail-1|awk'{print$1}'`

##主从端已经同步到相同的binlog
if[["${SLAVE_BINLOG1}"="${SLAVE_BINLOG2}"&&"${SLAVE_BINLOG1}"="${MASTER_BINLOG}"]];then
CURR_BINLOG="${MASTER_BINLOG}"

##主从端已经同步，但从端的binlog还没有追赶到主端最新的binlog
elif[["${SLAVE_BINLOG1}"="${SLAVE_BINLOG2}"&&"${SLAVE_BINLOG1}"!="${MASTER_BINLOG}"]];then
CURR_BINLOG="${SLAVE_BINLOG1}"

##主从端已经同步，主从端的binlog一致，但relaylog还不一致
elif[["${SLAVE_BINLOG1}"!="${SLAVE_BINLOG2}"&&"${SLAVE_BINLOG1}"="${MASTER_BINLOG}"]];then
CURR_BINLOG="${SLAVE_BINLOG2}"

else
echoHasnoknownerrorat：`date+%F""%H-%M-%S`>>${LOGFILE}
exit1
fi

mysql-uroot-e"purgebinarylogsto'${CURR_BINLOG}';"
if[[$?-eq0]];then
echoClearMySQLbinlogisokat:`date+%F""%H-%M-%S`>>${LOGFILE}
fi

#crontab-e
3005***/data/scripts/delete_mysql_binlog.sh>/dev/null2>&1

修复MySQL主从同步

#!/bin/sh
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin

LOGFILE=/data/repair_mysql_sync_`date+%F`.log
SQLCMD1="showslavestatus"

##查看MySQL是否已启动
if[[`psaux|grepmysqld|grep-vgrep`"X"="X"]];then
echoTheMySQLisnotrunningat:`date+%F""%H-%M-%S`>>${LOGFILE}
exit1
fi

##获得MySQL从端Relaybinlog的路径
retval=`grep"^relay-log"/etc/my.cnf|grep-vrelay-log-|grep'/'`
if[["${retval}"="X"]];then
RELAY_BINLOG_PATH=`psaux|grep-wmysqld|grep-vgrep|awk'{print$13}'|awk-F'=''{print$2}'`
else
RELAY_BINLOG_PATH=`dirname$(echo${retval}|awk-F'=''{print$2}')`
fi

##查找master.info文件，用于定位Binlog信息
MASTER_FILE=`psaux|grep-wmysqld|grep-vgrep|awk'{print$13}'|awk-F'=''{print$2}'`/master.info
if[[!-e${MASTER_FILE}]];then
echoThisServerisnotMySQLSlaveat:`date+%F""%H-%M-%S`>>${LOGFILE}
exit1
fi

##获得当前的同步状态
IO_STATUS=`mysql-uroot-e"${SQLCMD1}\G;"|awk'$1=="Slave_IO_Running:"{print$2}'`
SQL_STATUS=`mysql-uroot-e"${SQLCMD1}\G;"|awk'$1=="Slave_SQL_Running:"{print$2}'`
if[["${IO_STATUS}"="Yes"&&"${SQL_STATUS}"="Yes"]];then
echoNow,TheMySQLReplicationissynchronousat:`date+%F""%H-%M-%S`>>${LOGFILE}
exit0
fi

##从master.info文件中，获得MySQL主端的同步信息
REPLI_INFO=`sed'/^$/d'${MASTER_FILE}|tail+2|head-5`
REPLI_BINLOG_FILE=`echo${REPLI_INFO}|awk'{print$1}'`
REPLI_IPADDR=`echo${REPLI_INFO}|awk'{print$3}'`
REPLI_USER=`echo${REPLI_INFO}|awk'{print$4}'`
REPLI_PWD=`echo${REPLI_INFO}|awk'{print$5}'`

##删除无用的Relaybinlog
rm-rf${RELAY_BINLOG_PATH}/*-relay-bin.*

##直接从0位置开始同步
SQLCMD2="changemastertomaster_host='${REPLI_IPADDR}',master_user='${REPLI_USER}',master_password='${REPLI_PWD}',"
SQLCMD2="${SQLCMD2}master_log_file='${REPLI_BINLOG_FILE}',master_log_pos=0"
mysql-uroot-e"stopslave;"
mysql-uroot-e"${SQLCMD2};"
mysql-uroot-e"startslave;"

##如果同步的过程中，出现重复记录导致同步失败，就跳过
whiletrue
do
sleep2
IO_STATUS=`mysql-uroot-e"${SQLCMD1}\G;"|awk'$1=="Slave_IO_Running:"{print$2}'`
SQL_STATUS=`mysql-uroot-e"${SQLCMD1}\G;"|awk'$1=="Slave_SQL_Running:"{print$2}'`
BEHIND_STATUS=`mysql-uroot-e"${SQLCMD1}\G;"|awk'$1=="Seconds_Behind_Master:"{print$2}'`
SLAVE_BINLOG1=`mysql-uroot-e"${SQLCMD1}\G;"|awk'$1=="Master_Log_File:"{print$2}'`
SLAVE_BINLOG2=`mysql-uroot-e"${SQLCMD1}\G;"|awk'$1=="Relay_Master_Log_File:"{print$2}'`

##出现错误，就将错误信息记录到日志文件，并跳过错误继续同步
if[["${IO_STATUS}"!="Yes"||"${SQL_STATUS}"!="Yes"]];then
ERRORINFO=`mysql-uroot-e"${SQLCMD1}\G;"|awk-F':''$1=="Last_Error"{print$2}'`
echo"TheMySQLsynchronouserrorinformation:${ERRORINFO}">>${LOGFILE}
mysql-uroot-e"stopslave;"
mysql-uroot-e"setGLOBALSQL_SLAVE_SKIP_COUNTER=1;"
mysql-uroot-e"startslave;"

##已完成同步，就正常退出
elif[["${IO_STATUS}"="Yes"&&"${SQL_STATUS}"="Yes"&&"${SLAVE_BINLOG1}"="${SLAVE_BINLOG2}"&&${BEHIND_STATUS}-eq0]];then
echoTheMySQLsynchronousisokat:`date+%F""%H-%M-%S`>>${LOGFILE}
break
fi
done

MySQL的一些功能实用的Linux shell脚本分享

热门推荐

随机推荐