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

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=$?
  echo
  [ $retval -eq 0 ] && touch $lockfile
        
  return $retval
}
      
stop() {
  echo -n $"shutting down memcached......"
  /sbin/killproc $binfile
  retval=$?
  echo
  [ $retval -eq 0 ] && rm -f $lockfile
        
  return $retval
}
      
restart() {
  stop
  sleep 1
  start
}
      
reload() {
  echo -n $"reloading memcached......"
  /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 --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 [[ $? -eq 0 ]]; then
  echo clear mysql binlog is ok at: `date +%f" "%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