欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-22 21:26:10
memcached启动脚本 # vim /etc/init.d/memcached #!/bin/bash #==============...

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