本文主要用来监控维护mysql主从备份,可以看我的另一篇文章《Mysql 主从热备份》
脚本内容
#!/bin/bash . /etc/profile . ~/.bash_profile cnt=$(ps -ef |grep -v grep|grep 'sh /root/hotback.sh' -c) if [ $cnt -gt 2 ];then exit fi rm -f /root/hotback.log MIP='192.168.0.41' MU='slave' MP='123456' SU='root' SP='123456' MSU='master' MSP='123456' mysql -h $MIP -u$MU -p$MP -e " show master status\G quit" > /root/hotback.log mysql -u$SU -p$SP -e " show slave status\G quit" >> /root/hotback.log #show master status\G #M日志文件 File=$(grep -w "File" /root/hotback.log|sed "s#File..##g"|sed "s#[ \t]##g") #M版本号 Position=$(grep -w "Position" /root/hotback.log|sed "s#Position..##g"|sed "s#[ \t]##g") #show slave status\G #S获取的M日志文件 Master_Log_File=$(grep -w "Master_Log_File" /root/hotback.log|sed "s#Master_Log_File..##g"|sed "s#[ \t]##g") #S获取的M版本号 Read_Master_Log_Pos=$(grep -w "Read_Master_Log_Pos" /root/hotback.log|sed "s#Read_Master_Log_Pos..##g"|sed "s#[ \t]##g") #S执行的M日志文件 Relay_Master_Log_File=$(grep -w "Relay_Master_Log_File" /root/hotback.log|sed "s#Relay_Master_Log_File..##g"|sed "s#[ \t]##g") #S连接M的状态 Slave_IO_Running=$(grep -w "Slave_IO_Running" /root/hotback.log|sed "s#Slave_IO_Running..##g"|sed "s#[ \t]##g") #S执行sql命令状态 Slave_SQL_Running=$(grep -w "Slave_SQL_Running" /root/hotback.log|sed "s#Slave_SQL_Running..##g"|sed "s#[ \t]##g") #S执行的M版本号 Exec_Master_Log_Pos=$(grep -w "Exec_Master_Log_Pos" /root/hotback.log|sed "s#Exec_Master_Log_Pos..##g"|sed "s#[ \t]##g") #S执行sql报错日志 Last_Error=$(grep -w "Last_Error" /root/hotback.log|sed "s#Last_Error..##g"|sed "s#[ \t]##g") echo File:$File echo Position:$Position echo Master_Log_File:$Master_Log_File echo Read_Master_Log_Pos:$Read_Master_Log_Pos echo Relay_Master_Log_File:$Relay_Master_Log_File echo Slave_IO_Running:$Slave_IO_Running echo Slave_SQL_Running:$Slave_SQL_Running echo Exec_Master_Log_Pos:$Exec_Master_Log_Pos echo Last_Error:$Last_Error #echo "--------------------" >> /root/error.log #echo $(date +%Y-%m-%d\ %H:%M:%S) >> /root/error.log if [ "$File" != "$Master_Log_File" ]||[ "$Position" != "$Read_Master_Log_Pos" ] then if [ "$Slave_IO_Running" = "No" ] then echo "--------------------" >> /root/error.log echo $(date +%Y-%m-%d\ %H:%M:%S) >> /root/error.log echo "主从服务器连接失败,重启mysql服务。" >> /root/error.log service mysqld restart >> /root/error.log exit fi fi if [ "$Slave_SQL_Running" = "No" ] then echo "--------------------" >> /root/error.log echo $(date +%Y-%m-%d\ %H:%M:%S) >> /root/error.log echo "从服务器执行sql失败,获取新起点" >> /root/error.log mysql -h $MIP -u$MU -p$MP -e " purge binary logs to '$Relay_Master_Log_File'; show binlog events; quit" > binary.log Pos1=$(cat binary.log |grep -w Query|awk '{ print $2 }'|grep -A 1 $Exec_Master_Log_Pos|tail -1) Pos2=$(cat binary.log |grep -w Query|awk '{ print $5 }'|grep -A 1 $Exec_Master_Log_Pos|tail -1) echo Pos1=$Pos1 >> /root/error.log echo Pos2=$Pos2 >> /root/error.log if [ "$Pos1" = "$Exec_Master_Log_Pos" ] then echo "sql失败后无新sql日志,无法判断新起点" >> /root/error.log exit fi if [ "$Pos1" = "$Pos2" ] then echo "重新建立主从关系" >> /root/error.log mysql -u$SU -p$SP -e " STOP SLAVE; CHANGE MASTER TO MASTER_HOST='$MIP', MASTER_USER='$MSU', MASTER_PASSWORD='$MSP', MASTER_LOG_FILE='$Relay_Master_Log_File', MASTER_LOG_POS=$Pos2; quit" else Pos3=$(cat binary.log |grep -w Query|awk '{ print $2 }'|grep -A 1 $Pos1|tail -1) echo Pos3=$Pos3 >> /root/error.log echo "重新建立主从关系" >> /root/error.log mysql -u$SU -p$SP -e " STOP SLAVE; CHANGE MASTER TO MASTER_HOST='$MIP', MASTER_USER='$MSU', MASTER_PASSWORD='$MSP', MASTER_LOG_FILE='$Relay_Master_Log_File', MASTER_LOG_POS=$Pos3; quit" fi echo "重启mysql服务器" >> /root/error.log service mysqld restart >> /root/error.log fi
脚本分析
1.声明环境变量,否则脚本中的service mysql restart命令无法执行
#!/bin/bash . /etc/profile . ~/.bash_profile
2.判断该脚本的进程数量,预防重复执行
cnt=$(ps -ef |grep -v grep|grep 'sh /root/hotback.sh' -c) if [ $cnt -gt 2 ];then exit fi
3.定义变量
M开头:主数据库账号,从获取主的binlog.log日志;
S开头:从数据库账号,获取从的状态
MS开头:主数据库账号,获取主的状态
rm -f /root/hotback.log MIP='192.168.0.41' MU='slave' MP='123456' SU='root' SP='123456' MSU='master' MSP='123456'
4.hotback.log保存主从状态的文件,将主从状态输出到hotback.log
mysql -h $MIP -u$MU -p$MP -e " show master status\G quit" > /root/hotback.log mysql -u$SU -p$SP -e " show slave status\G quit" >> /root/hotback.log
5.获取主从状态中所需的值
#show master status\G #M日志文件 File=$(grep -w "File" /root/hotback.log|sed "s#File..##g"|sed "s#[ \t]##g") #M版本号 Position=$(grep -w "Position" /root/hotback.log|sed "s#Position..##g"|sed "s#[ \t]##g") #show slave status\G #S获取的M日志文件 Master_Log_File=$(grep -w "Master_Log_File" /root/hotback.log|sed "s#Master_Log_File..##g"|sed "s#[ \t]##g") #S获取的M版本号 Read_Master_Log_Pos=$(grep -w "Read_Master_Log_Pos" /root/hotback.log|sed "s#Read_Master_Log_Pos..##g"|sed "s#[ \t]##g") #S执行的M日志文件 Relay_Master_Log_File=$(grep -w "Relay_Master_Log_File" /root/hotback.log|sed "s#Relay_Master_Log_File..##g"|sed "s#[ \t]##g") #S连接M的状态 Slave_IO_Running=$(grep -w "Slave_IO_Running" /root/hotback.log|sed "s#Slave_IO_Running..##g"|sed "s#[ \t]##g") #S执行sql命令状态 Slave_SQL_Running=$(grep -w "Slave_SQL_Running" /root/hotback.log|sed "s#Slave_SQL_Running..##g"|sed "s#[ \t]##g") #S执行的M版本号 Exec_Master_Log_Pos=$(grep -w "Exec_Master_Log_Pos" /root/hotback.log|sed "s#Exec_Master_Log_Pos..##g"|sed "s#[ \t]##g") #S执行sql报错日志 Last_Error=$(grep -w "Last_Error" /root/hotback.log|sed "s#Last_Error..##g"|sed "s#[ \t]##g") echo File:$File echo Position:$Position echo Master_Log_File:$Master_Log_File echo Read_Master_Log_Pos:$Read_Master_Log_Pos echo Relay_Master_Log_File:$Relay_Master_Log_File echo Slave_IO_Running:$Slave_IO_Running echo Slave_SQL_Running:$Slave_SQL_Running echo Exec_Master_Log_Pos:$Exec_Master_Log_Pos echo Last_Error:$Last_Error
6.通过主从所需的状态值,判断主从备份是否正常,修复并输出日志到error.log
if [ "$File" != "$Master_Log_File" ]||[ "$Position" != "$Read_Master_Log_Pos" ] then if [ "$Slave_IO_Running" = "No" ] then echo "--------------------" >> /root/error.log echo $(date +%Y-%m-%d\ %H:%M:%S) >> /root/error.log echo "主从服务器连接失败,重启mysql服务。" >> /root/error.log service mysqld restart >> /root/error.log exit fi fi if [ "$Slave_SQL_Running" = "No" ] then echo "--------------------" >> /root/error.log echo $(date +%Y-%m-%d\ %H:%M:%S) >> /root/error.log echo "从服务器执行sql失败,获取新起点" >> /root/error.log mysql -h $MIP -u$MU -p$MP -e " purge binary logs to '$Relay_Master_Log_File'; show binlog events; quit" > binary.log Pos1=$(cat binary.log |grep -w Query|awk '{ print $2 }'|grep -A 1 $Exec_Master_Log_Pos|tail -1) Pos2=$(cat binary.log |grep -w Query|awk '{ print $5 }'|grep -A 1 $Exec_Master_Log_Pos|tail -1) echo Pos1=$Pos1 >> /root/error.log echo Pos2=$Pos2 >> /root/error.log if [ "$Pos1" = "$Exec_Master_Log_Pos" ] then echo "sql失败后无新sql日志,无法判断新起点" >> /root/error.log exit fi if [ "$Pos1" = "$Pos2" ] then echo "重新建立主从关系" >> /root/error.log mysql -u$SU -p$SP -e " STOP SLAVE; CHANGE MASTER TO MASTER_HOST='$MIP', MASTER_USER='$MSU', MASTER_PASSWORD='$MSP', MASTER_LOG_FILE='$Relay_Master_Log_File', MASTER_LOG_POS=$Pos2; quit" else Pos3=$(cat binary.log |grep -w Query|awk '{ print $2 }'|grep -A 1 $Pos1|tail -1) echo Pos3=$Pos3 >> /root/error.log echo "重新建立主从关系" >> /root/error.log mysql -u$SU -p$SP -e " STOP SLAVE; CHANGE MASTER TO MASTER_HOST='$MIP', MASTER_USER='$MSU', MASTER_PASSWORD='$MSP', MASTER_LOG_FILE='$Relay_Master_Log_File', MASTER_LOG_POS=$Pos3; quit" fi echo "重启mysql服务器" >> /root/error.log service mysqld restart >> /root/error.log fi
本文为博主原创文章,转载请注明出处,本文地址https://jeff.xin/post/13.html
本文链接:https://jeff.xin/post/13.html
--EOF--
Comments
发表评论:
◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。