Two scripts for shell monitoring MySQL Master/Slave status

This article is from yijiu.blog.51cto.com. You should know whether the master-slave replication is normal and whether the master-slave replication is working.

The content is my summary. If you are not welcome, click here.

The content is a summary of your own. If you are not welcome, click it.

This article is from reprinted and approved by the blogger

Monitor whether master-slave replication is normal

As you can see, the following commands are used to check whether the master and slave nodes are working properly:

Show slave status \ G;

Then, we need to pay attention to the following parameters:

1. Check whether the SQL and I/O threads are in the YES state (you must understand)

2. Is there latency greater than 0 # generally, the generated environment latency is greater than 500 seconds. If it is greater than 500, an alarm is triggered. If it is greater than 1000, a serious alarm is triggered.

# For example, if an SQL statement is passed to slave and the time stamp exists in eventhear in binlog, the time obtained in the next binlog is compared. If the current time is the same, if the update frequency is very high, more SQL statements will be generated in 500 seconds.

At least monitoring in production is implemented as well as monitoring plug-ins including nagios.

Value of primary interest this article is from reprinted please with the consent of the blogger,

1. Master_Log_File Read_master_log_Pos 2. Relay_Master_Log_FileExec_Master_log_pos 3. Seconds_Behind_master

Judge a database and observe the preceding points. If the master database crashes, Seconds_Behind_master will become NULL.

In this way, how do I observe whether the slave database completes log synchronization?

How to implement monitoring synchronization through shell

If you don't talk nonsense, simply serve food.

1. Use status to check whether synchronization has been completed

Judgment Formula

The following is the basis for determining the following values:

The values of Master_Log_File and Relay_Master_Log_File must be equal.

Determine the synchronization offset

The values of Read_master_log_Pos and Exec_Master_log_pos must be equal.

Whether the slave database can be upgraded to the master database based on the above basic judgment basis, it will judge from the database whether the master log file is read in the same location and find a top node to promote the master

The shell content is as follows: This article is from reprinted and approved by the blogger.

#! /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 | bytes | 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 umail@qq.com <$ catalog exit 1 fiif [" $ {status [4]} "> '123']; then echo "yan chi guo gao, at $ (date) "> $ catalog echo 'uname-n' | mail umail@qq.com <$ catalog exit 2 fiif [[$ {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 umail@qq.com <$ catalog exit 2fi

If the time is the same, it is considered normal. If there is a delay between the time on the master and the time on the slave, it proves that the delay exists, but this method has a defect, for example, if the master database crashes, it is unavailable.

#! /Bin/bashuser = 'root' password = 'mypass'/usr/local/mysql/bin/mysql-u "$ user"-p "$ password"-e' replace 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 "| 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 umail@qq.com <$ catalogfi

Note that during replication, if the row format is used, it is the time of the master database. If the row format is not used, this method can generate and write the now () built-in function in the s script.

The above two shell methods are used to monitor mysql master and slave databases. If you have any questions, I 'd like to thank you for your reference.

This article is from the "remain in the mood" blog. For more information, contact the author!