Percona-Tookit工具包之pt-find

 
Preface
 
    We used to use "find" command in linux or AIX when we need to get a certain file but cannot rember the precise name.We will execute "find /pathtobegin -name xxx" in our OS prompt.Is there any tool can find a specific table of MySQL database which similarly as "find" does?
 
Introduce
 
    pt-find is a very useful tool to find out a sepcific table with a condition you've specified.It even can execute a sql operation when finding it simutaneously.
 
Procedure
 
Usage
1 pt-find [OPTIONS] [DATABASES]

 

Common parameters

 1 Options:
 2 --day-start //Specify the meassure mothed of time when using "--cmin,--mmin,--ctime,--mtime,etc".
 3 --or //Change the combination test behavior as "or" instead of default "and".
 4 
 5 Actions:
 6 --exec //Specify the executing sql statement with each item found.
 7 --exec-plus //Specify the executing sql statement with all items at once.
 8 --print //Print the target database and table name.
 9 --printf //Print with a certain format.
10 
11 Tests:
12 --autoinc //Speicfy a value of auto_increment to test whether has auto_inrcrement column.
13 --avgrowlen //Specify the average 
14 --cmin //Specify the target table created n minutes ago.
15 --ctime //Specify the target table created n days ago.
16 --mmin //Specify the target table modified n minutes ago.
17 --mtime //Specify the target table modified n days ago.
18 --kmim //Specify the target table checked n minutes ago.
19 --ktime //Specify the target table checked n days ago.
20 --rowformat //Specify the row format of tables to match pattern.
21 --rows //Specify the rows the table contains.
22 --tablesize //Specify the size the table is.
23 --empty //Specify the talbe which has no rows.
24 --engines //Specify the engine of tabls.

 

Example
 
Find out all tables in all databases.
  1 [root@zlm2 07:54:01 /data/mysql/mysql3308/data]
  2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm
  3 `mysql`.`columns_priv`
  4 `mysql`.`db`
  5 `mysql`.`engine_cost`
  6 `mysql`.`event`
  7 `mysql`.`func`
  8 `mysql`.`general_log`
  9 `mysql`.`gtid_executed`
 10 `mysql`.`help_category`
 11 `mysql`.`help_keyword`
 12 `mysql`.`help_relation`
 13 `mysql`.`help_topic`
 14 `mysql`.`innodb_index_stats`
 15 `mysql`.`innodb_table_stats`
 16 `mysql`.`ndb_binlog_index`
 17 `mysql`.`plugin`
 18 `mysql`.`proc`
 19 `mysql`.`procs_priv`
 20 `mysql`.`proxies_priv`
 21 `mysql`.`server_cost`
 22 `mysql`.`servers`
 23 `mysql`.`slave_master_info`
 24 `mysql`.`slave_relay_log_info`
 25 `mysql`.`slave_worker_info`
 26 `mysql`.`slow_log`
 27 `mysql`.`tables_priv`
 28 `mysql`.`time_zone`
 29 `mysql`.`time_zone_leap_second`
 30 `mysql`.`time_zone_name`
 31 `mysql`.`time_zone_transition`
 32 `mysql`.`time_zone_transition_type`
 33 `mysql`.`user`
 34 `performance_schema`.`accounts`
 35 `performance_schema`.`cond_instances`
 36 `performance_schema`.`events_stages_current`
 37 `performance_schema`.`events_stages_history`
 38 `performance_schema`.`events_stages_history_long`
 39 `performance_schema`.`events_stages_summary_by_account_by_event_name`
 40 `performance_schema`.`events_stages_summary_by_host_by_event_name`
 41 `performance_schema`.`events_stages_summary_by_thread_by_event_name`
 42 `performance_schema`.`events_stages_summary_by_user_by_event_name`
 43 `performance_schema`.`events_stages_summary_global_by_event_name`
 44 `performance_schema`.`events_statements_current`
 45 `performance_schema`.`events_statements_history`
 46 `performance_schema`.`events_statements_history_long`
 47 `performance_schema`.`events_statements_summary_by_account_by_event_name`
 48 `performance_schema`.`events_statements_summary_by_digest`
 49 `performance_schema`.`events_statements_summary_by_host_by_event_name`
 50 `performance_schema`.`events_statements_summary_by_program`
 51 `performance_schema`.`events_statements_summary_by_thread_by_event_name`
 52 `performance_schema`.`events_statements_summary_by_user_by_event_name`
 53 `performance_schema`.`events_statements_summary_global_by_event_name`
 54 `performance_schema`.`events_transactions_current`
 55 `performance_schema`.`events_transactions_history`
 56 `performance_schema`.`events_transactions_history_long`
 57 `performance_schema`.`events_transactions_summary_by_account_by_event_name`
 58 `performance_schema`.`events_transactions_summary_by_host_by_event_name`
 59 `performance_schema`.`events_transactions_summary_by_thread_by_event_name`
 60 `performance_schema`.`events_transactions_summary_by_user_by_event_name`
 61 `performance_schema`.`events_transactions_summary_global_by_event_name`
 62 `performance_schema`.`events_waits_current`
 63 `performance_schema`.`events_waits_history`
 64 `performance_schema`.`events_waits_history_long`
 65 `performance_schema`.`events_waits_summary_by_account_by_event_name`
 66 `performance_schema`.`events_waits_summary_by_host_by_event_name`
 67 `performance_schema`.`events_waits_summary_by_instance`
 68 `performance_schema`.`events_waits_summary_by_thread_by_event_name`
 69 `performance_schema`.`events_waits_summary_by_user_by_event_name`
 70 `performance_schema`.`events_waits_summary_global_by_event_name`
 71 `performance_schema`.`file_instances`
 72 `performance_schema`.`file_summary_by_event_name`
 73 `performance_schema`.`file_summary_by_instance`
 74 `performance_schema`.`global_status`
 75 `performance_schema`.`global_variables`
 76 `performance_schema`.`host_cache`
 77 `performance_schema`.`hosts`
 78 `performance_schema`.`memory_summary_by_account_by_event_name`
 79 `performance_schema`.`memory_summary_by_host_by_event_name`
 80 `performance_schema`.`memory_summary_by_thread_by_event_name`
 81 `performance_schema`.`memory_summary_by_user_by_event_name`
 82 `performance_schema`.`memory_summary_global_by_event_name`
 83 `performance_schema`.`metadata_locks`
 84 `performance_schema`.`mutex_instances`
 85 `performance_schema`.`objects_summary_global_by_type`
 86 `performance_schema`.`performance_timers`
 87 `performance_schema`.`prepared_statements_instances`
 88 `performance_schema`.`pxc_cluster_view`
 89 `performance_schema`.`replication_applier_configuration`
 90 `performance_schema`.`replication_applier_status`
 91 `performance_schema`.`replication_applier_status_by_coordinator`
 92 `performance_schema`.`replication_applier_status_by_worker`
 93 `performance_schema`.`replication_connection_configuration`
 94 `performance_schema`.`replication_connection_status`
 95 `performance_schema`.`replication_group_member_stats`
 96 `performance_schema`.`replication_group_members`
 97 `performance_schema`.`rwlock_instances`
 98 `performance_schema`.`session_account_connect_attrs`
 99 `performance_schema`.`session_connect_attrs`
100 `performance_schema`.`session_status`
101 `performance_schema`.`session_variables`
102 `performance_schema`.`setup_actors`
103 `performance_schema`.`setup_consumers`
104 `performance_schema`.`setup_instruments`
105 `performance_schema`.`setup_objects`
106 `performance_schema`.`setup_timers`
107 `performance_schema`.`socket_instances`
108 `performance_schema`.`socket_summary_by_event_name`
109 `performance_schema`.`socket_summary_by_instance`
110 `performance_schema`.`status_by_account`
111 `performance_schema`.`status_by_host`
112 `performance_schema`.`status_by_thread`
113 `performance_schema`.`status_by_user`
114 `performance_schema`.`table_handles`
115 `performance_schema`.`table_io_waits_summary_by_index_usage`
116 `performance_schema`.`table_io_waits_summary_by_table`
117 `performance_schema`.`table_lock_waits_summary_by_table`
118 `performance_schema`.`threads`
119 `performance_schema`.`user_variables_by_thread`
120 `performance_schema`.`users`
121 `performance_schema`.`variables_by_thread`
122 `sys`.`host_summary`
123 `sys`.`host_summary_by_file_io`
124 `sys`.`host_summary_by_file_io_type`
125 `sys`.`host_summary_by_stages`
126 `sys`.`host_summary_by_statement_latency`
127 `sys`.`host_summary_by_statement_type`
128 `sys`.`innodb_buffer_stats_by_schema`
129 `sys`.`innodb_buffer_stats_by_table`
130 `sys`.`innodb_lock_waits`
131 `sys`.`io_by_thread_by_latency`
132 `sys`.`io_global_by_file_by_bytes`
133 `sys`.`io_global_by_file_by_latency`
134 `sys`.`io_global_by_wait_by_bytes`
135 `sys`.`io_global_by_wait_by_latency`
136 `sys`.`latest_file_io`
137 `sys`.`memory_by_host_by_current_bytes`
138 `sys`.`memory_by_thread_by_current_bytes`
139 `sys`.`memory_by_user_by_current_bytes`
140 `sys`.`memory_global_by_current_bytes`
141 `sys`.`memory_global_total`
142 `sys`.`metrics`
143 `sys`.`processlist`
144 `sys`.`ps_check_lost_instrumentation`
145 `sys`.`schema_auto_increment_columns`
146 `sys`.`schema_index_statistics`
147 `sys`.`schema_object_overview`
148 `sys`.`schema_redundant_indexes`
149 `sys`.`schema_table_lock_waits`
150 `sys`.`schema_table_statistics`
151 `sys`.`schema_table_statistics_with_buffer`
152 `sys`.`schema_tables_with_full_table_scans`
153 `sys`.`schema_unused_indexes`
154 `sys`.`session`
155 `sys`.`session_ssl_status`
156 `sys`.`statement_analysis`
157 `sys`.`statements_with_errors_or_warnings`
158 `sys`.`statements_with_full_table_scans`
159 `sys`.`statements_with_runtimes_in_95th_percentile`
160 `sys`.`statements_with_sorting`
161 `sys`.`statements_with_temp_tables`
162 `sys`.`sys_config`
163 `sys`.`user_summary`
164 `sys`.`user_summary_by_file_io`
165 `sys`.`user_summary_by_file_io_type`
166 `sys`.`user_summary_by_stages`
167 `sys`.`user_summary_by_statement_latency`
168 `sys`.`user_summary_by_statement_type`
169 `sys`.`version`
170 `sys`.`wait_classes_global_by_avg_latency`
171 `sys`.`wait_classes_global_by_latency`
172 `sys`.`waits_by_host_by_latency`
173 `sys`.`waits_by_user_by_latency`
174 `sys`.`waits_global_by_latency`
175 `sys`.`x$host_summary`
176 `sys`.`x$host_summary_by_file_io`
177 `sys`.`x$host_summary_by_file_io_type`
178 `sys`.`x$host_summary_by_stages`
179 `sys`.`x$host_summary_by_statement_latency`
180 `sys`.`x$host_summary_by_statement_type`
181 `sys`.`x$innodb_buffer_stats_by_schema`
182 `sys`.`x$innodb_buffer_stats_by_table`
183 `sys`.`x$innodb_lock_waits`
184 `sys`.`x$io_by_thread_by_latency`
185 `sys`.`x$io_global_by_file_by_bytes`
186 `sys`.`x$io_global_by_file_by_latency`
187 `sys`.`x$io_global_by_wait_by_bytes`
188 `sys`.`x$io_global_by_wait_by_latency`
189 `sys`.`x$latest_file_io`
190 `sys`.`x$memory_by_host_by_current_bytes`
191 `sys`.`x$memory_by_thread_by_current_bytes`
192 `sys`.`x$memory_by_user_by_current_bytes`
193 `sys`.`x$memory_global_by_current_bytes`
194 `sys`.`x$memory_global_total`
195 `sys`.`x$processlist`
196 `sys`.`x$ps_digest_95th_percentile_by_avg_us`
197 `sys`.`x$ps_digest_avg_latency_distribution`
198 `sys`.`x$ps_schema_table_statistics_io`
199 `sys`.`x$schema_flattened_keys`
200 `sys`.`x$schema_index_statistics`
201 `sys`.`x$schema_table_lock_waits`
202 `sys`.`x$schema_table_statistics`
203 `sys`.`x$schema_table_statistics_with_buffer`
204 `sys`.`x$schema_tables_with_full_table_scans`
205 `sys`.`x$session`
206 `sys`.`x$statement_analysis`
207 `sys`.`x$statements_with_errors_or_warnings`
208 `sys`.`x$statements_with_full_table_scans`
209 `sys`.`x$statements_with_runtimes_in_95th_percentile`
210 `sys`.`x$statements_with_sorting`
211 `sys`.`x$statements_with_temp_tables`
212 `sys`.`x$user_summary`
213 `sys`.`x$user_summary_by_file_io`
214 `sys`.`x$user_summary_by_file_io_type`
215 `sys`.`x$user_summary_by_stages`
216 `sys`.`x$user_summary_by_statement_latency`
217 `sys`.`x$user_summary_by_statement_type`
218 `sys`.`x$wait_classes_global_by_avg_latency`
219 `sys`.`x$wait_classes_global_by_latency`
220 `sys`.`x$waits_by_host_by_latency`
221 `sys`.`x$waits_by_user_by_latency`
222 `sys`.`x$waits_global_by_latency`
223 `zlm`.`t1`
224 `zlm`.`t2`
225 
226 //It shows all the tables in all databases one line each table.

 

Find out all the MyISAM tables.

 1 [root@zlm2 09:28:33 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --engine=MyISAM
 3 `mysql`.`columns_priv`
 4 `mysql`.`db`
 5 `mysql`.`event`
 6 `mysql`.`func`
 7 `mysql`.`ndb_binlog_index`
 8 `mysql`.`proc`
 9 `mysql`.`procs_priv`
10 `mysql`.`proxies_priv`
11 `mysql`.`tables_priv`
12 `mysql`.`user`

 

Find out all the tables in database "zlm" which does not have auto_increment column.

1 [root@zlm2 09:32:55 /data/mysql/mysql3308/data]
2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --dblike=zlm --autoinc=0
3 `zlm`.`t1`
4 `zlm`.`t2`

 

Find out all the tables which is empty.

 1 [root@zlm2 09:33:14 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --empty
 3 `mysql`.`columns_priv`
 4 `mysql`.`event`
 5 `mysql`.`func`
 6 `mysql`.`ndb_binlog_index`
 7 `mysql`.`plugin`
 8 `mysql`.`procs_priv`
 9 `mysql`.`servers`
10 `mysql`.`slave_master_info`
11 `mysql`.`slave_relay_log_info`
12 `mysql`.`slave_worker_info`
13 `mysql`.`time_zone`
14 `mysql`.`time_zone_leap_second`
15 `mysql`.`time_zone_name`
16 `mysql`.`time_zone_transition`
17 `mysql`.`time_zone_transition_type`
18 `performance_schema`.`metadata_locks`
19 `performance_schema`.`prepared_statements_instances`
20 `performance_schema`.`replication_group_member_stats`
21 `performance_schema`.`replication_group_members`

 

Find out all the tables sorted descendingly by size.

  1 [root@zlm2 09:38:22 /data/mysql/mysql3308/data]
  2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --printf "%T\t%D.%N\n" | sort -rn
  3 1671168    `mysql`.`help_topic`
  4 304624    `mysql`.`proc`
  5 180224    `mysql`.`help_keyword`
  6 81920    `mysql`.`help_relation`
  7 32768    `mysql`.`help_category`
  8 16384    `zlm`.`t2`
  9 16384    `zlm`.`t1`
 10 16384    `sys`.`sys_config`
 11 16384    `mysql`.`time_zone_transition_type`
 12 16384    `mysql`.`time_zone_transition`
 13 16384    `mysql`.`time_zone_name`
 14 16384    `mysql`.`time_zone_leap_second`
 15 16384    `mysql`.`time_zone`
 16 16384    `mysql`.`slave_worker_info`
 17 16384    `mysql`.`slave_relay_log_info`
 18 16384    `mysql`.`slave_master_info`
 19 16384    `mysql`.`servers`
 20 16384    `mysql`.`server_cost`
 21 16384    `mysql`.`plugin`
 22 16384    `mysql`.`innodb_table_stats`
 23 16384    `mysql`.`innodb_index_stats`
 24 16384    `mysql`.`gtid_executed`
 25 16384    `mysql`.`engine_cost`
 26 11110    `mysql`.`tables_priv`
 27 10053    `mysql`.`proxies_priv`
 28 6096    `mysql`.`db`
 29 5012    `mysql`.`user`
 30 4096    `mysql`.`procs_priv`
 31 4096    `mysql`.`columns_priv`
 32 2048    `mysql`.`event`
 33 1024    `mysql`.`ndb_binlog_index`
 34 1024    `mysql`.`func`
 35 0    `sys`.`x$waits_global_by_latency`
 36 0    `sys`.`x$waits_by_user_by_latency`
 37 0    `sys`.`x$waits_by_host_by_latency`
 38 0    `sys`.`x$wait_classes_global_by_latency`
 39 0    `sys`.`x$wait_classes_global_by_avg_latency`
 40 0    `sys`.`x$user_summary_by_statement_type`
 41 0    `sys`.`x$user_summary_by_statement_latency`
 42 0    `sys`.`x$user_summary_by_stages`
 43 0    `sys`.`x$user_summary_by_file_io_type`
 44 0    `sys`.`x$user_summary_by_file_io`
 45 0    `sys`.`x$user_summary`
 46 0    `sys`.`x$statements_with_temp_tables`
 47 0    `sys`.`x$statements_with_sorting`
 48 0    `sys`.`x$statements_with_runtimes_in_95th_percentile`
 49 0    `sys`.`x$statements_with_full_table_scans`
 50 0    `sys`.`x$statements_with_errors_or_warnings`
 51 0    `sys`.`x$statement_analysis`
 52 0    `sys`.`x$session`
 53 0    `sys`.`x$schema_tables_with_full_table_scans`
 54 0    `sys`.`x$schema_table_statistics_with_buffer`
 55 0    `sys`.`x$schema_table_statistics`
 56 0    `sys`.`x$schema_table_lock_waits`
 57 0    `sys`.`x$schema_index_statistics`
 58 0    `sys`.`x$schema_flattened_keys`
 59 0    `sys`.`x$ps_schema_table_statistics_io`
 60 0    `sys`.`x$ps_digest_avg_latency_distribution`
 61 0    `sys`.`x$ps_digest_95th_percentile_by_avg_us`
 62 0    `sys`.`x$processlist`
 63 0    `sys`.`x$memory_global_total`
 64 0    `sys`.`x$memory_global_by_current_bytes`
 65 0    `sys`.`x$memory_by_user_by_current_bytes`
 66 0    `sys`.`x$memory_by_thread_by_current_bytes`
 67 0    `sys`.`x$memory_by_host_by_current_bytes`
 68 0    `sys`.`x$latest_file_io`
 69 0    `sys`.`x$io_global_by_wait_by_latency`
 70 0    `sys`.`x$io_global_by_wait_by_bytes`
 71 0    `sys`.`x$io_global_by_file_by_latency`
 72 0    `sys`.`x$io_global_by_file_by_bytes`
 73 0    `sys`.`x$io_by_thread_by_latency`
 74 0    `sys`.`x$innodb_lock_waits`
 75 0    `sys`.`x$innodb_buffer_stats_by_table`
 76 0    `sys`.`x$innodb_buffer_stats_by_schema`
 77 0    `sys`.`x$host_summary_by_statement_type`
 78 0    `sys`.`x$host_summary_by_statement_latency`
 79 0    `sys`.`x$host_summary_by_stages`
 80 0    `sys`.`x$host_summary_by_file_io_type`
 81 0    `sys`.`x$host_summary_by_file_io`
 82 0    `sys`.`x$host_summary`
 83 0    `sys`.`waits_global_by_latency`
 84 0    `sys`.`waits_by_user_by_latency`
 85 0    `sys`.`waits_by_host_by_latency`
 86 0    `sys`.`wait_classes_global_by_latency`
 87 0    `sys`.`wait_classes_global_by_avg_latency`
 88 0    `sys`.`version`
 89 0    `sys`.`user_summary_by_statement_type`
 90 0    `sys`.`user_summary_by_statement_latency`
 91 0    `sys`.`user_summary_by_stages`
 92 0    `sys`.`user_summary_by_file_io_type`
 93 0    `sys`.`user_summary_by_file_io`
 94 0    `sys`.`user_summary`
 95 0    `sys`.`statements_with_temp_tables`
 96 0    `sys`.`statements_with_sorting`
 97 0    `sys`.`statements_with_runtimes_in_95th_percentile`
 98 0    `sys`.`statements_with_full_table_scans`
 99 0    `sys`.`statements_with_errors_or_warnings`
100 0    `sys`.`statement_analysis`
101 0    `sys`.`session_ssl_status`
102 0    `sys`.`session`
103 0    `sys`.`schema_unused_indexes`
104 0    `sys`.`schema_tables_with_full_table_scans`
105 0    `sys`.`schema_table_statistics_with_buffer`
106 0    `sys`.`schema_table_statistics`
107 0    `sys`.`schema_table_lock_waits`
108 0    `sys`.`schema_redundant_indexes`
109 0    `sys`.`schema_object_overview`
110 0    `sys`.`schema_index_statistics`
111 0    `sys`.`schema_auto_increment_columns`
112 0    `sys`.`ps_check_lost_instrumentation`
113 0    `sys`.`processlist`
114 0    `sys`.`metrics`
115 0    `sys`.`memory_global_total`
116 0    `sys`.`memory_global_by_current_bytes`
117 0    `sys`.`memory_by_user_by_current_bytes`
118 0    `sys`.`memory_by_thread_by_current_bytes`
119 0    `sys`.`memory_by_host_by_current_bytes`
120 0    `sys`.`latest_file_io`
121 0    `sys`.`io_global_by_wait_by_latency`
122 0    `sys`.`io_global_by_wait_by_bytes`
123 0    `sys`.`io_global_by_file_by_latency`
124 0    `sys`.`io_global_by_file_by_bytes`
125 0    `sys`.`io_by_thread_by_latency`
126 0    `sys`.`innodb_lock_waits`
127 0    `sys`.`innodb_buffer_stats_by_table`
128 0    `sys`.`innodb_buffer_stats_by_schema`
129 0    `sys`.`host_summary_by_statement_type`
130 0    `sys`.`host_summary_by_statement_latency`
131 0    `sys`.`host_summary_by_stages`
132 0    `sys`.`host_summary_by_file_io_type`
133 0    `sys`.`host_summary_by_file_io`
134 0    `sys`.`host_summary`
135 0    `performance_schema`.`variables_by_thread`
136 0    `performance_schema`.`user_variables_by_thread`
137 0    `performance_schema`.`users`
138 0    `performance_schema`.`threads`
139 0    `performance_schema`.`table_lock_waits_summary_by_table`
140 0    `performance_schema`.`table_io_waits_summary_by_table`
141 0    `performance_schema`.`table_io_waits_summary_by_index_usage`
142 0    `performance_schema`.`table_handles`
143 0    `performance_schema`.`status_by_user`
144 0    `performance_schema`.`status_by_thread`
145 0    `performance_schema`.`status_by_host`
146 0    `performance_schema`.`status_by_account`
147 0    `performance_schema`.`socket_summary_by_instance`
148 0    `performance_schema`.`socket_summary_by_event_name`
149 0    `performance_schema`.`socket_instances`
150 0    `performance_schema`.`setup_timers`
151 0    `performance_schema`.`setup_objects`
152 0    `performance_schema`.`setup_instruments`
153 0    `performance_schema`.`setup_consumers`
154 0    `performance_schema`.`setup_actors`
155 0    `performance_schema`.`session_variables`
156 0    `performance_schema`.`session_status`
157 0    `performance_schema`.`session_connect_attrs`
158 0    `performance_schema`.`session_account_connect_attrs`
159 0    `performance_schema`.`rwlock_instances`
160 0    `performance_schema`.`replication_group_member_stats`
161 0    `performance_schema`.`replication_group_members`
162 0    `performance_schema`.`replication_connection_status`
163 0    `performance_schema`.`replication_connection_configuration`
164 0    `performance_schema`.`replication_applier_status_by_worker`
165 0    `performance_schema`.`replication_applier_status_by_coordinator`
166 0    `performance_schema`.`replication_applier_status`
167 0    `performance_schema`.`replication_applier_configuration`
168 0    `performance_schema`.`pxc_cluster_view`
169 0    `performance_schema`.`prepared_statements_instances`
170 0    `performance_schema`.`performance_timers`
171 0    `performance_schema`.`objects_summary_global_by_type`
172 0    `performance_schema`.`mutex_instances`
173 0    `performance_schema`.`metadata_locks`
174 0    `performance_schema`.`memory_summary_global_by_event_name`
175 0    `performance_schema`.`memory_summary_by_user_by_event_name`
176 0    `performance_schema`.`memory_summary_by_thread_by_event_name`
177 0    `performance_schema`.`memory_summary_by_host_by_event_name`
178 0    `performance_schema`.`memory_summary_by_account_by_event_name`
179 0    `performance_schema`.`hosts`
180 0    `performance_schema`.`host_cache`
181 0    `performance_schema`.`global_variables`
182 0    `performance_schema`.`global_status`
183 0    `performance_schema`.`file_summary_by_instance`
184 0    `performance_schema`.`file_summary_by_event_name`
185 0    `performance_schema`.`file_instances`
186 0    `performance_schema`.`events_waits_summary_global_by_event_name`
187 0    `performance_schema`.`events_waits_summary_by_user_by_event_name`
188 0    `performance_schema`.`events_waits_summary_by_thread_by_event_name`
189 0    `performance_schema`.`events_waits_summary_by_instance`
190 0    `performance_schema`.`events_waits_summary_by_host_by_event_name`
191 0    `performance_schema`.`events_waits_summary_by_account_by_event_name`
192 0    `performance_schema`.`events_waits_history_long`
193 0    `performance_schema`.`events_waits_history`
194 0    `performance_schema`.`events_waits_current`
195 0    `performance_schema`.`events_transactions_summary_global_by_event_name`
196 0    `performance_schema`.`events_transactions_summary_by_user_by_event_name`
197 0    `performance_schema`.`events_transactions_summary_by_thread_by_event_name`
198 0    `performance_schema`.`events_transactions_summary_by_host_by_event_name`
199 0    `performance_schema`.`events_transactions_summary_by_account_by_event_name`
200 0    `performance_schema`.`events_transactions_history_long`
201 0    `performance_schema`.`events_transactions_history`
202 0    `performance_schema`.`events_transactions_current`
203 0    `performance_schema`.`events_statements_summary_global_by_event_name`
204 0    `performance_schema`.`events_statements_summary_by_user_by_event_name`
205 0    `performance_schema`.`events_statements_summary_by_thread_by_event_name`
206 0    `performance_schema`.`events_statements_summary_by_program`
207 0    `performance_schema`.`events_statements_summary_by_host_by_event_name`
208 0    `performance_schema`.`events_statements_summary_by_digest`
209 0    `performance_schema`.`events_statements_summary_by_account_by_event_name`
210 0    `performance_schema`.`events_statements_history_long`
211 0    `performance_schema`.`events_statements_history`
212 0    `performance_schema`.`events_statements_current`
213 0    `performance_schema`.`events_stages_summary_global_by_event_name`
214 0    `performance_schema`.`events_stages_summary_by_user_by_event_name`
215 0    `performance_schema`.`events_stages_summary_by_thread_by_event_name`
216 0    `performance_schema`.`events_stages_summary_by_host_by_event_name`
217 0    `performance_schema`.`events_stages_summary_by_account_by_event_name`
218 0    `performance_schema`.`events_stages_history_long`
219 0    `performance_schema`.`events_stages_history`
220 0    `performance_schema`.`events_stages_current`
221 0    `performance_schema`.`cond_instances`
222 0    `performance_schema`.`accounts`
223 0    `mysql`.`slow_log`
224 0    `mysql`.`general_log`

 

Find out all the tables over 16K.

 1 [root@zlm2 09:44:10 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --tablesize +16
 3 `mysql`.`columns_priv`
 4 `mysql`.`db`
 5 `mysql`.`engine_cost`
 6 `mysql`.`event`
 7 `mysql`.`func`
 8 `mysql`.`gtid_executed`
 9 `mysql`.`help_category`
10 `mysql`.`help_keyword`
11 `mysql`.`help_relation`
12 `mysql`.`help_topic`
13 `mysql`.`innodb_index_stats`
14 `mysql`.`innodb_table_stats`
15 `mysql`.`ndb_binlog_index`
16 `mysql`.`plugin`
17 `mysql`.`proc`
18 `mysql`.`procs_priv`
19 `mysql`.`proxies_priv`
20 `mysql`.`server_cost`
21 `mysql`.`servers`
22 `mysql`.`slave_master_info`
23 `mysql`.`slave_relay_log_info`
24 `mysql`.`slave_worker_info`
25 `mysql`.`tables_priv`
26 `mysql`.`time_zone`
27 `mysql`.`time_zone_leap_second`
28 `mysql`.`time_zone_name`
29 `mysql`.`time_zone_transition`
30 `mysql`.`time_zone_transition_type`
31 `mysql`.`user`
32 `sys`.`sys_config`
33 `zlm`.`t1`
34 `zlm`.`t2`

 

Find out table in "zlm" database which name is like "t3" then drop it.

 1 (zlm@192.168.1.101 3308)[zlm]>create table t3 like t2;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 (zlm@192.168.1.101 3308)[zlm]>show create table t3\G
 5 *************************** 1. row ***************************
 6        Table: t3
 7 Create Table: CREATE TABLE `t3` (
 8   `id` int(11) NOT NULL,
 9   `name` char(10) DEFAULT NULL,
10   PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
12 1 row in set (0.00 sec)
13 
14 (zlm@192.168.1.101 3308)[zlm]>show tables;
15 +---------------+
16 | Tables_in_zlm |
17 +---------------+
18 | t1            |
19 | t2            |
20 | t3            |
21 +---------------+
22 3 rows in set (0.00 sec)
23 
24 [root@zlm2 10:11:26 /data/mysql/mysql3308/data]
25 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --print zlm --tbllike=t3 --exec-plus "drop table %s"
26 `zlm`.`t3`
27 
28 (zlm@192.168.1.101 3308)[zlm]>show tables;
29 +---------------+
30 | Tables_in_zlm |
31 +---------------+
32 | t1            |
33 | t2            |
34 +---------------+
35 2 rows in set (0.00 sec)
36 
37 //The table has been droped directly by the last command in pt-find.

 

Summary
  • pt-find is rather useful when retrieving target table with flexible conditions you defined.
  • pt-find has a lot of options.We should be more careful when using ddl statement by "--exec" or "--exec-plus".
  • We can use it to collect the important information about a bunch of tables easily which can increse the working efficiency.

 

posted @ 2018-08-12 23:08  aaron8219  阅读(244)  评论(0)  编辑  收藏  举报