Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use pt-show-grants in MySQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Let's first take a look at the help of this tool:

[root@idb4 tmp] # pt-show-grants-- help

Pt-show-grants shows grants (user privileges) from a MySQL server. For more

Details, please use the-- help option, or try 'perldoc

/ usr/local/bin/pt-show-grants' for complete documentation.

Usage: pt-show-grants [OPTIONS] [DSN]

Options:

-- ask-pass Prompt for a password when connecting to MySQL

-- charset=s-A Default character set

-- config=A Read this comma-separated list of config files; if

Specified, this must be the first option on the command

Line

-- database=s-D The database to use for the connection

-- defaults-file=s-F Only read mysql options from the given file

-- drop Add DROP USER before each user in the output

-- flush Add FLUSH PRIVILEGES after output

[no] header Print dump header (default yes)

-- help Show help and exit

-- host=s-h Connect to host

-- ignore=an Ignore this comma-separated list of users

-- only=an Only show grants for this comma-separated list of users

-- password=s-p Password to use when connecting

-- pid=s Create the given PID file

-- port=i-P Port number to use for connection

-- revoke Add REVOKE statements for each GRANT statement

-- separate List each GRANT or REVOKE separately

-- set-vars=A Set the MySQL variables in this comma-separated list of

Variable=value pairs

-- socket=s-S Socket file to use for connection

[no] timestamp Add timestamp to the dump header (default yes)

-- user=s-u User for login if not current user

-- version Show version and exit

Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time

Rules:

This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.

DSN syntax is key=value [, key=value...] Allowable DSN keys:

KEY COPY MEANING

=

A yes Default character set

D yes Default database

F yes Only read default options from the given file

P yes Port number to use for connection

S yes Socket file to use for connection

H yes Connect to host

P yes Password to use when connecting

U yes User for login if not current user

If the DSN is a bareword, the word is treated as the 'h' key.

Options and values after processing arguments:

-- ask-pass FALSE

-- charset (No value)

-- config / etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-show-grants.conf,/root/.percona-toolkit.conf,/root/.pt-show-grants.conf

-- database (No value)

-- defaults-file (No value)

-- drop FALSE

-- flush FALSE

-- header TRUE

-- help TRUE

-- host (No value)

-- ignore (No value)

-- only (No value)

-- password (No value)

-- pid (No value)

-- port (No value)

-- revoke FALSE

-- separate FALSE

-- set-vars

-- socket (No value)

-- timestamp TRUE

-- user (No value)

-- version FALSE

Some parameter options are described as follows:

-- prompt for ask-pass Prompt for a password when connecting to MySQL connecting to MySQL as password

-- character set used by charset=s-A Default character set connections

-- database=s-the DB used by D The database to use for the connection to connect to the database

-- flush Add FLUSH PRIVILEGES after output refreshes permissions after output

-- [no] header Print dump header (default yes) print dump header information

-- help Show help and exit displays help

-- host=s-h Connect to host connection host information

Examples of usage:

[root@idb4 tmp] # pt-show-grants-u mdba-p 123456-S / tmp/mysql.sock

-- Grants dumped by pt-show-grants

-- Dumped from server Localhost via UNIX socket, MySQL 5.6.27-log at 2017-03-24 11:28:44

-- Grants for'@ 'idb4'

GRANT USAGE ON *. * TO'@ 'idb4'

-- Grants for'@ 'localhost'

GRANT USAGE ON *. * TO'@ 'localhost'

-- Grants for 'chaxun'@'%'

GRANT SELECT ON *. * TO 'chaxun'@'%' IDENTIFIED BY PASSWORD' * F58642CAC603E6D0F3667EB641534763E2FB19F9'

GRANT INSERT, SELECT, UPDATE ON `accesslog`.`accesslog` TO 'chaxun'@'%'

-- Grants for 'dsj'@'%'

GRANT SELECT ON *. * TO 'dsj'@'%' IDENTIFIED BY PASSWORD' * 004203D413B4B6A751113FEB906AC120AA382064'

GRANT INSERT, UPDATE ON `ixinnuo_ sjcj`.`data _ interface_gs_etr_ info`TO 'dsj'@'%'

-- Grants for 'ixinnuo_zx'@'%'

GRANT CREATE, CREATE ROUTINE, CREATE TABLESPACE, INSERT, SELECT, UPDATE ON *. * TO 'ixinnuo_zx'@'%' IDENTIFIED BY PASSWORD' * AF709110542C4C827FFFB9E77321B0D89259A662'

-- Grants for 'liqianying'@'%'

GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON *. * TO 'liqianying'@'%' IDENTIFIED BY PASSWORD' * 81822E6C8292D1A0D1CE24A3F55E5491EE592CB8'

GRANT ALL PRIVILEGES ON `ixinnuo\ _ sjcj-\ _ T`.* TO 'liqianying'@'%' WITH GRANT OPTION

GRANT CREATE, DELETE, INSERT, SELECT, UPDATE ON `ixinnuo\ _ Sjcj`. * TO 'liqianying'@'%'

GRANT INSERT, SELECT, UPDATE ON `accesslog`.`accesslog` TO 'liqianying'@'%'

-- Grants for 'mdba'@'%'

GRANT ALL PRIVILEGES ON *. * TO 'mdba'@'%' IDENTIFIED BY PASSWORD' * E5B360F1D4E45FEFDB70DFE5E2ABD47990A424D6 'WITH GRANT OPTION

-- Grants for 'mdba'@'localhost'

GRANT ALL PRIVILEGES ON *. * TO 'mdba'@'localhost' IDENTIFIED BY PASSWORD' * E97EDDC98587C3F06D9BA8D6BA8D6A17AFD471C4'

-- Grants for 'monitor'@'172.16.16.27'

GRANT PROCESS, SELECT, SUPER ON *. * TO 'monitor'@'172.16.16.27' IDENTIFIED BY PASSWORD' * 1975D095AC033CAF4E1BF94F7202A9BBFEEB66F1'

-- Grants for 'root'@'%'

GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY PASSWORD' * 432CBD2158D692A593E5C4C12188A6AF80589D91' WITH GRANT OPTION

GRANT INSERT, UPDATE ON `accesslog`. * TO 'root'@'%'

-- Grants for 'root'@'127.0.0.1'

GRANT ALL PRIVILEGES ON *. * TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD' * 432CBD2158D692A593E5C4C12188A6AF80589D91' WITH GRANT OPTION

-- Grants for 'root'@'::1'

GRANT ALL PRIVILEGES ON *. * TO 'root'@'::1' IDENTIFIED BY PASSWORD' * 432CBD2158D692A593E5C4C12188A6AF80589D91' WITH GRANT OPTION

-- Grants for 'root'@'idb4'

GRANT ALL PRIVILEGES ON *. * TO 'root'@'idb4' IDENTIFIED BY PASSWORD' * 432CBD2158D692A593E5C4C12188A6AF80589D91' WITH GRANT OPTION

GRANT PROXY ON'@''TO 'root'@'idb4' WITH GRANT OPTION

-- Grants for 'spxqn'@'%'

GRANT USAGE ON *. * TO 'spxqn'@'%' IDENTIFIED BY PASSWORD' * C2F4C85D0B14C67F3A3B020502A15BE797F00317'

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `ixinnuo\ _ sjcj-\ _ T`.* TO 'spxqn'@'%' WITH GRANT OPTION

-- Grants for 'user1'@'%'

GRANT USAGE ON *. * TO 'user1'@'%' IDENTIFIED BY PASSWORD' * DC58115FACCE299160B5C525C7EE22BE70028A8E'

GRANT ALL PRIVILEGES ON `std_ data. * TO 'user1'@'%'

[root@idb4 tmp] #

You can see from the full log:

1. Find all users and Host first

2. Then execute show grants one by one

Thank you for reading this article carefully. I hope the article "how to use pt-show-grants in MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.