mysql grants for_MySQL pt-show-grants用法介绍

我们先来看一下此工具的帮助:

[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=a            Ignore this comma-separated list of users

--only=a              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

部分参数选项介绍：

--ask-pass            Prompt for a password when connecting to MySQL     连接MySQL作为密码的提示

--charset=s       -A  Default character set            连接使用的字符集

--database=s      -D  The database to use for the connection   连接数据库使用到的DB

--flush               Add FLUSH PRIVILEGES after output    在输出后刷新权限

--[no]header          Print dump header (default yes)       打印dump头信息

--help                Show help and exit                    显示帮助

--host=s          -h  Connect to host                       连接主机信息

用法举例:

[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]#

从全日志中可以看到：

1、先查找所有用户和Host

2、然后逐个执行show grants

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2135994/，如需转载，请注明出处，否则将追究法律责任。