服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL的一些功能实用的Linux shell脚本分享

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

2020-05-27 15:02workming Mysql

这篇文章主要介绍了Linux系统下MySQL的一些实用功能的shell脚本分享,包括启动Memcached、binlog自动清理和修复主从同步这样三个简单的实例,需要的朋友可以参考下

Memcached启动脚本

?
1
# vim /etc/init.d/memcached
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
#!/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

           

?
1
2
3
4
5
exit $RETVAL
# chmod +x /etc/init.d/memcached
# chkconfig --add memcached
# chkconfig --level 235 memcached on
# service memcached start

binlog 自动清理脚本

?
1
# vim /data/scripts/delete_mysql_binlog.sh
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
#!/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
?
1
2
# crontab -e
30 05 * * * /data/scripts/delete_mysql_binlog.sh >/dev/null 2>&1

修复MySQL主从同步

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
#!/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

 

  • Linux
  • mysql
  • shell脚本

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1449 　 1053 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 2003 　 1175 　 1215 　 1615 　 1054 　 2006 　 1130 　 1366 　 10048 　 28000 　 1786 　 1135 　 1577 　 1665 　 1067 　 1093 　
    © 2019-2024 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图