LINUXQQ

#!/bin/sh
#
# created by yejr, 2010/10/30
#
# 此脚本参考mysql中文网原作者叶金荣,脚本经过linuxqq修改完善
# 如有高见发邮件至linuxqq@linuxqq.net
# 后续作者在本机部署了3个MYSQL，双主一从架构.
#
now=`date +”%Y%m%d%H%M%S”`
user=”root”
PASS=”123456″
StatFile=”./slave_status.$now”
StopFile=”./slave_stopus.$new”
export  Errno

# 此函数MYSQL是否运行正常
function chkcstatus(){
echo “show slave status\G” | mysql –defaults-file=/etc/my1.cnf -uroot -p$PASS > $StatFile
echo $StatFile
#取得 io_thread, sql_thread, last_errno 的状态
IoStat=`cat $StatFile | grep Slave_IO_Running | awk ‘{print $2}’`
SqlStat=`cat $StatFile | grep Slave_SQL_Running | awk ‘{ print $2}’`
Errno=`cat $StatFile | grep Last_Errno | awk ‘{print $2}’`
Behind=`cat $StatFile | grep Seconds_Behind_Master | awk ‘{print $2}’`

#IoStat=`cat $StatFile | head -n 12 | tail -n 1 | awk ‘{print $2}’`
#SqlStat=`cat $StatFile | head -n 13 | tail -n 1 | awk ‘{print $2}’`
#Errno=`cat $StatFile | head -n 20 | tail -n 1 | awk ‘{print $2}’`
if [ $IoStat = 'No' ] || [ $SqlStat = 'No' ] ; then
  echo “[chkslave]”
  return 1;
 fi
}
#此函数删除原有数据库导入最新数据,保证复制运行状态正常
function Repair(){
# 执行备份主数据操作,并且读取主BINLOG日志存放位置
mysqldump -uroot -p$PASS –default-character-set=utf8  pigeon_race >/root/sql.sql
  echo “FLUSH TABLES WITH READ LOCK; show master status;” |mysql –defaults-file=/etc/my.cnf -uroot -p$PASS >$StopFile
  MastFile=`cat $StopFile | grep mysql-bin | awk ‘{print $1}’`
  PosFile=`cat $StopFile | grep mysql-bin | awk ‘{print $2}’`
# 执行删除 自动导入操作
  echo “stop slave; drop database pigeron_race;create database pigeon_race default character set utf8 collate utf8_general_ci; \
  set names utf8;source /root/sql.sql; change master to master_host=’127.0.0.1′, master_user=’mysql1′, master_password=’mysql1admin’, master_log_file=”$MastFile” , master_log_pos=”$PosFile”; start slave;” | mysql –defaults-file=/etc/my1.cnf -uroot -p$PASS
  echo “stop slave; drop database pigeron_race;create database pigeon_race default character set utf8 collate utf8_general_ci; \
  set names utf8;source /root/sql.sql; change master to master_host=’127.0.0.1′, master_user=’mysql1′, master_password=’mysql1admin’, master_log_file=”$MastFile” , master_log_pos=”$PosFile”; start slave;” | mysql –defaults-file=/etc/my2.cnf -uroot -p$PASS
  echo “unlock tables;” | mysql –defaults-file=/etc/my.cnf -uroot -p$PASS
}
if [ chkstatus == 1 ] ; then
 #错误代码为 0，则可能是因为网络等原因导致复制中断，直接重新启动复制即可
 if [ "$Errno" -eq 0 ] ; then
  echo “start slave io_thread; start slave sql_thread;” | mysql –defaults-file=/etc/my1.cnf -uroot -p$PASS
  echo “start slave io_thread; start slave sql_thread;” | mysql –defaults-file=/etc/my2.cnf -uroot -p$PASS
 #如果是一些不是很要紧的错误代码，也可以直接略过
 elif [ "$Errno" -eq 1007 ] || [ "$Errno" -eq 1053 ] || [ "$Errno" -eq 1062 ] || [ "$Errno" -eq 1213 ] \
|| [ "$Errno" -eq 1158 ] || [ "$Errno" -eq 1159 ] || [ "$Errno" -eq 1008 ] ; then
  echo “stop slave; set global sql_slave_skip_counter=1; slave start;” | mysql –defaults-file=/etc/my1.cnf -uroot -p$PASS
  echo “stop slave; set global sql_slave_skip_counter=1; slave start;” | mysql –defaults-file=/etc/my2.cnf -uroot -p$PASS
   if [ chkstatus == 1 ] ;then
     Repair
     echo “repair mysql OK!”
  else
     echo “repair Failure”
    fi
  fi
fi
 
#删除临时文件
rm -f $StatFile
rm -f $StopFile
echo “[/chkslave]”

转载注明(LINUXQQ)