Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use pt tools to detect MySQL master-slave delay, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Installation and configuration of Percona-toolkit (R8 note day 86)

MySQL master inconsistent repair process

We create a user pt_checksum to facilitate data repair in the future, and all permissions are given.

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *. * TO 'pt_checksum'@'10.127.%.%' IDENTIFIED BY' pt_checksum'; and then we give this user access to the test database.

The specific parameters of the grant all privileges on test.* to pt_checksum@'10.127.%.%'; tool can be seen in pt-heartbeat-- help, and I can give you the key points.

Let's create the test table and start the heartbeat daemon in the background, where create-table is to create the test table, interval is the interval of 1 second, at least 0.01s, update is to update the test table on the test library, and replace is to update the time in the replacement table, regardless of whether there is data in the table, daemonize is the annotation running in the background.

Pt-heartbeat hobbies 10.127.128.99, using the command ps, you can see the following heartbeat process, or, for a change, you can see the following heartbeat process with pgrep-fl pt-heartbeat.

# ps-ef | grep heartbeat

Root 19920 10 22:35? 00:00:00 perl / usr/local/bin/pt-heartbeat hackers 10.127.128.99 root 19920 10 22:35? 00:00:00 perl / usr/local/bin/pt-heartbeat hackers 10.127.128.99 title upright ptacks checksummeng packs ptacks checksumption3306-D test-- create-table-- interval=1-- update-- replace-- daemonize the next step is to focus our work. We can turn on the monitor option to monitor master-slave delays. There is one point to mention, that is, you need to set up server-id.

# pt-heartbeat hobbies 10.127.xx.xxhandwriting, ptcounting checksummons, pairs3306-D test-- table=heartbeat-- monitor

The-- master-server-id option must be specified because the heartbeat table `test`.`heartbeat` uses the server_id column for-- update or-- check but the server's master could not be automatically determined.

Please read the DESCRIPTION section of the pt-heartbeat POD. Quick view on the main library.

> show slave hosts

+-+

| | Server_id | Host | Port | Master_id | Slave_UUID | |

+-+

| | 13058 | | 3306 | 20 | c6d66211-a645-11e6-a2b6-782bcb472f63 |

+-+

The results of 1 row in set (0.01sec) are consistent with those of show variables like 'server%', which is faster and more efficient.

Let's look at the delay.

# pt-heartbeat hobbies 10.127.xx.xxhandwriting, ptcounting checksummons, pairs3306-D test-- table=heartbeat-- monitor-- master-server-id=20

0.00s [0.00s, 0.00s, 0.00s]

0.00s [0.00s, 0.00s, 0.00s]

0.00s [0.00s, 0.00s, 0.00s]

0.00s [0.00s, 0.00s, 0.00s]

0.00s [0.00s, 0.00s, 0.00s]

0.00s [0.00s, 0.00s, 0.00s] you can see any delay in the current environment. What the index in square brackets means can be customized using frames. For example, the default is 1m, 5m, 15m, and we can customize it. For example, it is displayed as 1m, 2m, 3m, 4m.

# pt-heartbeat hobbies 10.127.xx.xxm.xxbooks, ptals checksummons, packs, ptacks, checksummons, packs, 3306-D test-- table=heartbeat-- monitor-- master-server-id=20-- frames=1m,2m,3m,4m

0.00s [0.00s, 0.00s, 0.00s, 0.00s]

0.00s [0.00s, 0.00s, 0.00s, 0.00s]

0.00s [0.00s, 0.00s, 0.00s, 0.00s] some students may say that no matter how it is displayed as 0, if you press it with sysbench, there may be an obvious difference in delay immediately. Let's do an overall comparison and test later.

If you want to check and see, just look at it once, you can use the check option, of course, this value does not have the time range of frame.

# pt-heartbeat hobbies 10.127.128.99 mentions ubiquitous ptents checksummpons pamphlets checksummers packs 3306-D test-- table=heartbeat-- master-server-id=20-- check

Of course, we started the background daemon, which is essentially a perl script. If you want to stop it and use the stop option to do it, you will change a temporary file on your birthday. If you need to restart it next time, you need to clean up this file.

# pt-heartbeat hobbies 10.127.xx.xx recording, ptasking checksumming, paging, ptcounting, checksumming, paging 3306-D test-- stop

Successfully created file / tmp/pt-heartbeat-sentinel

The above is all the contents of the article "how to use pt tools to detect MySQL master-slave delay". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.