pt-show-grants

 

这个工具的主要作用就是能够规范的打印出授权的相关信息，方便对比不同MySQL数据库的权限是不是一样，通过这个工具我们可以轻松地将用户从一个服务器复制到另一个服务器，可以简单地从第一台服务器提取授权，并将输出管道直接导入另一台服务器，也可以做权限的版本控制：

基本上就有上面的功能，具体使用时候可以具体测试，现在先看个基本使用如下：

[root@mxqmongodb2 bin]# ./pt-show-grants --host=172.16.16.35 --port=3306 --user=root --password=123456
-- Grants dumped by pt-show-grants
-- Dumped from server 172.16.16.35 via TCP/IP, MySQL 5.7.14-log at 2017-06-26 14:59:53
-- Grants for 'dev_01334938'@'%'
CREATE USER IF NOT EXISTS 'dev_01334938'@'%';
ALTER USER 'dev_01334938'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT ON `gshop_db`.* TO 'dev_01334938'@'%';
GRANT USAGE ON *.* TO 'dev_01334938'@'%';
-- Grants for 'mysql.sys'@'localhost'
CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';
ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
-- Grants for 'repl'@'172.16.16.%'
CREATE USER IF NOT EXISTS 'repl'@'172.16.16.%';
ALTER USER 'repl'@'172.16.16.%' IDENTIFIED WITH 'mysql_native_password' AS '*D36660B5249B066D7AC5A1A14CECB71D36944CBC' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl'@'172.16.16.%';
-- Grants for 'replication'@'10.102.2.%'
CREATE USER IF NOT EXISTS 'replication'@'10.102.2.%';
ALTER USER 'replication'@'10.102.2.%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'replication'@'10.102.2.%';
-- Grants for 'replication'@'10.102.4.%'
CREATE USER IF NOT EXISTS 'replication'@'10.102.4.%';
ALTER USER 'replication'@'10.102.4.%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT ON `gshop_db`.* TO 'replication'@'10.102.4.%';
GRANT USAGE ON *.* TO 'replication'@'10.102.4.%';
-- Grants for 'root'@'%'
CREATE USER IF NOT EXISTS 'root'@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
-- Grants for 'root'@'localhost'
CREATE USER IF NOT EXISTS 'root'@'localhost';
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;