Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
PERCONA-TOOLKIT(1)    User Contributed Perl Documentation   PERCONA-TOOLKIT(1)

NAME
       percona-toolkit - Advanced command-line tools for MySQL

DESCRIPTION
       Percona Toolkit is a collection of advanced command-line	tools used by
       Percona (<http://www.percona.com/>) support staff to perform a variety
       of MySQL	and system tasks that are too difficult	or complex to perform
       manually.

       These tools are ideal alternatives to private or	"one-off" scripts
       because they are	professionally developed, formally tested, and fully
       documented.  They are also fully	self-contained,	so installation	is
       quick and easy and no libraries are installed.

       Percona Toolkit is derived from Maatkit and Aspersa, two	of the best-
       known toolkits for MySQL	server administration.	It is developed	and
       supported by Percona.  For more information and other free, open-source
       software	developed by Percona, visit
       <http://www.percona.com/software/>.

TOOLS
       This release of Percona Toolkit includes	the following tools:

       pt-align
	   Align output	from other tools to columns.

       pt-archiver
	   Archive rows	from a MySQL table into	another	table or a file.

       pt-config-diff
	   Diff	MySQL configuration files and server variables.

       pt-deadlock-logger
	   Log MySQL deadlocks.

       pt-diskstats
	   An interactive I/O monitoring tool for GNU/Linux.

       pt-duplicate-key-checker
	   Find	duplicate indexes and foreign keys on MySQL tables.

       pt-fifo-split
	   Split files and pipe	lines to a fifo	without	really splitting.

       pt-find
	   Find	MySQL tables and execute actions, like GNU find.

       pt-fingerprint
	   Convert queries into	fingerprints.

       pt-fk-error-logger
	   Log MySQL foreign key errors.

       pt-heartbeat
	   Monitor MySQL replication delay.

       pt-index-usage
	   Read	queries	from a log and analyze how they	use indexes.

       pt-ioprofile
	   Watch process IO and	print a	table of file and I/O activity.

       pt-kill
	   Kill	MySQL queries that match certain criteria.

       pt-mext
	   Look	at many	samples	of MySQL "SHOW GLOBAL STATUS" side-by-side.

       pt-mysql-summary
	   Summarize MySQL information nicely.

       pt-online-schema-change
	   ALTER tables	without	locking	them.

       pt-pmp
	   Aggregate GDB stack traces for a selected program.

       pt-query-digest
	   Analyze MySQL queries from logs, processlist, and tcpdump.

       pt-show-grants
	   Canonicalize	and print MySQL	grants so you can effectively
	   replicate, compare and version-control them.

       pt-sift
	   Browses files created by pt-stalk.

       pt-slave-delay
	   Make	a MySQL	slave server lag behind	its master.

       pt-slave-find
	   Find	and print replication hierarchy	tree of	MySQL slaves.

       pt-slave-restart
	   Watch and restart MySQL replication after errors.

       pt-stalk
	   Collect forensic data about MySQL when problems occur.

       pt-summary
	   Summarize system information	nicely.

       pt-table-checksum
	   Verify MySQL	replication integrity.

       pt-table-sync
	   Synchronize MySQL table data	efficiently.

       pt-table-usage
	   Analyze how queries use tables.

       pt-upgrade
	   Verify that query results are identical on different	servers.

       pt-variable-advisor
	   Analyze MySQL variables and advise on possible problems.

       pt-visual-explain
	   Format EXPLAIN output as a tree.

       For more	free, open-source software developed Percona, visit
       <http://www.percona.com/software/>.

SPECIAL	OPTION TYPES
       Tool options use	standard types ("int", "string", etc.) as well as
       these special types:

       time
	   Time	values are seconds by default.	For example, "--run-time 60"
	   means 60 seconds.  Time values support an optional suffix: s
	   (seconds), m	(minutes), h (hours), d	(days).	 "--run-time 1m" means
	   1 minute (the same as 60 seconds).

       size
	   Size	values are bytes by default.  For example, "--disk-space-free
	   1024" means 1 Kibibyte.  Size values	support	an optional suffix: k
	   (Kibibyte), M (Mebibyte), G (Gibibyte).

       DSN See "DSN (DATA SOURCE NAME) SPECIFICATIONS".

       Hash, hash, Array, array
	   Hash, hash, Array, and array	values are comma-separated lists of
	   values.  For	example, "--ignore-tables foo,bar" ignores tables
	   "foo" and "bar".

CONFIGURATION FILES
       Percona Toolkit tools can read options from configuration files.	 The
       configuration file syntax is simple and direct, and bears some
       resemblances to the MySQL command-line client tools.  The configuration
       files all follow	the same conventions.

       Internally, what	actually happens is that the lines are read from the
       file and	then added as command-line options and arguments to the	tool,
       so just think of	the configuration files	as a way to write your command
       lines.

   SYNTAX
       The syntax of the configuration files is	as follows:

       o   Whitespace followed by a hash sign (#) signifies that the rest of
	   the line is a comment.  This	is deleted.  For example:

       o   Whitespace is stripped from the beginning and end of	all lines.

       o   Empty lines are ignored.

       o   Each	line is	permitted to be	in either of the following formats:

	     option
	     option=value

	   Do not prefix the option with "--".	Do not quote the values, even
	   if it has spaces; value are literal.	 Whitespace around the equals
	   sign	is deleted during processing.

       o   Only	long options are recognized.

       o   A line containing only two hyphens signals the end of option
	   parsing.  Any further lines are interpreted as additional arguments
	   (not	options) to the	program.

   EXAMPLE
       This config file	for pt-stalk,

	 # Config for pt-stalk
	 variable=Threads_connected
	 cycles=2  # trigger if	problem	seen twice in a	row
	 --
	 --user	daniel

       is equivalent to	this command line:

	 pt-stalk --variable Threads_connected --cycles	2 -- --user daniel

       Options after "--" are passed literally to mysql	and mysqladmin.

   READ	ORDER
       The tools read several configuration files in order:

       1.  The global Percona Toolkit configuration file,
	   /etc/percona-toolkit/percona-toolkit.conf.  All tools read this
	   file, so you	should only add	options	to it that you want to apply
	   to all tools.

       2.  The global tool-specific configuration file,
	   /etc/percona-toolkit/TOOL.conf, where "TOOL"	is a tool name like
	   "pt-query-digest".  This file is named after	the specific tool
	   you're using, so you	can add	options	that apply only	to that	tool.

       3.  The user's own Percona Toolkit configuration	file,
	   $HOME/.percona-toolkit.conf.	 All tools read	this file, so you
	   should only add options to it that you want to apply	to all tools.

       4.  The user's tool-specific configuration file,	$HOME/.TOOL.conf,
	   where "TOOL"	is a tool name like "pt-query-digest".	This file is
	   named after the specific tool you're	using, so you can add options
	   that	apply only to that tool.

   SPECIFYING
       There is	a special "--config" option, which lets	you specify which
       configuration files Percona Toolkit should read.	 You specify a comma-
       separated list of files.	 However, its behavior is not like other
       command-line options.  It must be given first on	the command line,
       before any other	options.  If you try to	specify	it anywhere else, it
       will cause an error.  Also, you cannot specify
       "--config=/path/to/file"; you must specify the option and the path to
       the file	separated by whitespace	without	an equal sign between them,
       like:

	 --config /path/to/file

       If you don't want any configuration files at all, specify "--config ''"
       to provide an empty list	of files.

DSN (DATA SOURCE NAME) SPECIFICATIONS
       Percona Toolkit tools use DSNs to specify how to	create a DBD
       connection to a MySQL server.  A	DSN is a comma-separated string	of
       "key=value" parts, like:

	 h=host1,P=3306,u=bob

       The standard key	parts are shown	below, but some	tools add additional
       key parts.  See each tool's documentation for details.

       Some tools do not use DSNs but still connect to MySQL using options
       like "--host", "--user",	and "--password".  Such	tools uses these
       options to create a DSN automatically, behind the scenes.

       Other tools uses	both DSNs and options like the ones above.  The
       options provide defaults	for all	DSNs that do not specify the option's
       corresponding key part.	For example, if	DSN "h=host1" and option
       "--port=12345" are specified, then the tool automatically adds
       "P=12345" to DSN.

   ESCAPING VALUES
       DSNs are	usually	specified on the command line, so shell	quoting	and
       escaping	must be	taken into account.  Special characters, like asterisk
       ("*"), need to be quoted	and/or escaped properly	to be passed as
       literal characters in DSN values.

       Since DSN parts are separated by	commas,	literal	commas in DSN values
       must be escaped with a single backslash ("\").  And since a backslash
       is the escape character for most	shells,	two backslashes	are required
       to pass a literal backslash.  For example, if the username is literally
       "my,name", it must be specified as "my\\,name" on most shells.  This
       applies to DSNs and DSN-related options like "--user".

   KEY PARTS
       Many of the tools add more parts	to DSNs	for special purposes, and
       sometimes override parts	to make	them do	something slightly different.
       However,	all the	tools support at least the following:

       A   Default character set for the connection ("SET NAMES").

	   Enables character set settings in Perl and MySQL.  If the value is
	   "utf8", 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.  Other values set	binmode	on
	   STDOUT without the utf8 layer and run "SET NAMES" after connecting
	   to MySQL.

	   Unfortunately, there	is no way from within Perl itself to specify
	   the client library's	character set.	"SET NAMES" only affects the
	   server; if the client library's settings don't match, there could
	   be problems.	 You can use the defaults file to specify the client
	   library's character set, however.  See the description of the F
	   part	below.

       D   Default database to use when	connecting.  Tools may "USE" a
	   different databases while running.

       F   Defaults file for the MySQL client library (the C client library
	   used	by DBD::mysql, not Percona Toolkit itself).  All tools all
	   read	the "[client]" section within the defaults file.  If you omit
	   this, the standard defaults files will be read in the usual order.
	   "Standard" varies from system to system, because the	filenames to
	   read	are compiled into the client library.  On Debian systems, for
	   example, it's usually "/etc/mysql/my.cnf" then "~/.my.cnf".	If you
	   place the following in "~/.my.cnf", you won't have to specify your
	   MySQL username and password on the command line:

	     [client]
	     user=your_user_name
	     pass=secret

	   Omitting the	F part is usually the right thing to do.  As long as
	   you have configured your "~/.my.cnf"	correctly, that	will result in
	   tools connecting automatically without needing a username or
	   password.

	   You can also	specify	a default character set	in the defaults	file.
	   Unlike the "A" part described above,	this will actually instruct
	   the client library (DBD::mysql) to change the character set it uses
	   internally, which cannot be accomplished any	other way.

       h   MySQL hostname or IP	address	to connect to.

       L   Explicitly enable LOAD DATA LOCAL INFILE.

	   For some reason, some vendors compile libmysql without the
	   --enable-local-infile option, which disables	the statement.	This
	   can lead to weird situations, like the server allowing LOCAL
	   INFILE, but the client throwing exceptions if it's used.

	   However, as long as the server allows LOAD DATA, clients can	easily
	   re-enable it; see
	   <https://dev.mysql.com/doc/refman/5.0/en/load-data-local.html> and
	   <http://search.cpan.org/~capttofu/DBD-mysql/lib/DBD/mysql.pm>.
	   This	option does exactly that.

       p   MySQL password to use when connecting.

       P   Port	number to use for the connection.  Note	that the usual
	   special-case	behaviors apply: if you	specify	"localhost" as your
	   hostname on Unix systems, the connection actually uses a socket
	   file, not a TCP/IP connection, and thus ignores the port.

       S   MySQL socket	file to	use for	the connection (on Unix	systems).

       u   MySQL username to use when connecting, if not current system	user.

   BAREWORD
       Many of the tools will let you specify a	DSN as a single	word, without
       any "key=value" syntax.	This is	called a 'bareword'.  How this is
       handled is tool-specific, but it	is usually interpreted as the "h"
       part.  The tool's "--help" output will tell you the behavior for	that
       tool.

   PROPAGATION
       Many tools will let you propagate values	from one DSN to	the next, so
       you don't have to specify all the parts for each	DSN.  For example, if
       you want	to specify a username and password for each DSN, you can
       connect to three	hosts as follows:

	h=host1,u=fred,p=wilma host2 host3

       This is tool-specific.

ENVIRONMENT
       The environment variable	"PTDEBUG" enables verbose debugging output to
       STDERR.	To enable debugging and	capture	all output to a	file, run the
       tool like:

	  PTDEBUG=1 pt-table-checksum ... > FILE 2>&1

       Be careful: debugging output is voluminous and can generate several
       megabytes of output.

SYSTEM REQUIREMENTS
       Most tools require:

       o   Perl	v5.8 or	newer

       o   Bash	v3 or newer

       o   Core	Perl modules like Time::HiRes

       Tools that connect to MySQL require:

       o   Perl	modules	DBI and	DBD::mysql

       o   MySQL 5.0 or	newer

       Percona Toolkit officially supports and is tested on many popular Linux
       distributions and MySQL 5.0 through 5.6;	see http://goo.gl/srHm7	for
       the list	of supported platforms and versions.

IPv6 support
       In order	to support IPv6	addresses to connect to	MySQL, Perl DBD::MySQL
       driver v4.033_01	is required. Also, as stated in	RFC 3986
       <https://www.ietf.org/rfc/rfc3986.txt> section 3.2.2 brackes must be
       used to distinguish host	and port.  Examples:
       <https://metacpan.org/pod/DBD::mysql#port>

BUGS
       Please report bugs at <https://jira.percona.com>.  Include the
       following information in	your bug report:

       o   Complete command-line used to run the tool

       o   Tool	"--version"

       o   MySQL version of all	servers	involved

       o   Output from the tool	including STDERR

       o   Input files (log/dump/config	files, etc.)

       If possible, include debugging output by	running	the tool with
       "PTDEBUG"; see "ENVIRONMENT".

AUTHORS
       Baron Schwartz
	   Baron created Maatkit, from which Percona Toolkit was forked.  Many
	   of the tools	and modules were originally written by Baron.

       Daniel Nichter
	   Daniel has been the project's lead developer	since 2008 until 2016.

       Frank Cizmich
	   Frank was a full-time Percona Toolkit developer employed by Percona
	   until 2016.

       Carlos Salguero
	   Carlos has been the project's lead developer	since 2016. He is
	   hired by Percona.

       Others
	   Many	people have contributed	code over the years.  See each tool's
	   "AUTHORS" section for details.

COPYRIGHT, LICENSE, AND	WARRANTY
       Percona Toolkit is copyright 2011-2018 Percona LLC and/or its
       affiliates, et al.  See each program's documentation for	complete
       copyright notices.

       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.

VERSION
       Percona Toolkit v3.2.0 released 2020-04-23

perl v5.32.0			  2020-04-23		    PERCONA-TOOLKIT(1)

NAME | DESCRIPTION | TOOLS | SPECIAL OPTION TYPES | CONFIGURATION FILES | DSN (DATA SOURCE NAME) SPECIFICATIONS | ENVIRONMENT | SYSTEM REQUIREMENTS | IPv6 support | BUGS | AUTHORS | COPYRIGHT, LICENSE, AND WARRANTY | VERSION

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=percona-toolkit&sektion=1&manpath=FreeBSD+12.2-RELEASE+and+Ports>

home | help