Percona-Tookit工具包之pt-mysql-summary

  • pt-mysql-summary is a report tool for
    collecting information of MySQL servers.
  • It’s easy enough to use by merely
    specify several connection options.
  • It can tremendously increase the
    efficiency of summaring a large amount of MySQL
    servers.

 

Procedure

**Specify the only user “repl” with no
timestamp.**

  1 [root@zlm1 08:57:10 ~]
  2 #pt-mysql-summary --user=root --password=Passw0rd --host=localhost
  3 mysql: [Warning] Using a password on the command line interface can be insecure.
  4 # Percona Toolkit MySQL Summary Report #######################
  5               System time | 2018-08-12 06:57:37 UTC (local TZ: CEST +0200)
  6 # Instances ##################################################
  7   Port  Data Directory             Nice OOM Socket
  8   ===== ========================== ==== === ======
  9 # MySQL Executable ###########################################
 10 # Slave Hosts ################################################
 11 No slaves found //The slave server is not started up yet.
 12 # Report On Port 3306 ######################################## //Summary of overall of MySQL server.
 13                      User | root@localhost
 14                      Time | 2018-08-12 08:57:37 (CEST)
 15                  Hostname | zlm1
 16                   Version | 5.7.21-log MySQL Community Server (GPL)
 17                  Built On | linux-glibc2.12 x86_64
 18                   Started | 2018-08-12 08:57 (up 0+00:00:37)
 19                 Databases | 7
 20                   Datadir | /data/mysql/mysql3306/data/
 21                 Processes | 2 connected, 1 running
 22               Replication | Is not a slave, has 0 slaves connected
 23                   Pidfile | mysql.pid (does not exist)
 24 # Processlist ################################################ //This is all the contents in processlist.
 25 
 26   Command                        COUNT(*) Working SUM(Time) MAX(Time)
 27   ------------------------------ -------- ------- --------- ---------
 28   Query                                 1       1         0         0
 29   Sleep                                 1       0         1         1
 30 
 31   User                           COUNT(*) Working SUM(Time) MAX(Time)
 32   ------------------------------ -------- ------- --------- ---------
 33   monitor                               1       0         0         0
 34   root                                  1       1         0         0
 35 
 36   Host                           COUNT(*) Working SUM(Time) MAX(Time)
 37   ------------------------------ -------- ------- --------- ---------
 38   localhost                             1       1         0         0
 39   zlm1                                  1       0         0         0
 40 
 41   db                             COUNT(*) Working SUM(Time) MAX(Time)
 42   ------------------------------ -------- ------- --------- ---------
 43   NULL                                  2       1         0         0
 44 
 45   State                          COUNT(*) Working SUM(Time) MAX(Time)
 46   ------------------------------ -------- ------- --------- ---------
 47                                         1       0         0         0
 48   starting                              1       1         0         0
 49 
 50 # Status Counters (Wait 10 Seconds) ########################## //This paragragh shows the global status of MySQL and with 10 secs' differential values.
 51 Variable                                Per day  Per second     10 secs
 52 Bytes_received                          4500000          50         600
 53 Bytes_sent                            100000000        1250        3000
 54 Com_admin_commands                         9000                        
 55 Com_select                                60000                       3
 56 Com_set_option                             2250                        
 57 Com_show_slave_status                     15000                        
 58 Com_show_status                            4500                        
 59 Com_show_variables                         2250                        
 60 Connections                               15000                       1
 61 Created_tmp_files                         12500                        
 62 Created_tmp_tables                         7000                       6
 63 Flush_commands                             2250                        
 64 Handler_commit                            22500                        
 65 Handler_external_lock                    500000           6            
 66 Handler_read_first                        30000                        
 67 Handler_read_key                          25000                        
 68 Handler_read_next                          4500                        
 69 Handler_read_rnd_next                   4000000          50          80
 70 Handler_write                           2000000          25          35
 71 Innodb_buffer_pool_bytes_data       20000000000      225000        3500
 72 Innodb_buffer_pool_pages_flushed          80000                        
 73 Innodb_buffer_pool_read_requests        4000000          45          25
 74 Innodb_buffer_pool_reads                1250000          15            
 75 Innodb_buffer_pool_write_requests        800000           8          25
 76 Innodb_data_fsyncs                        17500                        
 77 Innodb_data_read                    17500000000      225000            
 78 Innodb_data_reads                       1250000          15            
 79 Innodb_data_writes                       125000           1            
 80 Innodb_data_written                  1500000000       17500        7000
 81 Innodb_dblwr_pages_written                 4500                        
 82 Innodb_dblwr_writes                        2250                        
 83 Innodb_log_writes                          4500                        
 84 Innodb_os_log_fsyncs                       9000                        
 85 Innodb_os_log_written                   2500000          30            
 86 Innodb_pages_created                      80000                        
 87 Innodb_pages_read                       1000000          15            
 88 Innodb_pages_written                      80000                        
 89 Innodb_rows_read                          25000                       4
 90 Innodb_num_open_files                     80000                        
 91 Innodb_available_undo_logs               300000           3            
 92 Key_read_requests                         15000                        
 93 Key_reads                                  7000                        
 94 Open_table_definitions                   250000           2            
 95 Opened_files                             350000           3           1
 96 Opened_table_definitions                 250000           2            
 97 Opened_tables                            250000           3            
 98 Queries                                  100000           1           6
 99 Questions                                 90000           1           6
100 Select_scan                               15000                        
101 Table_locks_immediate                    225000           2            
102 Table_open_cache_hits                     12500                        
103 Table_open_cache_misses                  250000           3            
104 Threads_created                            4500                        
105 Uptime                                    90000           1           1
106 # Table cache ################################################
107                      Size | 2048
108                     Usage | 5%
109 # Key Percona Server features ################################ //Because mine is not percona version,so most of the features are not supported.
110       Table & Index Stats | Not Supported
111      Multiple I/O Threads | Enabled
112      Corruption Resilient | Not Supported
113       Durable Replication | Not Supported
114      Import InnoDB Tables | Not Supported
115      Fast Server Restarts | Not Supported
116          Enhanced Logging | Not Supported
117      Replica Perf Logging | Enabled
118       Response Time Hist. | Not Supported
119           Smooth Flushing | Not Supported
120       HandlerSocket NoSQL | Not Supported
121            Fast Hash UDFs | Unknown
122 # Percona XtraDB Cluster ##################################### //If we are using PXC structure,this paragraph will show the detail of it.
123 # Plugins ####################################################
124        InnoDB compression | ACTIVE
125 # Query cache ################################################
126          query_cache_type | OFF
127                      Size | 0.0
128                     Usage | 0%
129          HitToInsertRatio | 0%
130 # Semisynchronous Replication ################################ //Information of semi-replication if there's a master-slave replication.
131                    Master | Disabled
132                     Slave | Disabled
133 # Schema #####################################################
134 Specify --databases or --all-databases to dump and summarize schemas
135 # Noteworthy Technologies ####################################
136                       SSL | No
137      Explicit LOCK TABLES | No
138            Delayed Insert | No
139           XA Transactions | No
140               NDB Cluster | No
141       Prepared Statements | No
142  Prepared statement count | 0
143 # InnoDB #####################################################
144                   Version | 5.7.21
145          Buffer Pool Size | 100.0M
146          Buffer Pool Fill | 8%
147         Buffer Pool Dirty | 0%
148            File Per Table | ON
149                 Page Size | 16k
150             Log File Size | 3 * 100.0M = 300.0M
151           Log Buffer Size | 8M
152              Flush Method | O_DIRECT
153       Flush Log At Commit | 2
154                XA Support | ON
155                 Checksums | ON
156               Doublewrite | ON
157           R/W I/O Threads | 4 4
158              I/O Capacity | 2000
159        Thread Concurrency | 0
160       Concurrency Tickets | 5000
161        Commit Concurrency | 0
162       Txn Isolation Level | READ-COMMITTED
163         Adaptive Flushing | ON
164       Adaptive Checkpoint | 
165            Checkpoint Age | 9
166              InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
167        Oldest Transaction | 0 Seconds
168          History List Len | 0
169                Read Views | 0
170          Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
171         Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
172        Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
173       Pending I/O Flushes | 0 buf pool, 0 log
174        Transaction States | 1xnot started
175 # MyISAM #####################################################
176                 Key Cache | 8.0M
177                  Pct Used | 20%
178                 Unflushed | 0%
179 # Security ###################################################
180                     Users | 5 users, 0 anon, 0 w/o pw, 0 old pw
181             Old Passwords | 0
182 # Encryption #################################################
183 mysql: [Warning] Using a password on the command line interface can be insecure.
184 No keyring plugins found
185 # Binary Logging #############################################
186                   Binlogs | 6
187                Zero-Sized | 0
188                Total Size | 190.0k
189             binlog_format | ROW
190          expire_logs_days | 10
191               sync_binlog | 0
192                 server_id | 1003306
193              binlog_do_db | 
194          binlog_ignore_db | 
195 # Noteworthy Variables #######################################
196      Auto-Inc Incr/Offset | 1/1
197    default_storage_engine | InnoDB
198                flush_time | 0
199              init_connect | 
200                 init_file | 
201                  sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
202          join_buffer_size | 128k
203          sort_buffer_size | 128k
204          read_buffer_size | 2M
205      read_rnd_buffer_size | 16M
206        bulk_insert_buffer | 0.00
207       max_heap_table_size | 96M
208            tmp_table_size | 96M
209        max_allowed_packet | 4M
210              thread_stack | 192k
211                       log | 
212                 log_error | ./error.log
213              log_warnings | 2
214          log_slow_queries | 
215 log_queries_not_using_indexes | OFF
216         log_slave_updates | ON
217 # Configuration File #########################################
218               Config File | /etc/my.cnf
219 
220 [mysqld]
221 datadir                             = /var/lib/mysql
222 socket                              = /var/lib/mysql/mysql.sock
223 symbolic-links                      = 0
224 
225 [mysqld_safe]
226 log-error                           = /var/log/mariadb/mariadb.log
227 pid-file                            = /var/run/mariadb/mariadb.pid
228 
229 [mysql]
230 prompt                              = "\\u@\\h:\\p [\\d]\\>"
231 user                                = zlm
232 password                            = zlmzlm
233 host                                = 192.168.56.100
234 
235 [client]
236 user                                = zlm
237 password                            = zlmzlm
238 socket                              = /tmp/mysql3306.sock
239 host                                = 192.168.56.100
240 # Memory management library ##################################
241 jemalloc is not enabled in mysql config for process with id 4159
242 # The End ####################################################
243 
244 [root@zlm1 08:57:48 ~]
245 #

Procedure

 

 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;

 

Examples

Common
Parameters

 

 

Execute without  any parameters.(it
will read connection options in defatul my.cnf )

Preface

    User privileges regulation is pretty
important in DBAs routine job.As we all know,it’s the less the better to
reduce risks when someone who has no privileges manipulates data by
malevolence.On the other hand,when we implement HA,replication or the
other tools,it’s significant and efficient to grant proper privileges to
specific users.

Summary

**Parameters**

 

Distinguish
the difference of user privileges between zlm1 & zlm2 and make it be
same.

 

 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 //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.

 

**Specify the only user
“root”.**

Execute it
again while slave is working normally with option “–database” and
“–save-samples”

**Specify the only user “root” with
revoke statement separated with grant statement.**

Execute pt-mysql-summary with just
connect options(single master).

**Specify the only user “root” with drop
statement.**

Usage

Introduce

 

Preface

 

1 pt-show-grants [OPTIONS] [DSN]
1 --all-databases //Specify the scope is all databases for summarizing.
2 --databases //Specify the only database for summarizing.
3 --default-files //Specify the configuration file to use.
4 --list-encrypted-tables //Specify to contain encrypted tables in all databases.
5 --read-samples //Create a report from this file you sepcified.
6 --save-samples //Save to the direcotry you want to store the summary.
7 --sleep //Seconds to sleep for counter.

 

 

  • pt-show-grants
    helps us dba to manager user privileges better than MySQL command
    line “show grant for … ;” does.
  • pt-show-grants
    is convenient to use even with any options specified.
  • pt-show-grants
    can be used to check the difference of user privileges between the
    servers.
  • pt-show-grants
    is not a intrusive tool like pt-pmp,you can run it at any time you
    need.

Introduce

 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)

*    pt-mysql-summary can help us to print
information of our MySQL servers nicely.It’s not a tuning or diagnostic
tool but a tool just provides us a report with neat format.

*

 

    Sometimes we need to collect
information of  MySQL server as a report when we first time take over
the system by someone else.Espcially there’re lots of  servers for you
to check them one by one with manual command.We need a simple tool to
increase the efficiency when doing so.

Summary

1 pt-mysql-summary [OPTIONS]

**Specify the only user “repl” with no
header.**

 

 

  1 [root@zlm1 09:15:24 ~]
  2 #pt-mysql-summary --user=root --password=Passw0rd --host=localhost --databases zlm --save-samples=/root/zlm.rpl
  3 mysql: [Warning] Using a password on the command line interface can be insecure.
  4 # Percona Toolkit MySQL Summary Report #######################
  5               System time | 2018-08-12 07:15:49 UTC (local TZ: CEST +0200)
  6 # Instances ##################################################
  7   Port  Data Directory             Nice OOM Socket
  8   ===== ========================== ==== === ======
  9 # MySQL Executable ###########################################
 10 # Slave Hosts ################################################ //Now,it shows the slave's information.
 11 *************************** 1. row ***************************
 12  Server_id: 1013306
 13       Host: 
 14       Port: 3306
 15  Master_id: 1003306
 16 Slave_UUID: 2b199d1f-94df-11e8-ae46-080027de0e0e
 17 # Report On Port 3306 ########################################
 18                      User | root@localhost
 19                      Time | 2018-08-12 09:15:49 (CEST)
 20                  Hostname | zlm1
 21                   Version | 5.7.21-log MySQL Community Server (GPL)
 22                  Built On | linux-glibc2.12 x86_64
 23                   Started | 2018-08-12 08:57 (up 0+00:18:49)
 24                 Databases | 7
 25                   Datadir | /data/mysql/mysql3306/data/
 26                 Processes | 4 connected, 2 running
 27               Replication | Is not a slave, has 1 slaves connected
 28                   Pidfile | mysql.pid (does not exist)
 29 # Processlist ################################################
 30 
 31   Command                        COUNT(*) Working SUM(Time) MAX(Time)
 32   ------------------------------ -------- ------- --------- ---------
 33   Binlog Dump GTID                      1       1       800       800
 34   Query                                 1       1         0         0
 35   Sleep                                 2       0         7         5
 36 
 37   User                           COUNT(*) Working SUM(Time) MAX(Time)
 38   ------------------------------ -------- ------- --------- ---------
 39   monitor                               2       0         0         0
 40   repl                                  1       1       800       800
 41   root                                  1       1         0         0
 42 
 43   Host                           COUNT(*) Working SUM(Time) MAX(Time)
 44   ------------------------------ -------- ------- --------- ---------
 45   localhost                             1       1         0         0
 46   zlm1                                  2       0         0         0
 47   zlm2                                  1       1       800       800
 48 
 49   db                             COUNT(*) Working SUM(Time) MAX(Time)
 50   ------------------------------ -------- ------- --------- ---------
 51   NULL                                  4       2       800       800
 52 
 53   State                          COUNT(*) Working SUM(Time) MAX(Time)
 54   ------------------------------ -------- ------- --------- ---------
 55                                         2       0         0         0
 56   Master has sent all binlog to         1       1       800       800 //This is the dump thread of master working in replicatoin.
 57   starting                              1       1         0         0
 58 
 59 # Status Counters (Wait 10 Seconds) ##########################
 60 Variable                                Per day  Per second     10 secs
 61 Bytes_received                          4000000          45         700
 62 Bytes_sent                             60000000         700        4000
 63 Com_admin_commands                         9000                        
 64 Com_select                                60000                       3
 65 Com_set_option                             1000                        
 66 Com_show_binlogs                            150                        
 67 Com_show_databases                          150                        
 68 Com_show_engine_status                      150                        
 69 Com_show_master_status                      150                        
 70 Com_show_plugins                            150                        
 71 Com_show_processlist                        150                        
 72 Com_show_slave_hosts                        150                        
 73 Com_show_slave_status                     17500                        
 74 Com_show_status                             600                        
 75 Com_show_storage_engines                    150                        
 76 Com_show_variables                          225                        
 77 Connections                                6000                       1
 78 Created_tmp_disk_tables                    2000                       1
 79 Created_tmp_files                           400                        
 80 Created_tmp_tables                        10000                       8
 81 Flush_commands                               80                        
 82 Handler_commit                              800                        
 83 Handler_external_lock                     20000                       1
 84 Handler_read_first                         1500                        
 85 Handler_read_key                           1250                        
 86 Handler_read_next                           150                       9
 87 Handler_read_rnd_next                    600000           7         300
 88 Handler_write                            300000           3         150
 89 Innodb_buffer_pool_bytes_data         600000000        8000       22500
 90 Innodb_buffer_pool_pages_flushed           4000                       1
 91 Innodb_buffer_pool_read_requests         175000           1          70
 92 Innodb_buffer_pool_reads                  35000                        
 93 Innodb_buffer_pool_write_requests         60000                      60
 94 Innodb_data_fsyncs                          500                        
 95 Innodb_data_read                      600000000        7000            
 96 Innodb_data_reads                         40000                        
 97 Innodb_data_writes                         5000                       1
 98 Innodb_data_written                    70000000         800       30000
 99 Innodb_dblwr_pages_written                  150                        
100 Innodb_dblwr_writes                          80                        
101 Innodb_log_writes                           150                        
102 Innodb_os_log_fsyncs                        300                        
103 Innodb_os_log_written                     80000                        
104 Innodb_pages_created                       3000                       1
105 Innodb_pages_read                         35000                        
106 Innodb_pages_written                       4000                       1
107 Innodb_rows_inserted                      15000                      10
108 Innodb_rows_read                          15000                       9
109 Innodb_num_open_files                      2500                        
110 Innodb_available_undo_logs                10000                        
111 Key_read_requests                           450                        
112 Key_reads                                   225                        
113 Open_table_definitions                     8000                        
114 Opened_files                              12500                       1
115 Opened_table_definitions                   8000                        
116 Opened_tables                              9000                        
117 Queries                                  100000           1           9
118 Questions                                 90000           1           9
119 Select_scan                                2500                       1
120 Table_locks_immediate                      8000                        
121 Table_open_cache_hits                       500                        
122 Table_open_cache_misses                    9000                        
123 Threads_created                             300                        
124 Uptime                                    90000           1           1
125 # Table cache ################################################
126                      Size | 2048
127                     Usage | 5%
128 # Key Percona Server features ################################
129       Table & Index Stats | Not Supported
130      Multiple I/O Threads | Enabled
131      Corruption Resilient | Not Supported
132       Durable Replication | Not Supported
133      Import InnoDB Tables | Not Supported
134      Fast Server Restarts | Not Supported
135          Enhanced Logging | Not Supported
136      Replica Perf Logging | Enabled
137       Response Time Hist. | Not Supported
138           Smooth Flushing | Not Supported
139       HandlerSocket NoSQL | Not Supported
140            Fast Hash UDFs | Unknown
141 # Percona XtraDB Cluster #####################################
142 # Plugins ####################################################
143        InnoDB compression | ACTIVE
144 # Query cache ################################################
145          query_cache_type | OFF
146                      Size | 0.0
147                     Usage | 0%
148          HitToInsertRatio | 0%
149 # Semisynchronous Replication ################################ //The semi-sync replication is still not used.
150                    Master | Disabled
151                     Slave | Disabled
152 # Schema ##################################################### //Because of the option of "--databases",it shows the detail of it.
153 
154   Database Tables Views SPs Trigs Funcs   FKs Partn
155   zlm           1                                  
156 
157   Database InnoDB
158   zlm           1
159 
160   Database
161   zlm     
162 
163              i   c
164              n   h
165              t   a
166                  r
167   Database === ===
168   zlm        1   1
169 
170 # Noteworthy Technologies ####################################
171        Full Text Indexing | No
172          Geospatial Types | No
173              Foreign Keys | No
174              Partitioning | No
175        InnoDB Compression | No
176                       SSL | No
177      Explicit LOCK TABLES | No
178            Delayed Insert | No
179           XA Transactions | No
180               NDB Cluster | No
181       Prepared Statements | No
182  Prepared statement count | 0
183 # InnoDB #####################################################
184                   Version | 5.7.21
185          Buffer Pool Size | 100.0M
186          Buffer Pool Fill | 8%
187         Buffer Pool Dirty | 0%
188            File Per Table | ON
189                 Page Size | 16k
190             Log File Size | 3 * 100.0M = 300.0M
191           Log Buffer Size | 8M
192              Flush Method | O_DIRECT
193       Flush Log At Commit | 2
194                XA Support | ON
195                 Checksums | ON
196               Doublewrite | ON
197           R/W I/O Threads | 4 4
198              I/O Capacity | 2000
199        Thread Concurrency | 0
200       Concurrency Tickets | 5000
201        Commit Concurrency | 0
202       Txn Isolation Level | READ-COMMITTED
203         Adaptive Flushing | ON
204       Adaptive Checkpoint | 
205            Checkpoint Age | 9
206              InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
207        Oldest Transaction | 0 Seconds
208          History List Len | 0
209                Read Views | 0
210          Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
211         Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
212        Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
213       Pending I/O Flushes | 0 buf pool, 0 log
214        Transaction States | 1xnot started
215 # MyISAM #####################################################
216                 Key Cache | 8.0M
217                  Pct Used | 20%
218                 Unflushed | 0%
219 # Security ###################################################
220                     Users | 5 users, 0 anon, 0 w/o pw, 0 old pw
221             Old Passwords | 0
222 # Encryption #################################################
223 mysql: [Warning] Using a password on the command line interface can be insecure.
224 No keyring plugins found
225 # Binary Logging #############################################
226                   Binlogs | 6
227                Zero-Sized | 0
228                Total Size | 190.0k
229             binlog_format | ROW
230          expire_logs_days | 10
231               sync_binlog | 0
232                 server_id | 1003306
233              binlog_do_db | 
234          binlog_ignore_db | 
235 # Noteworthy Variables #######################################
236      Auto-Inc Incr/Offset | 1/1
237    default_storage_engine | InnoDB
238                flush_time | 0
239              init_connect | 
240                 init_file | 
241                  sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
242          join_buffer_size | 128k
243          sort_buffer_size | 128k
244          read_buffer_size | 2M
245      read_rnd_buffer_size | 16M
246        bulk_insert_buffer | 0.00
247       max_heap_table_size | 96M
248            tmp_table_size | 96M
249        max_allowed_packet | 4M
250              thread_stack | 192k
251                       log | 
252                 log_error | ./error.log
253              log_warnings | 2
254          log_slow_queries | 
255 log_queries_not_using_indexes | OFF
256         log_slave_updates | ON
257 # Configuration File #########################################
258               Config File | /etc/my.cnf
259 
260 [mysqld]
261 datadir                             = /var/lib/mysql
262 socket                              = /var/lib/mysql/mysql.sock
263 symbolic-links                      = 0
264 
265 [mysqld_safe]
266 log-error                           = /var/log/mariadb/mariadb.log
267 pid-file                            = /var/run/mariadb/mariadb.pid
268 
269 [mysql]
270 prompt                              = "\\u@\\h:\\p [\\d]\\>"
271 user                                = zlm
272 password                            = zlmzlm
273 host                                = 192.168.56.100
274 
275 [client]
276 user                                = zlm
277 password                            = zlmzlm
278 socket                              = /tmp/mysql3306.sock
279 host                                = 192.168.56.100
280 # Memory management library ##################################
281 jemalloc is not enabled in mysql config for process with id 4159
282 # The End ####################################################
283 
284 [root@zlm1 09:16:00 ~]
285 #ls -l|grep rpl
286 drwxr-xr-x   2 root root    4096 Aug 12 09:15 zlm.rpl
287 
288 [root@zlm1 09:16:40 ~]
289 #cd zlm.rpl
290 
291 [root@zlm1 09:16:42 ~/zlm.rpl]
292 #ls -l
293 total 88
294 -rw-r--r-- 1 root root   880 Aug 12 09:15 collect.err
295 -rw-r--r-- 1 root root  4051 Aug 12 09:15 innodb-status
296 -rw-r--r-- 1 root root   777 Aug 12 09:15 mysql-config-file
297 -rw-r--r-- 1 root root    66 Aug 12 09:15 mysql-databases
298 -rw-r--r-- 1 root root     0 Aug 12 09:15 mysqld-executables
299 -rw-r--r-- 1 root root   118 Aug 12 09:15 mysqld-instances
300 -rw-r--r-- 1 root root   642 Aug 12 09:15 mysqldump
301 -rw-r--r-- 1 root root   130 Aug 12 09:15 mysql-master-logs
302 -rw-r--r-- 1 root root   114 Aug 12 09:15 mysql-master-status
303 -rw-r--r-- 1 root root  2276 Aug 12 09:15 mysql-plugins
304 -rw-r--r-- 1 root root   796 Aug 12 09:15 mysql-processlist
305 -rw-r--r-- 1 root root     0 Aug 12 09:15 mysql-roles
306 -rw-r--r-- 1 root root     0 Aug 12 09:15 mysql-slave
307 -rw-r--r-- 1 root root   182 Aug 12 09:15 mysql-slave-hosts
308 -rw-r--r-- 1 root root  8730 Aug 12 09:15 mysql-status
309 -rw-r--r-- 1 root root  9682 Aug 12 09:15 mysql-status-defer
310 -rw-r--r-- 1 root root     8 Aug 12 09:15 mysql-users
311 -rw-r--r-- 1 root root 15562 Aug 12 09:15 mysql-variables
312 -rw-r--r-- 1 root root     0 Aug 12 09:15 ndb-status
313 
314 //The directory of "zlm.rpl" contains individual collecting information of the connected MySQL server.You can check them for future use.
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;

Example

 

 

 

 

 

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.%';

Usage

 

 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: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.%';

    pt-show-grants is the right tool which
can directly provide an overview of grants to all users on connected
MySQL db server what is rather convenient for us.There’re also several
advartages of using it.At first,you can dump user grants from one server
to another one simply. Secondly,you can organize version control by
placing the user grants with it since it will sort the grant statements
in order what “show grants;” may not do.Thirdly,now that it provide a
normalized format than “show grants;” does,you can distinguish the
different user grants between two servers efficiently.