1MK-HEARTBEAT(1)       User Contributed Perl Documentation      MK-HEARTBEAT(1)
2
3
4

NAME

6       mk-heartbeat - Monitor MySQL replication delay.
7

SYNOPSIS

9       Usage: mk-heartbeat [OPTION...] [DSN] --update|--monitor|--check|--stop
10
11       mk-heartbeat measures replication lag on a MySQL or PostgreSQL server.
12       You can use it to update a master or monitor a replica.  If possible,
13       MySQL connection options are read from your .my.cnf file.
14
15       Start daemonized process to update test.heartbeat table on master:
16
17         mk-heartbeat -D test --update -h master-server --daemonize
18
19       Monitor replication lag on slave:
20
21         mk-heartbeat -D test --monitor -h slave-server
22
23         mk-heartbeat -D test --monitor -h slave-server --dbi-driver Pg
24
25       Check slave lag once and exit (using optional DSN to specify slave
26       host):
27
28         mk-heartbeat -D test --check h=slave-server
29

RISKS

31       The following section is included to inform users about the potential
32       risks, whether known or unknown, of using this tool.  The two main
33       categories of risks are those created by the nature of the tool (e.g.
34       read-only tools vs. read-write tools) and those created by bugs.
35
36       mk-heartbeat merely reads and writes a single record in a table.  It
37       should be very low-risk.
38
39       At the time of this release, we know of no bugs that could cause
40       serious harm to users.
41
42       The authoritative source for updated information is always the online
43       issue tracking system.  Issues that affect this tool will be marked as
44       such.  You can see a list of such issues at the following URL:
45       <http://www.maatkit.org/bugs/mk-heartbeat>.
46
47       See also "BUGS" for more information on filing bugs and getting help.
48

DESCRIPTION

50       mk-heartbeat is a two-part MySQL and PostgreSQL replication delay
51       monitoring system that measures delay by looking at actual replicated
52       data.  This avoids reliance on the replication mechanism itself, which
53       is unreliable.  (For example, "SHOW SLAVE STATUS" on MySQL).
54
55       The first part is an "--update" instance of mk-heartbeat that connects
56       to a master and updates a timestamp ("heartbeat record") every
57       "--interval" seconds.  Since the heartbeat table may contain records
58       from multiple masters (see "MULTI-SLAVE HIERARCHY"), the server's ID
59       (@@server_id) is used to identify records.
60
61       The second part is a "--monitor" or "--check" instance of mk-heartbeat
62       that connects to a slave, examines the replicated heartbeat record from
63       its immediate master or the specified "--master-server-id", and
64       computes the difference from the current system time.  If replication
65       between the slave and the master is delayed or broken, the computed
66       difference will be greater than zero and potentially increase if
67       "--monitor" is specified.
68
69       You must either manually create the heartbeat table on the master or
70       use "--create-table".  See "--create-table" for the proper heartbeat
71       table structure.  The "MEMORY" storage engine is suggested, but not
72       required of course, for MySQL.
73
74       The heartbeat table must contain a heartbeat row.  By default, a
75       heartbeat row is inserted if it doesn't exist.  This feature can be
76       disabled with the "--[no]insert-heartbeat-row" option in case the
77       database user does not have INSERT privileges.
78
79       mk-heartbeat depends only on the heartbeat record being replicated to
80       the slave, so it works regardless of the replication mechanism (built-
81       in replication, a system such as Continuent Tungsten, etc).  It works
82       at any depth in the replication hierarchy; for example, it will
83       reliably report how far a slave lags its master's master's master.  And
84       if replication is stopped, it will continue to work and report
85       (accurately!) that the slave is falling further and further behind the
86       master.
87
88       mk-heartbeat has a maximum resolution of 0.01 second.  The clocks on
89       the master and slave servers must be closely synchronized via NTP.  By
90       default, "--update" checks happen on the edge of the second (e.g.
91       00:01) and "--monitor" checks happen halfway between seconds (e.g.
92       00:01.5).  As long as the servers' clocks are closely synchronized and
93       replication events are propagating in less than half a second, mk-
94       heartbeat will report zero seconds of delay.
95
96       mk-heartbeat will try to reconnect if the connection has an error, but
97       will not retry if it can't get a connection when it first starts.
98
99       The "--dbi-driver" option lets you use mk-heartbeat to monitor
100       PostgreSQL as well.  It is reported to work well with Slony-1
101       replication.
102

MULTI-SLAVE HIERARCHY

104       If the replication hierarchy has multiple slaves which are masters of
105       other slaves, like "master -> slave1 -> slave2", "--update" instances
106       can be ran on the slaves as well as the master.  The default heartbeat
107       table (see "--create-table") is keyed on the "server_id" column, so
108       each server will update the row where "server_id=@@server_id".
109
110       For "--monitor" and "--check", if "--master-server-id" is not
111       specified, the tool tries to discover and use the slave's immediate
112       master.  If this fails, or if you want monitor lag from another master,
113       then you can specify the "--master-server-id" to use.
114
115       For example, if the replication hierarchy is "master -> slave1 ->
116       slave2" with corresponding server IDs 1, 2 and 3, you can:
117
118         mk-heartbeat --daemonize -D test --update -h master
119         mk-heartbeat --daemonize -D test --update -h slave1
120
121       Then check (or monitor) the replication delay from master to slave2:
122
123         mk-heartbeat -D test --master-server-id 1 --check slave2
124
125       Or check the replication delay from slave1 to slave2:
126
127         mk-heartbeat -D test --master-server-id 2 --check slave2
128
129       Stopping the "--update" instance one slave1 will not affect the
130       instance on master.
131

MASTER AND SLAVE STATUS

133       The default heartbeat table (see "--create-table") has columns for
134       saving information from "SHOW MASTER STATUS" and "SHOW SLAVE STATUS".
135       These columns are optional.  If any are present, their corresponding
136       information will be saved.
137

OPTIONS

139       Specify at least one of "--stop", "--update", "--monitor", or
140       "--check".
141
142       "--update", "--monitor", and "--check" are mutually exclusive.
143
144       "--daemonize" and "--check" are mutually exclusive.
145
146       This tool accepts additional command-line arguments.  Refer to the
147       "SYNOPSIS" and usage information for details.
148
149       --ask-pass
150           Prompt for a password when connecting to MySQL.
151
152       --charset
153           short form: -A; type: string
154
155           Default character set.  If the value is utf8, sets Perl's binmode
156           on STDOUT to utf8, passes the mysql_enable_utf8 option to
157           DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL.  Any
158           other value sets binmode on STDOUT without the utf8 layer, and runs
159           SET NAMES after connecting to MySQL.
160
161       --check
162           Check slave delay once and exit.  If you also specify "--recurse",
163           the tool will try to discover slave's of the given slave and check
164           and print their lag, too.  The hostname or IP and port for each
165           slave is printed before its delay.  "--recurse" only works with
166           MySQL.
167
168       --config
169           type: Array
170
171           Read this comma-separated list of config files; if specified, this
172           must be the first option on the command line.
173
174       --create-table
175           Create the heartbeat "--table" if it does not exist.
176
177           This option causes the table specified by "--database" and
178           "--table" to be created with the following MAGIC_create_heartbeat
179           table definition:
180
181             CREATE TABLE heartbeat (
182               ts                    varchar(26) NOT NULL,
183               server_id             int unsigned NOT NULL PRIMARY KEY,
184               file                  varchar(255) DEFAULT NULL,    -- SHOW MASTER STATUS
185               position              bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
186               relay_master_log_file varchar(255) DEFAULT NULL,    -- SHOW SLAVE STATUS
187               exec_master_log_pos   bigint unsigned DEFAULT NULL  -- SHOW SLAVE STATUS
188             );
189
190           The heartbeat table requires at least one row.  If you manually
191           create the heartbeat table, then you must insert a row by doing:
192
193             INSERT INTO heartbeat (ts, server_id) VALUES (NOW(), N);
194
195           where "N" is the server's ID; do not use @@server_id because it
196           will replicate and slaves will insert their own server ID instead
197           of the master's server ID.
198
199           This is done automatically by "--create-table".
200
201           A legacy version of the heartbeat table is still supported:
202
203             CREATE TABLE heartbeat (
204               id int NOT NULL PRIMARY KEY,
205               ts datetime NOT NULL
206             );
207
208           Legacy tables do not support "--update" instances on each slave of
209           a multi-slave hierarchy like "master -> slave1 -> slave2".  To
210           manually insert the one required row into a legacy table:
211
212             INSERT INTO heartbeat (id, ts) VALUES (1, NOW());
213
214           The tool automatically detects if the heartbeat table is legacy.
215
216           See also "MULTI-SLAVE HIERARCHY".
217
218       --daemonize
219           Fork to the background and detach from the shell.  POSIX operating
220           systems only.
221
222       --database
223           short form: -D; type: string
224
225           The database to use for the connection.
226
227       --dbi-driver
228           default: mysql; type: string
229
230           Specify a driver for the connection; "mysql" and "Pg" are
231           supported.
232
233       --defaults-file
234           short form: -F; type: string
235
236           Only read mysql options from the given file.  You must give an
237           absolute pathname.
238
239       --file
240           type: string
241
242           Print latest "--monitor" output to this file.
243
244           When "--monitor" is given, prints output to the specified file
245           instead of to STDOUT.  The file is opened, truncated, and closed
246           every interval, so it will only contain the most recent statistics.
247           Useful when "--daemonize" is given.
248
249       --frames
250           type: string; default: 1m,5m,15m
251
252           Timeframes for averages.
253
254           Specifies the timeframes over which to calculate moving averages
255           when "--monitor" is given.  Specify as a comma-separated list of
256           numbers with suffixes.  The suffix can be s for seconds, m for
257           minutes, h for hours, or d for days.  The size of the largest frame
258           determines the maximum memory usage, as up to the specified number
259           of per-second samples are kept in memory to calculate the averages.
260           You can specify as many timeframes as you like.
261
262       --help
263           Show help and exit.
264
265       --host
266           short form: -h; type: string
267
268           Connect to host.
269
270       --[no]insert-heartbeat-row
271           default: yes
272
273           Insert a heartbeat row in the "--table" if one doesn't exist.
274
275           The heartbeat "--table" requires a heartbeat row, else there's
276           nothing to "--update", "--monitor", or "--check"!  By default, the
277           tool will insert a heartbeat row if one is not already present.
278           You can disable this feature by specifying
279           "--no-insert-heartbeat-row" in case the database user does not have
280           INSERT privileges.
281
282       --interval
283           type: float; default: 1.0
284
285           How often to update or check the heartbeat "--table".  Updates and
286           checks begin on the first whole second then repeat every
287           "--interval" seconds for "--update" and every "--interval" plus
288           "--skew" seconds for "--monitor".
289
290           For example, if at 00:00.4 an "--update" instance is started at 0.5
291           second intervals, the first update happens at 00:01.0, the next at
292           00:01.5, etc.  If at 00:10.7 a "--monitor" instance is started at
293           0.05 second intervals with the default 0.5 second "--skew", then
294           the first check happens at 00:11.5 (00:11.0 + 0.5) which will be
295           "--skew" seconds after the last update which, because the instances
296           are checking at synchronized intervals, happened at 00:11.0.
297
298           The tool waits for and begins on the first whole second just to
299           make the interval calculations simpler.  Therefore, the tool could
300           wait up to 1 second before updating or checking.
301
302           The minimum (fastest) interval is 0.01, and the maximum precision
303           is two decimal places, so 0.015 will be rounded to 0.02.
304
305           If a legacy heartbeat table (see "--create-table") is used, then
306           the maximum precision is 1s because the "ts" column is type
307           "datetime".
308
309       --log
310           type: string
311
312           Print all output to this file when daemonized.
313
314       --master-server-id
315           type: string
316
317           Calculate delay from this master server ID for "--monitor" or
318           "--check".  If not given, mk-heartbeat attempts to connect to the
319           server's master and determine its server id.
320
321       --monitor
322           Monitor slave delay continuously.
323
324           Specifies that mk-heartbeat should check the slave's delay every
325           second and report to STDOUT (or if "--file" is given, to the file
326           instead).  The output is the current delay followed by moving
327           averages over the timeframe given in "--frames".  For example,
328
329            5s [  0.25s,  0.05s,  0.02s ]
330
331       --password
332           short form: -p; type: string
333
334           Password to use when connecting.
335
336       --pid
337           type: string
338
339           Create the given PID file when daemonized.  The file contains the
340           process ID of the daemonized instance.  The PID file is removed
341           when the daemonized instance exits.  The program checks for the
342           existence of the PID file when starting; if it exists and the
343           process with the matching PID exists, the program exits.
344
345       --port
346           short form: -P; type: int
347
348           Port number to use for connection.
349
350       --print-master-server-id
351           Print the auto-detected or given "--master-server-id".  If
352           "--check" or "--monitor" is specified, specifying this option will
353           print the auto-detected or given "--master-server-id" at the end of
354           each line.
355
356       --recurse
357           type: int
358
359           Check slaves recursively to this depth in "--check" mode.
360
361           Try to discover slave servers recursively, to the specified depth.
362           After discovering servers, run the check on each one of them and
363           print the hostname (if possible), followed by the slave delay.
364
365           This currently works only with MySQL.  See "--recursion-method".
366
367       --recursion-method
368           type: string
369
370           Preferred recursion method used to find slaves.
371
372           Possible methods are:
373
374             METHOD       USES
375             ===========  ================
376             processlist  SHOW PROCESSLIST
377             hosts        SHOW SLAVE HOSTS
378
379           The processlist method is preferred because SHOW SLAVE HOSTS is not
380           reliable.  However, the hosts method is required if the server uses
381           a non-standard port (not 3306).  Usually mk-heartbeat does the
382           right thing and finds the slaves, but you may give a preferred
383           method and it will be used first.  If it doesn't find any slaves,
384           the other methods will be tried.
385
386       --replace
387           Use "REPLACE" instead of "UPDATE" for --update.
388
389           When running in "--update" mode, use "REPLACE" instead of "UPDATE"
390           to set the heartbeat table's timestamp.  The "REPLACE" statement is
391           a MySQL extension to SQL.  This option is useful when you don't
392           know whether the table contains any rows or not.  It must be used
393           in conjunction with --update.
394
395       --run-time
396           type: time
397
398           Time to run before exiting.
399
400       --sentinel
401           type: string; default: /tmp/mk-heartbeat-sentinel
402
403           Exit if this file exists.
404
405       --set-vars
406           type: string; default: wait_timeout=10000
407
408           Set these MySQL variables.  Immediately after connecting to MySQL,
409           this string will be appended to SET and executed.
410
411       --skew
412           type: float; default: 0.5
413
414           How long to delay checks.
415
416           The default is to delay checks one half second.  Since the update
417           happens as soon as possible after the beginning of the second on
418           the master, this allows one half second of replication delay before
419           reporting that the slave lags the master by one second.  If your
420           clocks are not completely accurate or there is some other reason
421           you'd like to delay the slave more or less, you can tweak this
422           value.  Try setting the "MKDEBUG" environment variable to see the
423           effect this has.
424
425       --socket
426           short form: -S; type: string
427
428           Socket file to use for connection.
429
430       --stop
431           Stop running instances by creating the sentinel file.
432
433           This should have the effect of stopping all running instances which
434           are watching the same sentinel file.  If none of "--update",
435           "--monitor" or "--check" is specified, "mk-heartbeat" will exit
436           after creating the file.  If one of these is specified,
437           "mk-heartbeat" will wait the interval given by "--interval", then
438           remove the file and continue working.
439
440           You might find this handy to stop cron jobs gracefully if
441           necessary, or to replace one running instance with another.  For
442           example, if you want to stop and restart "mk-heartbeat" every hour
443           (just to make sure that it is restarted every hour, in case of a
444           server crash or some other problem), you could use a "crontab" line
445           like this:
446
447            0 * * * * mk-heartbeat --update -D test --stop \
448              --sentinel /tmp/mk-heartbeat-hourly
449
450           The non-default "--sentinel" will make sure the hourly "cron" job
451           stops only instances previously started with the same options (that
452           is, from the same "cron" job).
453
454           See also "--sentinel".
455
456       --table
457           type: string; default: heartbeat
458
459           The table to use for the heartbeat.
460
461           Don't specify database.table; use "--database" to specify the
462           database.
463
464           See "--create-table".
465
466       --update
467           Update a master's heartbeat.
468
469       --user
470           short form: -u; type: string
471
472           User for login if not current user.
473
474       --version
475           Show version and exit.
476

DSN OPTIONS

478       These DSN options are used to create a DSN.  Each option is given like
479       "option=value".  The options are case-sensitive, so P and p are not the
480       same option.  There cannot be whitespace before or after the "=" and if
481       the value contains whitespace it must be quoted.  DSN options are
482       comma-separated.  See the maatkit manpage for full details.
483
484       ·   A
485
486           dsn: charset; copy: yes
487
488           Default character set.
489
490       ·   D
491
492           dsn: database; copy: yes
493
494           Default database.
495
496       ·   F
497
498           dsn: mysql_read_default_file; copy: yes
499
500           Only read default options from the given file
501
502       ·   h
503
504           dsn: host; copy: yes
505
506           Connect to host.
507
508       ·   p
509
510           dsn: password; copy: yes
511
512           Password to use when connecting.
513
514       ·   P
515
516           dsn: port; copy: yes
517
518           Port number to use for connection.
519
520       ·   S
521
522           dsn: mysql_socket; copy: yes
523
524           Socket file to use for connection.
525
526       ·   u
527
528           dsn: user; copy: yes
529
530           User for login if not current user.
531

DOWNLOADING

533       You can download Maatkit from Google Code at
534       <http://code.google.com/p/maatkit/>, or you can get any of the tools
535       easily with a command like the following:
536
537          wget http://www.maatkit.org/get/toolname
538          or
539          wget http://www.maatkit.org/trunk/toolname
540
541       Where "toolname" can be replaced with the name (or fragment of a name)
542       of any of the Maatkit tools.  Once downloaded, they're ready to run; no
543       installation is needed.  The first URL gets the latest released version
544       of the tool, and the second gets the latest trunk code from Subversion.
545

ENVIRONMENT

547       The environment variable "MKDEBUG" enables verbose debugging output in
548       all of the Maatkit tools:
549
550          MKDEBUG=1 mk-....
551

SYSTEM REQUIREMENTS

553       You need Perl, DBI, DBD::mysql, and some core packages that ought to be
554       installed in any reasonably new version of Perl.
555

BUGS

557       For a list of known bugs see
558       <http://www.maatkit.org/bugs/mk-heartbeat>.
559
560       Please use Google Code Issues and Groups to report bugs or request
561       support: <http://code.google.com/p/maatkit/>.  You can also join
562       #maatkit on Freenode to discuss Maatkit.
563
564       Please include the complete command-line used to reproduce the problem
565       you are seeing, the version of all MySQL servers involved, the complete
566       output of the tool when run with "--version", and if possible,
567       debugging output produced by running with the "MKDEBUG=1" environment
568       variable.
569

COPYRIGHT, LICENSE AND WARRANTY

571       This program is copyright 2007-2011 Percona Inc.  and copyright 2006
572       Proven Scaling LLC and Six Apart Ltd.  Feedback and improvements are
573       welcome.
574
575       THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
576       WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
577       MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
578
579       This program is free software; you can redistribute it and/or modify it
580       under the terms of the GNU General Public License as published by the
581       Free Software Foundation, version 2; OR the Perl Artistic License.  On
582       UNIX and similar systems, you can issue `man perlgpl' or `man
583       perlartistic' to read these licenses.
584
585       You should have received a copy of the GNU General Public License along
586       with this program; if not, write to the Free Software Foundation, Inc.,
587       59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
588

SEE ALSO

590       See also mk-slave-delay and mk-slave-restart.
591

AUTHOR

593       Proven Scaling LLC, SixApart Ltd, and Baron Schwartz
594

ABOUT MAATKIT

596       This tool is part of Maatkit, a toolkit for power users of MySQL.
597       Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
598       primary code contributors.  Both are employed by Percona.  Financial
599       support for Maatkit development is primarily provided by Percona and
600       its clients.
601

VERSION

603       This manual page documents Ver 1.0.23 Distrib 7540 $Revision: 7537 $.
604
605
606
607perl v5.32.0                      2020-07-28                   MK-HEARTBEAT(1)
Impressum