1MK-HEARTBEAT(1) User Contributed Perl Documentation MK-HEARTBEAT(1)
2
6 mk-heartbeat - Monitor MySQL replication delay.
7
9 Usage: mk-heartbeat [OPTION...] [DSN] --update|--monitor|--check|--stop
10 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 Start daemonized process to update test.heartbeat table on master:
16 mk-heartbeat -D test --update -h master-server --daemonize
18 Monitor replication lag on slave:
20 mk-heartbeat -D test --monitor -h slave-server
22 mk-heartbeat -D test --monitor -h slave-server --dbi-driver Pg
24 Check slave lag once and exit (using optional DSN to specify slave
26 host):
27 mk-heartbeat -D test --check h=slave-server
29
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 mk-heartbeat merely reads and writes a single record in a table. It
37 should be very low-risk.
38 At the time of this release, we know of no bugs that could cause
40 serious harm to users.
41 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 See also "BUGS" for more information on filing bugs and getting help.
48
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 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 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 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 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 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 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 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 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
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 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 For example, if the replication hierarchy is "master -> slave1 ->
116 slave2" with corresponding server IDs 1, 2 and 3, you can:
117 mk-heartbeat --daemonize -D test --update -h master
119 mk-heartbeat --daemonize -D test --update -h slave1
120 Then check (or monitor) the replication delay from master to slave2:
122 mk-heartbeat -D test --master-server-id 1 --check slave2
124 Or check the replication delay from slave1 to slave2:
126 mk-heartbeat -D test --master-server-id 2 --check slave2
128 Stopping the "--update" instance one slave1 will not affect the
130 instance on master.
131
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
139 Specify at least one of "--stop", "--update", "--monitor", or
140 "--check".
141 "--update", "--monitor", and "--check" are mutually exclusive.
143 "--daemonize" and "--check" are mutually exclusive.
145 This tool accepts additional command-line arguments. Refer to the
147 "SYNOPSIS" and usage information for details.
148 --ask-pass
150 Prompt for a password when connecting to MySQL.
151 --charset
153 short form: -A; type: string
154 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 --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 --config
169 type: Array
170 Read this comma-separated list of config files; if specified, this
172 must be the first option on the command line.
173 --create-table
175 Create the heartbeat "--table" if it does not exist.
176 This option causes the table specified by "--database" and
178 "--table" to be created with the following MAGIC_create_heartbeat
179 table definition:
180 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 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 INSERT INTO heartbeat (ts, server_id) VALUES (NOW(), N);
194 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 This is done automatically by "--create-table".
200 A legacy version of the heartbeat table is still supported:
202 CREATE TABLE heartbeat (
204 id int NOT NULL PRIMARY KEY,
205 ts datetime NOT NULL
206 );
207 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 INSERT INTO heartbeat (id, ts) VALUES (1, NOW());
213 The tool automatically detects if the heartbeat table is legacy.
215 See also "MULTI-SLAVE HIERARCHY".
217 --daemonize
219 Fork to the background and detach from the shell. POSIX operating
220 systems only.
221 --database
223 short form: -D; type: string
224 The database to use for the connection.
226 --dbi-driver
228 default: mysql; type: string
229 Specify a driver for the connection; "mysql" and "Pg" are
231 supported.
232 --defaults-file
234 short form: -F; type: string
235 Only read mysql options from the given file. You must give an
237 absolute pathname.
238 --file
240 type: string
241 Print latest "--monitor" output to this file.
243 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 --frames
250 type: string; default: 1m,5m,15m
251 Timeframes for averages.
253 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 --help
263 Show help and exit.
264 --host
266 short form: -h; type: string
267 Connect to host.
269 --[no]insert-heartbeat-row
271 default: yes
272 Insert a heartbeat row in the "--table" if one doesn't exist.
274 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 --interval
283 type: float; default: 1.0
284 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 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 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 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 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 --log
310 type: string
311 Print all output to this file when daemonized.
313 --master-server-id
315 type: string
316 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 --monitor
322 Monitor slave delay continuously.
323 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 5s [ 0.25s, 0.05s, 0.02s ]
330 --password
332 short form: -p; type: string
333 Password to use when connecting.
335 --pid
337 type: string
338 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 --port
346 short form: -P; type: int
347 Port number to use for connection.
349 --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 --recurse
357 type: int
358 Check slaves recursively to this depth in "--check" mode.
360 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 This currently works only with MySQL. See "--recursion-method".
366 --recursion-method
368 type: string
369 Preferred recursion method used to find slaves.
371 Possible methods are:
373 METHOD USES
375 =========== ================
376 processlist SHOW PROCESSLIST
377 hosts SHOW SLAVE HOSTS
378 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 --replace
387 Use "REPLACE" instead of "UPDATE" for --update.
388 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 --run-time
396 type: time
397 Time to run before exiting.
399 --sentinel
401 type: string; default: /tmp/mk-heartbeat-sentinel
402 Exit if this file exists.
404 --set-vars
406 type: string; default: wait_timeout=10000
407 Set these MySQL variables. Immediately after connecting to MySQL,
409 this string will be appended to SET and executed.
410 --skew
412 type: float; default: 0.5
413 How long to delay checks.
415 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 --socket
426 short form: -S; type: string
427 Socket file to use for connection.
429 --stop
431 Stop running instances by creating the sentinel file.
432 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 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 0 * * * * mk-heartbeat --update -D test --stop \
448 --sentinel /tmp/mk-heartbeat-hourly
449 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 See also "--sentinel".
455 --table
457 type: string; default: heartbeat
458 The table to use for the heartbeat.
460 Don't specify database.table; use "--database" to specify the
462 database.
463 See "--create-table".
465 --update
467 Update a master's heartbeat.
468 --user
470 short form: -u; type: string
471 User for login if not current user.
473 --version
475 Show version and exit.
476
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 · A
485 dsn: charset; copy: yes
487 Default character set.
489 · D
491 dsn: database; copy: yes
493 Default database.
495 · F
497 dsn: mysql_read_default_file; copy: yes
499 Only read default options from the given file
501 · h
503 dsn: host; copy: yes
505 Connect to host.
507 · p
509 dsn: password; copy: yes
511 Password to use when connecting.
513 · P
515 dsn: port; copy: yes
517 Port number to use for connection.
519 · S
521 dsn: mysql_socket; copy: yes
523 Socket file to use for connection.
525 · u
527 dsn: user; copy: yes
529 User for login if not current user.
531
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 wget http://www.maatkit.org/get/toolname
538 or
539 wget http://www.maatkit.org/trunk/toolname
540 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
547 The environment variable "MKDEBUG" enables verbose debugging output in
548 all of the Maatkit tools:
549 MKDEBUG=1 mk-....
551
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
557 For a list of known bugs see
558 <http://www.maatkit.org/bugs/mk-heartbeat>.
559 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 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
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 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 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 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
590 See also mk-slave-delay and mk-slave-restart.
591
593 Proven Scaling LLC, SixApart Ltd, and Baron Schwartz
594
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
603 This manual page documents Ver 1.0.23 Distrib 7540 $Revision: 7537 $.
604perl v5.28.1 2011-06-08 MK-HEARTBEAT(1)