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