MySQL主从同步

脚本

 1 [root@test scripts]# cat ss.sh 
 2 #!/bin/bash
 3 . /etc/init.d/functions
 4 MYUSER=root
 5 MYPASS=c565f972
 6 SOCKET="/data/3307/mysql.sock"
 7 MYCMD="/application/mysql/bin/mysql -u$MYUSER -p$MYPASS -S $SOCKET"
 8 STATUS=($($MYCMD -e "show slave status\G;"|egrep "Seconds_Behind|_Running|Last_SQL_Errno"|awk '{print $NF}'))
 9 ERRNU=(1158 1159 1008 1007 1062)
10 ###############Effect#####################
11 Wait(){
12 action "MySql Slave is fail !" /bin/false
13 echo -n "Trying to skip the error ,wait 3s"
14 for ((i=0;i<4;i++))
15 do
16         echo -n "."
17         sleep 1
18 done
19 echo
20 }
21 Print(){
22 action "Skip error is ok" /bin/true
23 action "MySql Slave is ok" /bin/true
24 read -p "Show slave status{IO|SQL|Seconds_Behind_Master|Last_SQL_Errno} ? please enter yes
25 /no.   :" enter
26 case $enter in
27         yes)
28                 echo "=================================================="
29                 $MYCMD -e "show slave status\G;"|egrep "Seconds_Behind|_Running|Last_SQL_Errno"
30                 echo "=================================================="
31                 exit 0
32                 ;;
33         no)
34                 exit 1
35                 ;;
36 esac
37 }
38 
39 ###############Check######################
40 Check_status(){
41 if [ "${STATUS[0]}" = "Yes" -a "${STATUS[1]}" = "Yes" -a "${STATUS[2]}" = "0" ]
42 then
43         action "MySql Slave is ok !" /bin/true
44         exit 0
45 else
46         sta=1
47         return $sta
48 fi
49 }
50 Check_err(){
51 Check_status
52 if [ $? -ne 0 ]
53 then
54         Wait            #调用前面的函数
55         for ((a=0;a<${#ERRNU[*]};a++))
56         do
57                 A=`echo ${ERRNU[a]}`
58                 if [ $A -eq "${STATUS[3]}" ] 
59                 then
60                         $MYCMD -e "stop slave;"
61                         $MYCMD -e "set global sql_slave_skip_counter = 1;"
62                         $MYCMD -e "start slave;"
63                 fi
64         done
65 fi
66 }
67 Check_again(){
68 STATUS=($($MYCMD -e "show slave status\G;"|egrep "Seconds_Behind|_Running|Last_SQL_Errno"|
69 awk '{print $NF}'))
70 if [ "${STATUS[0]}" = "Yes" -a "${STATUS[1]}" = "Yes" -a "${STATUS[2]}" = "0" ]
71 then
72         Print        #调用前面的函数，输入yes/no确认有没有跳过错误，看看状态
73 else
74         action "MySql Slave is fail" /bin/false
75         echo "MySql Slave is fail `date +%F\ %T`" >>/tmp/mysql_slave.log
76         mail -s "MySql Slave is fail `date +%F\ %T`" topaz1618@163.com </tmp/mysql_slave.log
77 fi
78 }
79 
80 ###############main######################
81 main(){
82 Check_err
83 Check_again
84 }
85 main

执行

没有问题的检查结果

[root@test scripts]# sh ss.sh          
MySql Slave is ok !                                        [  OK  ]

有问题的检查结果

[root@test scripts]# sh ss.sh  
MySql Slave is fail !                                      [FAILED] 
Trying to skip the error ,wait 3s....      　　　　　　　　　　　　　　 　　#提示正在跳过错误
Skip error is ok                                           [  OK  ] 　　#修复成功
MySql Slave is ok                                          [  OK  ] 　　#MySQL主从服务好了
Show slave status{IO|SQL|Seconds_Behind_Master|Last_SQL_Errno} ? please enter yes/no.
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#敲yes，看状态确认，no会直接退出
   :yes
==================================================
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
               Last_SQL_Errno: 0
==================================================
You have new mail in /var/spool/mail/root
posted @ 2016-01-17 15:06  topaz1618  阅读(180)  评论(0)    收藏  举报