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

时间:2019-05-11 09:47来源:计算机教程
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 ######################
  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 #

**Specify the only user "root".**

 

Usage

 

 

Introduce

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

Summary

 

 

1 pt-show-grants [OPTIONS] [DSN]

 

 

 

 

Example

**Specify the only user "root" with drop statement.**

Usage

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

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

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

**Specify the only user "repl" with no timestamp.**

 

Summary

1 pt-mysql-summary [OPTIONS]

 

 

 

Common Parameters

 

    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.

 

Preface

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

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

 

 

 

**Specify the only user "repl" with no header.**

编辑:计算机教程 本文来源:Percona-Tookit工具包之pt-mysql-summary

关键词:

  • 上一篇:没有了
  • 下一篇:没有了