14
pt-show-grants - Canonicalize and print MySQL grants so you can effectively
15
replicate, compare and version-control them.
23
Usage: pt-show-grants [OPTION...] [DSN]
25
pt-show-grants shows grants (user privileges) from a MySQL server.
34
pt-show-grants --separate --revoke | diff othergrants.sql -
43
The following section is included to inform users about the potential risks,
44
whether known or unknown, of using this tool. The two main categories of risks
45
are those created by the nature of the tool (e.g. read-only tools vs. read-write
46
tools) and those created by bugs.
48
pt-show-grants is read-only by default, and very low-risk. If you specify
49
"--flush", it will execute \ ``FLUSH PRIVILEGES``\ .
51
At the time of this release, we know of no bugs that could cause serious harm to
54
The authoritative source for updated information is always the online issue
55
tracking system. Issues that affect this tool will be marked as such. You can
56
see a list of such issues at the following URL:
57
`http://www.percona.com/bugs/pt-show-grants <http://www.percona.com/bugs/pt-show-grants>`_.
59
See also "BUGS" for more information on filing bugs and getting help.
67
pt-show-grants extracts, orders, and then prints grants for MySQL user
70
Why would you want this? There are several reasons.
72
The first is to easily replicate users from one server to another; you can
73
simply extract the grants from the first server and pipe the output directly
76
The second use is to place your grants into version control. If you do a daily
77
automated grant dump into version control, you'll get lots of spurious
78
changesets for grants that don't change, because MySQL prints the actual grants
79
out in a seemingly random order. For instance, one day it'll say
84
GRANT DELETE, INSERT, UPDATE ON `test`.* TO 'foo'@'%';
87
And then another day it'll say
92
GRANT INSERT, DELETE, UPDATE ON `test`.* TO 'foo'@'%';
95
The grants haven't changed, but the order has. This script sorts the grants
96
within the line, between 'GRANT' and 'ON'. If there are multiple rows from SHOW
97
GRANTS, it sorts the rows too, except that it always prints the row with the
98
user's password first, if it exists. This removes three kinds of inconsistency
99
you'll get from running SHOW GRANTS, and avoids spurious changesets in version
102
Third, if you want to diff grants across servers, it will be hard without
103
"canonicalizing" them, which pt-show-grants does. The output is fully
106
With the "--revoke", "--separate" and other options, pt-show-grants
107
also makes it easy to revoke specific privileges from users. This is tedious
116
This tool accepts additional command-line arguments. Refer to the
117
"SYNOPSIS" and usage information for details.
122
Prompt for a password when connecting to MySQL.
128
short form: -A; type: string
130
Default character set. If the value is utf8, sets Perl's binmode on
131
STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and
132
runs SET NAMES UTF8 after connecting to MySQL. Any other value sets
133
binmode on STDOUT without the utf8 layer, and runs SET NAMES after
142
Read this comma-separated list of config files; if specified, this must be the
143
first option on the command line.
149
short form: -D; type: string
151
The database to use for the connection.
157
short form: -F; type: string
159
Only read mysql options from the given file. You must give an absolute
166
Add DROP USER before each user in the output.
172
Add FLUSH PRIVILEGES after output.
174
You might need this on pre-4.1.1 servers if you want to drop a user completely.
184
The header precedes the dumped grants. It looks like:
189
-- Grants dumped by pt-show-grants 1.0.19
190
-- Dumped from server Localhost via UNIX socket, MySQL 5.0.82-log at 2009-10-26 10:01:04
193
See also "--[no]timestamp".
205
short form: -h; type: string
215
Ignore this comma-separated list of users.
223
Only show grants for this comma-separated list of users.
229
short form: -p; type: string
231
Password to use when connecting.
239
Create the given PID file. The file contains the process ID of the script.
240
The PID file is removed when the script exits. Before starting, the script
241
checks if the PID file already exists. If it does not, then the script creates
242
and writes its own PID to it. If it does, then the script checks the following:
243
if the file contains a PID and a process is running with that PID, then
244
the script dies; or, if there is no process running with that PID, then the
245
script overwrites the file with its own PID and starts; else, if the file
246
contains no PID, then the script dies.
252
short form: -P; type: int
254
Port number to use for connection.
260
Add REVOKE statements for each GRANT statement.
266
List each GRANT or REVOKE separately.
268
The default output from MySQL's SHOW GRANTS command lists many privileges on a
269
single line. With "--flush", places a FLUSH PRIVILEGES after each user,
270
instead of once at the end of all the output.
276
type: string; default: wait_timeout=10000
278
Set these MySQL variables. Immediately after connecting to MySQL, this
279
string will be appended to SET and executed.
285
short form: -S; type: string
287
Socket file to use for connection.
295
Add timestamp to the dump header.
297
See also "--[no]header".
303
short form: -u; type: string
305
User for login if not current user.
311
Show version and exit.
321
These DSN options are used to create a DSN. Each option is given like
322
\ ``option=value``\ . The options are case-sensitive, so P and p are not the
323
same option. There cannot be whitespace before or after the \ ``=``\ and
324
if the value contains whitespace it must be quoted. DSN options are
325
comma-separated. See the percona-toolkit manpage for full details.
330
dsn: charset; copy: yes
332
Default character set.
338
dsn: database; copy: yes
346
dsn: mysql_read_default_file; copy: yes
348
Only read default options from the given file
362
dsn: password; copy: yes
364
Password to use when connecting.
372
Port number to use for connection.
378
dsn: mysql_socket; copy: yes
380
Socket file to use for connection.
388
User for login if not current user.
398
Visit `http://www.percona.com/software/ <http://www.percona.com/software/>`_ to download the latest release of
399
Percona Toolkit. Or, to get the latest release from the command line:
404
wget percona.com/latest/percona-toolkit/PKG
407
Replace \ ``PKG``\ with \ ``tar``\ , \ ``rpm``\ , or \ ``deb``\ to download the package in that
408
format. You can also get individual tools from the latest release:
413
wget percona.com/latest/percona-toolkit/TOOL
416
Replace \ ``TOOL``\ with the name of any tool.
424
The environment variable \ ``PTDEBUG``\ enables verbose debugging output to STDERR.
425
To enable debugging and capture all output to a file, run the tool like:
430
PTDEBUG=1 pt-show-grants ... > FILE 2>&1
433
Be careful: debugging output is voluminous and can generate several megabytes
442
You need Perl, DBI, DBD::mysql, and some core packages that ought to be
443
installed in any reasonably new version of Perl.
451
For a list of known bugs, see `http://www.percona.com/bugs/pt-show-grants <http://www.percona.com/bugs/pt-show-grants>`_.
453
Please report bugs at `https://bugs.launchpad.net/percona-toolkit <https://bugs.launchpad.net/percona-toolkit>`_.
454
Include the following information in your bug report:
457
\* Complete command-line used to run the tool
465
\* MySQL version of all servers involved
469
\* Output from the tool including STDERR
473
\* Input files (log/dump/config files, etc.)
477
If possible, include debugging output by running the tool with \ ``PTDEBUG``\ ;
489
*********************
490
ABOUT PERCONA TOOLKIT
491
*********************
494
This tool is part of Percona Toolkit, a collection of advanced command-line
495
tools developed by Percona for MySQL support and consulting. Percona Toolkit
496
was forked from two projects in June, 2011: Maatkit and Aspersa. Those
497
projects were created by Baron Schwartz and developed primarily by him and
498
Daniel Nichter, both of whom are employed by Percona. Visit
499
`http://www.percona.com/software/ <http://www.percona.com/software/>`_ for more software developed by Percona.
502
********************************
503
COPYRIGHT, LICENSE, AND WARRANTY
504
********************************
507
This program is copyright 2007-2011 Baron Schwartz, 2011 Percona Inc.
508
Feedback and improvements are welcome.
510
THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
511
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
512
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
514
This program is free software; you can redistribute it and/or modify it under
515
the terms of the GNU General Public License as published by the Free Software
516
Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
517
systems, you can issue \`man perlgpl' or \`man perlartistic' to read these
520
You should have received a copy of the GNU General Public License along with
521
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
522
Place, Suite 330, Boston, MA 02111-1307 USA.
530
Percona Toolkit v1.0.0 released 2011-08-01
added
removed
docs/user/pt-show-grants.rst
