The shell monitors MySQL master-slave script two

Content for their own a little summary, if there is no welcome unveils to shoot bricks

This article from http://yijiu.blog.51cto.com/reprint please Chingbo Lord agree, pirated shameful

Monitor master-slave replication normal or not

As you should know, monitoring master and slave is working properly, involving commands as follows:

show slave status\g;

So, the parameters we need to focus on are as follows:

1. First look at whether the SQL and IO threads are yes (presumably everyone understands)

2. Whether there is a delay greater than 0 #一般生成环境延迟是否大于500秒, if more than 500 alarm, such as greater than 1000 serious alarm

#比如传递一个sql到slave上, there is a time stamp timestamp in the eventhear in the Binlog, the time in the next binlog is compared, and if the current time is how much it is displayed, If the update is very frequent 500 seconds will produce more SQL accumulate in it

At least that's how it's done in production, and so does the monitoring plug-in that includes Nagios.

Main concerns Value This article from http://yijiu.blog.51cto.com/reprint please Chingbo Lord agree that pirated shameful

1.master_log_file Read_master_log_pos 2.relay_master_log_fileexec_master_log_pos 3.Seconds_Behind_master

Judge a library mainly observe the above points, if the main library hangs, Seconds_behind_master will already become null

So how do you see if the log synchronization is done from the library, as shown below

A method for monitoring synchronization through the shell

No more nonsense, just serving.

1. Use status to see if the synchronization is complete

Judging formula

The following is the basis for judging, judging the following values

The values of Master_log_file and relay_master_log_file must be equal

To determine the offset of the synchronization

The values of Read_master_log_pos and exec_master_log_pos must be equal

Based on the above for the most basic judgment, whether it can be promoted from the library to the main library, will be in the library to determine whether the master log file read the same location and find one of the most forward node promotion mainly

The shell contents are as follows: This article from http://yijiu.blog.51cto.com/reprint please Chingbo Lord agree, pirated shameful

#!/bin/bashuser= ' root ' password= ' mypass ' thread_status= '/usr/local/mysql/bin/mysql -u ' $user " -p" $ Password " -S /tmp/mysql_3308.sock -e  ' Show slave status\g ' |grep -i yes |wc -l ' status= ('/usr/local/mysql/bin/mysql -u ' $user " -p" $password " -s /tmp/mysql_ 3308.sock -e  ' show slave status\g ' | egrep -i  "Master_Log_File| relay_master_log_file| read_master_log_pos| exec_master_log_pos| Seconds_behind_master " |awk -f ': '   ' {print $2} ') echo ${status[4]}if [[ " $ Thread_status " != 2 ]]; then        echo " the  replication is fault , at $ (date) " >  $catalog          echo  ' Uname -n '  | mail [email protected] <   $catalog         exit 1fiif [[  "${status[4"} " >  '  ]];then        echo   yan chi guo gao, at $ (date)   >  $catalog          echo  ' Uname -n '  | mail [email protected] <   $catalog         exit 2fiif [[ ${status[0]} = = ${status[2]} ]] && [[ ${status[1]} == ${status[3]} ]];  then        echo  ' The replication is normal '          exit 0    else         echo  "the replication is fault , at $ (date)"   >  $catalog         echo  ' uname -n '  | mail  [email protected] <  $catalog         exit 2fi 

This article from http://yijiu.blog.51cto.com/reprint please Chingbo Lord agree, pirated shameful

2. depends on the program detection this article from http://yijiu.blog.51cto.com/reprint please Chingbo Lord agree, pirated shameful

For example, the program establishes a table in master and optionally sets a field, and get a time on master and write

Now the time is obtained and recorded in the program itself, Finally, execute select in slave to see if the results correspond to the time

If the time is the same as normal, if the time on master is less than the time on the slave there is a delay, then it proves that the delay exists, Span style= "font-family: ' The song body '; font-size:14px;" > But there are drawbacks to this approach, such as that the main library is hung and then not available

#!/bin/bashuser= ' root ' password= ' mypass '/usr/local/mysql/bin/mysql -u "$user"  -p "$password"  -e   ' Replace into master.repl_heart set t=now (), id=1; '  >/dev/null 2>&1master_select= '/usr/local/mysql/bin/mysql -u ' $user " -p" $password " -S /tmp/mysql.sock -e  ' Select t from master.repl_heart where id = 1; '  | awk  ' {print $2} '  | tail -1 ' slave_select= '/usr/local/mysql/bin/mysql  -u "$user"  -p "$password"  -S /tmp/mysql_3308.sock -e  "Select t from  master.repl_heart where id=1; '  | awk  ' {print $2} '  | tail -1 ' master_date= ' date -d  ' $master _ Select " +%s ' slave_date= ' date -d " $slave _select " +%s ' delay= ' echo " $master _date "-" $ Slave_date "&NBSP;|&NBSP;BC ' if [[  $master _date ==  $slave _date  ]];thenecho  ' Is ok '    elif [[  $delay  -le 500 ]];then   echo cun zai  yan chi  "$delay"    else        echo   "the replication delay too large " $delay " , at $ (date)"   >  $catalogecho   ' uname -n '  | mail [email protected] < $ Catalogfi

Note that: In the copy, if the row format is the main library time, if not the line, this method can be now () this built-in function in the S script to generate and then write

Above, in order to monitor MySQL master and slave of the two shell of the wording, if there is insufficient, trouble points out, thank you

This article from the "Mood is still" blog, reproduced please contact the author!

Shell monitors MySQL master-slave script for two