1MK-UPGRADE(1) User Contributed Perl Documentation MK-UPGRADE(1)
2
6 mk-upgrade - Execute queries on multiple servers and check for
7 differences.
8
10 Usage: mk-upgrade [OPTION...] DSN [DSN...] [FILE]
11 mk-upgrade compares query execution on two hosts by executing queries
13 in the given file (or STDIN if no file given) and examining the
14 results, errors, warnings, etc.produced on each.
15 Execute and compare all queries in slow.log on host1 to host2:
17 mk-upgrade slow.log h=host1 h=host2
19 Use mk-query-digest to get, execute and compare queries from tcpdump:
21 tcpdump -i eth0 port 3306 -s 65535 -x -n -q -tttt \
23 | mk-query-digest --type tcpdump --no-report --print \
24 | mk-upgrade h=host1 h=host2
25 Compare only query times on host1 to host2 and host3:
27 mk-upgrade slow.log h=host1 h=host2 h=host3 --compare query_times
29 Compare a single query, no slowlog needed:
31 mk-upgrade h=host1 h=host2 --query 'SELECT * FROM db.tbl'
33
35 The following section is included to inform users about the potential
36 risks, whether known or unknown, of using this tool. The two main
37 categories of risks are those created by the nature of the tool (e.g.
38 read-only tools vs. read-write tools) and those created by bugs.
39 mk-upgrade is a read-only tool that is meant to be used on non-
41 production servers. It executes the SQL that you give it as input,
42 which could cause undesired load on a production server.
43 At the time of this release, there is a bug that causes the tool to
45 crash, and a bug that causes a deadlock.
46 The authoritative source for updated information is always the online
48 issue tracking system. Issues that affect this tool will be marked as
49 such. You can see a list of such issues at the following URL:
50 <http://www.maatkit.org/bugs/mk-upgrade>.
51 See also "BUGS" for more information on filing bugs and getting help.
53
55 mk-upgrade executes queries from slowlogs on one or more MySQL server
56 to find differences in query time, warnings, results, and other aspects
57 of the querys' execution. This helps evaluate upgrades, migrations and
58 configuration changes. The comparisons specified by "--compare"
59 determine what differences can be found. A report is printed which
60 outlines all the differences found; see "OUTPUT" below.
61 The first DSN (host) specified on the command line is authoritative; it
63 defines the results to which the other DSNs are compared. You can
64 "compare" only one host, in which case there will be no differences but
65 the output can be saved to be diffed later against the output of
66 another single host "comparison".
67 At present, mk-upgrade only reads slowlogs. Use "mk-query-digest
69 --print" to transform other log formats to slowlog.
70 DSNs and slowlog files can be specified in any order. mk-upgrade will
72 automatically determine if an argument is a DSN or a slowlog file. If
73 no slowlog files are given and "--query" is not specified then mk-
74 upgrade will read from "STDIN".
75
77 TODO
78
80 This tool accepts additional command-line arguments. Refer to the
81 "SYNOPSIS" and usage information for details.
82 --ask-pass
84 Prompt for a password when connecting to MySQL.
85 --base-dir
87 type: string; default: /tmp
88 Save outfiles for the "rows" comparison method in this directory.
90 See the "rows" "--compare-results-method".
92 --charset
94 short form: -A; type: string
95 Default character set. If the value is utf8, sets Perl's binmode
97 on STDOUT to utf8, passes the mysql_enable_utf8 option to
98 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
99 other value sets binmode on STDOUT without the utf8 layer, and runs
100 SET NAMES after connecting to MySQL.
101 --[no]clear-warnings
103 default: yes
104 Clear warnings before each warnings comparison.
106 If comparing warnings ("--compare" includes "warnings"), this
108 option causes mk-upgrade to execute a successful "SELECT" statement
109 which clears any warnings left over from previous queries. This
110 requires a current database that mk-upgrade usually detects
111 automatically, but in some cases it might be necessary to specify
112 "--temp-database". If mk-upgrade can't auto-detect the current
113 database, it will create a temporary table in the "--temp-database"
114 called "mk_upgrade_clear_warnings".
115 --clear-warnings-table
117 type: string
118 Execute "SELECT * FROM ... LIMIT 1" from this table to clear
120 warnings.
121 --compare
123 type: Hash; default: query_times,results,warnings
124 What to compare for each query executed on each host.
126 Comparisons determine differences when the queries are executed on
128 the hosts. More comparisons enable more differences to be
129 detected. The following comparisons are available:
130 query_times
132 Compare query execution times. If this comparison is disabled,
133 the queries are still executed so that other comparisons will
134 work, but the query time attributes are removed from the
135 events.
136 results
138 Compare result sets to find differences in rows, columns, etc.
139 What differences can be found depends on the
141 "--compare-results-method" used.
142 warnings
144 Compare warnings from "SHOW WARNINGS". Requires at least MySQL
145 4.1.
146 --compare-results-method
148 type: string; default: CHECKSUM; group: Comparisons
149 Method to use for "--compare" "results". This option has no effect
151 if "--no-compare-results" is given.
152 Available compare methods (case-insensitive):
154 CHECKSUM
156 Do "CREATE TEMPORARY TABLE `mk_upgrade` AS query" then
157 "CHECKSUM TABLE `mk_upgrade`". This method is fast and simple
158 but in rare cases might it be inaccurate because the MySQL
159 manual says:
160 [The] fact that two tables produce the same checksum does I<not> mean that
162 the tables are identical.
163 Requires at least MySQL 4.1.
165 rows
167 Compare rows one-by-one to find differences. This method has
168 advantages and disadvantages. Its disadvantages are that it
169 may be slower and it requires writing and reading outfiles from
170 disk. Its advantages are that it is universal (works for all
171 versions of MySQL), it doesn't alter the query in any way, and
172 it can find column value differences.
173 The "rows" method works as follows:
175 1. Rows from each host are compared one-by-one.
177 2. If no differences are found, comparison stops, else...
178 3. All remain rows (after the point where they begin to differ)
179 are written to outfiles.
180 4. The outfiles are loaded into temporary tables with
181 C<LOAD DATA LOCAL INFILE>.
182 5. The temporary tables are analyzed to determine the differences.
183 The outfiles are written to the "--base-dir".
185 --config
187 type: Array
188 Read this comma-separated list of config files; if specified, this
190 must be the first option on the command line.
191 --continue-on-error
193 Continue working even if there is an error.
194 --convert-to-select
196 Convert non-SELECT statements to SELECTs and compare.
197 By default non-SELECT statements are not allowed. This option
199 causes non-SELECT statments (like UPDATE, INSERT and DELETE) to be
200 converted to SELECT statements, executed and compared.
201 For example, "DELETE col FROM tbl WHERE id=1" is converted to
203 "SELECT col FROM tbl WHERE id=1".
204 --daemonize
206 Fork to the background and detach from the shell. POSIX operating
207 systems only.
208 --explain-hosts
210 Print connection information and exit.
211 --filter
213 type: string
214 Discard events for which this Perl code doesn't return true.
216 This option is a string of Perl code or a file containing Perl code
218 that gets compiled into a subroutine with one argument: $event.
219 This is a hashref. If the given value is a readable file, then mk-
220 upgrade reads the entire file and uses its contents as the code.
221 The file should not contain a shebang (#!/usr/bin/perl) line.
222 If the code returns true, the chain of callbacks continues;
224 otherwise it ends. The code is the last statement in the
225 subroutine other than "return $event". The subroutine template is:
226 sub { $event = shift; filter && return $event; }
228 Filters given on the command line are wrapped inside parentheses
230 like like "( filter )". For complex, multi-line filters, you must
231 put the code inside a file so it will not be wrapped inside
232 parentheses. Either way, the filter must produce syntactically
233 valid code given the template. For example, an if-else branch
234 given on the command line would not be valid:
235 --filter 'if () { } else { }' # WRONG
237 Since it's given on the command line, the if-else branch would be
239 wrapped inside parentheses which is not syntactically valid. So to
240 accomplish something more complex like this would require putting
241 the code in a file, for example filter.txt:
242 my $event_ok; if (...) { $event_ok=1; } else { $event_ok=0; } $event_ok
244 Then specify "--filter filter.txt" to read the code from
246 filter.txt.
247 If the filter code won't compile, mk-upgrade will die with an
249 error. If the filter code does compile, an error may still occur
250 at runtime if the code tries to do something wrong (like pattern
251 match an undefined value). mk-upgrade does not provide any
252 safeguards so code carefully!
253 An example filter that discards everything but SELECT statements:
255 --filter '$event->{arg} =~ m/^select/i'
257 This is compiled into a subroutine like the following:
259 sub { $event = shift; ( $event->{arg} =~ m/^select/i ) && return $event; }
261 It is permissible for the code to have side effects (to alter
263 $event).
264 You can find an explanation of the structure of $event at
266 <http://code.google.com/p/maatkit/wiki/EventAttributes>.
267 --fingerprints
269 Add query fingerprints to the standard query analysis report. This
270 is mostly useful for debugging purposes.
271 --float-precision
273 type: int
274 Round float, double and decimal values to this many places.
276 This option helps eliminate false-positives caused by floating-
278 point imprecision.
279 --help
281 Show help and exit.
282 --host
284 short form: -h; type: string
285 Connect to host.
287 --iterations
289 type: int; default: 1
290 How many times to iterate through the collect-and-report cycle. If
292 0, iterate to infinity. See also --run-time.
293 --limit
295 type: string; default: 95%:20
296 Limit output to the given percentage or count.
298 If the argument is an integer, report only the top N worst queries.
300 If the argument is an integer followed by the "%" sign, report that
301 percentage of the worst queries. If the percentage is followed by
302 a colon and another integer, report the top percentage or the
303 number specified by that integer, whichever comes first.
304 --log
306 type: string
307 Print all output to this file when daemonized.
309 --max-different-rows
311 type: int; default: 10
312 Stop comparing rows for "--compare-results-method rows" after this
314 many differences are found.
315 --order-by
317 type: string; default: differences:sum
318 Sort events by this attribute and aggregate function.
320 --password
322 short form: -p; type: string
323 Password to use when connecting.
325 --pid
327 type: string
328 Create the given PID file when daemonized. The file contains the
330 process ID of the daemonized instance. The PID file is removed
331 when the daemonized instance exits. The program checks for the
332 existence of the PID file when starting; if it exists and the
333 process with the matching PID exists, the program exits.
334 --port
336 short form: -P; type: int
337 Port number to use for connection.
339 --query
341 type: string
342 Execute and compare this single query; ignores files on command
344 line.
345 This option allows you to supply a single query on the command
347 line. Any slowlogs also specified on the command line are ignored.
348 --[no]report
350 default: yes
351 Print the "--reports".
353 --reports
355 type: Hash; default: queries,differences,errors,statistics
356 Print these reports. Valid reports are queries, differences,
358 errors, and statistics.
359 See "OUTPUT" for more information on the various parts of the
361 report.
362 --run-time
364 type: time
365 How long to run before exiting. The default is to run forever (you
367 can interrupt with CTRL-C).
368 --set-vars
370 type: string; default: wait_timeout=10000,query_cache_type=0
371 Set these MySQL variables. Immediately after connecting to MySQL,
373 this string will be appended to SET and executed.
374 --shorten
376 type: int; default: 1024
377 Shorten long statements in reports.
379 Shortens long statements, replacing the omitted portion with a
381 "/*... omitted ...*/" comment. This applies only to the output in
382 reports. It prevents a large statement from causing difficulty in
383 a report. The argument is the preferred length of the shortened
384 statement. Not all statements can be shortened, but very large
385 INSERT and similar statements often can; and so can IN() lists,
386 although only the first such list in the statement will be
387 shortened.
388 If it shortens something beyond recognition, you can find the
390 original statement in the log, at the offset shown in the report
391 header (see "OUTPUT").
392 --socket
394 short form: -S; type: string
395 Socket file to use for connection.
397 --temp-database
399 type: string
400 Use this database for creating temporary tables.
402 If given, this database is used for creating temporary tables for
404 the results comparison (see "--compare"). Otherwise, the current
405 database (from the last event that specified its database) is used.
406 --temp-table
408 type: string; default: mk_upgrade
409 Use this table for checksumming results.
411 --user
413 short form: -u; type: string
414 User for login if not current user.
416 --version
418 Show version and exit.
419 --zero-query-times
421 Zero the query times in the report.
422
424 These DSN options are used to create a DSN. Each option is given like
425 "option=value". The options are case-sensitive, so P and p are not the
426 same option. There cannot be whitespace before or after the "=", and
427 if the value contains whitespace it must be quoted. DSN options are
428 comma-separated. See the maatkit manpage for full details.
429 · A
431 dsn: charset; copy: yes
433 Default character set.
435 · D
437 dsn: database; copy: yes
439 Default database.
441 · F
443 dsn: mysql_read_default_file; copy: yes
445 Only read default options from the given file
447 · h
449 dsn: host; copy: yes
451 Connect to host.
453 · p
455 dsn: password; copy: yes
457 Password to use when connecting.
459 · P
461 dsn: port; copy: yes
463 Port number to use for connection.
465 · S
467 dsn: mysql_socket; copy: yes
469 Socket file to use for connection.
471 · u
473 dsn: user; copy: yes
475 User for login if not current user.
477
479 You can download Maatkit from Google Code at
480 <http://code.google.com/p/maatkit/>, or you can get any of the tools
481 easily with a command like the following:
482 wget http://www.maatkit.org/get/toolname
484 or
485 wget http://www.maatkit.org/trunk/toolname
486 Where "toolname" can be replaced with the name (or fragment of a name)
488 of any of the Maatkit tools. Once downloaded, they're ready to run; no
489 installation is needed. The first URL gets the latest released version
490 of the tool, and the second gets the latest trunk code from Subversion.
491
493 The environment variable "MKDEBUG" enables verbose debugging output in
494 all of the Maatkit tools:
495 MKDEBUG=1 mk-....
497
499 You need Perl and some core packages that ought to be installed in any
500 reasonably new version of Perl.
501
503 For a list of known bugs see <http://www.maatkit.org/bugs/mk-upgrade>.
504 Please use Google Code Issues and Groups to report bugs or request
506 support: <http://code.google.com/p/maatkit/>. You can also join
507 #maatkit on Freenode to discuss Maatkit.
508 Please include the complete command-line used to reproduce the problem
510 you are seeing, the version of all MySQL servers involved, the complete
511 output of the tool when run with "--version", and if possible,
512 debugging output produced by running with the "MKDEBUG=1" environment
513 variable.
514
516 This program is copyright 2009-2011 Percona, Inc. Feedback and
517 improvements are welcome.
518 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
520 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
521 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
522 This program is free software; you can redistribute it and/or modify it
524 under the terms of the GNU General Public License as published by the
525 Free Software Foundation, version 2; OR the Perl Artistic License. On
526 UNIX and similar systems, you can issue `man perlgpl' or `man
527 perlartistic' to read these licenses.
528 You should have received a copy of the GNU General Public License along
530 with this program; if not, write to the Free Software Foundation, Inc.,
531 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
532
534 Baron Schwartz, Daniel Nichter
535
537 This tool is part of Maatkit, a toolkit for power users of MySQL.
538 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
539 primary code contributors. Both are employed by Percona. Financial
540 support for Maatkit development is primarily provided by Percona and
541 its clients.
542
544 This manual page documents Ver 0.9.8 Distrib 7540 $Revision: 7531 $.
545perl v5.28.0 2011-06-08 MK-UPGRADE(1)