当前位置：移动技术网 > IT编程>数据库>Mysql > MySQL的一些功能实用的Linux shell脚本分享

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

2017年12月12日 | 移动技术网IT编程 | 我要评论

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

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

ubuntu 20.04上搭建LNMP环境的方法步骤

简单说明由于之前是用centos7搭建的，后来使用ubuntu 20.04的系统做为个人开发环境，所以想在ubuntu上也搭建一下环境，和centos有一些小区... [阅读全文]
在Ubuntu20.04中安装ROS Noetic的方法

申明：由于项目需要用到ros环境，所以在此记录一下ubuntu20.04下的ros noetic安装过程，全程顺序执行一次性安装成功，当然执行下一步的时候上一步... [阅读全文]
在Ubuntu/Linux环境下使用MySQL开放/修改3306端口和开放访问权限

操作系统：ubuntu 17.04 64位mysql版本：mysql 5.7一、查看3306端口是否开放netstat -an|grep 3306如果看到下图这... [阅读全文]
ORM框架Peewee(高级连接）peewee连接池、主从、重连、ssh连接

表示使用连接池from playhouse.shortcuts import model_to_dictfrom... [阅读全文]
总结阿里专家mysql讲解笔记

文章目录观点观点数据库mysql底层是由B+树构成，是平衡树树高=主键+所有列长度（主键在分支节点上 16kb，... [阅读全文]
JDBC如何插入Blob类型字段

Blob类型字段Blob:在MySQL中 Blob是一个二进制大型对象，可以是存储大量数据的容器，他能容纳不同大... [阅读全文]
记一次Mysql主从复制延迟，Waiting for dependent transaction to commit

记一次Mysql主从复制延迟，Waiting for dependent transaction to comm... [阅读全文]
安晴同学|大榕树下的友谊

老母亲says安晴读小学了，老母亲的工作重点又从妹妹转到了安晴身上。刚开始的时候很害怕每天中午的接送，觉得太麻烦... [阅读全文]
MySQL学习11_表连接

文章目录表连接表连接方式表连接在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据表连接... [阅读全文]
MySql – 浅谈MyISAM与InnoDB的区别及选择

MySql – 浅谈MyISAM与InnoDB的区别及选择1.MyISAM2.InnoDB3.MyISAM与In... [阅读全文]

网友评论


验证码：

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

2017年12月12日 | 移动技术网IT编程 | 我要评论

您可能感兴趣的文章:

相关文章:

网友评论