1 pt-show-grants [OPTIONS] [DSN]
1 //Regular parameters. 2 --drop //Add "DROP USER" before each user of output,which can be used to get a ddl of droping user. 3 --flush //Add "FLUSH PRIVILEGES" after output(version ahead 4.1.1 need). 4 --ignore //Speicify the ignore user. 5 --only //on the contrary of "--ignore" does,specify the only user you want. 6 --include-unused-roles //This options merely for MySQL 8.0 + version which support roles. 7 --seperate //List the grant and revoke statement respectively. 8 9 //Dump hearder relevant. 10 --no-header //Don't print head information of dump. 11 --no-timestamp //Don't add timestam to the head of dump.
1 [root@zlm1 06:17:09 ~] 2 #pt-show-grants 3 -- Grants dumped by pt-show-grants 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 07:59:32 5 -- Grants for 'bkuser'@'localhost' 6 CREATE USER IF NOT EXISTS 'bkuser'@'localhost'; 7 ALTER USER 'bkuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3DE5D9E4FBC1E464DA1B1172D6333CE89FDE5C61' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; 8 GRANT LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost'; 9 -- Grants for 'mysql.session'@'localhost' 10 CREATE USER IF NOT EXISTS 'mysql.session'@'localhost'; 11 ALTER USER 'mysql.session'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK; 12 GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost'; 13 GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost'; 14 GRANT SUPER ON *.* TO 'mysql.session'@'localhost'; 15 -- Grants for 'mysql.sys'@'localhost' 16 CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost'; 17 ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK; 18 GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost'; 19 GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost'; 20 GRANT USAGE ON *.* TO 'mysql.sys'@'localhost'; 21 -- Grants for 'repl'@'192.168.56.%' 22 CREATE USER IF NOT EXISTS 'repl'@'192.168.56.%'; 23 ALTER USER 'repl'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; 24 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%'; 25 -- Grants for 'root'@'localhost' 26 CREATE USER IF NOT EXISTS 'root'@'localhost'; 27 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; 28 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; 29 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
1 [root@zlm1 08:38:28 ~] 2 #pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root 3 -- Grants dumped by pt-show-grants 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:38:30 5 -- Grants for 'root'@'localhost' 6 CREATE USER IF NOT EXISTS 'root'@'localhost'; 7 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; 8 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; 9 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
1 [root@zlm1 08:38:30 ~] 2 #pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root --separate --revoke 3 -- Grants dumped by pt-show-grants 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:38:46 5 -- Revoke statements for 'root'@'localhost' 6 REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'localhost'; 7 REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost'; 8 REVOKE PROXY ON ''@'' FROM 'root'@'localhost'; 9 REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost'; 10 -- Grants for 'root'@'localhost' 11 CREATE USER IF NOT EXISTS 'root'@'localhost'; 12 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; 13 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; 14 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
1 [root@zlm1 08:38:46 ~] 2 # pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only=root --drop 3 -- Grants dumped by pt-show-grants 4 -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:39:03 5 DROP USER 'root'@'localhost'; 6 DELETE FROM `mysql`.`user` WHERE `User`='root' AND `Host`='localhost'; 7 -- Grants for 'root'@'localhost' 8 CREATE USER IF NOT EXISTS 'root'@'localhost'; 9 ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; 10 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; 11 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
1 [root@zlm1 08:40:57 ~] 2 #pt-show-grants -h192.168.56.100 -P3306 -urepl -prepl4slave --only repl --no-timestamp 3 -- Grants dumped by pt-show-grants 4 -- Dumped from server 192.168.56.100 via TCP/IP, MySQL 5.7.21-log 5 -- Grants for 'repl'@'192.168.56.%' 6 CREATE USER IF NOT EXISTS 'repl'@'192.168.56.%'; 7 ALTER USER 'repl'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; 8 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%';
1 [root@zlm1 08:41:46 ~] 2 #pt-show-grants -h192.168.56.100 -P3306 -urepl -prepl4slave --only repl --no-header 3 -- Grants for 'repl'@'192.168.56.%' //Only message of annotation this time. 4 CREATE USER IF NOT EXISTS 'repl'@'192.168.56.%'; 5 ALTER USER 'repl'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; 6 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%';
Distinguish the difference of user privileges between zlm1 & zlm2 and make it be same.
1 //Dump the user grans into a file on server zlm1 2 [root@zlm1 08:43:15 ~] 3 #pt-show-grants -h192.168.56.100 -P3306 -urepl -prepl4slave --only repl > repl_grants.sql 4 5 //Copy the file to zlm2. 6 [root@zlm1 08:43:18 ~] 7 #scp repl_grants.sql 192.168.56.101:~ 8 repl_grants.sql 100% 436 0.4KB/s 00:00 9 10 [root@zlm1 08:43:40 ~] 11 12 //Show user infomation on zlm2. 13 root@localhost:mysql.sock [(none)]>select user,host from mysql.user; 14 +---------------+--------------+ 15 | user | host | 16 +---------------+--------------+ 17 | repl | 192.168.56.% | 18 | bkuser | localhost | 19 | mysql.session | localhost | 20 | mysql.sys | localhost | 21 | root | localhost | 22 +---------------+--------------+ 23 5 rows in set (0.00 sec) 24 25 //Show user grants infomation. 26 root@localhost:mysql.sock [(none)]>show grants for repl@'192.168.56.%'; 27 +---------------------------------------------------------+ 28 | Grants for repl@192.168.56.% | 29 +---------------------------------------------------------+ 30 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.%' | 31 +---------------------------------------------------------+ 32 1 row in set (0.00 sec) 33 34 //Revoke the privileges from repl user to mimic difference. 35 root@localhost:mysql.sock [(none)]>revoke all on *.* from repl@'192.168.56.%'; 36 Query OK, 0 rows affected (0.00 sec) 37 38 root@localhost:mysql.sock [(none)]>show grants for repl@'192.168.56.%'; 39 +---------------------------------------------+ 40 | Grants for repl@192.168.56.% | 41 +---------------------------------------------+ 42 | GRANT USAGE ON *.* TO 'repl'@'192.168.56.%' | 43 +---------------------------------------------+ 44 1 row in set (0.00 sec) 45 46 //Check difference with zlm1. 47 [root@zlm2 08:47:56 ~] 48 #pt-show-grants -hlocalhost -P3306 -uroot -pPassw0rd --only repl | diff repl_grants.sql - 49 2c2 50 < -- Dumped from server 192.168.56.100 via TCP/IP, MySQL 5.7.21-log at 2018-06-29 08:43:18 51 --- 52 > -- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-log at 2018-06-29 08:48:00 53 6c6 54 < GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%'; //User privileges in dump file. 55 --- 56 > GRANT USAGE ON *.* TO 'repl'@'192.168.56.%'; //User privileges in local server. 57 58 [root@zlm2 08:48:00 ~] 59 #mysql -hlocalhost -S /var/lib/mysql/mysql.sock -uroot -pPassw0rd < repl_grants.sql 2>/dev/null //Import the user grants from dump file. 60 61 //Check the privileges of user repl again. 62 [root@zlm2 08:48:29 ~] 63 #mysql 64 Welcome to the MySQL monitor. Commands end with ; or \g. 65 Your MySQL connection id is 31 66 Server version: 5.7.21-log MySQL Community Server (GPL) 67 68 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 69 70 Oracle is a registered trademark of Oracle Corporation and/or its 71 affiliates. Other names may be trademarks of their respective 72 owners. 73 74 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 75 76 root@localhost:mysql.sock [(none)]>show grants for repl@'192.168.56.%'; 77 +------------------------------------------------------+ 78 | Grants for repl@192.168.56.% | 79 +------------------------------------------------------+ 80 | GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.56.%' | //The privileges are same with zlm1 now. 81 +------------------------------------------------------+ 82 1 row in set (0.00 sec)
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
【MySQL牛客】10.获取所有非manager的员工emp_no
网友评论