MK-UPGRADE(1)

📦
version 7540 · 3 versions
, in fedora 43

🎀 MK-UPGRADE(1) 🎀

User Contributed Perl Documentation — hi bestie, welcome to the group chat 💖

✨ NAME ✨

mk-upgrade — run your queries on a whole bunch of servers at once and catch every single difference between them. it's giving detective energy, fr fr 🔍💅

🦋 SYNOPSIS 🦋

okay so the vibe is:

Usage: mk-upgrade [OPTION...] DSN [DSN...] [FILE]

mk-upgrade compares how queries execute on two hosts by running the queries in the given file (or STDIN if you don't hand it a file) and then side-eyeing the results, errors, warnings, etc. that each one produced. no cap, it notices everything.

Run every query in slow.log on host1 and compare it to host2:

mk-upgrade slow.log h=host1 h=host2

Use mk-query-digest to grab, execute and compare queries straight out of tcpdump:

tcpdump -i eth0 port 3306 -s 65535  -x -n -q -tttt     \
  | mk-query-digest --type tcpdump --no-report --print \
  | mk-upgrade h=host1 h=host2

Compare only the query times on host1 vs host2 vs host3:

mk-upgrade slow.log h=host1 h=host2 h=host3 --compare query_times

Compare a single query, no slowlog required, we're keeping it cute and simple:

mk-upgrade h=host1 h=host2 --query 'SELECT * FROM db.tbl'

💀 RISKS 💀

bestie. sit down. we need to talk about the potential risks, whether known or unknown, of using this tool. there are two main categories of risk: the ones that come from the nature of the tool itself (read-only tools vs. read-write tools, iykyk) and the ones that come from bugs. 😭

mk-upgrade is a read-only tool that is meant to be used on non-production servers. it executes the SQL you feed it, which could absolutely cause undesired load on a production server. like, please don't do that to your prod box, she's been through enough. 🙈

At the time of this release, there is a bug that causes the tool to crash, and a bug that causes a deadlock. yeah. two of them. not me telling you this on the first date 💀

The authoritative source for updated info is always the online issue tracking system. Issues that affect this tool will be marked as such. You can peep the full list at:

<http://www.maatkit.org/bugs/mk-upgrade>

See also "BUGS" for more info on filing bugs and getting help.

🌸 DESCRIPTION 🌸

mk-upgrade executes queries from slowlogs on one or more MySQL servers to find differences in query time, warnings, results, and other aspects of how those queries executed. this is the whole point, bestie — it helps you evaluate upgrades, migrations, and configuration changes. 👑

The comparisons you specify with --compare decide what differences can actually be found. A report gets printed that lays out all the differences it sniffed out; see "OUTPUT" below.

The first DSN (host) you drop on the command line is the authoritative one — she's the main character 👑 — and she defines the results that every other DSN gets compared against. You can also "compare" just one host, in which case there will be no differences at all, but you can save that output and diff it later against the output of another single-host "comparison". iconic behavior, honestly.

At present, mk-upgrade only reads slowlogs. Use mk-query-digest --print to transform other log formats into slowlog.

DSNs and slowlog files can be specified in any order — mk-upgrade will automatically figure out whether an argument is a DSN or a slowlog file. If you give it no slowlog files and don't specify --query, then mk-upgrade reads from STDIN. 🫶

🌈 OUTPUT 🌈

TODO

like… literally just TODO. that's all she wrote. the vibes are pending. 🎀

✨ OPTIONS ✨

This tool accepts additional command-line arguments. Refer to the "SYNOPSIS" and usage info for the deets.

  • --ask-pass — Prompt for a password when connecting to MySQL. 🔐
  • --base-dir — type: string; default: /tmp

Save outfiles for the rows comparison method in this directory.

See the rows --compare-results-method.

  • --charset — short form: -A; type: string

Default character set. If the value is utf8, it sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

  • --[no]clear-warnings — default: yes

Clear warnings before each warnings comparison. 🧼

If you're comparing warnings (--compare includes warnings), this option makes mk-upgrade execute a successful SELECT statement which clears any warnings left over from previous queries. This requires a current database that mk-upgrade usually detects automatically, but in some cases it might be necessary to specify --temp-database. If mk-upgrade can't auto-detect the current database, it will create a temporary table in the --temp-database called mk_upgrade_clear_warnings.

  • --clear-warnings-table — type: string

Execute SELECT * FROM ... LIMIT 1 from this table to clear warnings.

  • --compare — type: Hash; default: query_times,results,warnings

What to compare for each query executed on each host. 🫶

Comparisons determine differences when the queries are executed on the hosts. More comparisons = more differences detected. The following comparisons are available:

  • query_times — Compare query execution times. If this comparison is disabled, the queries are still executed so that other comparisons will work, but the query time attributes are removed from the events. ⏱️
  • results — Compare result sets to find differences in rows, columns, etc. What differences can be found depends on the --compare-results-method used.
  • warnings — Compare warnings from SHOW WARNINGS. Requires at least MySQL 4.1.
  • --compare-results-method — type: string; default: CHECKSUM; group: Comparisons

Method to use for --compare results. This option has no effect if --no-compare-results is given.

Available compare methods (case-insensitive):

  • CHECKSUM — Do CREATE TEMPORARY TABLE mk_upgrade AS query then CHECKSUM TABLE mk_upgrade``. This method is fast and simple, but in rare cases it might be inaccurate because the MySQL manual says:
[The] fact that two tables produce the same checksum does not mean that the tables are identical.

Requires at least MySQL 4.1.

  • rows — Compare rows one-by-one to find differences. This method has advantages and disadvantages. Its disadvantages are that it may be slower and it requires writing and reading outfiles from disk. Its advantages are that it is universal (works for all versions of MySQL), it doesn't alter the query in any way, and it can find column value differences. 🔍

The rows method works as follows:

  1. Rows from each host are compared one-by-one.
  2. If no differences are found, comparison stops, else…
  3. All remain rows (after the point where they begin to differ) are written to outfiles.
  4. The outfiles are loaded into temporary tables with LOAD DATA LOCAL INFILE.
  5. The temporary tables are analyzed to determine the differences.

The outfiles are written to the --base-dir.

  • --config — type: Array

Read this comma-separated list of config files; if specified, this must be the first option on the command line. first in line, no cutting bestie 💅

  • --continue-on-error — Continue working even if there is an error. we push through 💪
  • --convert-to-select — Convert non-SELECT statements to SELECTs and compare.

By default non-SELECT statements are not allowed. This option causes non-SELECT statments (like UPDATE, INSERT and DELETE) to be converted to SELECT statements, executed and compared.

For example, DELETE col FROM tbl WHERE id=1 is converted to SELECT col FROM tbl WHERE id=1.

  • --daemonize — Fork to the background and detach from the shell. POSIX operating systems only. 🧚 she's independent now
  • --explain-hosts — Print connection information and exit.
  • --filter — type: string

Discard events for which this Perl code doesn't return true.

This option is a string of Perl code or a file containing Perl code that gets compiled into a subroutine with one argument: $event. This is a hashref. If the given value is a readable file, then mk-upgrade reads the entire file and uses its contents as the code. The file should not contain a shebang (#!/usr/bin/perl) line.

If the code returns true, the chain of callbacks continues; otherwise it ends. The code is the last statement in the subroutine other than return $event. The subroutine template is:

sub { $event = shift; filter && return $event; }

Filters given on the command line are wrapped inside parentheses like ( filter ). For complex, multi-line filters, you must put the code inside a file so it will not be wrapped inside parentheses. Either way, the filter must produce syntactically valid code given the template. For example, an if-else branch given on the command line would not be valid:

--filter 'if () { } else { }'  # WRONG

Since it's given on the command line, the if-else branch would be wrapped inside parentheses which is not syntactically valid. So to accomplish something more complex like this, you'd have to put the code in a file, for example filter.txt:

my $event_ok; if (...) { $event_ok=1; } else { $event_ok=0; } $event_ok

Then specify --filter filter.txt to read the code from filter.txt.

If the filter code won't compile, mk-upgrade will die with an error. If the filter code does compile, an error may still occur at runtime if the code tries to do something wrong (like pattern match an undefined value). mk-upgrade does not provide any safeguards so code carefully! 😭

An example filter that discards everything but SELECT statements:

--filter '$event->{arg} =~ m/^select/i'

This is compiled into a subroutine like the following:

sub { $event = shift; ( $event->{arg} =~ m/^select/i ) && return $event; }

It is permissible for the code to have side effects (to alter $event).

You can find an explanation of the structure of $event at <http://code.google.com/p/maatkit/wiki/EventAttributes>.

  • --fingerprints — Add query fingerprints to the standard query analysis report. This is mostly useful for debugging purposes. 🐛
  • --float-precision — type: int

Round float, double and decimal values to this many places.

This option helps eliminate false-positives caused by floating-point imprecision. math is fake, this is the receipts 🧮

  • --help — Show help and exit. 🫶
  • --host — short form: -h; type: string

Connect to host.

  • --iterations — type: int; default: 1

How many times to iterate through the collect-and-report cycle. If 0, iterate to infinity. See also --run-time. ♾️

  • --limit — type: string; default: 95%:20

Limit output to the given percentage or count.

okay bestie we are literally not done yet — this page keeps going. keep scrolling 💅✨

okay so the argument for that one up there 👆 — if you hand it an integer, it only reports the top N worst queries. If you hand it an integer with a % sign stuck on it, it reports that percentage of the worst queries. And if that percentage is followed by a colon and another integer, it reports the top percentage or the number that integer says — whichever comes first. flexible queen behavior ✨

  • --log — type: string. When you daemonize, all the output goes into this file. 📝
  • --max-different-rows — type: int; default: 10. For --compare-results-method rows, it stops comparing rows once it's found this many differences. it's like "okay bestie, we get it, they're different" 💀
  • --order-by — type: string; default: differences:sum. Sorts events by this attribute and aggregate function.
  • --password — short form: -p; type: string. The password you use when connecting. 🔐
  • --pid — type: string. Makes the given PID file when daemonized. The file holds the process ID of the daemonized instance, and it gets removed when that instance exits. When the program starts it checks if the PID file exists; if it does, and a process with the matching PID exists, the program exits. no duplicates allowed, we're exclusive 💅
  • --port — short form: -P; type: int. Port number for the connection.
  • --query — type: string. Executes and compares this single query; it ignores files on the command line. This option lets you supply a single query right there on the command line, and any slowlogs you also specified on the command line get ignored. one query, main character energy 👑
  • --[no]report — default: yes. Prints the --reports.
  • --reports — type: Hash; default: queries,differences,errors,statistics. Prints these reports. The valid ones are queries, differences, errors, and statistics. Peep the OUTPUT section for more on the various parts of the report.
  • --run-time — type: time. How long it runs before exiting. Default is to run forever (you can interrupt with CTRL-C). forever era 🫶
  • --set-vars — type: string; default: wait_timeout=10000,query_cache_type=0. Sets these MySQL variables. Immediately after connecting to MySQL, this string gets appended to SET and executed.
  • --shorten — type: int; default: 1024. Shortens long statements in reports. It replaces the omitted portion with a "/*... omitted ...*/" comment. This applies only to the output in reports, and it keeps a huge statement from making a report a nightmare. The argument is the preferred length of the shortened statement. Not every statement can be shortened, but very large INSERT and similar statements often can — and so can IN() lists, although only the first such list in the statement gets shortened.

If it shortens something past the point of recognition, you can find the original statement in the log, at the offset shown in the report header (see OUTPUT).

  • --socket — short form: -S; type: string. Socket file to use for the connection. 🧦
  • --temp-database — type: string. Uses this database for creating temporary tables. If you give it, this database is used to create temporary tables for the results comparison (see --compare). Otherwise, the current database (from the last event that specified its database) is used.
  • --temp-table — type: string; default: mk_upgrade. Uses this table for checksumming results.
  • --user — short form: -u; type: string. User for login if it's not the current user.
  • --version — Shows version and exits. bye bestie 👋
  • --zero-query-times — Zeroes the query times in the report.

💫 DSN OPTIONS 💫

These DSN options are used to create a DSN. Each option is given like option=value. The options are case-sensitive, so P and p are not the same option — yes, they're different girlies. There can be no whitespace before or after the =, and if the value contains whitespace it must be quoted. DSN options are comma-separated. See the maatkit manpage for full details.

  • A

dsn: charset; copy: yes

Default character set.

  • D

dsn: database; copy: yes

Default database.

  • F

dsn: mysql_read_default_file; copy: yes

Only read default options from the given file

  • h

dsn: host; copy: yes

Connect to host.

  • p

dsn: password; copy: yes

Password to use when connecting.

  • P

dsn: port; copy: yes

Port number to use for connection.

  • S

dsn: mysql_socket; copy: yes

Socket file to use for connection.

  • u

dsn: user; copy: yes

User for login if not current user.

🦋 DOWNLOADING 🦋

You can download Maatkit from Google Code at http://code.google.com/p/maatkit/, or you can get any of the tools easily with a command like the following:

wget http://www.maatkit.org/get/toolname
or
wget http://www.maatkit.org/trunk/toolname

Where "toolname" can be replaced with the name (or fragment of a name) of any of the Maatkit tools. Once downloaded, they're ready to run — no installation is needed. The first URL gets the latest released version of the tool, and the second gets the latest trunk code from Subversion. grab and go, no setup arc 😌✨

🌈 ENVIRONMENT 🌈

The environment variable "MKDEBUG" enables verbose debugging output in all of the Maatkit tools:

MKDEBUG=1 mk-....

🌸 SYSTEM REQUIREMENTS 🌸

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

💀 BUGS 💀

For a list of known bugs see http://www.maatkit.org/bugs/mk-upgrade.

Please use Google Code Issues and Groups to report bugs or request support: http://code.google.com/p/maatkit/. You can also join #maatkit on Freenode to discuss Maatkit.

Please include the complete command-line used to reproduce the problem you're seeing, the version of all MySQL servers involved, the complete output of the tool when run with --version, and if possible, debugging output produced by running with the MKDEBUG=1 environment variable. receipts, bestie — give us the whole story 📋

This program is copyright 2009-2011 Percona, Inc. Feedback and improvements are welcome.

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic License. On UNIX and similar systems, you can issue man perlgpl or man perlartistic to read these licenses.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

🎀 AUTHOR 🎀

Baron Schwartz, Daniel Nichter

⭐ ABOUT MAATKIT ⭐

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

💜 VERSION 💜

This manual page documents Ver 0.9.8 Distrib 7540 $Revision: 7531 $.

and that's a wrap on MK-UPGRADE(1), bestie 💖 this lil page is served up by perl v5.42.0 and it's dated 2025-07-24 — go forth and upgrade, you absolute icon 🦋✨

📦 where this page comes from