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

NAME

6       mk-upgrade - Execute queries on multiple servers and check for
7       differences.
8

SYNOPSIS

10       Usage: mk-upgrade [OPTION...] DSN [DSN...] [FILE]
11
12       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
16       Execute and compare all queries in slow.log on host1 to host2:
17
18         mk-upgrade slow.log h=host1 h=host2
19
20       Use mk-query-digest to get, execute and compare queries from tcpdump:
21
22         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
26       Compare only query times on host1 to host2 and host3:
27
28         mk-upgrade slow.log h=host1 h=host2 h=host3 --compare query_times
29
30       Compare a single query, no slowlog needed:
31
32         mk-upgrade h=host1 h=host2 --query 'SELECT * FROM db.tbl'
33

RISKS

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
40       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
44       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
47       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
52       See also "BUGS" for more information on filing bugs and getting help.
53

DESCRIPTION

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
62       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
68       At present, mk-upgrade only reads slowlogs.  Use "mk-query-digest
69       --print" to transform other log formats to slowlog.
70
71       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

OUTPUT

77       TODO
78

OPTIONS

80       This tool accepts additional command-line arguments.  Refer to the
81       "SYNOPSIS" and usage information for details.
82
83       --ask-pass
84           Prompt for a password when connecting to MySQL.
85
86       --base-dir
87           type: string; default: /tmp
88
89           Save outfiles for the "rows" comparison method in this directory.
90
91           See the "rows" "--compare-results-method".
92
93       --charset
94           short form: -A; type: string
95
96           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
102       --[no]clear-warnings
103           default: yes
104
105           Clear warnings before each warnings comparison.
106
107           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
116       --clear-warnings-table
117           type: string
118
119           Execute "SELECT * FROM ... LIMIT 1" from this table to clear
120           warnings.
121
122       --compare
123           type: Hash; default: query_times,results,warnings
124
125           What to compare for each query executed on each host.
126
127           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
131           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
137           results
138               Compare result sets to find differences in rows, columns, etc.
139
140               What differences can be found depends on the
141               "--compare-results-method" used.
142
143           warnings
144               Compare warnings from "SHOW WARNINGS".  Requires at least MySQL
145               4.1.
146
147       --compare-results-method
148           type: string; default: CHECKSUM; group: Comparisons
149
150           Method to use for "--compare" "results".  This option has no effect
151           if "--no-compare-results" is given.
152
153           Available compare methods (case-insensitive):
154
155           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
161                 [The] fact that two tables produce the same checksum does I<not> mean that
162                 the tables are identical.
163
164               Requires at least MySQL 4.1.
165
166           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
174               The "rows" method works as follows:
175
176                 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
184               The outfiles are written to the "--base-dir".
185
186       --config
187           type: Array
188
189           Read this comma-separated list of config files; if specified, this
190           must be the first option on the command line.
191
192       --continue-on-error
193           Continue working even if there is an error.
194
195       --convert-to-select
196           Convert non-SELECT statements to SELECTs and compare.
197
198           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
202           For example, "DELETE col FROM tbl WHERE id=1" is converted to
203           "SELECT col FROM tbl WHERE id=1".
204
205       --daemonize
206           Fork to the background and detach from the shell.  POSIX operating
207           systems only.
208
209       --explain-hosts
210           Print connection information and exit.
211
212       --filter
213           type: string
214
215           Discard events for which this Perl code doesn't return true.
216
217           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
223           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
227             sub { $event = shift; filter && return $event; }
228
229           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
236             --filter 'if () { } else { }'  # WRONG
237
238           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
243             my $event_ok; if (...) { $event_ok=1; } else { $event_ok=0; } $event_ok
244
245           Then specify "--filter filter.txt" to read the code from
246           filter.txt.
247
248           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
254           An example filter that discards everything but SELECT statements:
255
256             --filter '$event->{arg} =~ m/^select/i'
257
258           This is compiled into a subroutine like the following:
259
260             sub { $event = shift; ( $event->{arg} =~ m/^select/i ) && return $event; }
261
262           It is permissible for the code to have side effects (to alter
263           $event).
264
265           You can find an explanation of the structure of $event at
266           <http://code.google.com/p/maatkit/wiki/EventAttributes>.
267
268       --fingerprints
269           Add query fingerprints to the standard query analysis report.  This
270           is mostly useful for debugging purposes.
271
272       --float-precision
273           type: int
274
275           Round float, double and decimal values to this many places.
276
277           This option helps eliminate false-positives caused by floating-
278           point imprecision.
279
280       --help
281           Show help and exit.
282
283       --host
284           short form: -h; type: string
285
286           Connect to host.
287
288       --iterations
289           type: int; default: 1
290
291           How many times to iterate through the collect-and-report cycle.  If
292           0, iterate to infinity.  See also --run-time.
293
294       --limit
295           type: string; default: 95%:20
296
297           Limit output to the given percentage or count.
298
299           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
305       --log
306           type: string
307
308           Print all output to this file when daemonized.
309
310       --max-different-rows
311           type: int; default: 10
312
313           Stop comparing rows for "--compare-results-method rows" after this
314           many differences are found.
315
316       --order-by
317           type: string; default: differences:sum
318
319           Sort events by this attribute and aggregate function.
320
321       --password
322           short form: -p; type: string
323
324           Password to use when connecting.
325
326       --pid
327           type: string
328
329           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
335       --port
336           short form: -P; type: int
337
338           Port number to use for connection.
339
340       --query
341           type: string
342
343           Execute and compare this single query; ignores files on command
344           line.
345
346           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
349       --[no]report
350           default: yes
351
352           Print the "--reports".
353
354       --reports
355           type: Hash; default: queries,differences,errors,statistics
356
357           Print these reports.  Valid reports are queries, differences,
358           errors, and statistics.
359
360           See "OUTPUT" for more information on the various parts of the
361           report.
362
363       --run-time
364           type: time
365
366           How long to run before exiting.  The default is to run forever (you
367           can interrupt with CTRL-C).
368
369       --set-vars
370           type: string; default: wait_timeout=10000,query_cache_type=0
371
372           Set these MySQL variables.  Immediately after connecting to MySQL,
373           this string will be appended to SET and executed.
374
375       --shorten
376           type: int; default: 1024
377
378           Shorten long statements in reports.
379
380           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
389           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
393       --socket
394           short form: -S; type: string
395
396           Socket file to use for connection.
397
398       --temp-database
399           type: string
400
401           Use this database for creating temporary tables.
402
403           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
407       --temp-table
408           type: string; default: mk_upgrade
409
410           Use this table for checksumming results.
411
412       --user
413           short form: -u; type: string
414
415           User for login if not current user.
416
417       --version
418           Show version and exit.
419
420       --zero-query-times
421           Zero the query times in the report.
422

DSN OPTIONS

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
430       ·   A
431
432           dsn: charset; copy: yes
433
434           Default character set.
435
436       ·   D
437
438           dsn: database; copy: yes
439
440           Default database.
441
442       ·   F
443
444           dsn: mysql_read_default_file; copy: yes
445
446           Only read default options from the given file
447
448       ·   h
449
450           dsn: host; copy: yes
451
452           Connect to host.
453
454       ·   p
455
456           dsn: password; copy: yes
457
458           Password to use when connecting.
459
460       ·   P
461
462           dsn: port; copy: yes
463
464           Port number to use for connection.
465
466       ·   S
467
468           dsn: mysql_socket; copy: yes
469
470           Socket file to use for connection.
471
472       ·   u
473
474           dsn: user; copy: yes
475
476           User for login if not current user.
477

DOWNLOADING

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
483          wget http://www.maatkit.org/get/toolname
484          or
485          wget http://www.maatkit.org/trunk/toolname
486
487       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

ENVIRONMENT

493       The environment variable "MKDEBUG" enables verbose debugging output in
494       all of the Maatkit tools:
495
496          MKDEBUG=1 mk-....
497

SYSTEM REQUIREMENTS

499       You need Perl and some core packages that ought to be installed in any
500       reasonably new version of Perl.
501

BUGS

503       For a list of known bugs see <http://www.maatkit.org/bugs/mk-upgrade>.
504
505       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
509       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

COPYRIGHT, LICENSE AND WARRANTY

516       This program is copyright 2009-2011 Percona, Inc.  Feedback and
517       improvements are welcome.
518
519       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
523       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
529       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

AUTHOR

534       Baron Schwartz, Daniel Nichter
535

ABOUT MAATKIT

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

VERSION

544       This manual page documents Ver 0.9.8 Distrib 7540 $Revision: 7531 $.
545
546
547
548perl v5.28.0                      2011-06-08                     MK-UPGRADE(1)
Impressum