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

站长资源 2024/11/23 佚名

2 0 1

Memcached启动脚本

# vim /etc/init.d/memcached

#!/bin/bash
#=======================================================================================
# chkconfig: - 80 12
# description: Distributed memory caching daemon
# processname: memcached
#=======================================================================================
IPADDR=`/sbin/ifconfig eth1 | awk -F ':' '/inet addr/{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 $"Starting memcached......"
  $BINFILE -d -l $IPADDR -p $PORT -u $USER -m $SIZE -c $CONNNUM -P $PIDFILE
  RETVAL=$"Shutting down memcached......"
  /sbin/killproc $BINFILE
  RETVAL=$"Reloading memcached......"
  /sbin/killproc $BINFILE -HUP
  RETVAL=$"$1" in
start)
  start
  ;;
        
stop)
  stop
  ;;
        
restart)
  restart
  ;;
        
condrestart)
  [ -e $LOCKFILE ] && restart
  RETVAL=$"Usage: $0 {start|stop|restart|condrestart|status}"
  RETVAL=1
esac

exit $RETVAL
# chmod +x /etc/init.d/memcached
# chkconfig --add memcached
# chkconfig --level 235 memcached on
# service memcached start

binlog 自动清理脚本

# vim /data/scripts/delete_mysql_binlog.sh

#!/bin/bash
#=======================================================================================
#  用于删除 MySQL Master 端已经同步完的 binlog【需在 Master 端运行】，以减少磁盘空间
#  每天凌晨 5:30 分运行一次
#
#  注：需在 Slave 端添加允许 Master 端访问的帐号【帐号：check_binlog，密码：binlog_2356】
#     运行于 MySQL Master 端【目前只用于一主一从的同步模式，对于多从的情况暂时未考虑】
#=======================================================================================
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="show slave status"
   
#=======================================================================================
## 检查MySQL是否已经运行
if [[ `ps aux | grep mysql[d] | wc -l` -eq 0 ]]; then
  echo The MySQL is not running at: `date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 1
fi
   
## 测试Slave端的连通性
nohup mysqladmin -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} ping > ${PINGFILE}
retval=`grep "^error" ${PINGFILE}`
rm -f ${PINGFILE}
if [[ "${retval}X" != "X" ]]; then
  echo The MySQL Slave can not be connected at: `date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 1
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/ifconfig eth1 | awk -F ':' '/inet addr/{print $2}' | sed 's/[a-zA-Z ]//g'`
if [[ "${MASTER_ADDR}" != "${LOCAL_ADDR}" ]]; then
  echo The MySQL Slave is not lawful at: `date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 1
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
  echo The MySQL Replication is not synchronous at: `date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 1
fi
   
## 再做一次判断，以保证数据同步绝对正常【创建测试数据】
mysql -uroot -e "create database if not exists mytestdb;"
sleep 3
   
retval=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e "show databases;" | grep mytestdb`
mysql -uroot -e "drop database if exists mytestdb;"
if [[ "${retval}X" = "X" ]]; then
  echo The MySQL Replication is not synchronous at: `date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 1
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 "show master status;" | 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
  echo Has noknown error at：`date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 1
fi
   
mysql -uroot -e "purge binary logs to '${CURR_BINLOG}';"
if [[ $" "%H-%M-%S`  ${LOGFILE}
fi

# crontab -e 
30 05 * * * /data/scripts/delete_mysql_binlog.sh >/dev/null 2>&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="show slave status"
  
## 查看MySQL是否已启动
if [[ `ps aux | grep mysqld | grep -v grep`"X" = "X" ]]; then
  echo The MySQL is not running at: `date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 1
fi
  
## 获得MySQL从端Relay binlog的路径
retval=`grep "^relay-log" /etc/my.cnf | grep -v relay-log- | grep '/'`
if [[ "${retval}" = "X" ]]; then
  RELAY_BINLOG_PATH=`ps aux | grep -w mysqld | grep -v grep | awk '{print $13}' | awk -F '=' '{print $2}'`
else
  RELAY_BINLOG_PATH=`dirname $(echo ${retval} | awk -F '=' '{print $2}')`
fi
  
## 查找master.info文件，用于定位Binlog信息
MASTER_FILE=`ps aux | grep -w mysqld | grep -v grep | awk '{print $13}' | awk -F '=' '{print $2}'`/master.info
if [[ ! -e ${MASTER_FILE} ]]; then
  echo This Server is not MySQL Slave at: `date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 1
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
  echo Now, The MySQL Replication is synchronous at: `date +%F" "%H-%M-%S`  ${LOGFILE}
  exit 0
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}'`
  
## 删除无用的Relay binlog
rm -rf ${RELAY_BINLOG_PATH}/*-relay-bin.*
  
## 直接从0位置开始同步
SQLCMD2="change master to master_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 "stop slave;"
mysql -uroot -e "${SQLCMD2};"
mysql -uroot -e "start slave;"
  
## 如果同步的过程中，出现重复记录导致同步失败，就跳过
while true
do
  sleep 2
  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 "The MySQL synchronous error information: ${ERRORINFO}"  ${LOGFILE}
    mysql -uroot -e "stop slave;"
    mysql -uroot -e "set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;"
    mysql -uroot -e "start slave;"
      
  ## 已完成同步，就正常退出
  elif [[ "${IO_STATUS}" = "Yes" && "${SQL_STATUS}" = "Yes" && "${SLAVE_BINLOG1}" = "${SLAVE_BINLOG2}" && ${BEHIND_STATUS} -eq 0 ]]; then
    echo The MySQL synchronous is ok at: `date +%F" "%H-%M-%S`  ${LOGFILE}
    break
  fi
done

MySQL,shell

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

评论“MySQL的一些功能实用的Linux shell脚本分享”

暂无评论...

www.wwsws.com 伏龙阁资源网

39,976影音资源

44,792技术资源

21,817软件资源

651,128站长资源

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

MySQL中join语句的基本使用教程及其字段对性能的影响

MySQL中几种数据统计查询的基本使用教程

评论“MySQL的一些功能实用的Linux shell脚本分享”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

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

MySQL中join语句的基本使用教程及其字段对性能的影响

MySQL中几种数据统计查询的基本使用教程

评论“MySQL的一些功能实用的Linux shell脚本分享”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存