Mysql Master/Slave latency monitoring and mysql Master/Slave latency

Mysql Master/Slave latency monitoring and mysql Master/Slave latency
Introduction

The master-slave latency is a very important issue in the master-slave environment. Sometimes we can use the show slave status command to view the Seconds_Behind_Master value to analyze the master-slave latency; however, because the value is from the completion time of the binlog file statement, this value may be inaccurate when an SQL statement is executed for a long time, is there any tool that can accurately analyze the master-slave latency? The PT-heartbeat tool in the pt tools can solve this problem well.

 

Principle

The pt tool creates a test table on the master database, updates the record at a frequency of one second, and writes the current time to the field, the time difference is obtained by comparing the time of master-slave synchronization with the current time.

 

Method

1. Create a background update process on the master database

pt-heartbeat -uroot -proot  -D chenmh --create-table --update  --daemonize

-U: the user who connects to the master database

-P: User Password used to connect to the master database

-D: the database that exists on the master database. This database is randomly specified but must exist.

-- Create-table: by default, a "heartbeat" table is created in the database specified by the master database.

 

2. Monitor slave Database

pt-heartbeat -uroot -proot -D chenmh --table=heartbeat --master-server-id=10  --monitor -h 192.168.137.20 --interval=1 

In this example, except that the -- master-server-id is the serverid of the master database and other parameters are the slave database. Note that the -- master-server-id must not be written incorrectly. Otherwise, the result is incorrect, after logging, You can monitor and warn the value.

3. Write monitoring logs for warning analysis

pt-heartbeat -uroot -proot -D chenmh --table=heartbeat --master-server-id=10  --monitor -h 192.168.137.20 --interval=1  --file=/tmp/heart.log

cat /tmp/heart.log |awk -F "s"  '{print $1}'

4. Stop background update

pt-heartbeat --stop

When you stop the background update process, a pt-heartbeat-sentinel file is generated under the/tmp directory. You must delete the file before starting the background process next time. Otherwise, the file cannot be started.

rm -rf /tmp/pt-heartbeat-sentinel

 

Parameters

Usage: pt-heartbeat [OPTIONS] [DSN] -- update | -- monitor | -- check | -- stop where -- update, -- moniter, -- check, -- stop are used independently, and -- update, -- monitor, and -- check are mutually exclusive
-- Daemonize and -- check are mutually exclusive. They are mutually exclusive.

Options: -- ask-pass prompt when using the password for mysql connection
-- Charset = s-A default character option -- check is executed once the slave database monitoring ends -- check-read-only if it is A read-only server, use this option to keep the inserted
-- Config = A is separated by commas (,). If specified, this parameter is used as the first option of the command line.
-- Create-table create table heartbeat if the table does not exist
-- Daemonize: Create an updated shell in the background
-- Database = s-D specifies the connected database
-- Dbi-driver = s Specify a driver for the connection; mysql and Pg are supported (default mysql) -- defaults-file = s-F connect to mysql through the provided file
-- File = s: output the latest -- monitor monitoring information to the specified file.
-- Frames = s set the time period (default 1 m, 5 m, 15 m) -- help: displays the help information -- host = s-h specifies the connected host -- [no] insert-heartbeat-row. When using -- tables, a record is inserted to the table heartbeat by default. the record row does not exist in the table -- interval = f specifies the update and monitoring frequency of the heartbeat table. The default value is 1 S.
-- Log = s when daemonized is used for background update, all information is output to the specified file -- master-server-id = s specifies the master server-id
-- Monitor the parameter option of the slave server -- password = s-p specify password -- pid = s create pid file -- port = I-P specify the port used for connection
-- Print-master-server-id print Output master-server-id
-- Recurse = I Check slaves recursively to this depth in -- check mode -- recursion-method = a Preferred recursion method used to find slaves (default processlist, hosts) -- replace with replace -- UPDATE operation
-- Run-time = m indicates the monitoring duration, in the unit of s = seconds, m = minutes, h = hours, d = days; if the default value is seconds
-- Sentinel = s Exit if this file exists (default/tmp/pt-heartbeat-sentinel) -- set-vars = A Set the MySQL variables in this comma-separated list of variable = value pairs -- skew = f specifies the default latency of the slave database check is 0.5.
-- Socket = s-S specifies the socket file used for connection
-- Stop the background update process and generate the file specified by -- sentinel -- table = s specifies the updated table (heartbeat by default) -- update the master heartbeat table, this parameter is required by the background process. You can also use -- replace to replace -- user = s-u to specify the user name.
-- Utc ignore system time only use UTC -- version to display version information -- [no] version-check for the latest version of Percona Toolkit, MySQL, and other programs (default yes)

 

Summary

You can use the monitoring output file information to send an email alert. If you are interested, you can write such a shell.

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion