占座 ...
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
